Software Engineer
asked on
SSRS: Subreports Are One Day Behind
Hello:
In SQL Reporting Services (SSRS), the main report containing data from two subreports is displaying data accurately.
But, I'm curious as to why the data displayed upon generating the subreports "by themselves" is being pulled one day behind the data displayed on the main report.
Again, the data in the main report and pulled from the subreports is showing as accurate. But, the subreports "by themselves" are one day behind.
What are some things to check, to determine why the timing in the main report is correct as compared to the subreports?
Thanks!
John
In SQL Reporting Services (SSRS), the main report containing data from two subreports is displaying data accurately.
But, I'm curious as to why the data displayed upon generating the subreports "by themselves" is being pulled one day behind the data displayed on the main report.
Again, the data in the main report and pulled from the subreports is showing as accurate. But, the subreports "by themselves" are one day behind.
What are some things to check, to determine why the timing in the main report is correct as compared to the subreports?
Thanks!
John
Pushpakumara Mahagamage
Do you have separate data source I mean D-1 replication for Sub reports. Please check data source of the subreports with the issue
ASKER
Hi Pushpakumara:
The main report and the subreports are using the same data source.
What else can I check?
John
The main report and the subreports are using the same data source.
What else can I check?
John
Hi John,
Then check the report SP,
Then check the report SP,
ASKER
Does "SP" = stored procedure"? The main report and the subreports use the same stored procedure and their makeup is the same.
Yes stored procedure,
How do you call the report from application side.
Do you passing the date parameter . Check the start date settings for Main report and subreports (.rdl)
what happend if you check the report from the SSRS report portal
How do you call the report from application side.
Do you passing the date parameter . Check the start date settings for Main report and subreports (.rdl)
what happend if you check the report from the SSRS report portal
ASKER
The start date and end date parameter fields, as well as the datasets for those fields, in both the main report and the two subreports are all configured the same.
The same issue occurs in the portal, as well as in Visual Studio Data Tools.
Depending on the time of day, the reports will match. If I run the reports early in the morning, the discrepancies appear. But, if I run the reports at any other time of day, they match.
There must be some sort of "-1" date calculation that must be running somewhere, but I don't know.
The same issue occurs in the portal, as well as in Visual Studio Data Tools.
Depending on the time of day, the reports will match. If I run the reports early in the morning, the discrepancies appear. But, if I run the reports at any other time of day, they match.
There must be some sort of "-1" date calculation that must be running somewhere, but I don't know.
Have you try to catch the query at target SQL Server. run Sql server profile with neccessory filters to catch your main report and sub report. and then compare queries . I'm sure you will be able to find the issue.
ASKER
Hi Pushpakumara:
Thank you, for the great advice! :)
If the reports use the same query, then how would I configure the query in SQL Server Management Studio to show differences?
John
Thank you, for the great advice! :)
If the reports use the same query, then how would I configure the query in SQL Server Management Studio to show differences?
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The data set of the main report and of each of the two subreports is based on a stored procedure and not a query.
How would I find a difference, in that case?
Thanks, again, Pushpakumara!
John
How would I find a difference, in that case?
Thanks, again, Pushpakumara!
John
Normally we debug, when we couldn’t find the bug or issue, but in SSRS you can’t just debug. So when we are facing issue such as you. We run sql server profiler or execute sp_whoisactive in SSMS to catch the exact query executing in database. Then you can catch the culprit
I would check all PCs and servers for correct date on the machines.