Count expression in SSRS 2008

Question

I have a situation where I have a column name `NotifcationLog.Status.

The status can be of 3 types Accepted, Pending & Overdue. I need to have a count of all the Notifications status .

I created a calculated field with the following expression

=COUNT(IIF(Fields!NotificationStatus.Value="Accepted",1,Nothing))

When I tried to add this Calculated field to the table and preview it , I got a error stating "aggregate ,row number,running value,previous and lookup functions cannot be used in calculated field expressions "

What should i do now ??

Answer 1

You try adding

 =IIF(Fields!NotificationStatus.Value="Accepted",1,0)

as your calculated field. This returns back 1 or 0 depending on if the status is accepted.

and then where you want to use it you can just SUM your calculated field to give you a count.

=Sum(Fields!NewCalculatedField.Value)

Use this in a table / matrix etc. where your data is grouped.

Answer 2

The error seems straight forward enough, the reason you can't do this is that it wouldn't make sense without any grouping. Imagine following dataset:

+-------+------------+
| ID    |   Status   |
|-------+------------+
|  1    |   Accepted |
|  2    |   Pending  |
|  3    |   Accepted |
|  4    |   Overdue  |
+-------+------------+

If you were to add a third column with your expression, it would be the SQL Equivalent of

SELECT  ID, Status, COUNT(CASE WHEN Status = 'Accepted' THEN 1 END)
FROM    T

With no group by this is not valid syntax. You could add a report field with your count expression, but not a calculated field in your dataset. The dataset you are trying to make is as follows:

+-------+------------+----------+
| ID    |   Status   | Accepted |
|-------+------------+----------+
|  1    |   Accepted |    2     |
|  2    |   Pending  |    2     |
|  3    |   Accepted |    2     |
|  4    |   Overdue  |    2     |
+-------+------------+----------+

Which does not really make sense to have the value repeated for all rows, but you can do it in SQL using windowed functions:

SELECT  ID, 
        Status, 
        Accepted = COUNT(CASE WHEN Status = 'Accepted' THEN 1 END) OVER()
FROM    T;