In this post we are going to see one of the real time use of Column Visibility in SSRS with an example. As we all know column visibility property is used to show/hide the column in the report. Let us see how we can use this property to allow the business users to select what they want to see in a report rather than the one provided by engineering team.
Generally we design an SSRS report for a set of people. Among those people everyone will not be interested in all the information. They might be viewing the fields which are more important for them instead of all the fields. If the report contains 5-6 reporting fields then it will not be difficult for people to take a look at all the columns. As and when the number of fields grow it becomes increasingly difficult to analyze the data which is related to them. This is where the column visibility property can be used to provide the user with the power to select what they want to see. In the following example let us see how we can provide the users with option to select the fields.
Let us create the sales details report using the data available in the Adventureworks database. Create a dataset with the help of the following query
SELECT Sales.vSalesPerson.SalesPersonID ,Sales.vSalesPerson.FirstName ,Sales.vSalesPerson.MiddleName ,Sales.vSalesPerson.LastName ,Sales.vSalesPerson.JobTitle ,Sales.vSalesPerson.Phone ,Sales.vSalesPerson.EmailAddress ,Sales.vSalesPerson.EmailPromotion ,Sales.vSalesPerson.AddressLine1 ,Sales.vSalesPerson.City ,Sales.vSalesPerson.StateProvinceName ,Sales.vSalesPerson.PostalCode ,Sales.vSalesPerson.CountryRegionName ,Sales.vSalesPerson.TerritoryName ,Sales.vSalesPerson.TerritoryGroup ,Sales.vSalesPerson.SalesQuota ,Sales.vSalesPerson.SalesYTD ,Sales.vSalesPerson.SalesLastYear FROM Sales.vSalesPerson
Let us drag the tablix control into the report and set the dataset property. Assign the fields to the tablix fields, please refer to the screenshot below. I am limiting the number of fields for the demo purpose
Let us run the report and check the details.
Introduce a parameter called select fields in which we can add the fields label and their value. The parameter for the salesdetails report will have the following values.
Add all the values in the Default Values screen. Now the report will have a parameter which contains the labels of the fields available and their respective values.
Following are steps to add the column visibility to a report field
1. Open the report
2. Right Click on the tablix column and select Column Visibility, Refer to the screenshot below
3. Select the option “Show or hide based on an expression” and click on fx, please refer to the screenshot below
4. Enter the following expression, and enter the appropriate report field (eg. FirstName – Parameter value)
In the above image we are setting the property of the report field “FirstName”, similarly we need to apply the column visibility for all the columns.
The final rendered report will be as follows
The report will have a report filter which will have all the report fields names. If the users are interested in some of the report fields then they can select the appropriate filter values and they can render the reports. For eg. if the user want to see the data for the fields First Name, Last Name and sales then select the appropriate values in report parameters. Please refer to the screenshot below
Since we selected only the First Name, Last Name and the Sales amount only the above mentioned report fields are displayed in the report. This functionality will help the business user s to select the information which they are interested in.