Reporting Services Count

Hi All,
I am trying to compare two dates: actual shipping date > promise shipping date.
If the sctual shipping date is greater that the promised shipping date than count that order number if not than it is null.

This is in the report designer in the "edit expression"

=Count(iif(Fields!act_shp_dt.Value > Fields!prm_shp_dt.Value, Fields!ord_nbr.Value, ""))

It is counting everything as true.
Can anyone help me.

Thanks, Kerrie

Try this:

=Sum(iif(Fields!act_shp_dt.Value > Fields!prm_shp_dt.Value, Fields!ord_nbr.Value, 1, 0))

I think that should work. or even try.

=Count(iif(Fields!act_shp_dt.Value > Fields!prm_shp_dt.Value, Fields!ord_nbr.Value, 1, 0))

It might be because you are not supplying a return value for the iif statement.

Martin

|||Kerrie - did this work for you - I am trying a very similar thing but when I use martins solution I get compilation errors.

I have tried using a hidden field to show a 1 against exh record if condition is true - then I try to do a simple count on this field but RS will only allow me to count things in the dataset so unless I can find a simple solution I'm going back to Crystal Reports.

Simon|||Note: the Count() function in RS works the same way as the Count aggregate in SQL - every value different than NULL will be counted.
Hence, when using Count, the expression has to look like this:
=Count(iif(Fields!act_shp_dt.Value > Fields!prm_shp_dt.Value, Fields!ord_nbr.Value, Nothing))
Alternatively, you can use the expression with Sum() as shown by Martin.

-- Robert|||

The answer is wrong or at least doesn't work for me.

Reason:

Function IIF only has 3 parameters not 4.

Also if I put Nothing in for the 2nd parameter in Count I get

[BC30205] End of statement expected.

Example: Count(iif(Fields!act_shp_dt.Value > Fields!prm_shp_dt.Value, Fields!ord_nbr.Value, 1), Nothing)

What is the correct answer? I've been trying to do a conditional count based on the detail rows for sometime and haven't figured it out and there isn't much documentation out there.