SSRS Tutorial 90- How to Create SSRS Report with Dynamic Columns ( Source Definition Change)

In this video of SSRS Tutorial, we are going to create a report in which the number of columns can change anytime. This report that we want to create should be Tabular report ( Detail Report).

The problem with the scenario is , we are using a table/view in which we can add or drop columns anytime. If we create simple tabular report , the problem is that every time our Table/View Definition change, we have to alter our report definition and redeploy.

In this video, we are going to learn some tricks, How to create report with Dynamic Columns. Here are the steps involved

We will create our SQL queries with Unpivot, Convert the columns to the Rows

Use Matrix Item in SSRS Report, so the dynamic columns can be handle dynamically

Use the Primary Key or ID as part of Rows so we have detail level data

Delete the Row level Column to hide the Primary Key

Change the definition of Table/View and check if everything working for our Dynamic Column SSRS Report

Visit our website to check out SSRS Tutorial Step by Step

link for the script used in the demo

[JohnPlebanski]

This was just what I need to help me resolve an issue I was having. Thanks!

[angelikaadass]

Thank you so much! I am learning your technique how to create stored.proc in this video. Also i like the fact that you are making  mistakes and then troubleshoot it in front of audience... for us to learn what has happened :-)

[ValleyofEndar]

22:00 dynamic column adding part in SSRS is at - uses Matrix. Thank you. I was trying to send pivoted results to SSRS, and didn't know how to handle the columns, but one does not have to as the Matrix structure will handle the unpivoted data for you.

[pinky]

Thank you for this very interesting topic, one question what if I need to group by some of the columns, how would I do that dynamically? Thanks

[josephserake]

this is help full thanks ! but, what if the datatype column have 'datetime ' data type . how will i handle the conversation of data type ?

[LawdJay]

is there anyway to reorder the columns? the output is in alphabetical order, as opposed to the order that the columns may be in within sql?

[MrAmaniyar]

Hello Aamir,
thanks for all the videos for SSRS. I find it very helpful.

I referred the query given by you, but I am having difficulty understanding the 4th line from last - Exec sp_executesql @SQL;


I don't see if we have already created the procedure earlier in the video/query.
your help is much appreciated!
Thanks

[yvarjun]

Thanks. I have a scenario to execute stored procedure with dynamic columns with SSRS?

[zakariaahammed]

Plz give me some clue how can i implement this scenario in RDLC report

[DeepakMalik-pdok]

How to show 10 record per page in this approach. Rownumber and ceiling are not working in this. Thanks in advance

[jayshrimahajan]

I am facing error "invalid object name ##temp.(Microsoft SQL server, error 208)" at SSRS dataset creation, while my Stored procedure working perfect at SSMS.Please help me to resolve this.

[krismaly]

I think you should redo this video. Users may not like or get confused