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:
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:ReportOutput ()Returns the name of the output format. Possible return values are: CSV, HTML, layoutDataXML, MHT, PDF, rawXML, singleXLS, spreadsheetML, XLS, XML, XLWA.
|CSV||Comma Seperated Value|
|MHT||I assume this is a MHTML output type for 'saved html'|
|singleXLS||Excel 2000 single sheet|
|spreadsheetML||Excel 2007 output|
|XLS||appears not to be generated in xls outputs as of 8.4|
- Create a copy of your list A and A1.
- Set the rows per page property to 150 on A.
- Leave the rows per page property blank on A2
- Create a new string variable with 2 Values 'XLS' and 'Other' the expression can be defined as:
if (ReportOutput () = 'spreadsheetML') then ('XLS') else ('Other')
- Set the Render Variable property to this new variable on list A and check the Other value.
- Set the Render Variable property to this new variable on list A1 and check the XLS value.