Column Data as a Column Header in SSRS 2012

Question

I want to use the column data as a column header in the SSRS report. I am using the column data as a column group while creating the SSRS report. But depending on the filter conditions, the header columns changes dynamically. How can we make it static?

For eg.

Supplier         Flow    Amount       

======      =====  ========

A                  Adhoc       20

A                  Pay          30

B                 Adhoc        15

So when I select the Supplier A as a parameter, report output is

Supplier            Adhoc                     Pay

A                        20                         30

but when I select the supplier B as  a parameter, report output is

Supplier            Adhoc

B                          15

What I want when supplier B is selected, report output should be

Supplier         Adhoc                     Pay

B                     15                         

Arunava

Answer 1

hi,

you have to use expression on your headers and hide columns depending on your parameters

regards

---

Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

Answer 2

Hi Arunava,

I guess,you will get the desired report view by default in SSRS

What I want when supplier B is selected, report output should be

Supplier         Adhoc                     Pay

B                     15  

Only if you want hide the column Pay for parameter = Supplier B,make use of IsVisibility Function in SSRS

http://sathyadb.blogspot.in/2013/01/ssrs-ismissing-visibility-function.html

---

Thanks & Regards, sathya

Answer 3

I am using a column group for pivoting the column data into column header (value of "Flow" into column headers in the report). Now, if the Flow value "Pay" itself is not present in the data set when I select Supplier B, how can I use the expression to hide?

Answer 4

Pay does not exist for Supplier B so there is no data to generate that column header when B is selected. The only way I know of to work around this is to ensure that all desired header values are included in the data for all suppliers.

If you are using TSql, you can use a UNION statement to add a default "zero" row for each supplier. This works best for a small, fixed number of suppliers. If you have a variable or larger number of suppliers then a UNION that adds a single "zero" row for a default supplier, maybe 'Default Supplier'. You then need to rework you supplier group to group on =IIf(Fields!Supplier.Value="Default Supplier", Parameters!Supplier.Value, Fields!Supplier.Value). This should group the default "zero" data row with whichever Supplier is selected in the parameter. This assumes the parameter is a single-value parameter, not multi-value. Also assumes the parameter value will be identical to one of the values returned for Supplier in the dataset.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.