ssrs 2010 shared dataset

  • wendy elizabeth

    SSCoach

    Points: 16909

    In an existing ssrs 2010 report there is a shared dataset that I would like to know how it works. Here are are my questions:
    1. In the report there is a shared dataset called test. The sql that is contained within this dataset is appears to have the sql
       that is used within the report. When I compare the shared dataset called test that is contained on the right side of visual studio ide, the other datasets that are contained on the left side of the visual studio ide, what are the datasets on different sides of the visual studio 2010 ide? Is there a way to place the datasets all on the left side of the ide?
     2. What is the benefit of using a shared dataset? Can other reports access the shared dataset and if so how?
     3. This report uses a lookup ssrs call with the shared dataset. Can you tell me what this accomplishes?
     4. When using the visual studio management studio, it seems that the shared data is setup in a specific database. Is this possible? If so, can you show me/tell me how to access  the shared dataset in the specific database?

  • pietlinden

    SSC Guru

    Points: 62614

    And you've been using SSRS for how long now?
    1. datasets on the RIGHT are for the PROJECT. datasets on the LEFT are for the report you're working on. If you're using a shared dataset in your report, it's just basically creating a reference/pointer back to the shared dataset. So if you were to change something in the shared dataset, it would show up in your report dataset.
    Shared datasets are at PROJECT level. ANY report can use them.

  • wendy elizabeth

    SSCoach

    Points: 16909

    How do you change the shared datasets so that the change shows up? Where is the shared dataset stored and how do you access the shared dataset? Can shared datasets get loaded with data in advance like views do? Can some kind of a shared dataset get reloaded with data from some scheduled job that is executed?
    I have never heard of a shared dataset for ssrs reports since I have basically been working with ssrs 2008 reports.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88174

    wendy elizabeth - Wednesday, February 27, 2019 8:24 AM

    How do you change the shared datasets so that the change shows up? Where is the shared dataset stored and how do you access the shared dataset? Can shared datasets get loaded with data in advance like views do? Can some kind of a shared dataset get reloaded with data from some scheduled job that is executed?
    I have never heard of a shared dataset for ssrs reports since I have basically been working with ssrs 2008 reports.

    Shared DataSets will be deployed to the server in whatever folder you have defined for that project, but generally will be deployed to the Datasets folder.  To use a shared dataset in your project - you have to add it to your project so any report datasets you add to a report can reference the shared dataset.

    Views do not load data - but shared datasets can be setup with a cache schedule and refresh schedule once deployed.  There are many options - depending on how you want to use that shared dataset.  In some cases, you would refresh the shared dataset on a daily basis and all downstream reports would work from the cached data - in other cases you would setup the shared dataset to refresh when called...again, all depends on what the shared dataset was designed for...

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • doug.brown

    SSCertifiable

    Points: 5528

    I have mainly used shared datasets where I have common lookups for parameters in reports (location, section, accounting category, etc.).  Rather than make a new dataset every time I have a report with a store parameter, I can just assign the shared Store dataset as the lookup for the parameter.  I've never tried using a shared dataset as the actual dataset for the report, though.

  • Michael L John

    One Orange Chip

    Points: 25797

    wendy elizabeth - Tuesday, February 26, 2019 9:10 PM

     2. What is the benefit of using a shared dataset? Can other reports access the shared dataset and if so how?

    As a previous poster stated, creating shared data sets for parameters that are used in many reports is the most common usage of them. For example, how many reports contain a "Client List" drop down?  Write once, use many.
    A less common usage of a shared dataset, ot at least what we have done, is that there are a number of reports that are a series of sub-reports. 
    The first section may be the clients demographics.  That would be a shared dataset. The subsequent sections, which are sub-reports, are specific to the client.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • wendy elizabeth

    SSCoach

    Points: 16909

    Since this is the first time that I have even seen shared datasets for ssrs reports, how can I tell if any existing data set is refreshed on a daily basis or some other schedule and/or whenn the dataset is called by the report?

  • pietlinden

    SSC Guru

    Points: 62614

    Maybe this article will help?
    http://www.sqlblog.nl/dynamic-refresh-ssrs-cache-using-sql/

    (Doesn't directly answer your question, but explains how to refresh the cache on a schedule)

  • wendy elizabeth

    SSCoach

    Points: 16909

    Thank you very much!
    That article helps a lot! However can you tell how I can tell if the report is cached or updated by the report?
    How do you deploy the shared dataset? is there a difference between a deploy where the shared dataset is called by the report or by some kind of a schedule?
    T

  • pietlinden

    SSC Guru

    Points: 62614

    That article helps a lot! However can you tell how I can tell if the report is cached or updated by the report?
    How do you deploy the shared dataset? is there a difference between a deploy where the shared dataset is called by the report or by some kind of a schedule?

    If the data is cached, the report runs a LOT faster. (Because the results are being read from memory, instead of reading from a drive).
    Depending on how often you set the cache to expire, you can control how frequently a report will use a cached dataset or rerun the stored procedure on whatever server it's on and then store the result in the cache. You could then set the stored procedure to run on a schedule

    Here's a good article on SSRS report data caching... Complete with screenshots - probably a worthwhile read:
    https://www.mssqltips.com/sqlservertip/1919/how-to-enable-caching-in-sql-server-reporting-services-ssrs/

Viewing 10 posts - 1 through 10 (of 10 total)

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