November 21, 2008 at 9:58 am
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
Tim
November 21, 2008 at 12:43 pm
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.
November 21, 2008 at 1:13 pm
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.
Tim
November 21, 2008 at 2:10 pm
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.
March 10, 2011 at 12:37 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply