SSRS and conditional statements

1

Good Morning. I am creating a report in SSRS and I cannot figure out how to do this.
I am trying to get the count of records that match certain criteria. For example, If the value of column A is "Refinance" and the value of column B is greater than 35, add it to the total. I thought using an IIF inside of a CountDistinct would work but no such luck.

Any help would be greatly appreciated.
Jason

2

If you can post the expression you are using, that would be helpful to someone who wants to respond. What you should be doing is something like this:

-- For counting the number of rows where id is greater than 2
=SUM(IIF(Fields!id.Value > 2, 1,0), "DataSet1")

or

-- For counting distinct number id values in the dataset.
=CountDistinct(Fields!id.Value,"DataSet1")
3

Whoops! Yep, I forgot my expression. Here is what I was trying to use:
="Purchase 35 DTC: " & COUNT(IIF(Fields!TransType.Value = P AND Fields!DTC.Value < 35, Fields!TransType, NOTHING), "dataset1")

And the error I get:
The value expression for the text box 'txtPurchase35DTC' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of the containing group, the name of the containing data region or the name of the dataset.

4

Your expression looks okay to me, but I would make the following changes:

  1. insert double-quotes around P, as in Fields!TransType.Value = "P"
  2. Convert the number to a string before concat. as in & CStr(COUNT(..."dataset1"))
  3. Make sure that the name of the data set is spelled with the correct case. DataSet1 is not the same as dataset1

If none of that helps, simplify your expression to the very basic, get that working, and add additional pieces one step at a time.

5

Ah HA! thanks for the help, dam spelling...:slight_smile:
I can now compile and view the report but I get this error for the textbox that is using this code:
="Purchase 45 DTC: " & CStr(COUNT(IIF(Fields!TransType.Value = "P" AND Fields!DTC.Value < 45, Fields!TransType, NOTHING), "DataSet1"))

Error:
The value expression for the textrun 'txtPurchase35DTC.Paragraphs[0].TextRuns[0]' uses an aggregate function with an expression that returned a datatype not valid for the aggregate function.

One error down, one more to go.

I really appreciate the help

6

ah HAAA!
dam brain...I forgot to add ".value" to the Fields!TransType.value

sighs!