August 18, 2022 at 10:57 pm
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!
August 18, 2022 at 11:57 pm
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.) 🙂
"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me
August 19, 2022 at 2:03 am
Mostly integers and strings. The procedure returns about a hundred records and filtered, only 6.
August 19, 2022 at 3:38 am
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.
August 19, 2022 at 4:28 am
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.
August 19, 2022 at 1:12 pm
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.
"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply