SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Total Row Count in SSRS Report Builder


Total Row Count in SSRS Report Builder

Author
Message
slimchance99
slimchance99
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 219
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



laubenth
laubenth
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 201
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.
slimchance99
slimchance99
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 219
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.



laubenth
laubenth
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 201
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.
georgemathew2
georgemathew2
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search