Get column names of the published report SSRS

Question

Hi ,

I have a published report template and I wanted to make it dynamic so that the users can select the fields that they want to be displayed on the report.

e.g there is a matrix table in the report that shows heading t1,t2,t3,t4. I want to know if the reporting services has some method to pull column names like they have for  the parameter names?

I want to give them the ability to see only the fields they select.

Thanks

---

RJ

Answer 1

Hi,

SQL Server 2012 Reporting Services + Power View provides necesary functionality out of the box.

I dont think that regular ssrs services can provide such dynamic behavior by easy way.

Answer 2

Hi RJ,

In Reporting Services, we can control the visibility of a report item when we want to conditionally hide an item based on a report parameter or some other criteria specified.

In this issue, you can add a multi-value parameter to the report and then set the visibilities of the columns based on the parameter. Please refer to the steps below:

1. Add a multi-value parameter “T” to the report. Specify the “Available Values” for the parameter as follows:
   Label: t1  Value: t1
   Label: t2  Value: t2
   Label: t3  Value: t3
   Label: t4  Value: t4
2. Right-click on the column handle of the column which contains “t1” field, click the “Column Visibility…” Select “Show or hide based on an expression” and set the expression to:
   =IIF(InStr(Join(Parameters!T.Value, “,”), “t1”)=0, true, false) 
3. Repeat the steps above with the other three columns.

Note: The InStr() function returns an integer specifying the start position of the first occurrence of one string within another. So the expression also works if we change the “t1” to any other string value. 

Meanwhile, we can also achieve this goal by adding a custom code to the report. For the details information, please see the answer of Challen in the related thread:
How to dynamical display the tablix's column

Reference:
Expression Examples-String Functions

If you have any questions, please feel free to ask.

Regards,
Mike Yin

Answer 3

Hello,

You can easily do it using filters.

Please refer to the steps below:

1) Assumptions: a) I am assuming "temp" as column name with values t1, t2, t3, t4

                           b) and parameter name as Parameter_temp

2) Go to table/matrix properties , for that you just need to right click on table handle on extreme left side and select "Tablix Properties".

3) Go to Filters, then click on Add.

4) In the Expression field choose your Column name (temp as per assumption)

5) Now for operator field choose"In".

6) In value field ,single click on fx button and write " =Parameters!Parameter_temp.Value "

7) Click on OK, and run the report.

PFA Screenshot for clarity.

Hope this helps.

Answer 4

Hi RJ,

I should add that the solution I have posted above can be used when you want to hide four different columns of the report design structure not four columns of the report in report preview layout.

If the t1, t2, t3 and t4 you referred to are just four values of the matrix column group, please refer to Neeraj’s suggestion to add a parameter and filter to the matrix.

Reference:
Add a Parameter to Your Report

Regards,
Mike Yin

Answer 5

Thanks everyone for your valuable suggestions! I was able to use them to make my report dynamic.

Thanks again!

---

RJ