i have a stored proc returning inventory amounts between a start and end date.
the QTY field shows on-hand inventory adjustments by date, either IN or OUT.
i have a "run balance" field that needs to show the total QTY each day. if the InOut field is 0, it's QTY in. if it's a 1, its QTY out. the values reflected in In and Out are coded that way.
EXAMPLE. if i start on 4/1/14 with 5216. then 1061 of product is made on 4/1/14 i need the run balance to take 5216 and add 1061 on the next line. the next line should read 6277 instead of 1061. then if some is shipped, take 6277 and subtract, and so on.
i tried the running value on QTY, but it just repeats the QTY. doesn't calculate anything.
=RunningValue(Fields!QTY.Value,Sum,"Details")
i tried to post an image of my report preview, but i don't have the reputation ;-)
any help would be MUCH appreciated.
EDIT: OK, i tried this code block:
= Switch(RunningValue(Iif(Fields!InOut.Value = 0, Fields!QTY.Value,0),Sum,"Details"),
RunningValue(Iif(Fields!InOut.Value = "1", Fields!RunBalance.Value - Fields!QTY.Value,0),Sum,"Details"))
and i am getting a 0 on the sum fields, and #Error on the subtraction fields. but, i think this is more the direction i need to go (i hope)....but i am still not getting it.
There are a couple of things to look at here.
First, you specify the Scope of "Details"
in your RunningValue
expression... By default this group will have no grouping value, i.e. each row in the dataset will be in its own group. Which means the RunningValue
will only ever be applied to one row only. Change the Scope to "MyDataset"
or Nothing
, whatever is appropriate.
Secondly, you need to consider how the InOut
field affects the RunningValue
.
I have created a simple Dataset:
And a simple table:
The Balance
expression is:
=RunningValue(IIf(Fields!InOut.Value = 0, Fields!QTY.Value, Fields!QTY.Value * -1)
, Sum
, Nothing)
You can see this changes the Scope from your original statement, and also applies a multiplier to QTY
based on InOut
.
Works OK for my sample data: