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