3

Maybe rookie question but: Is there a posibility to force a SSRS report to run a specific sproc or script in the data source before loadin any data source / data set cache etc ...? In order to guarantee that no matter in which order the datasets are loaded into cache the effects of the sproc are in place?

Dvintila
  • 212
  • 3
  • 13
  • Are there returns from this sproc that you need for your parameters or something? – SFrejofsky Feb 26 '15 at 12:53
  • No return parameters. It just updates a table that constitues a dataset in the report. That table is updated (by the sproc) by various criteria and I want to be sure that the report display up to date info hence the table needs to be up to date hance the sproc must run (I don't want to gather the data directly ... I Mean to make the report do what the sproc is doing) – Dvintila Feb 26 '15 at 13:09
  • Its kind of a hacky way to do it but you could set that sproc as the first internal parameter for the report. The sproc would run automatically any time the report would load. I would not recommend it if it is a long running sproc as it will kill performance. This will also pose a potential for lost or deadlocked information if the report is loaded multiple times and the load times overlap. Personally I would rethink the structure of your process if it relies so heavily on this sproc. – SFrejofsky Feb 26 '15 at 13:32
  • setting it as an internal parameter would execute it? – Dvintila Feb 26 '15 at 13:59
  • Have the report call a wrapper proc that first calls your update proc, and then calls the proc that returns the dataset for the report. – Tab Alleman Feb 26 '15 at 15:55
  • Setting a sproc as an internal parameter will execute it as it is expecting the return to be used as a feed for another parameter. You would set the parameters default value to generate from query and state exec "SPROC" Like I said before its hacky at best – SFrejofsky Feb 26 '15 at 16:11
  • Another idea just hit me. you can serialize you datasets in their execution and execute that sproc as the first step in the first dataset that generates. This is similar to what Tab Alleman said but the flow is controlled by ssrs rather than an external wrapper – SFrejofsky Feb 26 '15 at 16:16
  • dataset serialize and or wrapper sproc does the trick ... thank you guys for your help – Dvintila Feb 26 '15 at 17:05

0 Answers0