SSRS Dynamic report, how did that column just appeared in there ? Part 2

The report itself  is pretty straightforward. The only difference from a regular report here, is that we will need to map  the sequence and the field name for the columns. Other than that, easy! There is, of course, a limit on how many columns can be  displayed and this would have to observe each particular project requirement. For my example, it is 50 columns across.

We can call the fields for dataset1 whatever you like. I named mine C1,C2 ans so on.

The important part here is the expression to display the correct column name and the correct sequence they are supposed to appear.

We can use a lookup function in order to achieve that:

= Lookup(1, Fields!Sequence.Value, Fields!FieldName.Value, "MappedFields")

This would retrieve column 1 for example. In order to retrieve next column, we would increase the lookup number by one, for example.

And we show /hide the column according to data availability on data set 1, as below. 

All the above information, will be used to display data to our report from the below stored procedure. First of all, we will retrieve  the active columns and use a cursor to create a string that we will pass to get our data.

Once we have this data, we can loop through  and create our query using  the variable @ColumnString. 

Here we keep track of our sequence and the order in which the columns will appear in the report.

And here the full cursor script:

And finally,

we create our select statement and just run it!!!!

As you can see this can be a bit complicated to set up due to all the moving parts but once we have this working, adding, removing  or simply updating the report will be a breeze. Best of all, the end user will do it for you , while you can catch up on watching all those cats videos on YouTube. :)