Calculate Opening Balance Using SQL Server Reporting Services? RRS feed

  • Question

  • Well, not quite as easy as I thought!

    Everything went on well except my main problem: the Opening Balance.

    I followed this Walk Through: MSDN: Adding Parameters to Your Report

    What I need is to add a TextBox on top of the [Report] Table and then use an Expression to display Opening Balances. For instance, say I am retrieving sales data for the period 01/01/2001 and 01/01/2003 for a given Sales person. I want to calculate the Sales person's total sales UP TO 31/12/2000 and place it in the textbox. Note that the Opening balance will depend on whatever value entered by the user as the Start Date parameter. The Opening Balance for a Start Date of say 01/01/2000 will not be the same as the opening balance of 01/01/2001.

    In Access I could have used DSUM and then create a Parameter form that prompts a user for start and end dates.

    Your answer will be appreciated.

    Only performance counts!
    Thursday, May 20, 2010 10:39 PM


  • Thanks to everyone. I knew this was going to be an easy thing to do but I'm surprised that no one was able to give me a direct and straight answer even though Igor and others did provide some useful clues.

    It was indeed very easy:

    1. I created a New DataSet, dsOpeningBal with a single column (LineTotal) and single parameter (opBalStartDate)

    2. I set the Default value of the opBalStartDate to the startDate parameter of the main DataSet

    3. I also set the Available Value property of the opBalStartDate as StartDate

    4. This ensures that the opBalStartDate is the same as the startDate at all times

    5. I then used the following Expression to set the value of the TextBox:


    =Sum(Fields!LineTotal.Value, "dsOpBal")


    And that did it!

    I will write a blog on this later to assist others with similar problems.

    I'm really disappointed that more than four days of Googling and Binging and over 6 related questions in different Forums could not help me out of a very simple question like this.

    Thanks to everyone.

    Only performance counts!
    Sunday, May 23, 2010 3:56 PM