0

enter image description here

How to do this report using SSRS?

Paul Bambury
  • 1,252
  • 1
  • 13
  • 17
Ajit Kumar
  • 65
  • 1
  • 1
  • 3
  • What is the query that supports this report? – SS_DBA Jan 25 '17 at 18:56
  • One way would be to return your data in SomeRowGroupID - FieldID - FieldOrder - FieldName - FieldValue fashion and then add a row group to the report based on "SomeRowGroupID" and then add a columnwise group based on "FieldID" ordered by "FieldOrder". – Ross Bush Jan 25 '17 at 18:59
  • Are you saying you want your report to be a table that only shows the columns selected? – Bostaevski Jan 26 '17 at 17:06
  • yes @Bostaevski . All the table columns should come in a drop down with checkbox and the report will generate based on selected columns from the drop down. – Ajit Kumar Jan 27 '17 at 06:00
  • @WEI_DBA its SQL SERVER – Ajit Kumar Jan 27 '17 at 06:01
  • I know, but what query generates the dataset for this report? Can you post the query? – SS_DBA Jan 27 '17 at 13:19
  • If you are after something where the end user is able to dynamically build a report, SSRS cannot do this. You need to look at Power BI or data import into Excel. – Paul Bambury Jan 27 '17 at 17:03

1 Answers1

0

Yes it can be done for simple tables. May also work for complex groups but I haven't tested that.

Starting with this as a sample dataset:

SELECT
    v.Column1
,   v.Column2
,   v.Column3
,   v.Column4
FROM (VALUES
    (1,2,3,4)
,   (2,4,6,8)
,   (4,8,12,16)
) v (Column1, Column2, Column3, Column4)

Here is the table in SSRS

enter image description here

Now add a new parameter to your report. Make it a multi-value parameter. Add a label/value pair for each column you want to be able to show/hide.

enter image description here

Finally, for each column, right-click the column and select Column Visibility...

Set to "Show or hide based on an expression"

Set the expression to this (replacing "Column1" with the name of the relevant column):

=IIF(Array.IndexOf(Parameters!ColumnChooser.Value, "Column1") > -1, FALSE, TRUE)

That setting the Hidden property to TRUE if the column is not checked in the multi-value drop down.

For example, I unchecked Column2 and Column3 in the dropdown list and ran the report:

enter image description here

Bostaevski
  • 307
  • 2
  • 11