September 15, 2008 at 8:23 am
Anybody care to cast their eyes over this situation?. (SSRS - 2005)
There is a report with three columns in the detail line.
QuantityPriceValue
Quantity and price are pulled directly from the data set. Value is calculated to be zero - OR - if Quantity is greater than 0. (Ie. sometimes it's negative), then it's Quantity * Price.
So it might appear as:-
Quantity PriceValue
10 12.00120.00
-58.000.00
-15.000.00
210.0020.00
That's ok - I calculate the 'Value' field using an iif expression on quantity > 0.
However, when I come to sub total (or grand total) on 'Value' It just won't like it - because of the Iif statement. I've tried using a calculated field to keep a running total - but it doesn't like that either - becuase of the Iif statement.
Any ideas?
September 15, 2008 at 8:52 am
Can you share your IIF expression? And the error message and/or result you're getting when you attempt to sum the Value column.
Depending on what you've written or are getting back, there might be some syntactical tricks we can try.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
September 16, 2008 at 3:44 am
The detail row is grouped - because there are negative and positive values for quantity.
The Iif statement on the Total column is :=
=iif(sum(Fields!ADJUST_QTY.Value*Fields!ASTD_TOTAL_COST.Value) > 0, sum(Fields!ADJUST_QTY.Value*Fields!ASTD_TOTAL_COST.Value),0)
Which gives a correct value for the detail row, but when it totals up this column in a group of report footer, it's including all the rows that have a negative value.
Eg.
Qty Cost Value of +/- Total
-10 5.00 -50.00 0.00
20 10.00 200.00 200.00
-5 5.00 -25.00 0.00
Grand Total 125.00 125.00 (When I want it to be 200.00). I tried to use a calculated field for the Total Column - but that complains about 'Public member 'Value' on type 'Decimal' not found.' - Even when I tried to 'Val' everything.
I can see why it's doing what it does, but lost as to a way around it!!!
September 16, 2008 at 7:18 am
Peter,
Try this:
1. Create a calculated field called PosQty as
=IIF(Fields!ADJUST_QTY.Value > 0, Fields!ADJUST_QTY.Value,0)
2. Create a second calculated field called PosCost as
= IIF(IIF(Fields!ADJUST_QTY.Value > 0, Fields!ASTD_TOTAL_COST.Value,0)
3. For your total line use =SUM(PosQty) * SUM(PosCost)
I think that should work. If not, post back and we'll try something else.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply