Display a # of rows per page based on output type

Most of these articles are about things that frustrated me for enough of my day to swear a bunch and then say 'hey that's annoying enough to make an article about'. Today's tip has the added bonus of being part of a report I didn't even want to build. My user wants a summary of dollars over a variable period of time with the ability to drill into the total and see the distinct set of users that gave those dollars. This is fine, but my dataset has some spots in it that it'd be pretty inefficient to drill through on. Though the standard deviation of donors/unit is pretty high, I'd rather work on a way to assure a horrid query is never passed to the database. (hard to read, large query, resources, developers headaches, etc. But hey data analysis for potential hazard queries and allowing dump reports are post to save for another time)

That being said, the user also wanted to potentially run this thing out to HTML or Excel. Well, if the data set is potentially large I want to show a good amount of it on the screen in html but not enough that rendering will take a significant amount of time. For this you can use the Rows Per Page property.

This is fine for HTML only. Based on this setting when you try to run to xls 2007 this rows per page option will create a separate workbook tab for each 150 rows. If you run to xls 2003 single sheet cognos will contain the output to one workbook but the headers and footers will be repeated each 150 rows. BUT, in xls 2007 if the default rows per page (20 unless your admin changes it) setting will cause output to be rendered on a single tab.

What do we do? I want 150 rows in html and 1 workbook tab in XLS. Well, it's Report Expressions to the rescue! There is a cognos report function ReportOutput() that will return a string populated with the output type at render time. The function definition (as of the 8.4.1 documentation) is defined as:

ReportOutput ()

Returns the name of the output format. Possible return values are: CSV, HTML, layoutDataXML, MHT, PDF, rawXML, singleXLS, spreadsheetML, XLS, XML, XLWA.

Which is somewhat obtuse, but here's a nice chart of the output formats that I'm interested in. One thing to note is that you need the right string for the version of xls you're expecting to use:

CSV	Comma Seperated Value
HTML	HTML output
layoutDataXML	XML
MHT	I assume this is a MHTML output type for 'saved html'
PDF	PDF Output
rawXML	XML
singleXLS	Excel 2000 single sheet
spreadsheetML	Excel 2007 output
XLS	appears not to be generated in xls outputs as of 8.4
XML	XML
XLWA	Excel 2003

So, ideally I'd like to be able to update the properties setting on the list based on the report format. I couldn't figure out if that was possible to overload via javascript, so I took the easy way out. I created 2 lists, one that renders when xls is run and one for html:

1. Create a copy of your list A and A1.
2. Set the rows per page property to 150 on A.
3. Leave the rows per page property blank on A2
4. Create a new string variable with 2 Values 'XLS' and 'Other' the expression can be defined as:
   

   if (ReportOutput () = 'spreadsheetML') then ('XLS') else ('Other')

5. Set the Render Variable property to this new variable on list A and check the Other value.
6. Set the Render Variable property to this new variable on list A1 and check the XLS value.