SSRS Subreport not showing

[jdc136]

I am using Microsoft SQL Server Reporting Services Version 15.0.1103.12 (i.e. SQL Server 2019 Reporting Services). I am creating a report using the standalone Report Builder (v15.0.20283.0). I am querying an Oracle DB data source.

I have a report that I would like to create that is a collation of other reports, i.e. a collation of sub-reports. However, I am running into an issue where the sub-report(s) do not show. There is no error or any indication that there is a problem, the sub-report simply doesn't show. Each of the sub-reports is basically a Tablix, and they do have a NoRowsMessage populated, so even if no rows are returned, a message should display (and a message does display when opening the sub-report on its own).

What I would like to do, ideally, is populate the parameter of each sub-report with a parameter in the main report. So what I started with is a sub-report directly on the Body surface, and configured the subreport parameter "Year" to be associated with the main report parameter "Year".

This seems to work. However, when I add an additional subreport, nothing displays. No error message(s), no subreport(s) - nothing.

Basically, when I add a single subreport, it seems to work. Then, I add another subreport, and I get the error message below and if I refresh, no subreports display.

Exception of type 'Microsoft.ReportingServices.ReportProcessing.ReportProcessing+DataCacheUnavailableException' was thrown. The data cache for the report edit session is not valid. Rerun the report to create a new and valid data cache. (rsDataCacheMismatch)

Sometimes, I won't see a subreport when there is only one subreport as well, but I assume that is some problem with the cache after having added and then removed a second subreport. On the other hand, this does occasionally occur even when I refresh the report and I assume that clears the cache.

I've gone through all the iterations / implementations I can think of, and which I could find through a Google search, but I'm not having any luck. It seems like most of the information out there is for older versions of SQL Server Reporting Services as well, so I am left unsure why this is a problem. Any advice or insight would be greatly appreciated!

[Anonymous]

Hi, @jdc136 

I wish you all the best. Previously we have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li

[Anonymous]

Hi, @jdc136 

Based on your description, I created the corresponding dataset and tablix:

Associate Year to Sub1 and Sub2:

Try previewing the report:

With regard to your later question, are you using a data source from a shared dataset?

Cache reports - SQL Server Reporting Services (SSRS) | Microsoft Learn

Cache shared datasets - SQL Server Reporting Services (SSRS) | Microsoft Learn

Try setting a timeout value for it:

Set time-out values for report and shared dataset processing in Reporting Services - SQL Server Repo...

To avoid caching issues, you can connect directly to your data source in a report and then create a report based on it.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdc136]

Hi @Anonymous thank you so much for replying to my issue!

First, you're not exactly reproducing what I have. You are using datasets within the main report whereas I am referencing wholly different reports, as described in the documentation for subreports.

Second, I played around a bit more, and it seems that there is an issue but I can work around it. I have two subreports:

@Year will default to the year of the current day, so for today that would be 2025. My subreports for today have associated data, and the subreports appear as expected:

If I change it to 2024, which is the year that was populating when I was testing this the other day, I get nothing:

However, if I click "View Report" again I get the NoRowsMessage for each subreport:

So the problem seems to lie in that NoRowsMessage does not appear for a subreport initially, when there is more than one subreport. I seem to be able to reproduce this consistently. Is this a known issue? Are you able to reproduce this behavior?

[Anonymous]

Hi, @jdc136 

Thank you very much for this information. First, I reproduced the error in your post:

Exception of type 'Microsoft.ReportingServices.ReportProcessing.ReportProcessing+DataCacheUnavailableException' was thrown.
----------------------------
The data cache for the report edit session is not valid. Rerun the report to create a new and valid data cache. (rsDataCacheMismatch)

Dynamic parameter :

I guess you applied this Dynamic parameter in the filter panel of the Main dataset, causing this issue.

You can do this by adding a Year dataset to each report:

The two subreports will display as expected:

Of course, when I select a data item that the main report has but the subreport doesn't, it causes the subreport query to fail, which is normal. This is because this value does not exist in the parameters of the subreport.

To solve this problem, we need to carry the parameters of the subreport in the query, not in the filter panel:

The primary dataset still needs to set an available value in order to pass it to the subreports.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdc136]

Hi @Anonymous thank you for getting back to me again! So for additional information, this is the setup I have. Here is what one of my subreports looks like:

You can see I have a parameter tied to a parameter in the "Main" dataset. The Tablix showing the results from the main dataset has a NoRowsMessage property entered.

If I enter the year "2024" for this particular report, I get the NoRowsMessage: 

Here is the main report that contains a reference to this report as a subreport:

You can see that I have my report parameter and am passing it to the subreport parameter "TaxYear". When I run this report for 2024, as above, I do not see the NoRowsMessage like I do when I run the report on its own.

Reviewing your response, I have a few questions. What do you mean add a "year dataset to each report"? In your case, your dataset is using the filter panel, whereas mine is using a parameter, which is what you recommend a couple steps later ("carry the parameters of the subreport in the query, not in the filter panel"). My underlying SQL query is simply stating alias.year = :TaxYear, which differs slightly from what you have using an in operator.

When I remove the message from NoRowsMessage, I still get a "result", but it is empty. Again, this is when running the subreport on its own. 

However, running the main report, again, I do not see an empty table - I see nothing at all, just blank space.

What I am expecting is that I can set NoRowsMessage in the tablix of my subreport, and if I get no rows returned from the query, I see my message both in the subreport itself and in the main report referencing the subreport. 

[Anonymous · Accepted solution]

Hi, @jdc136 

Thank you very much for your reply. Here are the full demo processes, you can try to follow them:

For the Sub1 report I set it up as follows:

For the Sub2 report I set it up as follows:

For the main report, I set the following:

It delivers the results you're looking for:

In order to be able to get multiple choices, I use the IN keyword in my query here. Also add a dataset Year to the main report:

Modify the year parameter of the main report as follows:

Here are the results:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdc136]

Thank you for the detailed response @Anonymous. I was able to get the NoRowsMessage to appear when there are multiple subreports. Appreciate it!