Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chriskhan2000
Aged Yak Warrior
544 Posts |
 Posted - 2005-07-14 : 17:39:45
|
I've asked this so many time that I'm losing hope. I can't seem to sum the detail field of what I see. Reason why I need to sum what I see is base on a long condition. If I just sum, it will sum everything which I don't want and only want what my condition outputs.Here's my codition:=IIF( Fields!ACCT_API_AMOUNT.Value IS NOTHING AND Fields!ACCT_PUR_AMOUNT_TYPE.Value IS NOTHING, Sum(Fields!DEBIT_CREDIT.Value), IIF( Fields!ACCT_API_AMOUNT_TYPE.Value = "DR", SUM( Fields!ACCT_API_AMOUNT.Value), IIF( Fields!ACCT_API_AMOUNT_TYPE.Value = "CR", SUM( Fields!ACCT_API_AMOUNT.Value) * -1, IIF( Fields!ACCT_PUR_AMOUNT_TYPE.Value = "DR", SUM( Fields!ACCT_PUR_AMOUNT.Value), SUM( Fields!ACCT_PUR_AMOUNT.Value) * -1)))) With condition it will pull each record correctly base on what the cnodition states. If I don't use this then I get all sorts of numbers from all over the place. Tried doing ReportItems, but only works in Footer. Anyone experience enough to get this to work in custom code? Or any ideas how to go about just Summing what was output? |
|
|
jhermiz
3564 Posts |
Posted - 2005-07-14 : 23:06:49
|
quote:
Originally posted by chriskhan2000
I've asked this so many time that I'm losing hope. I can't seem to sum the detail field of what I see. Reason why I need to sum what I see is base on a long condition. If I just sum, it will sum everything which I don't want and only want what my condition outputs.Here's my codition:=IIF( Fields!ACCT_API_AMOUNT.Value IS NOTHING AND Fields!ACCT_PUR_AMOUNT_TYPE.Value IS NOTHING, Sum(Fields!DEBIT_CREDIT.Value), IIF( Fields!ACCT_API_AMOUNT_TYPE.Value = "DR", SUM( Fields!ACCT_API_AMOUNT.Value), IIF( Fields!ACCT_API_AMOUNT_TYPE.Value = "CR", SUM( Fields!ACCT_API_AMOUNT.Value) * -1, IIF( Fields!ACCT_PUR_AMOUNT_TYPE.Value = "DR", SUM( Fields!ACCT_PUR_AMOUNT.Value), SUM( Fields!ACCT_PUR_AMOUNT.Value) * -1)))) With condition it will pull each record correctly base on what the cnodition states. If I don't use this then I get all sorts of numbers from all over the place. Tried doing ReportItems, but only works in Footer. Anyone experience enough to get this to work in custom code? Or any ideas how to go about just Summing what was output?
Evening Chris,Chris if you just want to sum specific data, your query should ONLY pick up that data and nothing more. Than your SUM becomes a simple statement rather than this huge IIF.The other method to do this is to use a group by that field and sum based on the GROUP footer and not the details section.Please post sample data line items so that I can show you how to pull that exact data and how to sum it.You can also if you want perform your sum in your stored procedure.Jon
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
 |
|
|
chriskhan2000
Aged Yak Warrior
544 Posts |
Posted - 2005-07-15 : 09:38:36
|
| Jon,Thanks for the advice. I didn't realize it until you mention about working with in my SP. What I did was use a CASE exactly as my expressional condition and got the same result.Reason why I have to use a CASE or Expression is that it's one huge query with 2 sub queries. So I need for it to see if it's Credit, Debit, or if those fields are empty and then use another field for the Debit and Credit. Kind of confusing, but that's what the financial people wants so now it's all good.Thanks again. |
|
|
|
|
|
|
|
|
Sum Field