# SUM of count distinct RSS

## 3 replies

Last post Apr 20, 2016 09:46 AM by Weibo Zhang

Member

64 Points

784 Posts

### SUM of count distinct

Intersection        xxxxxx               2300                             2

Duct                       xxxxxx               2400                          2

Intersection        xxxxxx               2300                             3

Flyover                  xxxxxx               2400                           5

_____________________________________________

Totals                                                                              12

___________________________________________

I have a count distinct column. – ARCount. =CountDistinct(Fields!RoadWayNumber.Value)

In the total row below I need to show 12, I tried like SUM(=CountDistinct(Fields!RoadWayNumber.Value))

And it is not working, it shows just 2.  what should I be doing instead?

Star

7990 Points

1586 Posts

### Re: SUM of count distinct

Apr 08, 2016 03:04 AM|Weibo Zhang|LINK

Hi nissan,

#### nissan

In the total row below I need to show 12, I tried like SUM(=CountDistinct(Fields!RoadWayNumber.Value))

And it is not working, it shows just 2.  what should I be doing instead?

You should first know that the CountDistinct Function return the count of all distinct non-null values specified by the expression, evaluated in the context of the given scope. The return value is an integer of the count. For more thing, you’d better have a look at below thread.

http://stackoverflow.com/questions/14244368/ssrs-count-distinct-fieldsb-value-where-fieldsa-value

Back to your issue, if you want to get the 12 (=2+2+3+5), you should use “=SUM(Field! ARCount.Value)” to achieve your goal.

Best Regards,

Weibo Zhang

Member

64 Points

784 Posts

### Re: SUM of count distinct

ARCount is not coming from the dataset. It is a computed column

How can I do SUM(Field! ARCount.Value) as you suggested?

Star

7990 Points

1586 Posts

### Re: SUM of count distinct

Apr 20, 2016 09:46 AM|Weibo Zhang|LINK

Hi Nissan,

#### Nissan

ARCount is not coming from the dataset. It is a computed column

You could try defining the “ARCount” as a group variable and then use this group variable to get the final result. The following blog provides a demo to show how to use it and you could have a look.

https://blogs.msdn.microsoft.com/robertbruckner/2008/07/21/using-group-variables-in-reporting-services-2008-for-custom-aggregation/

Besides, if possible, you could make some changed on your SQL code to calculate the “ARCount” value from the database and then bind it to Report easily.

Best Regards,

Weibo Zhang