We help IT Professionals succeed at work.

Reuse Data Set by two tablix, but the script needs a param (from tablix)

ToddBeaulieu
ToddBeaulieu asked
on
Hello. I'm new to SSRS.

I have a data set and a tablix. Works fine.

I want another tablix that uses the same data set, but the SQL behind it needs to know which tablix it's feeding. This is because the query is almost the same, but slightly different. I was hoping to be able to pass a parameter to it, but I can't find a way to populate the data set parameter from the tablix.

I'm hoping to avoid copying the query and then modifying it. This would be a maintenance PITA.

And I can't simply filter off it, because the query really needs to know whether it's including certain data as it processes.

Thanks!
Comment
Watch Question

Hi,

Is the one query a superset of columns of the other, or does this affect the selection criteria as well?
Is the query held in a stored procedure or view - can't understand why you would need a maintenance change if the query was embedded in the report (so I assume it's not).

Tim

Author

Commented:
The query is in the report. I need to run it twice. What will change is the selection criteria. The report has a lot of aggregate expressions which of course require the appropriate target data to work with. There is just a slight difference between the runs. It will drive me nuts to copy and paste and then change 2 lines.

Finally, maintenance happens when you need to change logic for any reason.where the code lives wouldn't influence the need for maintenance, so I don't follow that line of thinking.

Imagine a query that uses a variable int the selection logic. When running manually I can change the variable and use the same script just fine. I'd like to figure out how to plug that into the report.

Thank you.
ok, sorry - misunderstood the maintenance problem.

Anyway, I don't think you'll be able to do this. The processing sequence in SSRS is to set the parameters, execute the queries and then render the report. You won't be able to influence this or force execution of the dataset a second time with different parameters. I've tried setting stuff in code, but the queries execute before any of the code invocations (from within textboxes) are actioned.

If you can, move the query to a stored procedure to avoid dupolicating the bulk of the sql in the report. You'll still have to create a second dataset in the report - and if you have a lot of parameters I know this can be painful. I tend to cut and paste the dataset definition block in the xml directly in these cases (saving a copy first!) - it can save a lot of time.

Tim

Author

Commented:
Thanks Tim. I have this nagging feeling that there's some form of trickery that can accomplish this, but I just don't have time to keep messing with it.

I ended up copying the script and setting the variable at the top of the script. At least now I can use the same script in both and if replacing it, simply set the variable accordingly in each.

I see that you can set a ds parameter to a report variable. I see that you can add code to the report in the form of functions. I wonder if there's some way to get call a function as part of the tablix population (event), which would then set the variable, which the ds would use. Then, when the second tablix calls for data, it would repeat those steps, setting the variable to the new value. If I had to bet a dollar, though, I'd say you're right ... can't be done. I wonder if I could have done this through subreports and if each one could use the same ds and feed a parameter to it?
Hi - a subreport is a possibility, but as soon as there's  any significant deveiation between the two queries you might find you have to duplicate the query anyway...
The other approach is exactly what I have tried previously and you can't render a tablix, set a variable in code and then attempt to get the ds refreshed using the updated variable.

Tim