SSRS Sub-Report not displaying in production

Question

Some days I just really hate M$.

I have an SSRS Report, the dataset is being populated by a stored procedure. Report display the results one district (grouped rows) per page. Stored procedure is returning aggregated counts. So far so good.

I've added two sub reports to the tablix, one in the left column, one in the right. Almost exactly the same. I've clicked on the cell, clicked insert, and selected SUB-REPORT. Then I click on the new SUB-REPORT, and use the drop down to specify which report to use as a sub-report.

They too have a data set being fed by a stored procedure, which may return from 0 to several rows. Both sub-reports use exactly the same parameters, with the same names.

---------------------------------------------------
|___________________DISTRICT 12___________________|
|_____________Item 1|__75|____________Item 2 |__42|
|_____________Item 3|__15|____________Item 4 |__45|
|_____________Item 5|___5|____________Item 6 |__16|
|_____________Item 7|__65|____________Item 8 |___0|
|_____________Item 9|__12|___________Item 10 |__55|
|=================================================|
|______Sub Report 1______|______Sub Report 2______|
|_________category 1|__27|_________category A|__16|
|_________category 2|__15|_________category B|__42|
|_________category 3|___2|_________category C|__60|
|_________category 4|___6|                        |
|_________category 5|__16|                        |
---------------------------------------------------

It looks and works fine in Visual Studio. Each sub report works fine on it's own, and works fine within the main report as well. But when I deploy all 3 reports, sub report 1 says "Error: Subreport could not be shown."

Sub-reports take exactly the same parameters. Sub reports get district ID, year, etc to pull data.

I've recreated the offending sub-report with another name, replaced it in the main report, with the same result.

I've recreated the main report, giving it both sub-reports, with the exact same result, sub-report 1 and sub-report 1b dos not work, sub-report 2 does.

I compared the sub report files with each other using ExamDiff (shameless plug, excellent tool!) and found no real differences.

I cracked open the main report file with XML Notepad (another useful tool) and found no real differences in the way they are set up.

All reports are stored in the same directory.

I opened the project file, and found nothing of interest there.

I even added the external tool to clear local report cache as suggested elsewhere.

I am stumped. I am certain this has been encountered before. Any pointers?

As always, thank you to my compatriots on Stack Overflow. Best source of advice for developers on the planet.

What path are you using to refer to the subreports? I assume you developed this on a local environment which would mean your paths are referring to a local file folder. Typically, it works best to use a relative path to the subreports, such as just the name Subreport1. Documentation Link — Steve-o169
Since I select the sub report from a drop down, I don't really know that a path is referenced. As I said, all reports are in the same folder. Looking in the main report, the XML is only listing the report name. CellContents>SubReport>ReportName>#text>rpt_Report225Counts. Next node is Parameters. The 2nd sub-report is selected the same way, uses the same parameters, and works correctly. — ARLibertarian
You can typically right click the subreport object and select the subreport properties to check the path. It'd be wise to take a good look at the properties of the subreport as those are usually the biggest pain point with this sort of thing. Oftentimes the parameters don't get populated as they should. — Steve-o169
Hmmm.. well, I guess I've only really used various versions of Report Builder to develop SSRS reports. Maybe someone with more experience in VS can come along with better suggestions. — Steve-o169
I mostly build ssrs report for Microsoft dynamic crm application. I created many reports with subreport and as you mentioned in your question I use visual studio and I get option to select subreport from the available report in my project. But here is the catch, when I deploy to target environment(prod) link between them is most of the time broken. What I then do is open subreport properties in dynamics crm and set its parent record and in this way I get report working. I am not sure if this is completely relevant for you but might give you a hint. Let me know if this helps. — AnkUser

Ruby Blue Ruby Blue · Accepted Answer · 2019-10-04T07:36:36

This could be the old FMTONLY and SSRS problem.
For more details please see these links
http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/119024/

No fields for dynamic SQL stored procedure in SSRS with SET FMTONLY

I use this in some of my old SSRS stored procedures when the resultsets do not get displayed initially

CREATE PROCEDURE report1 AS
BEGIN
     DECLARE @FMTONLY BIT;  
     IF 1 = 0               
      BEGIN  
        SET @FMTONLY = 1;  
        SET FMTONLY OFF;  
      END 

     -- Stored proc main code here

    IF @FMTONLY = 1  
     BEGIN  
        SET FMTONLY ON;  
     END  

END  -- End of stored proc / RETURN

SSRS Sub-Report not displaying in production

2 Answers