December 19, 2011 at 3:28 pm
VS 2008 V3.5 sp1
SSRS 2008 R2
I have a report with simple parameter choice. The issue is that the report does not behave the way I want it to. The tables will display in the IDE when I don't evpect it.
For example. User passes parameter = 'I'. The result I expect from the code on the four available tables below is that only table 1 would display on screen. Then when the user exports that result, only table two exports to Excel. The reason for this code is to strip out the header (company name, logo, etc) on the exported table such that the user does not have to in Excel.
What is happening is that both Display_Table_1 and Display_Table_2 show and both table_1 and table_2 and export table 4 render in excel. Does anyone have logic for getting to the end? I have tried many combinations always not quite getting what I need.
Thanks. John
Parm_choices are 'I,P,R'
Display_Table_1
=IIF( (Globals!RenderFormat.Name = "RPL" or Globals!RenderFormat.Name = nothing)
AND Parameters!app.Value <> "R" or Parameters!app.Value <> "P", false,true)
Export_Table_2
=IIF((Globals!RenderFormat.Name = "RPL" or Globals!RenderFormat.Name = nothing)
and Parameters!app.Value <> "R" or Parameters!app.Value <> "P", TRUE,FALSE)
Display_Table_3
=IIF( (Globals!RenderFormat.Name = "RPL" or Globals!RenderFormat.Name = nothing)
AND Parameters!app.Value <> "I" or Parameters!app.Value <> "R", false,true)
Export_Table_4
=IIF((Globals!RenderFormat.Name = "RPL" or Globals!RenderFormat.Name = nothing)
and Parameters!app.Value <> "I" or Parameters!app.Value <> "R", TRUE,FALSE)
John A. Byrnes
December 21, 2011 at 7:26 am
Ok I resolved this little issue, and have a tip for anyone wanting to do this as well.
The main problem is that nicely formatted report can have merged columns, and usually have the standard company logo and report criterion in a report header. This causes some work for our users when they want to export the report to excel. I have used a solution in the past that does not use a report header, but instead imbeds the logo and criteria in a header row in the tablix. A second report tablix which is a copy of the first is used for exporting and it only contains the column labels and the data and most importantly, no merged cells.
I have the following code in the tablix visibility and it is now working then way I wanted it to. This also still supports the other render formats, like PDF and Word, etc.
ITSM_Display
=IIF((Globals!RenderFormat.Name <> "EXCEL") AND (Parameters!app.Value = "ITSM"), false,true)
itsm_export
=IIF((Globals!RenderFormat.Name = "EXCEL") and (Parameters!app.Value = "ITSM"), false,true)
pro_display
=IIF((Globals!RenderFormat.Name <> "EXCEL") and (Parameters!app.Value = "PRO"), false,true)
pro_export
=IIF((Globals!RenderFormat.Name = "EXCEL") and (Parameters!app.Value = "PRO"), false,true)
RFC_display
=IIF((Globals!RenderFormat.Name <> "EXCEL") and (Parameters!app.Value = "RFC"), false,true)
RFC_export
=IIF((Globals!RenderFormat.Name = "EXCEL") and (Parameters!app.Value = "RFC"), false,true)
One other trick is to set the location left to 0 which starts the data in Excel column A. Unfortunately, you cannot set the top to 0 with our piling the tablix controls on top of one another which may be confusing to another developer. Having a Top location other than 0 is not a big deal, and the user simply has to delete the first row in the spreadsheet.
John A. Byrnes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply