CODEWITHSUNDEEP
dynamicreporting-services
Manoj
Manoj

Reputation: 21

Dynamic SSRS report

I had a problem in creating the Dynamic report in SSRS. My problem is:

In a table I have stored SQL scripts with the column SQLScripts. If you execute these SQL scripts you get different number of columns for each script.

My problem is, I have one report with buttons of these scripts, for example test1, test2...like that. If you press test1 button this should take the test one SQL script and should display the report with appropiate columns in that sqlscripts.

I can't create individual reports for each test report, they are plenty. Are there any options for me to solve this problem...

Upvotes: 2

Views: 3442

Answers (3)

Eddy Jawed
Eddy Jawed

Reputation: 457

You basically need to create a 'master dataset' from the other Datasets that are based on your multitude of SQL scripts first.The master dataset should contain the data to be presented in it's most simplistic form, i.e. in a simple list format. Finally, go to the toolbar in SSRS and drag a 'Matrix' into the report. A Matrix table acts similar to a pivot table in Excel or a CrossTab query in Access that will display whatever's in the Dataset.

Upvotes: 0

Buddy Lee
Buddy Lee

Reputation: 376

The only way I've been able to get this to work sofar is:

Each report has 2 datasets.

  1. ReportData
  2. DataHeaders

The "DataHeaders" need to have the proper name of the datafields in "ReportData". Be careful since SSRS replaces blanks and special characters with "_"

Now, create a table (or matrix) and drag the DataHeaders as the Columns of your report. (This should be a grouped column). If you run it at this point, you'll see all your columns without any data. Now comes the magic:

Create another report that takes a "DataField" parameter. Create another table or matrix within this report and set it's dataset property to be "ReportData". In the DATA cell for the table, set it to the expression =Fields(Parameters!DataField.Value).Value

Now go back to your first report. Right click and insert a subreport. Right click on the subreport and select "Subreport Properties". Under general, select the second report you created to be used as the subreport. Under parameters, select the DataField parameter and set its value to something like =Fields!DataField.Value

In my case I did some formatting in this expression to fix the above mentioned issue with spaces and special characters, since my stored procedure was initially used in ASP.NET and this was just a proof of concept.

Also in my experience the performance isn't great. In fact it was kinda slow, though I haven't had a chance to switch it to use a shared dataset, which I suspect would help a bit. Please let me know if you find a better solution.

Upvotes: 2

Joel Beckham
Joel Beckham

Reputation: 18664

I have not found a way to do this completely dynamically. Here is a similar question with some possible solutions: How do i represent an unknown number of columns in SSRS?

Upvotes: 0

Related Questions