My boss wants a report to show how many requests we are receiving and closing in a month which is easy enough. He also wanted to show an Opening Balance and Closing Balance column, which is causing me problems.
Here’s a mockup of the report.
Month JAN_OPEN_BAL Received Completed Closing Balance
January 283 100 68 315
February 88 33 55
March 115 56 59
April 116 65 51
May 99 88 11
June 133 114 19
We don’t really have a way of going back and finding the opening balance so for January I created a Freehand query as a separate data provider hardcoding 283 and January, whcih I then linked to the other data provider.
Closing Balance is a variable and is defined as Jan_Open_Bal + Received - Completed.
What I’d like to do is create a new Opening Balance variable to get the previous month’s Closing Balance and then have the Closing Balance reference the new Opening Balance variable in it’s definition.
That doesn’t work because I get a Looping Variable Definition error.
Does anyone have a suggestion on how to do what I’m trying ? I’ve tried all types of combinations of variables but it’s not working.
Using the example you provided. what are the results you want to see in this new variable.
If I know the results we’re after I should be able to get it working
That looks interesting, I didn’t think to use the RunningCount function. Feel free to mail the report to me. If you click on the email button in one of my posts, it should give you my email address.