Post reply

Expression Syntax

  • January 29, 2009 at 8:20 am

    #196667

    Hi. I have developed a report and was asked to add an additional field to display a value. This I have done however if there is no value then the field is left blank. I would like this to display 0.00.

    Field expression: '=Sum(Fields!WithoutOptionValue.Value)'

    Bit of background......

    A sales order item has a boolean value for 'System option'.

    If the flag is true (i.e. is an option) then the Total Order Value = SUM of 'CurItemValue' where the System Option Flag = 0

    --INNER SELECT--

    (SELECT SUM(SalesOrderItems.CurItemValue)

    WHERE CVOption.CustomFieldValueBit = 0) AS 'WithoutOptionValue'

    So when the Sales Order is made up of Line Items that are all Options the 'WithoutOptionValue' is blank. As stated earlier I would like this to be substituted with 0.00.

    I hope that makes sense?

    Thanks,

    Phil.

    Update:

    I have tried a few options but they return #Error

    =Sum(IIF(Fields!WithoutOptionValue.Value = " ","0.00",Fields!WithoutOptionValue))

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • January 29, 2009 at 3:52 pm

    #935154

    Try changing your sum statement to this:

    sum(coalesce(SalesOrderItems.CurItemValue,0))

    If nulls are in your SalesOrderItems.CurItemValue field, this will correct the problem....

  • January 30, 2009 at 10:00 am

    #935682

    Hi Bob. I looked at this from a different angle and decided to modify the query rather than format in the report using visual studio. I then posted to the TSQL forum when I hit a snag. The 2 threads are pretty similar so do not want to double post. I tried your code but still get the NULLS!

    Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • January 30, 2009 at 10:09 am

    #935689

    Hi

    Sum function can compute only Numeric values.

    So Try this

    = Sum(iif(Ltrim(Rtrim(Fields!Exp1.Value)) = "",0.00,Fields!Exp1.Value))

    Dhans

  • January 30, 2009 at 10:18 am

    #935704

    You may also try formatting the text box:

    http://msdn.microsoft.com/en-us/library/ms157406(SQL.90).aspx

    Use the above codes in the textbox properties under the 'format' tab in the 'format code' box.

  • January 31, 2009 at 2:33 am

    #935968

    Thanks guys.

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply