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.

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

perhaps something like this :
=Count(iif(Fields!act_shp_dt.Value > Fields!prm_shp_dt.Value, 1, 0))

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
--
Message posted via http://www.sqlmonster.comYou're on the right track, but rather than using a count, try the following
with a sum.
Create a non-visible textbox with the following expression:
=iif(Fields!act_shp_dt.Value > Fields!prm_shp_dt.Value, 1, 0)
Then create a summary field that calculates a sum of the above values.
"Kerrie S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5145CB76A8536@.SQLMonster.com...
> 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
>
> --
> Message posted via http://www.sqlmonster.com|||goodman93 wrote:
>You're on the right track, but rather than using a count, try the following
>with a sum.
>Create a non-visible textbox with the following expression:
>=iif(Fields!act_shp_dt.Value > Fields!prm_shp_dt.Value, 1, 0)
>Then create a summary field that calculates a sum of the above values.
>> Hi All,
>> I am trying to compare two dates: actual shipping date > promise shipping
>[quoted text clipped - 13 lines]
>> Thanks, Kerrie
--
You did IT!!!!!!!!!!!!
Thank you SO MUCH, Kerrie
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200507/1

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.