SUM(Field1) based on value of Field2

[ousoonerjoe]

I am working on an accounting report and have run into an issue. I have 3 fields: CurrAmt, FutureAmt, Discount. I need to report the individual values as well as a Grouped Value. How can this be accomplished in a Report Field?

for example:

[u]CurrAmt[/u]  [u]FutureAmt[/u]   [u]Discount[/u]
 100.00       0.00       1.00
   0.00     120.00       0.00  
  50.00       0.00       2.00
   0.00      20.00      10.00
   0.00      25.00       5.00

Current Discount:  3.00
Future Discount : 15.00

As always, any suggestions are welcome. Thank you.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[MDXer]

I'm not a major SSRS user but the way I have solved this in the past was with a seperate dataset for Current Discount and Future Discount.

Select
SUM(Discount)
From Table
Where CurrentAmt > 0

or FutureAmt > 0 depending on the dataset

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ousoonerjoe]

yeah.... i was trying to avoid adding another dataset to the report. I know there's a way to do it, but I don't know how.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[xlbo]

Can create a report variable:

NewVar:
=iif(CurrentAmt>0,Discount,0)

Then

=sum(NewVar)

should do the trick

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ousoonerjoe]

Could you please explain the variables a little further. Just to be clear, I'm using 2005 Reporting Services.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[xlbo]

What aspect do you need explaining?

Right click on any data item in your available fields and choose "Add"

Select "Calculated Field"
Give it a name
Enter the formula
et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ousoonerjoe]

Thought I'd post my fix.

I made a slight logic change. Adjusted the Stored Proc to tell me what was a "Current Amount" and what was "Future Amount". Then i had the SP just return a simple "Gross Amount" from there, i was able to use the following to achieve the data I was looking for (adjusting accordingly):

=Sum(IIF(Fields!PayCode.Value = "Current", Fields!Gross_Amount.Value, CDec("0.00")))

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.