Problem while trying to sum up a column

• syedrizwanhaidernaqvi

  Newbie

  Points: 5

  More actions

  March 23, 2014 at 11:15 am

  #305572

  I have created a report (for sales Order as well as Purchase Order) which contain a lot of fields from many different tables. Every field has an expression in order to get the correct values in front of sales/purchase order e.g:

  iif(isNothing(Fields!VendorInvoiceAmount.Value), Fields!CustomerInvoiceAmount.Value, Fields!VendorInvoiceAmount.Value)

  The problem is if a vendor/customer have 2 different Invoices and different types of tax are applied on them.

  I have the following table (just 3-4 fields to make you understand).

  SourceInvoiceNo - InvoiceAmount - SalesTaxAmount - Total

  INV-000001 ----- - ----- 1000 ----- - ------- 200 ------ - -------- 1200

  INV-000002 ----- - ----- 2000 ----- - ------- 100 ------ - -------- 2300

  INV-000002 ----- - ----- 2000 ----- - ------- 200 ------ - -------- 2300

  AP-0001 -------- - ------ 3000 ----- - ------- 100 ------ ---- ----- 3100

  GRAND TOTAL-------------------------------------------------------8600

  Like in INV-000002 on the same invoice amount 2 different type of taxes are applied.

  the total should be 2000+100+200 = 2300. but here grand total is creating the problem.

  i tried to solve this but putting this expression in Total.

  Iif(Fields!SourceInvoiceNo.value = Previous(Fields!SourceInvoiceNo.value), 0 , iif(isNothing(Fields!VendorInvoiceAmount.Value), (Fields!CustomerInvoiceAmount.Value + Fields!SourceRegulateAmount), (Fields!VendorInvoiceAmount.Value + Fields!SourceRegulateAmount))

  Note: Source Regulate Amount is Sales Tax Amount.

  this gives the following output (which i wanted) but grand total is still the same.

  SourceInvoiceNo - InvoiceAmount - SalesTaxAmount - Total

  INV-000001 ----- - ----- 1000 ----- - ------- 200 ------ - -------- 1200

  INV-000002 ----- - ----- 2000 ----- - ------- 100 ------ - -------- 2300

  INV-000002 ----- - ----- 2000 ----- - ------- 200 ------ - -------- 0

  AP-0001 -------- - ------ 3000 ----- - ------- 100 ------ ---- ----- 3100

  GRAND TOTAL-------------------------------------------------------8600

  Grand total should be 6600.

  In grand total i have this expression.

  iif(isNothing(Fields!VendorInvoiceAmount.Value), (Fields!CustomerInvoiceAmount.Value + Fields!SourceRegulateAmount), (Fields!VendorInvoiceAmount.Value + Fields!SourceRegulateAmount)