SSRS Reorder columns based on parameter

Question

I have a report that I want to share between multiple clients. However, each client wants different columns. So based on one of the report parameters, for certain values I hide the columns that client isn't interested in.

However, some clients also want the columns in a different order. Is it possible to use a report parameter to re-order the columns?

One solution

Say Client 1 wants columns A | B in that order, but Client 2 wants them as B | A.

Create columns A | B | A (duplicate A)
If Client 1 is selected, hide the second A
If Client 2 is selected, hide the first A.

end

I don't want to use the above solution because we could end up having 30 different column orderings.

So, is there another, easier way to re-order columns based on a report parameter, or would I be better off sharing the DataSet and creating different reports (.rdls) per client?

Do all your fields share the same data type, formatting and length? — Ross Bush
All my fields? You mean the same field between clients? Yes. Otherwise I'd add a new column. — DLeh

Mike Honey Mike Honey · Accepted Answer · 2014-06-04T06:11:42

You could go down the path of using Switch functions in every detail cell to pick the field based on a parameter. I wouldn't. I did it once for an insistent client and regretted it every since - maintenance and debugging is a nightmare.

It's just a limitation of SSRS. I would build a series of tables and conditionally hide them based on the parameter.

SSRS Reorder columns based on parameter

1 Answers