Receivables Aging in Analysis Cubes for Excel
Posted Thursday, November 5th, 2009
One of the default cubes in the Analysis Cubes for Excel product for Dynamics GP is the Receivables cube. It can be used to generate refreshable aging reports via an Excel Pivot Table.
To do this, users should add the Company ID attribute to the Report Filter area to filter the results for a single company. Select the Aging Periods attribute for the Column Labels and the Customers attribute as the Row Label. After that, all that’s left is to add the Aging Amount measure as the Values section in the PivotTable.
If the aging routine hasn’t been run recently for your company database, however, an area of confusion may arise as users try to tie the cube aging report to the one found in GP. This has to do with how the aging routine and the cube data load work together to populate the cubes in the data warehouse.
When the aging routine is run in GP (Tools >> Routines >> Sales >> Aging), it updates several fields in the RM20101 table. If documents in this table have aged to the point that they should be bucketed in a different aging period, this routine ensures that the aging period index for that document is updated appropriately in the underlying GP table.  Coincidentally, it is from this very same table from which the Receivables cube draws its data. Not surprisingly, the data load for the Receivables cube will only be correct as of the settings identified during the last time the aging routine has been run.Â
Therefore, in order to ensure that amounts from the various Receivables documents are bucketed in the correct periods, you may want to consider automating the Receivables aging routine to run just prior to the data load for the cubes. Additionally, you’ll want to make sure that both procedures occur on the same day to ensure that documents are bucketed correctly. Doing so will ensure that the data in the cube will tie out to any Aged Trial Balance report printed from GP during the course of the business day immediately following the data load.
Although several methods exist for automating the aging process, including requiring your users to run the process manually at COB. One of the best descriptions of how to accomplish this process I have seen resides over on Michael Johnson’s blog. Be sure to check the following link for a detailed description for automating your aging process: http://mbsguru.blogspot.com/2009/04/automatic-aging-for-gp.html
Tags: Analysis Cube for Excel, AR, Business Intelligence, Dynamics GP
Posted in Business Intelligence, Dynamics GP, Reporting
Need Expert Help With Dynamics GP?
Get the support you need to effectively manage your business.
Learn more about our Dynamics GP support options
[...] [...]
[...] a lot of time with Analysis Cubes for GP. In his latest post up at DynamicsCare, he looks at creating a refreshable AR aging report using Analysis Cubes and an Excel Pivot Table. Published: Thursday, November 05, 2009, 11:00 [...]
The report is nice, but it sure would be great if it would work with companies in the rows rather than customers. This would give us a consolidated aging summary by company. When I substitute company for customer, the consolidated grand total amounts are reported for each company as well as the total. Is this something we can fix?