SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 sum a column which based on some expr in SSRS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rizwan.haider
Starting Member

Pakistan
1 Posts

Posted - 03/23/2014 :  04:04:47  Show Profile  Reply with Quote
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))

Edited by - rizwan.haider on 03/23/2014 04:16:18
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000