Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Total Row Count in SSRS Report Builder Expand / Collapse
Author
Message
Posted Friday, November 21, 2008 9:58 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 11:51 AM
Points: 614, Visits: 168
Does anyone know how the 'Total Row Count' text box works in SSRS Report Builder?

Specifically, we have noticed that the Total Row Count is correct when displaying fields from a single entity, but when a field from another related entity is added, the Total Row Count shows a higher value than the actual rows returned on the report, which doesn't change.

I'm looking for an explanation for this behaviour, and any possible workarounds. Or an alternate method to get the accurate rowcount in a Report Builder report, since it doesn't look like it is possible to add an expression to the report footer.

Thanks
Tim Harding



Post #606654
Posted Friday, November 21, 2008 12:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:14 PM
Points: 28, Visits: 168
First make sure you are using your count field from the proper entity in the report, I forget if you get a choice for the report footer.

Use profiler and take a look at the query generated for the report. Run it to see the results that actually come back. I found that it was adding an extra join that didn't make sense and was bringing back the same set of rows multiple times. In my case I needed to add an extra relationship between the two tables/views in the data source view. Note, adding 2 relationships (with 1 column each) between 2 entities and adding 1 relationship (with 2 columns) between 2 entities can produce different results.

I also had to add a fake ID column (ROW_NUMBER()) as the results of my views and use that ID in the model for each entity's identifying attribute.

After both of these steps my number are always correct.
Post #606793
Posted Friday, November 21, 2008 1:13 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 11:51 AM
Points: 614, Visits: 168
From looking at the query results, it is apparent that the total row count in Report Builder is the number of rows returned by the query, not the number of rows in the report. As you said, the query returns lots of duplicate rows, and the report rendering seems to add the 'distinct' property to produce the correct output. If I add the 'distinct' keyword to the query in SSMS, I get the correct number of rows.

it seems odd to me that the default Total Row Count text box (which does not appear to be configurable in any way) would not count the number of rows in the report.



Post #606814
Posted Friday, November 21, 2008 2:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:14 PM
Points: 28, Visits: 168
I would double check your queries/tables to make sure all of the joins are set up properly this could help reduce the number of rows and get rid of the dups. The row count is based on the number of rows returned not what is shown. I think that has to do with how things can be filter, grouped, etc.

I added a distinct to my views that I use for the model to help with this but their joins still produce dups depending on what fields get selected.
Post #606844
Posted Thursday, March 10, 2011 12:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:30 AM
Points: 3, Visits: 30
I have also a similar problem. I have a parent report and inside the parent report I have textbox and two subreports.
My requirement is this, I have to hide the textbox if both the subreports are empty. I am not using any tablix for the parent report. Is there any way to get the number of rows in suibreports from the parent report???
or In what way we could do this , Only want to hide the text box if the subreports are empty
Post #1076034
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse