Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.04 seconds. Powered By: Snitz Forums 2000