help with an expression

I need to create an expression to handle a simple calculation. Here is what I have:

=Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")

What do I need to add to handle the when the MH_POS_Goal = 0?

I tried isnull and nullif and kept getting errors.

Hi

You can create a User Define function for that where you can check the MH_POS_Goal value. If it is 0 then return 1 other wise retirn the acutal value... and I hope that you know anything deiveded by 1 is by nature and don't change the value . means that

1 = 1/1

200 = 200/1

|||

Something like this?

= IF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1,Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")

,'')

|||

This expression processes ok. But I am still getting the error message. Can someone help me update it to work correctly?

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")),0)

|||

The render engine of SSRS evaluates the complete expresse you enter.

In general this means that preventing a DivByZero exception with an IIF statement to check for a zero (null) value and display something else in case it's true, isn't enough. For the 'divided by' number also a IIF statement is needed, because the complete expression is evaluated.

So in your case this would be: =IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

Notice that the true-part, 1, of the last IIF statement is never used, but is only needed for the SSRS engine not to generate an error.

|||

I didn't means this ....

I mean .....

IDF_CHECK (value)
IF Value = 0
Value = 1
RETURN Value

And Now check ......

(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")/IDF_CHECK(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))

|||

I tried this:

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

and I got this error: Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'

Any ideas?

|||

There was an extra ')'.

This works:

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

Thanks for the help!

|||

If I use this in a report with subtotals I get the calculation at the total level and not at the subtotal level. Any ideas how to get the calculation to perform at the subtotal level?

|||

Anyone?