Running balance question

• Question

• I have a requirement to build a report that has a beginning and ending measure of quantity on hand. During a month, the level of quantity can change. The ending QOH is the following month's beginning QOH. This value will be used for subsequent calculations. Here is a sample of the data set:

```MONTH	        QOH		Change
May		       50		(10)
June		       0		25
July		       0		35	```

What I want to do is get it into a format like this:

```MONTH	       Beg QOH	  Change  EndQOH
May		        50		(10)		40
June	        	40		25		65
July	        	65		35		100```

I can already get a running total in a separate column, but visually, I want to see it like in the second example.

• Edited by Wednesday, May 16, 2012 4:18 AM
Wednesday, May 16, 2012 4:16 AM

• Hi There

I have managed to put something together for you that might full fill your requirement and resolve your multi product values as well

This will be the formula for your End QOH

=RunningValue(Fields!Change.Value,sum,"Product")+first(Fields!BegQOH.Value,"Product")

WHERE

“Product” is the group I am assuming inside your tablix and Change is you values being changed during the month. So take the running value of thechange quanity and ass the first value of your opening

This will be the formula for your opening QOH

=RunningValue(Fields!Change.Value,sum,"Product")+first(Fields!BegQOH.Value,"Product")-Fields!Change.Value

WHERE

“Product” is the group I am assuming inside your tablix and Change is you values being changed during the month and I am adding the first month value into it and subtracting the current month change value. I hope you understand that.

I am putting screenshot for your help

If you have any question please let me know.

Many Thanks

Syed Qazafi Anjum

```--For Begining of Quantity
=RunningValue(Fields!Change.Value,sum,"Product")+first(Fields!BegQOH.Value,"Product")-Fields!Change.Value
--for end of Qty
=RunningValue(Fields!Change.Value,sum,"Product")+first(Fields!BegQOH.Value,"Product")```

Friday, May 18, 2012 1:44 AM
• Hi There

```Beginning QOH:
=RunningValue(Fields!NETCHANGE.Value,sum,"Group2") + first(Fields!QOH.Value) - Fields!NETCHANGE.Value```

Many Thanks

Syed

Friday, May 18, 2012 4:40 PM

All replies

• Hi JSmith,

in your report just add the thrid column and write below expression

Fields!QOH.value + Fields!Change.value.

You can achieve this using matrix or table in SSRS.

Dasari

Wednesday, May 16, 2012 4:25 AM
• Hi Dasari,

Once I get the End QOH, which adding QOH + Change would get me, I need to set the following month's Beginning QOH to what the previous month's End QOH was.

Wednesday, May 16, 2012 2:14 PM
• Hi Smith,

you need to write CTE SQL query.

I hope that will solve ur issue.

Dasari

• Marked as answer by Thursday, May 17, 2012 6:06 AM
• Unmarked as answer by Thursday, May 17, 2012 9:07 PM
Wednesday, May 16, 2012 11:00 PM
• A CTE on this would take awhile. I am using a SP with temp tables instead. This solution only gets me part of the way there.

The link supposes that I am only talking about inventory on one part. What would happen if was taking inventory on multiple parts? I know this is out of scope from the original question, but it is more inline with my goal. Data below.

 MONTH PartNum QOH Change May 12234 50 -10 June 12234 0 25 July 12234 0 35 May ABC1 75 -5 June ABC1 0 100 July ABC1 0 65

• Edited by Thursday, May 17, 2012 10:02 PM
Thursday, May 17, 2012 10:01 PM
• Hi There

I have managed to put something together for you that might full fill your requirement and resolve your multi product values as well

This will be the formula for your End QOH

=RunningValue(Fields!Change.Value,sum,"Product")+first(Fields!BegQOH.Value,"Product")

WHERE

“Product” is the group I am assuming inside your tablix and Change is you values being changed during the month. So take the running value of thechange quanity and ass the first value of your opening

This will be the formula for your opening QOH

=RunningValue(Fields!Change.Value,sum,"Product")+first(Fields!BegQOH.Value,"Product")-Fields!Change.Value

WHERE

“Product” is the group I am assuming inside your tablix and Change is you values being changed during the month and I am adding the first month value into it and subtracting the current month change value. I hope you understand that.

I am putting screenshot for your help

If you have any question please let me know.

Many Thanks

Syed Qazafi Anjum

```--For Begining of Quantity
=RunningValue(Fields!Change.Value,sum,"Product")+first(Fields!BegQOH.Value,"Product")-Fields!Change.Value
--for end of Qty
=RunningValue(Fields!Change.Value,sum,"Product")+first(Fields!BegQOH.Value,"Product")```

Friday, May 18, 2012 1:44 AM
• Wow, Syed, very close! Results are below. The second row (06/01/2012) decrements the quantity incorrectly. This skews the results, but the format is exactly what I am looking for. (Ignore other columns besides the relevant one)

I actually solved the multiple parts issue by changing the ROW_NUMBER command. Instead of doing it on date, I changed it to part number.

• Edited by Friday, May 18, 2012 4:27 PM
Friday, May 18, 2012 3:54 PM
• Hi There

I am really glad that your problem has been resolved. Every day we learn a lot from each other that what we called sharing thoughts.

Many thanks

Syed Qazafi Anjum

Friday, May 18, 2012 4:02 PM
• Hi There

I am really glad that your problem has been resolved. Every day we learn a lot from each other that what we called sharing thoughts.

Many thanks

Syed Qazafi Anjum

Any thoughts on why the second row is not showing the correct beginning QOH?
Friday, May 18, 2012 4:05 PM
• Hi

Can you please share with me the expression which you are putting inside your beginning QOH and enind QOH

what are the values you have inisde your dataset for beginning QOH

is it 5 against 5/1/2012

Many thanks

Syed Qazafi Anjum

Friday, May 18, 2012 4:23 PM
• Hi,

Beginning QOH:

=RunningValue(Fields!NETCHANGE.Value,sum,"Group2") + FIRST(Fields!QOH.Value) - Fields!NETCHANGE.Value

End QOH:

=RunningValue(Fields!NETCHANGE.Value,sum,"Group2") + FIRST(Fields!QOH.Value)

Start value for Beginning QOH for this part is 5 for 5/1/2012

• Edited by Friday, May 18, 2012 4:37 PM
Friday, May 18, 2012 4:35 PM
• Hi There

```Beginning QOH:
=RunningValue(Fields!NETCHANGE.Value,sum,"Group2") + first(Fields!QOH.Value) - Fields!NETCHANGE.Value```

Many Thanks

Syed

Friday, May 18, 2012 4:40 PM
• Hi Syed,

Nevermind on the problem. Bad data (or more like bad SQL) was causing it. Your code is correct.
• Edited by Friday, May 18, 2012 5:46 PM
Friday, May 18, 2012 4:49 PM
• Hi There

Great to hear this as I could not reproduced it on my test environment

Many thanks and good luck again

Syed Qazafi Anjum

Friday, May 18, 2012 5:49 PM