dynamically adding columns into SQL Reporting services

Question

The stored procedure mapped to a report query builder returns values dynamically mean to say once 8 columns and sometimes 6 columns. My question is how to name the header for the extra added columns?

Answer 1

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).

Answer 2

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!