SQL Server Report Builder - show number under groups

Question

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

Store Customer Customer Order Items 

Thus, each report is a grouping of stores with a subset of customers in each store, and then elements for each customer. I try to show the total sale and other information in each heading of the corresponding group in the report. Most of them work well, but for each store header entry I want to show the number of customers. I try to use some options and / or a combination of the functions RowCount, CountDistinct and other aggregates, but to no avail.

Can someone help me determine how I can essentially get the “number” of customer groups to display in the store level header? TIA!

Answer 1

CountDistinct The client should work fine - no need to specify the area if it is in the header line of the Store group.

I put a simple test together.

Data:

Report in the designer:

The most important thing is to mark the CountDistinct for the client in the Store header line; this is just the expression used:

 =CountDistinct(Fields!customer.Value) 

The end result showing the correct values:

Please let me know if I missed something.

Edit after comment:

Sorry how long this takes.

The previous report had line groups for the store and the customer, but I changed this to make it more understandable, I hope. Still based on the same DataSet:

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

In the title bar of the Store group, I saved this expression CountDistinct . I also added the expression CountRows() to show how many actual rows are available in each of the different groups.

Here you can see for Store1, CountRows returns 4 , i.e. there are four lines in this area that we collect what we expect from viewing a DataSet.

Similarly, when we apply =CountDistinct(Fields!customer.Value) in the Store area, we look at the same lines 4 , and we see two different customers for Store1, which seems right to me.

For Store2, we are considering lines 6 , which have three different customers. Again, simply by applying =CountDistinct(Fields!customer.Value) , we get the correct value.

I hope this regiered report helps you figure it out. If I still will not receive your requirements, can you explain what numbers are wrong in my sample report based on my sample DataSet? That way, I can easily customize things on my side.