SQL Reporting Services 2005 Updating a table using data from the report!
Posted on 2006-10-24
I don't think this can be done in RS, but I thought I'd see if anyone can come up with a good idea.
Basically I have alot of data that is created in multiple stored procedures and runs in a data driven subscription passing in different ids through each run.
The sprocs use temp databases to get the data in the form needed and returns to RS.
However, in an ideal world what I want is to sum up the totals in these various sprocs which RS does and then update a table in SQL at the end of the report with the sums of the totals from the sprocs that RS has summed up.
The srpocs all take a while to process all of them about a couple of mins, so I can;'t really run them once in SQL server to get the data and do the updates then again in RS to get the data for the report, due to the number of times the data driven sub needs to run.
RS won't update the rows in the table because it won't allow me to use field names in parameters, ie in my update statement say I have a parameter called @total it won't allow me to assign @total from another query made up of say sum(total1) + sum(total2) + sum(total3) etc, and report parameters work the same way.
I presume this just simply can't be done in RS unless someone can tell me otherwise, so I think I'll prob have to stop using temp tables and get SQL Server to do all the work in tables, then get RS to read from them, then somehow clear them down before the next ones runs in the data driven subscription.
Anyone any ideas?