locked
Running balance question RRS feed

  • 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 DL_Spokane Wednesday, May 16, 2012 4:18 AM
    Wednesday, May 16, 2012 4:16 AM

Answers

  • 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

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    --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

    please try

    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.

    I hope this will help you.


    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.

    please click on the below link

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9104b950-33c6-4fc4-823b-a071e3c684bf/#d06de583-0a3b-4022-810d-91452c604b51

    I hope that will solve ur issue.


    Dasari

    • Marked as answer by Lola Wang Thursday, May 17, 2012 6:06 AM
    • Unmarked as answer by DL_Spokane 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 DL_Spokane 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

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    --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 DL_Spokane 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.

    Good luck for your project

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

    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.

    Good luck for your project

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

    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

    please update me then i might be able to help you out

    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

    Thank you for your time.



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

    please try

    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 DL_Spokane 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