Dataset as source for another dataset

  • I have been working with SQL for almost 20 years, but I’m somewhat of a novice when it comes to SSRS.

    Is it possible to use a dataset as a source for another dataset?  I am trying to call a sproc to populate a dataset and then filter on it using another dataset.  So the first dataset would populate the second and the second would include a filter.  My end goal is to have the procedure run once, instead of again and again for each value I am filtering on.

    As an alternative, I also tried entering each column as an expression where the formula was simply firstdataset.column. That was accepted but a query or proc is still required. If the dataset is all expressions, how do I get around this requirement?

    Or is there a better way to achieve what I need, please provide your suggestions.

    I am developing in VS2019 (solution with rdl files).

    Thanks!

  • Hi!  Would you mind talking me through an example of what kind of data you're looking at/filtering?  (I suspect that the answer may depend on the size of the datasets involved.)  🙂

    "The Bible tells us to love our neighbors, and also to love our enemies; probably because they are generally the same people." G. K. Chesterton

  • Mostly integers and strings.  The procedure returns about a hundred records and filtered, only 6.

  • You can go to the FILTERS section in SSRS and add additional filters for your report. (And add parameters etc). So the "raw" dataset could pull say 100 records, and then you could use parameters to filter that down further, so the report is showing 100 or fewer records.

     

  • If I use a parameter, then the proc will run for each value.  I am looking to run it once in full and filter on the dataset produced.

  • Not necessarily (if I understand correctly).  If you set up your main stored proc driven dataset as a shared dataset and cache it (daily perhaps), then inside your RDL you could filter that dataset's results by a parameter (and that parameter could be populated based on a secondary dataset.)  In this instance you're not passing the parameter through to the the SQL using the stored proc, just filtering it.

    "The Bible tells us to love our neighbors, and also to love our enemies; probably because they are generally the same people." G. K. Chesterton

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

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