February 11, 2019 at 9:44 am
Hello - This post/question is for anyone who is familiar with SSRS and Tableau. In Tableau, you can create a "quick filter" that essentially captures the distinct values of a field within a dataset. That "quick filter" can then act as a parameter to further define the display/output. I'm trying to figure out if SSRS can do something similar. (Come to think of it, I think Power BI has a similar feature called "Slicers")
For example, lets say I have a dataset defined in SSRS called "Orders" that is produced by a stored proc which requires only a date parameter (e.g., "Order Date") and this stored procedure returns a list of orders that contains Products, Categories, and Customers (i.e., lots of details) for the specified "Order Date" parameter. Is there a way to create another parameter in SSRS that allows a user to further define the report results by displaying a distinct list of Products as a secondary parameter? I'm looking to do something similar to a cascading parameter, but the catch is that I actually want the Products parameter to be limited to the distinct values captured in the initial dataset called "Orders".
What I'm not looking for is a cascading parameter that's written like "Select distinct Product from Dim_Products" -- This will retrieve all products from dim_products. I'm looking to retrieve only the products captured within the dataset "Orders" that is based on stored proc. So, I'm wondering if a dependent parameter can be defined similar to "Select distinct Product from <Orders dataset>"? The goal is to retrieve a distinct list of Products within the context of the "Orders" dataset.
I've developed a work around for a Products parameter whereby the param query is a stripped down version of the "Orders" stored proc such that it queries "Select distinct Product from <various table joins used in Orders dataset> where order_date = @OrderDate". Although this returns the distinct list of Products for that order date, I don't like that it's mimicking the "Orders" stored proc in terms of table joins & other limiting criteria. I feel as if the report is taking two trips to SQL server rather than making just one initial trip to retrieve relevant data and then further filter by product within the report.
To be honest, I may be overlooking something obvious -- it's been a couple of years since I've developed a SSRS; been doing more with Tableau. But, a recent request came my way and now I'm dusting off the SSRS cobwebs.
Thanks in advance,
Pete
February 13, 2019 at 12:50 pm
Nope. You'll need the cascading parameter set up. SSRS doesn't hold the query results somewhere, and thus has nowhere it can go but back to the server to get them, so you end up needing the cascading parameter. Understand that Tableau doesn't become better simply because it DOES do the holding on to the data. It just does things differently. How would you get Tableau to let go of data that is out of date? That's the disadvantage of such a hold... Mind you ... SSRS only "sort of" does that, in that it stores the data retrieved in a .rdl.data file of the same name as the report .rdl file, but it doesn't work the same way at all. Sometimes with SSRS, you definitely need to delete that file to stop SSRS from playing that game.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply