Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Shared dataset for subreports

Answered Shared dataset for subreports

  • Friday, December 14, 2012 5:03 AM
     
     
    Sign In to Vote
    0

    Hi there

    i have created a shared dataset in my SQL 2008 R2 report project.

    I have one main report which has 10 subreports on it.  Each of these subreports calls the same stored procedure.

    I thought that the shared dataset would mean that when i run my main report, the dataset only calls the database once.   However this does not seem to be the case.  Am I using shared datasets incorrectly?  Is there a way I can get it to only be called once still using subreports?  Otherwise what is the point of the shared dataset?

    Thanks in advance

     

All Replies

  • Friday, December 14, 2012 5:25 AM
     
     Answered
    Sign In to Vote
    0

    It seems i missed to turn on caching for the dataset in report manager.  Once I did that, the stored proc got called a lot less

    • Marked As Answer by ReportCreator Friday, December 14, 2012 5:25 AM
    •  
     
  • Wednesday, December 26, 2012 7:12 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi ClareMoore,

    When we run the main report, each of the subreport will run separately. That way, the stored procedure will be called when each subreport runs. If you want to reduce the number of times that the stored procedure runs agains the data source, we can enable the dataset cache as you have done. After caching is enabled, the query results for a shared dataset are copied to the cache on first use. While the query results for a specific parameter combination are in the cache, each report that is launched for processing and that includes a reference to the shared dataset with those parameter values will use the cached data. If the shared dataset has parameters, each combination of parameters creates a new entry in the cache.

    Reference:
    Cache Shared Datasets (SSRS)

    Hope this helps. Merry Christmas.

    Regards,

    Mike Yin
    TechNet Community Support