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?
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThere is one issue with your expression: it will return extra columns if you have another with a name that is a substring of your test column name. For instance, if you are testing a column called ""Fiscal Year", and your table has columns named "Year" and "Fiscal Year", both would be returned.
ReplyDeleteHI i am venkatesh ,
ReplyDeletei am also follow same thing,its working fine but if i select 1 and 3 columns means its coming gap between 1 and 3 columns.. How can remove the gap..
please help me its urgent
HI Kabu
ReplyDeleteMy problem is related by quite different, I have a pivoted report that needs the report user to select a date range, the column headers gets generated based on the wideness of the date range like [Jan 2013], [Feb 2013] etc. these headers get generated only at run time by the proc. how do capture these in ssrs. thanks
This comment has been removed by the author.
ReplyDeleteWhat I have written above is just confusion, I would like to delete it. Your tip works great! Thank you Hari Sharma!
ReplyDeleteThis comment has been removed by the author.
ReplyDeletethis expression not working on ssrs 2008 . please help me .
ReplyDelete=IIF(InStr(JOIN(Parameters!pDisplayFields.Value,","),"Year")>0,False,True)
Thank you very much. You saved my time very much.
ReplyDeletehow to add total field in dynamic column please provide me the code
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThank you Hari, a great option!
ReplyDeleteIn my report I called the parameter "Display additional fields" because I want to have the first 4 columns always visible, and then the rest of them as optional. So I listed in the dataset only the optional ones and set the visibility condition just for those fields. The thing is the parameter doesn't allow blanks, so it's not returning the mandatory 4 columns if I don't choose any of the additional fields. Any suggestions to do this?
I don't believe I'm using the right version of the ReportViewer.
ReplyDeleteI was able to create and display a report, but when I added the parameter, I get the following error now:
The definition of the report 'D:\Users\les.stockton\source\repos\angularjsssrs\AngularJSSample\Reports\Report4.rdlc' is invalid.
The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' which cannot be upgraded.
I'm using Visual Studio 2017.
my packages.config shows:
the packages.config shows:
ReplyDeleteI guess I can't embedd the packages.config.
ReplyDeleteid="Microsoft.ReportViewer.2015.Runtime" version="12.0.2.2402" targetFramework="net45"
Is this possible in Power BI?
ReplyDeleteI have same requirement to implement same in Power BI. So Dynamic Column selection is possible in Power BI?
ReplyDelete