Calculate Total Revenue and COGS in Generic Inquiry

Hi ​@jzhu ,

Please export the XML of your GI and attach it so we can take a look.

Thanks,

Laura

Hi ​@lauraj46 Thank you! 

Hi ​@lauraj46 I checked the source data of GLHistory DAC, there are records for the selected accounts and financial periods, however they still do not show in the GI. Do you know if there is another way to calculate the Total Revenue and COGS? I tried using the GLTran DAC and it did not work either. 

Hi ​@jzhu ,

That’s very odd that the GLHistory record would not be included in GI results.  Did you try to troubleshoot the GI by starting with just that table and no conditions?  Add back one bit at a time time until you find where it breaks.

GLTran would be my other suggestion for calculating account balances.  Try the same thing with that table - just a simple query on that one DAC with no other relations.

I’m not sure why that wouldn’t return data, unless there weren’t any transactions in the period.  Does the ARM report show a non-zero value for the account and period?

Hi ​@lauraj46 I looked at a few other calculations and their accounts, it seems like for some, the issue is that they are not in the GLHistory DAC source data, which explains why those accounts don’t show up in the GI. I’m not sure I understand why they don’t show up in the DAC. 

My question then would be, where is the ARM report pulling the information from? On the ARM report, when the selected period has no credit or debit amount, its ending balance would just equal to the beginning balance. Is there another way to bring the accounts into the GI even if they are not on the source data? 

Hi ​@jzhu ,

I’m not sure about the data source for ARM reports, but I would guess that it’s GLTran. To include all accounts, try taking a look at the standard Trial Balance Summary report.  This report uses the DAC GLHistoryByPeriod.

Hope this helps!

Laura

Hi ​@lauraj46 I tried relating GLHistoryByPeriod and GLHistory table like the Trial Balance but still don’t get the same result. I think the issue is that even though GLHIstoryByPeriod has all the accounts, but in GLHistory, it does not have the records I need. Thep pattern I’ve found is if no transaction is posted to it, then it does not show up. 

Hi ​@jzhu ,

If you use a left join between GLHistoryByPeriod and GLHistory then the record will be included even if there’s no  GLHistory.

Hope this helps!

Laura

Hi ​@lauraj46 I tried matching the relations exactly like the Trial Balance Summary report and while it does give me all the accounts, it does not have all the financial periods. It’s because no transactions are posted into that account in that period, so when I group by finperiodID, the balance is still wrong and does not match the P&L unfortunately. Thank you for your suggestions!! 

Hi ​@lauraj46 Do you if there is a way to show all financial periods regardless of the activities? For example, 01-2024 had no activities in any accounts so it shows 0, then skips February and March. Similar to 2025, no financial periods are shown after 02-2025 and it jumps to 01-2026. I’m not sure why only 01-2024 and 2026 displays zero, but is there a way to show other periods as well even when value is zero?

Hi ​@jzhu ,

Most likely the reason you are missing these accounts is that there is no GLHistory record for the period.  To include all accounts regardless of activity, you could start with the Account DAC and use a left join to the GLHistory DAC.  You will need to move the date condition to be a part of the join, otherwise the blank records will be filtered out.  Finally, change the account code and description fields in the results to use the Account DAC.

I’ve attached a revised query for your reference.

Hope this helps!

Laura

Hi ​@lauraj46 

I’m revisiting this topic for a different generic inquiry and what you provided here helps a lot! I do have an additional question on a small change. For the new GI, I don’t need the date condition. I would like the GI to display every period and account regardless of if there were activities or not. In that case, what would I be putting in the join for the for the parent field?

Hi ​@jzhu ,

In that case you can delete the row with the date condition from the join.  

Hope this helps!

Laura