Example: A report contains more than 30 fields. Some users want to see only 5 fields, some users 10 fields, and other may want to see 20 fields.
Solution: Add a Report Parameter having the values as the name of all the fields of dataset. Now set hidden expression for each column of the tabular report.
Here is the solution with an example:
STEP1:
Create a report with required dataset. Drag and drop table control and select dataset fields.
In my example, I have following fields in the dataset: Year, Quarter, Month, Date, Product Name, Customer Name, Sales Region, Sales Country, Order Number, Sales Amount.
STEP2:
Create a dataset dsColumns using below query:
SELECT 1 ID, 'Year' AS ColumnName UNION
SELECT 2 ID, 'Quarter' AS ColumnName UNION
SELECT 3 ID, 'Month' AS ColumnName UNION
SELECT 4 ID, 'Date' AS ColumnName UNION
SELECT 5 ID, 'Product Name' AS ColumnName UNION
SELECT 6 ID, 'Customer Name' AS ColumnName UNION
SELECT 7 ID, 'Sales Region' AS ColumnName UNION
SELECT 8 ID, 'Sales Country' AS ColumnName UNION
SELECT 9 ID, 'Order Number' AS ColumnName UNION
SELECT 10 ID,'Sales Amount' AS ColumnName
STEP3:
Create a new parameter with name pDisplayFields and Promt Display Columns as shown below:
In Default Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field.
STEP4:
Now you have to set the expression to display the colummns which are selected in the pDisplayColumn parameter. Right click on First Column (Year in my example) and click Column Visibility...
=IIF(InStr(JOIN(Parameters!pDisplayFields.Value,","),"Year")>0,False,True)
Now repeat this expression for all the columns by modify the expression for the respective column name accordingly.
Thats all. Now preview the report. You will see all the columns by default.
Now select required columns in Display Column parameter to modify the report layout at run time.
Cheers!!!
Interesting idea.
ReplyDeleteIn our situation, we had a table that contained 1) ID 2) column name as displayed in the report 3) column name as it is in the table 4) table name. We built an SP with dynamic query which takes the selected column IDs as comma separated string parameter and based on the selected parameters query was built dynamically. This way, the data will be filtered while building the data set itself and will reduce lot of processing on the report server end as well as reduce lot of data traffic over network. We used a matrix report to display the column names and data from the data set.
ReplyDeleteGreat one dude
ReplyDeleteThanks Kiran for the great comment. I had implemented the logic you mentioned couple of years back. Also I am planning to post this in my next article.
ReplyDeleteHowever there are certain things we should consider while choosing appropriate approach:
1. What if source is CUBE instead of SQL Server
2. For dynamic sql we need to make sure the query optimizer is generating and using the optimal query plan.
3. For heavy report we should use cached report to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. You may not be able to use cached features in case of Dynamic SQL.
Hi Hari - I'm trying to create a report showing an area chart based on some dynamic sql that creates a pivot statement. The problem is I don't know how many columns there will be in the data set. I start with 3 columns in my result set and then the rest are built dynamically. Hence all I see in BIDS is a 3 column table. Do you have any ideas as to how this might be accomplished?
ReplyDeleteHi Hari,
ReplyDeleteI used an alternative form of the hide column expression when dealing with the multivalue parameter.
=IIF(Filter(Parameters!MultiOption.Value,"6",true).Length>0,False,True)
Which means to show this column if it's selected in the parameter list. In this example, 6 is the id of the label.
I had such a small dataset, so going to the trouble of tailoring the query was not important.
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHi Hari,
ReplyDeleteI have a stored proc which returns different number of columns based on some condition selected everytime(depends user input). How to build dynamic columns to map the dataset returned in SSRS?
Its awesome......
ReplyDeleteHi,
ReplyDeleteI need to show preecidng months as i select any month, and they all should come dynamically.. for e.g. If i select month april, then jan, feb , mar, apr should come...How is it possible..Please Help !