dynamically adding columns into SQL Reporting services

The stored procedure mapped to a report query builder returns valu开发者_JS百科es dynamically mean to say once 8 columns and sometimes 6 columns. My question is how to name the header for the extra added columns?

---

For each dynamic column, right-click the column header and choose properties. In the properties pane, under the Visibility section, click the down arrow beside Hidden and choose <Expression...>. Paste in the following and change the the field name to match your database.

=IIf(Fields!name.Value Is Nothing, True, False)

When you run the report, if the values in the dynamic columns are NULL, the column will be hidden. If it contains non-null values, the columns will be displayed.

---

One Important thing to remember: When we are binding a dynamic dataset, SSRS dont have capability to detect the Fields present in the dataset. For this we need to specify the all possible fields in the Dataset, To do this follow the steps:

1. Goto Data tab in the report and open the dataset which returns dynamic columns
2. Click on the … button besides the dataset to edit the dataset
3. A pop-up window for dataset will appear, in that window go to Fields tab
4. There we have to write all possible fields in the Dataset
5. Click OK and Preview your report

---

Have you tried doing a COALESCE in your sp, where you always get back those 2 "dynamic" columns? Something like COALESCE(sp.YourColumn, NULL). Then you would be able to create an expression in the report and have it hide that column when NULL or a blank is returned. Maybe something like for the Visbility = IIF(Fields!DynamicField1.Value is Nothing, False, True).

---

Tweak your stored procedure so that you always return 8 columns. Simply return NULL in the two dynamic columns if you don't want them in the report.

For each dynamic column, right-click the column header and choose properties. In the properties pane, under the Visibility section, click the down arrow beside Hidden and choose <Expression...>. Paste in the following and change the the field name to match your database.

=IIf(Fields!name.Value Is Nothing, True, False)

When you run the report, if the values in the dynamic columns are NULL, the column will be hidden. If it contains non-null values, the columns will be displayed.

Good luck, and welcome to the site!