12

I have a dataset called 'dsAllStuTargetData' - i'm trying to count the number of the 'A' values that appear in the column 'Target'.

I'm doing this using a textbox with an expression, I can count the total number of values using the following:

=Count(Fields!Target.Value, "dsAllStuTargetData")

However when I try to count where the value equals 'A' it doesn't work.

=Count(IIF(Fields!Target.Value, "dsAllStuTargetData")="A",1,0)
nowYouSeeMe
  • 935
  • 6
  • 11
  • 21

2 Answers2

35

For this case you need a Sum, not a Count, i.e. something like:

=Sum(IIf(Fields!Target.Value = "A", 1, 0), "dsAllStuTargetData")

Count will just count the number of rows; the IIf doesn't do anything there - something like CountDistinct can be affected in certain cases but this will not work here.

However, Sum will take the total of all rows which meet the IIf condition, i.e. the total of all 1 values in the DataSet, which is what you're after.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • 1
    hello Ian, It's working fine, but what if I want to group by the counting of one field on the base of Date field. How to use Group by or Aggregate with above expression?? Thanks. – Mogli Jul 20 '16 at 05:07
1

IIF wants it's arguments in the format:

IIF(condition, true part, false part)

Which would equate to something like

Count(IIF(Fields!Target.Value = "A",1,0),"dsAllStuTargetData")

Does that work?

Matt Roberts
  • 26,371
  • 31
  • 103
  • 180
  • Hi Matt - unfortunately it doesn't work it still returns the total number of records (23). When in fact it should count 10 'A' values. – nowYouSeeMe Sep 19 '13 at 11:19