Render variables displayed in MS XL spreadsheet

[rjhead]

I have a drill down report that has three parameters passed to it.  When the report runs, it can be exported to a .pdf file with those three parameters displayed as text on the top of the first page.  They are not column headers, just parameters that are passed to the drill down report.  I would like to display them in a Microsoft XL spreadsheet as merely text items in a row of their own, but cannot make that happen.  Has anyone ever done this with or without a render variable?  Any advice is appreciated.

Thank you,
R.J. Head

[navissar]

Have you tried placing each in a table cell?

[rjhead]

Yes.  Just now, without help.  Currently, these variables are 'render' variables set to appear in either .pdf or MS XL.  They appear in .pdf, but not XL. Thank you for your input.

R.J. Head

[Lynn]

What is the expression used for the render variable that controls the display?

[rjhead]

The render variables, three in number, are all defined as "vreportoutput."  I tried changing the name of one of them to "vreportoutput1," to no avail.  They display fine in .pdf, but not MS XL.  The ReportExpression in the properties window has the text:

case when ParamDisplayValue('~p_term~') is not null then ParamDisplayValue('~p_term~')
else 'All Terms'
end

I truly appreciate your interest, thank you.
R.J. Head

[Lynn]

I suspect the issue is with the render variable expression which you haven't yet provided. To find it, go to the Condition Explorer in Report Studio. The Condition Explorer is found in the vertical bar running alongside the source and properties panes. It is the third one, beneath the Page and Query Explorers.

Hover over it and then select the Variables folder to enter the explorer. Once there you should see your vreportoutput variable. Double click it or use the properties pane to find out what the expression is.

It is most likely using the ReportOutput() function:
Returns the name of the output format, such as CSV, HTML, layoutDataXML, MHT, PDF, rawXML, singleXLS, spreadsheetML, XLS, XML, or XLWA.

[rjhead]

Following your instructions, I see PDF, XLS, and XLSX in the pane for "Values" to the right.  These are valid outputs for the variables on the left.  The condition explorer has vreportoutput in the pane to the left.  That pane is labeled "Variables."  I tried placing "XLS" inside the parenthesis of the Report Expression "ReportOutput()" and the entire report fails.

Again, thank you for your interest.  This is just a really odd situation.

Thank you,
RJH

[Lynn]

Sounds like it is a string variable if you see Values to the right. A boolean variable would just have a Yes or No. You can see the Type of variable in the properties pane.

The report expression on the page is probably set to render for PDF but not the others. You could just remove the render variable property of your report expression and it would render regardless of output format.

[rjhead]

When I double left click the ellipsis button on the Render Variable item in Properties, I get a small window that lists the variable as "vreportoutput."  Beneath that variable window is another larger window entitled "Render for:".  There are three checked icons there.  They are PDF, XLS, and XLSX.  I am wondering if this has something to do with 32-bit/64-bit machines or something else that is totally unforeseen.  Everything I've done to date (including your sage advice) points logically to this working just as one would expect, yet these variables stubbornly remain absent from the XL spreadsheet output.

Thank you again,
RJH

[Lynn]

If you want it rendered for any output format then simply removing the render variable altogether seems like the best idea. If you look at the explanation of the ReportOutput function in my earlier post you will see there are a number of different values possible (none of which are XLSX).

I think it is doing exactly what you are telling it to do...render only for PDF, XLS, or XLSX. But when you run to Excel it isn't any of those types. There are several output to Excel options and the output format is different for each. The value returned by the ReportOutput function is not simply the file extension.

Try putting a report expression for ReportOutput() directly on your layout without any render variable tied to it. Then run to Excel and see what the value is (my money is on spreadsheetML).

Once you understand what value is associated with the output you want, you can adjust your render variable accordingly, or just get rid of it since it seems you want the report expression regardless of output format.

[rjhead]

I think that might've done the trick.  I am testing now.  For the record (and others who might be watching), leave "Render Variable" blank.  Under "Text Source" in the properties pane, use "Report Expression" in the Source Type property, and the case statement I cited above in the Report Expression property under the Source Type property.  All of these are found in the Properties window on the left in the user interface.

Lynn, I appreciate all your sage advice.  I will come back to this board with another question shortly.

RJH