Our company is new to Epicor 10 (came over from 7 … I know, I know, big leap). We’re very new to the BAQ tool.
In E7 we had the use of Explorer to run queries on YTD account balances.
I know YTD balances appear in Chart Tracker, but I can’t find a relevant display field in BAQ designer to pull this info in. I had to resort to a calculated field. So, two questions:
Do I need to use a calculated field for this? And,
If I do, my current calculation requires us to generate all periods in the query (beginning of year until now) in order to get the right YTD balance. Any way to make it easier if we only want to run data for the 1 period?
(sum(GLPeriodBal.BalanceAmt) OVER (PARTITION BY GLPeriodBal.FiscalYear, GLPeriodBal.Company, GLPeriodBal.SegValue2, GLPeriodBal.SegValue1, GLPeriodBal.SegValue3 ORDER BY GLPeriodBal.Company, GLPeriodBal.SegValue2, GLPeriodBal.SegValue1, GLPeriodBal.SegValue3, GLPeriodBal.FiscalPeriod)) as [Calculated_YTDBalance]
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”.
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.
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.
