Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Conditional report rendering based on render formats for SSRS Reports

MSSQLTips author Siddharth Mehta By:   |   Read Comments (21)   |   Related Tips: > 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.


Last Update: 9/3/2010


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, October 29, 2013 - 10:51:26 AM - Sooraj K Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Very Nice Article. 


Thursday, May 17, 2012 - 6:00:57 AM - Anil Thakur Read The Tip

 

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


Wednesday, November 30, 2011 - 3:35:27 AM - Samuel Read The Tip

 

Nice article.

Thanks,

Samuel


Wednesday, March 02, 2011 - 6:26:36 AM - Siddalingesha Gr Read The Tip

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 Read The Tip
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 Read The Tip
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 Read The Tip
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 Read The Tip
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 Read The Tip
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 04, 2010 - 3:08:47 AM - Siddharth Mehta Read The Tip
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 Read The Tip
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 Read The Tip
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 03, 2010 - 9:41:30 PM - Samson J. Loo Read The Tip
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 03, 2010 - 6:29:39 PM - Samson J. Loo Read The Tip
My mistake Hussein I should have stated that I wished this feature was available in SSRS 2005.


Friday, September 03, 2010 - 4:45:08 PM - Hussein Yousef Read The Tip
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 03, 2010 - 2:19:16 PM - Siddharth Mehta Read The Tip
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 Read The Tip
why do we have to see your picture in the example? i'm kidding...Great tip...thanks man..



Friday, September 03, 2010 - 12:06:04 PM - Samson J. Loo Read The Tip
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?




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.