Analytical Accounting includes a number of inquiries, which can be used to view information.
The Transaction Dimension Relation is the simplest of the inquiry windows and does not have any user-definable criteria. The inquiry window shows the hierarchical structure, if any, created for the alphanumeric dimension codes.
The Analytical Accounting – Journal Entry Inquiry can be used to view the analysis information for posted transactions as well as the consolidated analysis information for the balances that are brought forward.
To view a posted transaction's analysis information, perform the following steps:
Open the Analytical Accounting – Journal Entry Inquiry window in Dynamics GP by selecting Financial from the navigation pane on the left, and then clicking on Journal Entry Inquiry in the area page under Inquiry | Analytical Accounting.
In the Budget ID field, enter or perform a lookup for a posted transaction with analysis information. In this example, I am using journal
3,454.Use the navigation buttons next to the Distribution field to scroll through the distributions to view the analysis information in the window at the bottom of the window.
The Analytical Accounting Distribution Query Wizard is used to create, execute, and export queries to a Microsoft Excel worksheet and allows queries to be saved for later reuse.
Posted analysis information for transaction dimension codes that have subsequently been set to inactive status can be viewed, as well as the consolidated balances that are brought forward when a fiscal year is closed; the beginning value of the period range must be 0 in the Distribution Query Wizard – Finish window in order to view consolidated balances.
To use the Distribution Query Wizard to run a query, follow these steps:
Open the Distribution Query Wizard window in Dynamics GP by selecting Financial from the navigation pane on the left, and then clicking on Distribution Query Wizard in the area page under Inquiry | Analytical Accounting.
On the Column Selection step, select the following items in the Available Columns list, and then click on the Insert>> button after selecting each one:
Journal Entry
GL Posting Date
Account Number
Account Description
Credit Amount
Debit Amount
Vendor ID
Item Number
Trx Dimension – Alphanumeric
Marketing Channels
Click on Next.
In the list, select Marketing Channels to populate the Selected Column field.
Set the Select Type to Is Between.
Set the range to
FACEBOOKandTWITTER.Mark the Balance Sheet checkbox.
Click on Next.
Click on Next.
On the Completing the Options step, set Period in the From field to
1and the To field to12.Enter
2017 Marketing Channelsin the Comment field.Click on Finish to generate the inquiry to Excel.
The preceding steps show how to create an ad hoc query, but the inquiry also allows queries to be saved for later by adding a couple of extra steps to them. To save a query, perform these steps:
Open the Distribution Query Wizard window in Dynamics GP by selecting Financial from the navigation pane on the left, and then clicking on Distribution Query Wizard in the area page under Inquiry | Analytical Accounting.
Select Query Maintenance and click on Next.
In the Query ID field, enter
2017MARKETING.Enter
2017 Marketing Channelsin the Description field.Click on Next.
Use the steps from the Ad Hoc Query steps mentioned previously to create the query and save it for use.
The query can be run using the third option on Distribution Query Wizard. To do so, follow these steps:
Open the Distribution Query Wizard window in Dynamics GP by selecting Financial from the navigation pane on the left, and then clicking on Distribution Query Wizard in the area page under Inquiry | Analytical Accounting.
Select 2017MARKETING in Query ID, and click on Next.
Enter a Comment and click on Finish to run the query as saved, or use the < Back button to amend the query.
There is no limit to the number of queries that can be saved in this inquiry.
The Analytical Accounting Multilevel Query Wizard is used to create, execute, and export queries to a Microsoft Excel worksheet. Queries can be created to analyze the data based on transaction dimensions, which can include dimensions set to inactive as well as consolidated balances that are brought forward when a fiscal year is closed.
As with the Distribution Query Wizard, queries can be saved for reuse as well as being constructed and used on an ad hoc basis.
Creating and saving a Multilevel Query is the same as creating and saving a Distribution Query. In this example, we'll cover creating an ad hoc query. To create one, perform the following steps:
Open the Multilevel Query Wizard window in Dynamics GP by selecting Financial from the navigation pane on the left, and then clicking on Multilevel Query Wizard in the area page under Inquiry | Analytical Accounting.
Select Execute Ad Hoc Query, and click on Next.
On the Level Selection step, select the following items in the Available Items list, and click on the Insert>> button after selecting each one:
Time
Account Number
Marketing Channels
Click on Next.
On the Column Spreads step, click on Next.
Enter
MT_ACCOUNT, or perform a lookup and selectMT_ACCOUNTas the Tree for the Account Number level.Enter
MT_MARKETING, or perform a lookup and selectMT_MARKETINGas the Tree for theMarketing Channelslevel.Click on Next.
Mark the Balance Sheet checkbox, and click on Next.
On the Column Selection step, select the following Available Columns and click on the Insert>> button.
Net change
Net change YTD
Click on Next.
Set the Period range in the From field to
4and the To field to4.Click on Finish to generate the query.
In the resulting Excel spreadsheet, click on the expansion button to show the multilevel data.
The Analytical Accounting Budget vs Actual Inquiry window can be used to view the actual and budgeted amounts for a selected node along with the variance calculated as both an amount and a percentage. To use the inquiry, perform the following steps:
Open the Analytical Accounting Budget vs Actual Inquiry window in Dynamics GP by selecting Financial from the navigation pane on the left, and then clicking on Budget vs Actual in the area page under Inquiry | Analytical Accounting.
Enter
2017MARKETINGin the Budget ID field.Enter
2017in the Year field.
In the Dimension Code Tree list, click on Online Adverts to populate the grid at the bottom of the window with the actual and budget values for the selected dimension code.