Reporting Services is commonly used in an enterprise and Report Manager is one of the most convenient ways to make reports accessible to the users. When using Report Manager, users can export reports to different formats like PDF, XLS and others. When reports are exported to other formats they also include all of the content from the report, but rendered to the format of the report output. There maybe scenarios where you would not want a particular part or component of report to appear in these other report formats. A practical example of this could be that you want to display images when the report is displayed in HTML, but not when the report is exported to a PDF format. In this tip we take a look at a new feature in Reporting Services that allows us to control these items without having to create multiple report versions.
In SQL Server Reporting Services 2008 R2, a new global variable has been introduced - "RenderFormat". This variable can be used in a variety of scenarios including for the problem in question.
When the report is developed using BIDS or viewed from Report Manager, a common rendering extension is used and when the report is exported to any other available format, it's corresponding rendering extension is used to generate the report output. Rendering extensions can be classified into two different modes known as "Interactive mode" and "Non-Interactive mode". Interactive means the report can be controlled by the end user such as an HTML report in a browser. Where non-interactive could be a report exported as a PDF file.
Based on certain properties of the global variable "RenderFormat" like "IsInteractive" or the "Name" property, if the visibility of a particular control on the report is hidden based on the "RenderFormat" that particular control would not display on an interactive report, but would display on a non-interactive report or vice versa. To test this, follow the below exercise.
1) Create a new blank report using Business Intelligence Development Studio (BIDS) and name it "ExportTest".
2) Add at least two controls to the report, so that we can test the difference. In my case I have added an image control and a text box control.
3) Deploy the report and view the report using report manager. You should be able to view the report with these two controls that were added.
4) Now go back to BIDS and select the Image control. Edit the "Hidden" property by clicking on the dropdown and select "Expression" as highlighted in the below screenshot.
5) Enter the expression as shown in the below screenshot. Our intention is to test the visibility of this image control when the report is viewed from Report Manager or from BIDS, and when the report is exported to some other format. After you have made the changes deploy the report again.
6) Now view this report from Report Manager. Both the controls should be visible as shown below.
7) Now export this report to a PDF format. The PDF format should show only the text in the exported report and not the image as shown in the below screenshot. The reason for this is that the PDF rendering extension is considered to not have interactive features. Now export the same report to a MHTML format and you will find that the image control is visible, because the MHTML format is considered to be an interactive format.
If you carefully analyze the steps we followed, you can start the analysis with the fact that PDF rendering format is not considered interactive. So the value of the "Globals!RenderFormat.IsInteractive" property got evaluated to "False" and the result of "NOT(False)" would be True. So when the value of the "Hidden" property is set to True, that would mean that the control should not be visible, which is apparent in the PDF report output. If you remove the "NOT" operator, you would achieve the opposite solution for the example that we discussed.
I hope you find a good use of this technique in your reports.
Use the Name property of RenderFormat global variable to detect which RenderFormat is used in BIDS and Report Manager.
Modify the expression such that image control should not be visible even in MHTML format.
Last Update: 9/3/2010
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.
Hi Sidharth, Really a very nice topic. But I need to apply this thing in SQL 2008 only coz my company is not providing other than version. As they are strict with this version due to some banking domain structure through out the organisation. Pls suggest me to this..?
Can I use some .NET code (where can I put the code) or something else..?
Thank you so much for this article. I've been struggling with my reports to export to Excel. I've 2 images and execute information and parameter information in the header and footer and when I tried to print in Excel it used the full header and footer size and leave me with a very small space for the body of the report.
With the export now to Excel it seems to sort out this problem and I gain full space back for my report and the header and footer is resize.
That should have been a very easy problem to sort out in SSRS caused me major headaches but luckily this sorted it out.
Monday, September 16, 2013 - 2:39:24 AM - Shashnak Jain
In my report i have one Chart and Matrix, i am hiding Chart based on rendering format name (EXCEL), its working fine in BIDS but after deploying to report server i am getting error saying : error in hidden expression for the Chart1.....,
it works fine in BIDS, but Throws error on deploying.
please can anyony help on this.
Monday, September 27, 2010 - 8:58:24 AM - Siddharth Mehta
Are you using SQL Server 2008 R2 ? This enhancement is available only in R2 version.
Regarding your question, you should try to figure out the back records and that make your report fail. It seems like an issue with the output from your SP, as the report works for some records, but fails due to bad records. The error msg signals that the SP is being called with some invalid value in the parameter. Try checking the parameters tab...
Else I suggest to take this issue to SSRS forums for detailed help on your query.
I had a look and can't find Globals!RenderFormat.IsInteractiv in my built in fields?
I am mainly looking for an answer for a different question but you seem like a gun so thought I'd ask. I am adding database images but some are not the right format. I call a stored procedure and when I run it for one person who's image I know works it presents it fine, but when I run it for a whole recordset because some of the images are not in the correct format the whole report falls over and I get an error "Parameter is not valid" is there some code to validate an image before displaying. I can't find any info on this and the Tech Net site falls over every time I try to submit a question.
Just thought I'd ask
Friday, September 17, 2010 - 12:04:28 PM - Janus Lin
Rats. Looks like Page Header Height only takes unit strings. It doesn't appear that I can put an expression in there. And it looks like the minimum value that can be manually entered is the bottom-most element of the header anyway. I remember reading somewhere that exporting SSRS reports to Excel can be finicky, but I was hopeful that this was a way around it.
We'll find uses for it in other parts of our reports! Thanks for this article!
Thursday, September 16, 2010 - 1:27:34 PM - Siddharth Mehta
To the best of my knowledge, you can apply this on the Hidden property, which is generally for controls. You can try to set the height of header / footer based on the RenderFormat variable based on the format of the export. Setting size to 0 for excel format, would virtually make the header or footer invisible in the report.
Thursday, September 16, 2010 - 9:24:45 AM - Janus Lin
We run into situations where an SSRS report looks fine when displayed in the browser, but when exported to Excel the columns are driven by the report body, page header, page footer, etc. Once we've cleaned up the report body, we resort to creating an "Excel-friendly" version of the report that doesn't have a page header, footer, etc. and both are deployed.
Can we use the approach described here to hide the page header, footer, etc.?
Saturday, September 04, 2010 - 3:08:47 AM - Siddharth Mehta
Actually you can keep an XML config file and import that data into a dataset. Based on that values, you can set the values for the Hidden property of various controls in the report. XML Config file can contain tags for each control on the report. This is a theory as per my understanding, but you need to test the implementation part.
Friday, September 03, 2010 - 10:22:31 PM - Samson J. Loo
I was just thinking about this... you know what would be really cool if we could have a control that will provide the end user the choice to have the (control or field) as part of the export. If we could use the same syntax triggered by a toggle from another control as a (Print or Don't Print) option and the condition would or wouldn't be applied at export. This would elminate the need for a second report. Hopefully this makes sense.
Friday, September 03, 2010 - 9:48:16 PM - Samson J. Loo
On the Group Properties, under visibility I set the Show or Hide expression to =NOT(Globals!RenderFormat.IsInteractive). I returned the value for Visibility for the row itself back to false. Then tested the report. I was able to see my toggle rows and exported to excel and there was no grouping visible in excel!!!
Friday, September 03, 2010 - 9:41:30 PM - Samson J. Loo
It works my friend. I had an existing report with a child row that can be toggled by a parent row using grouping in SSRS. I applied the expression as you illustrated and upon export to excel the data is not visible. Which is great. However, though the export does not contain the data in excel still shows the grouping without data contained in the cells. When collapsed in excel the + symbols are present. When I expand the rows I see multiple blank rows between the parent records. What I need to figure out is how to use this conditional rendering to diable the toggle on export for excel.
Thank you for posting this... it is a tremendous help!
Friday, September 03, 2010 - 6:29:39 PM - Samson J. Loo
Samson - I am sure this would be possible for the entire control. But to be honest, I have not tried it at the row level. It would be a good idea to assign the data source for the tablix or matrix depending upon the renderformat, so you do not need to mani***te the data set. Insted use appropriate data source depending upon the render format.
Friday, September 03, 2010 - 1:26:32 PM - Hussein Yousef
Man-o-man this was exactly what I was looking for with SSRS 2005. One question, though I suspect the same prin***l applys but does this work with data exports using a tablix or matrix structure. Could I opt to not export certain rows depending on the data of field?