SSRS Dynamic Columns Display

Question

Using SQL 2008R2

I have a need to create a SSRS report where the user can specify the Columns returned AND the order in which they are returned. Dynamic data and ordering.

Example: Available Columns A,B,C,D,E

User specifies they want to see: C,D,A

No issue on the data side, I'm using a stored procedure and can handle this no issue.

On the SSR side, I've seen similar question mention using a "matrix".
However I'm looking for opinions on the best approach on how to handle this on the SSRS Side. What is the best way to handle dynamic number of returned columns and dynamic ordering of columns..

Answer 1

As has already been mentioned, SSRS is not the way to go for this.

If the order of the columns were not customizable then you could handle column visibility using SSRS expressions but presenting the columns in a dynamic order is not easy in SSRS.

For that kind of thing you could use Excel's pivot table functionality, use a 3rd party .NET solution like MVC or build some home-grown ASP.net solution.

Answer 2

Try this:
1. In SSRS, create parameters ColumnA and ColumnB

2. Create your dataset---don't directly type your query, use expression (the fx button)

3. In the expression, you can write your query like this:
   ="SELECT " + Parameters!ColumnA.Value + "," + Parameters!ColumnA.Value + " FROM Table"

You can solve your dynamic ORDER BY problem by the same way.