Report with On-Page Prompts - Need Prompt Values to Render to Excel

[pammassey]

I have created a report with on-page prompts on the list report. As long as the report remains in HTML, I can see the prompt values. But once the report is downloaded to Excel, I no longer see the prompt values. Is there a way to render the on-page prompt values to Excel?

[Lynn]

I have created a report with on-page prompts on the list report. As long as the report remains in HTML, I can see the prompt values. But once the report is downloaded to Excel, I no longer see the prompt values. Is there a way to render the on-page prompt values to Excel?

I am pretty sure that this is the same situation as in 10.x versions. The prompts on an HTML page allow you to re-run the report with different prompt selections. When rendering to Excel (or PDF for that matter) you will only get the data for whatever prompt values are specified at the time of export. There isn't any further interaction possible with the output once it leaves the Cognos realm so there is no point in rendering the prompt controls.

Of course the exception would be to author an Active Report that would allow offline interaction with data, but that is only suitable for highly summarized content and not for lengthy lists or large crosstabs.

[pammassey]

I'm not really looking for further report interaction once the report is rendered to Excel. I just want to see the prompt values as matter of record in Excel.

[Lynn]

I'm not really looking for further report interaction once the report is rendered to Excel. I just want to see the prompt values as matter of record in Excel.

In that case I'd suggest using a layout calculation to display that information. You can set it to only render for Excel output so it doesn't appear for HTML.

I'm assuming this is all still the same as it was in 10.x versions so hopefully someone will correct me if that is not the case.

Code Select Expand


ParamDisplayValue('YourParameterName')


[pammassey]

I'm trying to avoid that solution because too much real estate would be taken with those values as well as the prompts which do show up on the rendered excel spreadsheet (though empty).

[Lynn]

I'm trying to avoid that solution because too much real estate would be taken with those values as well as the prompts which do show up on the rendered excel spreadsheet (though empty).

I would suppress rendering of the empty prompts and instead render the selected values. You'd only have the prompt controls for HTML and only have the layout calculation for Excel. Maybe others will have better ideas if this doesn't suit.

[pammassey]

That would work.  How would I do that?

[Lynn]

That would work.  How would I do that?

I'm still on version 10.2.2 so I don't know how different the technique is in the brave new world of Cognos Analytics. Hopefully this is close enough or helpful for searching the new documentation to find the equivalent method.

Assuming there is some similarity, a report function can be used as the basis for a variable to determine which output method has been requested. You'd use that variable as the render variable for the appropriate object.

ReportOutput ()
Returns the name of the output format, such as CSV, HTML, HTMLFragment, layoutDataXML, MHT, PDF, rawXML, spreadsheetML (Excel 2007 format), XHTML, xlsxData (Excel 2007 Data format), XLWA (Excel 2002 format), XML, singleXLS (deprecated), XLS (deprecated).

For example, a Boolean variable defined as ReportOutput () = 'HTML' would be set as the render variable for your prompt controls so they would only render for that output format. Another Boolean defined as ReportOutput () = 'spreadsheetML' would do the same for your param display objects.

[pammassey]

I'm not using Cognos Analytics either.  I'm in 10.2.

[pammassey]

That totally does what I need.  Thanks!