CODEWITHSUNDEEP
reporting-services
ewitkows
ewitkows

Reputation: 3618

SQL Server Report Builder - Show Count of Sub Groups

I have a SQL Server Reporting Services report that shows customer order data, but it's grouped as follows:

Store
    Customer
        Customer Order Items

So, each report is a grouping of stores, with a subgroup of customers per store, and then the items per customer. I'm trying to show aggregate sale and other information at each header record of the appropriate group in the report. Most of this is working well, but for each store header record, I want to show a count of the customers. I'm trying to use some variation and\or combination of RowCount, CountDistinct and other aggregate functions, but to no avail.

Can anyone help me determine how I essentially can get a "count" of customer groups to show at the Store level header? TIA!

Upvotes: 4

Views: 7836

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

CountDistinct on Customer should work fine - no need to specify scope if it's in the Store group header row.

I put a simple test together.

Data:

enter image description here

Report in designer:

enter image description here

Most important thing to note is the CountDistinct on Customer in the Store header row; this is just the expression used:

=CountDistinct(Fields!customer.Value)

End result, showing correct values:

enter image description here

Please let me know if I'm missing something.

Edit after comment:

Apologies in advance for how long this is getting.

The previous report did have row groups for Store and Customer, but I've modified this to make it more clear, hopefully. Still based on the same DataSet:

enter image description here

You can see there are three row groups, and each row in the report is actually a group header row belonging to one of those groups.

In the Store group header row I've kept that same CountDistinct expression. I've also added a CountRows() expression to show how many actual rows are available in each of the different groups.

enter image description here

Here you can see for Store1, CountRows is returning 4, i.e. there are four rows that we are aggregating in this scope, which is what we expect looking at the DataSet.

Similarly, when we apply =CountDistinct(Fields!customer.Value) in the Store scope we are considering these same 4 rows, and we see two distinct customers for Store1, which seems correct to me.

For Store2 we are considering 6 rows in total, which have three distinct customers. Again, just by applying =CountDistinct(Fields!customer.Value) we get correct value.

Hopefully this rejigged report helps clear things up. If I'm still not getting your requirements, can you please explain what numbers are wrong in my sample report based on my sample DataSet? That way I can adjust things easily on my side.

Upvotes: 6

Related Questions