SSRS Subscription Paramter for Second dataset not updating

0
votes

I'm running into an issue with an SSRS subscription report that I built. Basically I have 2 datasets and 1 parameter that gets fed to both of them called @ReportDate.

enter image description here

enter image description here

@ReportDate has a default value =Today(). When the report runs every morning and gets emailed out by the subscription, dataset1 always gets updated information with today's date and works fine, however dataset2 always returns data for the previous date that the report successfully ran. So it's like the parameter isn't feeding the updated value through to that dataset, which is odd because both datasets use the same parameter. When I go to troubleshoot the problem and run the report dataset2 will then come back with data for todays date so I can't replicate the issue. Am I doing something wrong? I'm using SSRS 2008 r2. Thanks for your help.

Edit: Found the problem. Both datasets shared the same datasource which means the datasets run in parallel, not in the order which I see in report builder. Dataset2's data is dependent on a table which get's populated during the execution of dataset1. Dataset2 was finishing before dataset1, thus it wasn't picking up current days data. I updated a setting on the datasource to make it so that the datasets run 1 at a time in the order in which they're shown in report builder. The setting I changed was checking a check box in the datasource properties called "Use single transaction when processing the queries".

1
reporting-servicesreportingssrs-2008-r2
do you have the where clause in your parameter set to look a day back like where date = dateadd(dd, -1, @DateParameter) ? – NewGuy
There is some error checking at the beginning of dataset1 that references the parameter, but outside of that the where clause that actually pulls the data in both datasets is identical. – BrandedSwordsMan
Are both datasets using the same data source? – StevenWhite
Yes, both datasets use the same datasource. – BrandedSwordsMan
The data could be cached. Try deleting the .data file in the same folder with your .rdl file and then run it again. – StevenWhite

1 Answers

1
votes

Found the problem. Both datasets shared the same datasource which means the datasets run in parallel, not in the order which I see in report builder. Dataset2's data is dependent on a table which get's populated during the execution of dataset1. Dataset2 was finishing before dataset1, thus it wasn't picking up current days data. I updated a setting on the datasource to make it so that the datasets run 1 at a time in the order in which they're shown in report builder. The setting I changed was checking a check box in the datasource properties called "Use single transaction when processing the queries".

Updating DataSource