CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2012
ARLibertarian
ARLibertarian

Reputation: 167

SSRS Sub-Report not displaying in production

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.

Upvotes: 0

Views: 2547

Answers (2)

ARLibertarian
ARLibertarian

Reputation: 167

Seriously, sometimes I hate M$.

The stored procedures for the two sub reports only bring back 4 fields, one I don't even use (district ID). The report only has three text boxes in a table, one is just a literal. The other two are

=Description + " (" + code + ")"

and 

=DistCounts

ABSOLUTELY NOTHING TO THESE.

Finally we took the one that worked, copied the XML to an new sub report, changed the dataset, field names and literal, redeployed, and it works. Comparing the one that works with the 2 that didn't ... shows nothing of interest. Arrrrgh!

As I said, both used the same input parameters, the stored procedures were just counting different fields. Wasted more than a day on a wild goose chase, and we still don't know what was wrong.

Upvotes: 0

Ruby Blue
Ruby Blue

Reputation: 41

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

Upvotes: 0

Related Questions