June 12, 2017 at 4:33 pm
All,
I'm upgrading all my SSRS 2005 reports to SSRS 2014 and finding that the order of execution of my datasets for both parent and sub report differs between these two versions. In 2005 we have relied upon one dataset to populate the data to a table and then subsequent datasets (in order of appearance in VS) would run one after another. Also, the subsequent datasets would push data staged from populate data into a sub report. By bringing the RDLs straight over from a 2005 environment into a 2014 development environment, all the datasets run in parrallel at the parent report and the sub report data sets. So I was enlightened to the data source property titled, "Use single transaction when processing the queries" to assist with this and to force the datasets to run as a single transaction (since they use the same datasource). However, the sub reports still ran in their own transaction. So I enabled the Merge Transaction property of the sub report on the parent report to keep sub report transactions with the parent report transaction. But this still did not solve my issue.
When setting the Use single transaction in the parent report and the sub report, they data sets were executed out of order. The only way to get the data sets to execute in order was to set the Use single transaction in the sub report only and leave the parent report data source unchecked. This seems backwards to me.
So in my VS 2013 development environment for SSRS 2014, I have the execution order working by setting NOT setting the use single transaction property in my parent data source, setting the Merge transactions in the sub report property of the parent report, and setting the use single transaction in the sub report data source. This all works in Visual Studio....but when we deploy to report manager, everything executes out of order again. Is there a config setting for a data source in Report Manager that needs to be set manually for data sets to use a single transaction? What am I missing?
Thanks for any guideance you all may have.
June 19, 2017 at 10:27 am
I'm guessing you never got an answer because designing an SSRS report to be dependent on execution order of data sources is a particularly bad idea, because it suggests that some kind of data preparation is required to be able to run the report at all, and as is almost always the case, that's not a particularly good scenario for any kind of reporting. I would ask if there's any particular reason NOT to pre-compute that portion of things in some kind of nightly job, so that the pre-computed results are sitting in a table somewhere, ready to go, and order of execution ceases to matter. I also always wonder why the use of sub-reports is so common, when it's really not a very good idea. If it's; for all practical purposes; an entirely separate report (even if it's related in some way); then why is it a sub-report to begin with? Mind you, I'm not looking to start a flame war over reporting perspectives, but I'm pretty sure the human brain can only consume so much level of detail before overload occurs. Sub-Reports contribute mightily to that kind of information overload.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2017 at 9:36 pm
Thanks for the reply Steve. The nature of the business is to collect data between a 9am and 1PM each day and then generate reports out to customers using the same day data from 1PM to 3PM. I guess my concern is this worked perfectly in 2005. I think if anything we'll still be generating the data and storing in a table but the rendering part of the ssrs report(s) will be 15 or so minutes after the data gen. Thanks again.
June 20, 2017 at 7:30 am
michael_baker - Monday, June 19, 2017 9:36 PMThanks for the reply Steve. The nature of the business is to collect data between a 9am and 1PM each day and then generate reports out to customers using the same day data from 1PM to 3PM. I guess my concern is this worked perfectly in 2005. I think if anything we'll still be generating the data and storing in a table but the rendering part of the ssrs report(s) will be 15 or so minutes after the data gen. Thanks again.
I hear you on the "worked perfectly in 2005", but as we all know, technology changes. And, even in 2005, the idea of creating a dependency on execution order wasn't a good choice. Bad choices in information technology have a nasty habit of coming back around and biting you in the backside.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply