question

JMohan avatar image
JMohan asked

Row visibility in SSRS

I had to generate a report which look like... Report contains one Multi select parameter which contains information like contractee, class, Teacher, Date and Report Date. All the above five are optional. I may one or two or three or all depending on the requirement. All the above five are also available in the body section.If I select two optional parameters out of five then only the selected parameters should be visible in the body section and the remaining three should be invisible but there should be no gaps in between rows in the body section.can anyone please solve it.my email id is 121mohan@gmail.com
ssrsformattingvisibility
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jmohan - people will leave an answer here so that other people can share the solution. We wont be sending the answer by email. you can set the forum to notify you by email that there is a new answer waiting here for you.
5 Likes 5 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
The solution is simple. To display the parameters in the report itself, use Table or Tablix (in SQL2008). Then if you want the parameters in separate rows, create as many row in the table header as many parameters you have. Then simply for each row of the header specify `Hidden` property as expression `=IsNothing(Parameters!param_name.Value)`. In this way you will see values of parameters which have been provided and the one not provided will be hidden and there will be no gaps between rows.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What you mean by multiple optional parameters? I assume, that optional parameter, which was not specified will have a default NULL value. As you have described, you have multiple parameters. Some of them you provide and the rest will have NULL value. In that case if you put each parameter value in one header row and the Hidden property of each row will be set using the expression I have provided, then the result will be, that the provided parameters will be printed one on each row and the not provided will not be printed. Also you will not have a gaps between rows as the rows for NULL value parameters will not be visible at all. You have to specify the expression on the Hidden property of each header row and a corresponding parameter name must be specified. If you will print parameter with name "A" then also in the =IsNothing(...) expression you have to use the "A".
2 Likes 2 ·
Thanks Pavel Pawlowski... Iif(Parameters!parameterName.Value="somename",false,true) works for only one parameter. In my case I need Multiple optional parameters.The expression you have given was not working.If any alternatives please suggest me.... Thanks
0 Likes 0 ·
Tatyana avatar image
Tatyana answered
Hey... I think we do this, too. The user can select from the list of fields which ones they want to see. The list of available fields is written to a dataset, say, FieldList: "SELECT 'contractee' AS Field UNION SELECT 'class' UNION SELECT 'Teacher' [...] ORDER BY 1" . Then you will feed your multivalue parameter from a query based on this dataset. Then, for a main dataset you'll have something like this: "SELECT CASE WHEN 'contractee' IN (@FieldList) THEN T.'contractee' ELSE NULL END AS 'contractee' , CASE WHEN 'class' IN (@FieldList) THEN T.class ELSE NULL END AS class, CASE WHEN 'Teacher' IN (@FieldList) THEN T.'Teacher' ELSE NULL END AS 'Teacher' , [...] FROM YourTable". And, finally, the Visibility for each column in your report's table will be set to the Expression like (for 'contractee' column here): `" =IIF(InStr(Join(Parameters!FieldList.Value), "contractee") > 0, False, True)"`. Do you think this is what you asked about?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.