Post reply

Contents of Shared Data set into ##GlobalTable so that it can be shared across SSRS reports?

  • July 6, 2017 at 8:25 am

    #343451

    Hi

    I've developed a date driven Multi-Statement Table Valued Function in SQL Server 2012, which returns around 46k rows and takes ~10 minutes to generate

    The purpose of the function (in a nutshell) is to clean the source data and apply business rules.

    I want to achieve the following within SSRS:

    1. Load the output of the function (ran from the a shared dataset) into a temporary table called  ##MovementAnalysisYTD  so it only has to run once and can then be shared globally in SSRS.

    2. Create a dataset (A) at report level which will access the ##MovementAnalysisYTD  (this data set will be filtered to only allow certain partition_types), these partition types allow me to know what that partition contains.

    3. Create a new dataset (B) which is based on the excellent work of Steve and Jacob found on this thread : Dataset-Aggregate-data-contained-in-different-groups, using data that has come from the ##MovementAnalysisYTD  (again filtered by partition_type)

    is this possible?
    if not, can you please suggest an alternative approach
    any questions - please ask

    Thanks
    Andy

  • July 6, 2017 at 8:35 am

    #1949729

    My first question is, why do you want to store this in a Global Temporary Table? As the name suggest, these are temporary, they're not designed to be static. If you're going to be hinging reports off the table, why not create a proper table to store the data in?

    If you really are hung up on using a temporary table, will this be rebuilt every time the service restarts? If not, when? If those reports run before you have, the table isn't going to exist and your report will fail.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • July 6, 2017 at 8:45 am

    #1949732

    Thom A - Thursday, July 6, 2017 8:35 AM

    My first question is, why do you want to store this in a Global Temporary Table? As the name suggest, these are temporary, they're not designed to be static. If you're going to be hinging reports off the table, why not create a proper table to store the data in?

    If you really are hung up on using a temporary table, will this be rebuilt every time the service restarts? If not, when? If those reports run before you have, the table isn't going to exist and your report will fail.

    Hi Thom,

    Thanks for your reply - in all honesty - I didn't know that you could create and populate a proper table within SSRS.

    I think a proper table would be a much better approach.

    Thanks
    Andy

  • July 6, 2017 at 9:02 am

    #1949737

    andyc76 - Thursday, July 6, 2017 8:45 AM

    Hi Thom,

    Thanks for your reply - in all honesty - I didn't know that you could create and populate a proper table within SSRS.

    I think a proper table would be a much better approach.

    Thanks
    Andy

    I've therefore misunderstood your question then.

    I think you might be misunderstanding what a dataset is in terms of SSRS. A dataset is a result set which is available on a single report. A shared dataset is the same again, but available to all reports in that project. You don't use SSRS to build a table in a dataset, you simply need to have a resultset returned.

    Using a dataset to store the results into a (temporary) table, and then reading from that table will not give you any benefits; infact, it will slow things down. That's because you're adding extra overheads; firstly to store the data (in tempdb), and then to read it back again.

    If you want to a dataset available to multiple reports, just return the data the data, don't insert it and then select it out. as part of the dataset process.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply