Using Count() and IIF in SSRS expression

Question

I am attempting to count the number of values where the Previous Year Comp is greater than 0. I found examples on StackOverflow but nothing is giving me the count I want.

I have the following expression:

= IIF((Fields!Previous_Year_Comp.Value) > "0.00",
      count(Fields!Previous_Year_Comp.Value),0)

This expression is counting the 0 values. Keep in mind that this expression has undergone several modifications. What am I missing?

You need to combine `Sum` and `IIf`: http://stackoverflow.com/questions/18892911/reporting-services-count-column-values-if-equals-a — Ian Preston, Jul 22 '16 at 14:42

score 2 · Answer 1 · answered Jul 22 '16 at 20:41

2

Your COUNT should be around your IIF.

=COUNT(IIF(Fields!Previous_Year_Comp.Value > "0.00", Fields!Previous_Year_Comp.Value, NOTHING)

NOTHING is SSRSs NULL that isn't counted with COUNT.

answered Jul 22 '16 at 20:41

Hannover Fist

10,393
1
18
39

score 0 · Answer 2 · answered Jul 22 '16 at 14:20

0

You are comparing a string to an integer value. Try this:

=IIF(Fields!Previous_Year_Comp.Value > 0, count(Fields!Previous_Year_Comp.Value),0)

Of course, if Previous_Year_Comp is a VARCHAR value and not a DECIMAL or INTEGER, you may need something like this:

=IIF(Fields!Previous_Year_Comp.Value <> "0.00", count(Fields!Previous_Year_Comp.Value),0)

answered Jul 22 '16 at 14:20

S3S

24,809
5
26
45

Unfortunately, neither solution worked. Prev_Year_Comp is a decimal. – FoxyB Jul 22 '16 at 14:58
=count( IIF( IIF(Fields!Previous_Year_Comp.Value > 0, true, false) ,true,0 ) ) I also tried this. No bueno. – FoxyB Jul 22 '16 at 15:07

Using Count() and IIF in SSRS expression

2 Answers2