Search code examples
sql-serverreporting-servicesssrs-2008ssrs-2012ssrs-tablix

SSRS Performance with multiple datasets

I have two major questions on the SSRS performance. I am in the process of consolidating 10 SSRS report (10 summary RDLs and 10 Detail RDLs with each report having ~4 tablix). Each report hits a stored procedure to get the results. Now we have consolidated all tablixs into one RDL and have created a new parameter where user can select which report he wants to open and accordingly that tablix is displayed through visibility property. Now in terms of performance I want to understand whats better. Questions Below:

  1. After consolidation, In one RDL we have 10 datasets hitting 10 different procedure. Should I consolidate all the procedures into one and run the section of the procedure as per user selection of report from parameter using IF Else conditions. Example User selects report A then in the procedure IF @report = A --Code for A-- ELSE IF @report = B --Code for B--. If I do this, will I be able to maintain cache for each of the 10 reports.?

  2. For the 10 detail RDLs which again has its own 10 different detail stored procedures. Is it a good idea to consolidate all the detail Tablixs into one and show/hide the tablix as per the user selection of the report from report parameter. Basically, I will pass the report name as a parameter through Action property and then show/hide tablixs with IIF conditions. OR should I keep 10 different RDLs for detail reports. I have that option open. What will be better for performance.

Solution

  • what I would do is - assuming the 10 RDL are running 10 different stored procedures, assign each stored procedure a hidden parameter. lets call it @report_number (for example). So for report one, in the stored procedure, add a where clause to include and @report_number = 1 this should technically stop the stored procedure running as soon as it sees a different number passed (say 2 for report 2).. so on and so forth..

    The @report_number is assigned in a separate dataset.
    e.g.

    report1 - value = 1

    report2 - value = 2

    .

    .

    .

    report10 - value = 10

    so when user runs report 1, then the value 1 for @report_number is passed to that dataset.. the rest of the datasets shouldn't run the procedure associated with it as they won't have the expected value in the where clause.

    Hope that made sense.