RESOLVED - How to export to Excel selected Value Prompt value?

[abcuser]

Hi,
in Report Studio having crosstab with Value Prompt objects of year-level data. When report is executed end-user selects a year e.g. 2015 and crosstab is filtered by this year. Now end-user exports data to Excel (selects View in Excel | View in Excel 2007 Format), but Value Prompt value is not exported. Is there a way to export end-user selected value to Excel?

My system:
- Cognos BI 10.2.1 fixpack 6 as reporting system
- Cognos TM1 10.1.1 fixpack 2 as data source (cube)

Thanks

[TheFrenchGuy]

Hello,

you want to extract values selected by user ?
You can put a singleton in your report, in which you integrate a text item. You change "Source type" in "Report Expression" and in "Report Expression" (line below) you write "ParamDisplayValue(' " - your parameter - "  ') "

For example : ParamDisplayValue('Selected Region') retrieves all regions I have selected in my prompt.

[abcuser]

Hi,
thank you for your help. I have done as you have instructed, but now this parameter value "2015" is displayed in Cognos Viewer when Value Prompt selection is performed. Is there a way this value is only displayed in Excel and not in Cognos Viewer?

Is there some dummy solution like putting text object behind the Value Prompt, so when report executed in Value Prompt gets displayed over hidden text object, but when report is exported to Excel then Text Item is displayed, because Value Prompt object is ignored by export.
Thanks

[TheFrenchGuy]

Hi,

the way to do this is create a variable in order to hide parameter value when the output is in HTML and show parameter value when report output is in Excel.

Then you can :
1. Create a New Boolean Variable
2. With this definition :

Code Select Expand

ReportOutput() in ('singleXLS', 'XLWA','spreadsheetML')
3.Go to the page layout, select your singleton and render it with your brand new boolean variable

Keep me informed pls =)

[abcuser]

Excellent this works just like you have written.

The only little thing, I have simplified this:

You can put a singleton in your report, in which you integrate a text item.

Singleton object isn't required, enough is just creating Text Item object without Singleton.

Also function:

Code Select Expand

ReportOutput() in ('singleXLS', 'XLWA','spreadsheetML')
can be simplified to:

Code Select Expand

ReportOutput() in ('spreadsheetML')

Thanks a million time. Man you rock.

[abcuser]

To recap whole thread I did the following:
1. Created new Crosstab report. Added one measure to Columns and one member to Rows.
2. Added Value Prompt object and in wizard named parameter "Parameter_Year", checked Make this filter optional and selected Year level.
3. From Value Prompt's Properties window I have set General | Auto-Submit = Yes.
4. Added Text Item to report and left text field empty.
5. Clicked on Text Item on report and from Properties window change Text Source | Source Text from Text to Report Expression.
6. Clicked "..." next to Text Source | Report Expression.
7. Clicked on Parameters tab bellow.
8. Moved Parameter_Year from Available Components field to Expression Definition, so Expression Definition has the following formula:
   ParamDisplayValue('Parameter_Year')
9. Clicked on Conditional Explorer and clicked on Variables folder link.
10. From Toolbox field I have moved Boolean Variable to Variables field.
11. In Expression Definition paste in code:

Code Select Expand

ReportOutput() in ('spreadsheetML')
12. (step not required) Clicked on Boolean1 inside Variables field and in Properties window changed name in Miscellaneous | Name from Boolean1 to Variable_DisplayInSpreadsheet.
13. Click on Page Explorer | Report Pages | Page1 link to get back to report.
14. Click on Text Item on report (on report it is displayed "<%ParamDisplay...%>", so you know where to click).
15. From Properties on Conditional | Render Variable click on "...".
16. Render Variable dialog opens. From Variables drop-down menu select Variable_DisplayInSpreadsheet and leave Render for checkbox checked on Yes.
17. Run report.
18. Report is displayed in Cognos Viewer. Select e.g. year 2015 from Value Prompt object.
19. On top-right site of report click on View in HTML icon | View in Excel Options | View in Excel 2007 Format.
20. Report is opened in Excel and end-user selected value from Value Prompt e.g. 2015 is displayed in Excel.


ADDITIONAL INFO: DO YOU NEED THIS SOLUTION FOR OTHER OUTPUT FORMAT LIKE "PDF FILE"?
If you ckeck the ReportOutput help function you will get:
ReportOutput ()
Returns the name of the output format, such as CSV, HTML, layoutDataXML, MHT, PDF, rawXML, singleXLS, spreadsheetML, XLS, XML, or XLWA.

If spreedsheet and PDF file is required as output format then:

Code Select Expand

ReportOutput() in ('spreadsheetML', 'PDF')


ADDITIONAL INFO: DO YOU NEED THIS SOLUTION FOR MULTIPLE VALUE PROMPT OBJECTS?
Just copy/paste above created Text Item for every Value Prompt you have in report and in Text Item Properties window change Text Source | Report Expression from:
ParamDisplayValue('Parameter_Year') to ParamDisplayValue('Parameter_Month')

In this case all of Text Items will use the same boolean variable, because there is no need of having multiple boolean variables.

[TheFrenchGuy]

Nice  guideline !

You're welcome =)