Conditional report rendering based on render formats for SSRS Reports

By:   |   Comments (26)   |   Related: > Reporting Services Dynamic Reports


Problem

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.

Solution

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.

Example

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.

In SQL Server Reporting Services 2008 R2, a new global variable has been introduced - "RenderFormat"

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.

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

6) Now view this report from Report Manager. Both the controls should be visible as shown below.

view this report from Report Manager

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.

export this report to a PDF format

Summary

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.

Next Steps
  • 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, August 8, 2019 - 10:23:28 AM - Michael Blasco Back To Top (82003)

Thank you, this was very helpful. IsInteractive was precisely what I needed for my problem.

Tuesday, January 22, 2019 - 9:21:59 AM - Ajay Back To Top (78839)

 Hi ,

let  me know , if we need to hide the report header only for excel format . so what should be expression.

i have used the existing expression but it hoiding the info for both excel and pdf.

Friday, January 29, 2016 - 6:57:51 AM - vinayak salunkhe Back To Top (40540)

 Is it possible to  Send mail in body i.e. MHTML and Attachment in single mail via Report server

 

Wednesday, May 13, 2015 - 3:25:18 AM - Anurag Khare Back To Top (37161)

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..?

Thanks in Advance..!!

-Anurag

Friday, November 14, 2014 - 12:44:22 AM - Deepak Back To Top (35284)

Hi

This works in SQL 2008R2 but not 2008. Can you tell me if we want to implement same in 2008 then what to do

 

Tuesday, October 29, 2013 - 10:51:26 AM - Sooraj K Back To Top (27319)

How do we incorporate the same visibility set for MHTML format as well?

In my case, all downloaded format should behave in the same way. That is, while dowloading to MHTML some hidden controls also should be displayed.

It is working for PDF,Excel and Word, but for MHTML it is not working.

Can you help me to have an expression that work across all Rendering formats.

Thursday, October 10, 2013 - 8:43:54 AM - chris Back To Top (27101)

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 Back To Top (26803)

Hi Siddharth,

Thanks for such a nice post,

My problem is i need to show ssrs reports in local mode, so i am unable to find Globals!RenderingFormat property in this case.

can you please suggest me if any solution is thare.

 

Friday, April 12, 2013 - 1:54:57 AM - badar Riyas Back To Top (23320)

Very Nice Article. 

Thursday, May 17, 2012 - 6:00:57 AM - Anil Thakur Back To Top (17496)

 

Great Post thanks a lot. I was looking exactly some code.

Wednesday, November 30, 2011 - 3:35:27 AM - Samuel Back To Top (15244)

 

Nice article.

Thanks,

Samuel

Wednesday, March 2, 2011 - 6:26:36 AM - Siddalingesha Gr Back To Top (13089)

Hi ALL,

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.

Thanks,

Siddu

Monday, September 27, 2010 - 8:58:24 AM - Siddharth Mehta Back To Top (10208)
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.

Monday, September 27, 2010 - 2:32:10 AM - Brian Back To Top (10207)
Hi

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

Thanks Brian

Friday, September 17, 2010 - 12:04:28 PM - Janus Lin Back To Top (10171)
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 Back To Top (10165)
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 Back To Top (10162)
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.?

Thanks!

Janus

Saturday, September 4, 2010 - 3:08:47 AM - Siddharth Mehta Back To Top (10126)
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 3, 2010 - 10:22:31 PM - Samson J. Loo Back To Top (10124)
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 3, 2010 - 9:48:16 PM - Samson J. Loo Back To Top (10123)
I got it!!!

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!!!

Thank you!

Friday, September 3, 2010 - 9:41:30 PM - Samson J. Loo Back To Top (10122)
Siddharth,

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 3, 2010 - 6:29:39 PM - Samson J. Loo Back To Top (10121)
My mistake Hussein I should have stated that I wished this feature was available in SSRS 2005.

Friday, September 3, 2010 - 4:45:08 PM - Hussein Yousef Back To Top (10120)
Siddharth, I thought that only in 2008 as you mentioned in the article, but Samson is talking about SSRS 2005? is it the same thing?

Friday, September 3, 2010 - 2:19:16 PM - Siddharth Mehta Back To Top (10119)
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 3, 2010 - 1:26:32 PM - Hussein Yousef Back To Top (10118)
why do we have to see your picture in the example? i'm kidding...Great tip...thanks man..

Friday, September 3, 2010 - 12:06:04 PM - Samson J. Loo Back To Top (10117)
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?














get free sql tips
agree to terms