GLPeriodBal.BalanceAmt is the right field which stores balance for each fiscal period. You can add a BAQ Parameter for Fiscal Year and Fiscal Period (<= period value like 1) which should give YTD balance.
Thanks, good to know we’re on the right track. Could you be a bit more specific on the parameter? How would I go about doing that? We have been defining parameters, but I’m not sure how I would go about specifying the “<=period value like 1”.
In a BAQ, you can add criteria to a Table, to limit the records fetched.
- Select the table in the designer
- Select the Table Criteria tab
- Click the icon for Add Row
- Select the table’s field to use for the condition
- Select the
- Click the underlined “specified”
- In the pop-up wind select the paramter you created.
Thanks Calvin. We do have a number of defined parameters set up, but I’m actually wondering specifically about the “<=period value like 1” that I would need to create to generate the YTD balance. Any help would be appreciated, thanks.
Fiscal period 0 of GLPeriodBal will hold the opening balance for the Fiscal Year.
You might need to make a subquery the has just the GLPeriodBal table, with table criteria of
OpenBalance = constant 0. In the columns for that subquery, include: Company, BookID, BalanceAcct, FiscalYear, and OpeningBalance.
Then add that subquery to your mainquery, linking the Company, BookID, and FiscalYear to the exist query. Finally include the OpenBalance column (from the sub query) to the list of columns on the main query.
Ok got it. Sorry, one more question. What do you mean by “include the OpenBalance column (from the sub query) to the list of columns on the main query”? Thanks.
You don’t have to include it as a column, it can be used in a calculated field. But if you’re going to do some of the summation and what-not in the Report, then you’ll need this value in the dataset.
The following shows a very simple BAQ of the GLJrnDtl table. all but the last column come from this table, and the last comes from the subquery that gets the Opening Balance for the FY.
Got it, thanks. But I’m confused, how is this going to help me get my YTD balance?
Maybe we need to step back and revisit what you’re trying to do.
What exactly are you trying get (or calculate) the YTD balance of?
And “YTD Balance” is kind of an oxymoron. You want the sum of transactions from the beginning of the year through today?
Like what would be the “YTD Balance” of a bank account, that had a balance of $10,000 on 12/31/2019, and a lone deposit of $5,000 on 6/1/2020?
Correct. So in your example, my YTD Balance for that bank account, if I ran the query 06/01/2020, would be $15,000.
So that’s just the balance. The “Year To Date” doesn’t really mean anything in this context. My “YTD Balance” on any given date is just the Balance on that date.
When I think of YTD, I take it to mean only things that occurred since the beginning of the year. For example, “YTD Sales” are only the sales (minus returns) that happened after the start of the year.
For balance sheet, yes. However, that would not be true for income statement accounts. For I were to run the query, it would only give me the monthly activity for a particular income statement account, not the running balance.
I’ve recreated a simple query, using defined parameters (=) around FiscalYear and BalanceAccount. Below is snip from query:
And here is snip from chart tracker:
Notice how the balance from the query is only the monthly activity, not the running total.
My other issue is that the query doesn’t return 0 balances. So if an account has no activity in a period, it won’t pull that period into the query, even though I’m telling the query to run all periods:
Notice how the query in this example skips period 3, because period 3 had no activity in that month.
My approach to items like this is to create a subquery that join the GL account table to the fiscal period. This subquery will be the base I use for all my other queries since it will return all GL accounts and all fiscal periods. Then I can join that subquery to the period balance to get the total monthly amount. To get the ytd amount I’ll join the period balance again, but this time FiscalYear = FiscalYear and FiscalPeriod <= FiscalPeriod summing all the period balances to get the YTD total.
The YTD could also be done with a sum with a partition by clause on it.
Ok thanks. Is it possible to see a visual of this? We’re very new to Epicor 10 and BAQs, so we’re still trying to wrap our heads around it. Greatly appreciated.
I’ll do a video on it. I think making “fake” records in queries is an interesting problem.
That would be amazing, thank you.
Here you go.
Worked perfectly, really appreciate this Carson!
Your video is super helpful!! Just wanted to say thank you!