Reuse Data Set by two tablix, but the script needs a param (from tablix)
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!
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!
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
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
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