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?

Help with an expression

The error is:
The value expression for the textbox â'BillAddressâ' contains an error:
[BC30201] Expression expected.
=If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr (10) +
Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.Value) = 0 AND
Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line2.Value) = 0 AND
Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.Value)= 0 AND
Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.value)= 0 AND
Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
Fields!AcctName.Value + Chr(10) +
"No Address Available" + Chr(10) +
"ID: " + AccountNumber
Many ThanksJeff,
I thought VB was the scripting language to be used in expressions. Why
are you concatinating with '+' operators, when you should be using '&'
operators?
thx
-jsh
"Jeff Metcalf" wrote:
> The error is:
> The value expression for the textbox â'BillAddressâ' contains an error:
> [BC30201] Expression expected.
>
> =If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
> And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr (10) +
> Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.Value) = 0 AND
> Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
>
> Else If Len(Fields!BillTo_Line2.Value) = 0 AND
> Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.Value)= 0 AND
> Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.value)= 0 AND
> Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
> Fields!AcctName.Value + Chr(10) +
> "No Address Available" + Chr(10) +
> "ID: " + AccountNumber
> Many Thanks|||Beats me...I'm just trying to get it to work. The '+' operator works for
for concatinating in other SRS reports and in SQL. I'm not a VB or .Net
programmer, so I have no idea if it's right or not, lol.
"jsh02_nova@.hotmail.com" wrote:
> Jeff,
> I thought VB was the scripting language to be used in expressions. Why
> are you concatinating with '+' operators, when you should be using '&'
> operators?
> thx
> -jsh
> "Jeff Metcalf" wrote:
> > The error is:
> >
> > The value expression for the textbox â'BillAddressâ' contains an error:
> > [BC30201] Expression expected.
> >
> >
> > =If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
> > And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr (10) +
> > Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > > 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > > 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.Value) = 0 AND
> > Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> >
> > Else If Len(Fields!BillTo_Line2.Value) = 0 AND
> > Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.Value)= 0 AND
> > Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.value)= 0 AND
> > Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > "No Address Available" + Chr(10) +
> > "ID: " + AccountNumber
> >
> > Many Thanks|||I'm not sure if this is what's causing the problem but SRS uses an
"inline if" -IIF- instead of IF THEN ELSE.
The syntax is =IIF(test expression,true action, false action).
Using your expression from above it would be something like this:
=IIf( Len(Fields!BillTo_Line1.Value)= 0 And
Len(Fields!BillTo_Line2.Value) > 0
And Len(Fields!BillTo_Line3.Value) > 0 ,
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr (10) +
Fields!BillTo_City.value + ", " +
Fields!BillTo_StateOrProvince.Value + " " +
Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value ,
IIF( Len(Fields!BillTo_Line2.Value)= 0 And
Len(Fields!BillTo_Line1.Value) > 0 And Len(Fields!BillTo_Line3.Value) >
0 , and so on
Nested IIFs can get really ugly. If I were you and if it's possible
I'd do as much as you can with case statements in SQL.
Good luck

Help w/aggregate function in Matrix

On a report we have a matrix. The data cell has the following expression:
=iif(Fields!Score.Value=0, "", Fields!Score.Value)
When we run the report, we get the following warning:
The value expression for the textbox 'Score' references a field
outside an aggregate function. Value expressions in matrix cells should be
aggregates, to allow for subtotaling.
What does this mean and how can I resolve it?
The goal it to suppress the display of zero (0). We have tried setting the
format of the data cell to be "#", but the zero is still displayed. So we
have been using expressions like the above to achieve this.
The user creating this report is using the stand-alone C# IDE with Reporting
Services. This warning prevents them from previewing the report. Another
user using VS.NET 2003 is able to preview the report despite the warning.
The report renders on our test reporting server. If we need to just ignore
the warning, how can we get the user using C# to be able to preview the
report?
Thanks,
ChrisMatrix cells are always in the scope of two groupings and you could have
multiple data rows which match the group instance values. Therefore, you
should always use aggregate functions when referencing fields in a matrix
cell (hence, a processing warning gets generated).
If you don't use an explicit aggregate function in the matrix cell, we would
implicitly use the first row's field value. I believe you actually don't
want just the first value, but rather the sum - so you should change the
expression to:
=iif(Sum(Fields!Score.Value)=0, "", Sum(Fields!Score.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
In your case, I believe you want
"Chris Walls" <chwalls@.community.nospam> wrote in message
news:OVqzX42OFHA.1500@.TK2MSFTNGP09.phx.gbl...
> On a report we have a matrix. The data cell has the following expression:
>
> =iif(Fields!Score.Value=0, "", Fields!Score.Value)
>
> When we run the report, we get the following warning:
>
> The value expression for the textbox 'Score' references a field
> outside an aggregate function. Value expressions in matrix cells should
> be aggregates, to allow for subtotaling.
>
>
> What does this mean and how can I resolve it?
>
>
> The goal it to suppress the display of zero (0). We have tried setting
> the format of the data cell to be "#", but the zero is still displayed.
> So we have been using expressions like the above to achieve this.
>
>
> The user creating this report is using the stand-alone C# IDE with
> Reporting Services. This warning prevents them from previewing the
> report. Another user using VS.NET 2003 is able to preview the report
> despite the warning. The report renders on our test reporting server. If
> we need to just ignore the warning, how can we get the user using C# to be
> able to preview the report?
>
>
> Thanks,
> Chris
>
>