Search code examples
c#sqlvisual-studio-2010reporting-servicesreport-viewer2010

How can I add a unique count of a specific field to my report?

I have a C# program with output that looks like this (unexpanded rows): unexpanded rows

and this (expanded rows): expanded rows

Here's my SQL for the Fill method:

SELECT       MBRSEP, LOCATION, BILLMOYR, RATE
FROM         CAR1.CAV_MBRHISTDETL
WHERE        (BILLMOYR IN ('1104', '1105', '1106', '1107', '1108', '1109')) AND (RATE = '0096')
ORDER BY     BILLMOYR ASC, MBRSEP ASC

What this report is showing is a breakdown of customers with a rate type of 0096 for the months of April, May, June, July, August, and September.

What I'd like to see is a unique count for each distinct customer (MBRSEP) (a grand total of unique customers for those months), but I can't get my head around how to do this. Can anyone help or point me in the right direction? This little program was written with Visual Studio 2010 using a blank form, a databound dataset, and a reportviewer control.

EDIT: Also, here is the report source on the report design screen:

report source

On the designer screen, I'm assuming there's some way to add a field that will show total number of unique values in a certain field in the report, but I'm not sure how to do that. (NOTE: I just added the SQL statements in a text field on the report for clarity)

Solution

  • Have you tried the SSRS CountDistinct function?