locked
Can't work out the difference between 2 values. RRS feed

  • Question

  • Hi,

    I'm using SQL Server Data Tools to create fetch based reports for my CRM online installation.

    In my data query I pull 2 fields that contain values in currency (linetotal and grandtotal).

    I'm trying to create an expression against a table column inside my report that simply subtracts the linetotal value from the grandtotal value so I can work out what the discount value is as I sometimes apply a % discount to the linetotal value which is then reflected in the grandtotal value.

    I'm using the below code but receive "Error" when I run my report.

    =Sum(Fields!grandtotal.Value - Fields!linetotal.Value)

    Any help would be appreciated, I'm not that good with SQL!

    Many Thanks,


    James Bratley

    Friday, August 16, 2013 10:55 AM

Answers

  • Thanks for the help but I'd tried that query at first and received the same error. I figured out I needed to use the basevalue versions of the fields for this to work:

    =Fields!grandtotal_baseValue.Value-Fields!linetotal_baseValue.Value


    James Bratley

    • Marked as answer by James Bratley Friday, August 16, 2013 12:33 PM
    Friday, August 16, 2013 12:33 PM

All replies

  • Hi James,

    Try like this by removing the Sum,

    =Fields!grandtotal.Value - Fields!linetotal.Value

    and see whether it helps.

    • Proposed as answer by B sreenivasulu Friday, August 16, 2013 11:15 AM
    Friday, August 16, 2013 11:10 AM
  • Thanks for the help but I'd tried that query at first and received the same error. I figured out I needed to use the basevalue versions of the fields for this to work:

    =Fields!grandtotal_baseValue.Value-Fields!linetotal_baseValue.Value


    James Bratley

    • Marked as answer by James Bratley Friday, August 16, 2013 12:33 PM
    Friday, August 16, 2013 12:33 PM