Error: Sub report could not be shown
-
Hi Guys,
I am so stuck, and have been for several days now.
I am running SQL Server 2014, 2014.0120.6372.01
I have a report which has many sub reports within, the sub reports are all the same report but I pass in a different parameter for each one.
Sub Report 1 = Parameters!BusinessArea.Value(0)
Sub Report 2 = Parameters!BusinessArea.Value(1)
Sub Report 3 = Parameters!BusinessArea.Value(2)
etc.
When I run the report on the report server, it runs successfully and I get a different page for each business area I have selected, but when I run the job as a weekly subscription for the same business areas, only the first four pages render and for the remainder I get Error: Sub report could not be shown.
This report was working just fine up until a few weeks back, the subscriptions work just fine on our pre-production server, we only run into these issues on our production server.
I have double checked all of my parameters and I am confident they are correct
I have attempted to rebuild the report and add the sub reports again
I checked the reporting services logs and I can see the following error message for several of the data sets:
ERROR: An exception has occurred in data set 'FleetData'.
Details: Query execution failed for dataset 'FleetData'.
Server: The operation was cancelled by the user.
It is not clear to me why this runs when I execute the report manually, however it fails when it runs as part of a subscription.
Any help you can offer would be greatly appreciated.
-
so if you use the report, it works, but when run as the automation account, it fails, right?
sounds like permissions.
check the data source for the sub report. it should be using a shared data source, and the account used for the data source needs access to the data for the underlying FleetData.
in the image below, for your setup it is either as shown, or maybe the radio button for "Windows integrated security" is checked, one or the other most likely.
When it is set like that, you can see how it will pass the calling user credential, and not use a specific user? the data source needs to be configured as "Credentials stored securely in the report server", so that it saves specific credentials to be used for the data source, and and use that for the report.
">Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell,
Thanks for your reply, the sub report is the same report multiple times (just with a different parameter each time), so it doesn't make sense why it would work for some but not others.
The data source in the sub report isn't a shared data source, could this be the issue?
Here is how the service account has been set up:
-
Just to add, I came across the following page https://stackoverflow.com/questions/33385147/sub-report-could-not-be-shown-sub-report-works-separately-not-with-main-repor , which is very similar to what I'm doing and also the same error message.
It appears that this may be a known microsoft bug, I'm wondering if this is also the cause of my issue.
-
the stack overflow article mentions bugs in SSRS 2008R2(and maybe SSRS 2012), so the first part does not appear to apply if you are running SSRS 2014.
The second part, where the parameter data types being passed do not match is something you'd have to review, but if it works in one place but not the other, I'd still lean towards datasource permissions over a misconfigured parameter set.
what does your data source look like? is it using a shared data source, and cached credentials?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yes my data source is caching the credentials, I have posted it above
-
I opened the same rdl files using Power BI Report Builder, published them to the service and set up the subscriptions from there. They are now working fine. I guess this points to an issue with Reporting Services.
">
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply