Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to add Multidimensional Analysis information to the Trial Balance Detail report in Microsoft Dynamics GP

How to add Multidimensional Analysis information to the Trial Balance Detail report in Microsoft Dynamics GP

View products that this article applies to.

INTRODUCTION

This article describes how to add Multidimensional Analysis information to the Trial Balance Detail report in Microsoft Dynamics GP and in Microsoft Business Solutions – Great Plains.

↑ Back to the top

MORE INFORMATION

To add Multidimensional Analysis information to the Trial Balance Detail report, follow these steps.

 

Step 1: Back up the Reports.dic file, and then start Report Writer

1. If you have any modified Microsoft Dynamics GP reports, back up the Reports.dic file. To locate the Reports.dic file, follow these steps:
a.

Follow the appropriate step:

• In Microsoft Dynamics GP 10.0, point to Tools on the Microsoft Dynamics GP menu, point to Setup , point to System , and then click Edit Launch File .
• In Microsoft Dynamics GP 9.0 and in Microsoft Business Solutions - Great Plains 8.0, point to Setup on the Tools menu, point to System , and then click Edit Launch File .



     
b. If you are prompted, type the system password.
c.

Follow the appropriate step:

• In Microsoft Dynamics GP 10.0 and in Microsoft Dynamics GP 9.0, click Microsoft Dynamics GP in the Edit Launch File window.
• In Microsoft Business Solutions - Great Plains 8.0, click Great Plains in the Edit Launch File window.



     
d. Note the path that appears in the Reports box.
e. To close the Edit Launch File window, click OK .
2.

Follow the appropriate step:

• In Microsoft Dynamics GP 10.0, point to Tools on the Microsoft Dynamics GP menu, point to Customize , and then click Report Writer .
• In Microsoft Dynamics GP 9.0 and in Microsoft Business Solutions - Great Plains 8.0, point to Customize on the Tools menu, and then click Report Writer .



     
3.

Follow the appropriate step:

• In Microsoft Dynamics GP 10.0 and in Microsoft Dynamics GP 9.0, click Microsoft Dynamics GP in the Product list, and then click OK .
• In Microsoft Business Solutions - Great Plains 8.0, click Great Plains in the Product list, and then click OK .



     

Step 2: Create table relationships

1. Click Tables , click Tables , click GL_YTD_TRX_OPEN , and then click Open .
2. In the Table Definition window, click Relationships .
3. In the Table Relationship window, click New .
4. In the Table Relationship Definition window, click the ellipsis button (...) that is next to the Secondary Table field, select Transaction Analysis Groups , and then click OK .
5. In the Secondary Table Key list, click DTA_Trx_Groups_WORK_Key3 .
6. Select the account index in the Primary Table field to match the account index in the Secondary Table field, and then click OK .
7. Close the Table Relationship window.
8. Click OK to close the Table Relationship Definition window.
9. Click OK to close the Table Definition window.
10. Close the Tables window.
11. Click Reports , click Trial Balance Detail in the Original Reports list, and then click Insert .
12. In the Modified Reports list, click Trial Balance Detail , and then click Open to open the Report Definition window.
13. In the Report Definition window, click Tables .
14. In the Report Table Relationships window, click Year-to-Date Transaction Open* , and then click New .
15. In the Related Tables window, click Transaction Analysis Groups* , and then click OK .
16. In the Report Table Relationships window, click Transaction Analysis Groups* , and then click New .
17. In the Related Tables window, click Transaction Analysis Codes* , and then click OK .
18. In the Report Table Relationships window, click Transaction Analysis Codes* , and then click New .
19. In the Related Tables window, click Analysis Codes Master , and then click OK .
20. In the Report Table Relationships window, click Analysis Codes Master , and then click New .
21. In the Related Tables window, click Analysis Group Master , and then click OK .

The table hierarchy is displayed as follows:

General Ledger Trial Balance Temporary
- Account Master
- Year-to-Date Transaction Open*
-- Transaction Analysis Groups*
--- Transaction Analysis Codes*
---- Analysis Codes Master
----- Analysis Group Master
22. Click Close to close the Report Table Relationships window.

Step 3: Create restrictions

1. In the Report Definition window, click Restrictions , and then click New .
2. In the Report Restriction Definition window, type ACTINDX in the Restriction Name field, click Transaction Analysis Groups in the Report Table field, click Account Index in the Table Fields field, click Add Field , click = under Operators , click Year-to-Date Transaction Open in the Report Table field, click Account Index in the Table Fields field, and then click Add Field .

The restriction is displayed in the Restriction Expression field as follows:

DTA_Transaction_Groups_WORK.Account Index = GL_YTD_TRX_OPEN.Account Index
3. Click OK .
4. In the Report Restrictions window, click New , type IDENTITY in the Restriction Name field, click Transaction Analysis Codes in the Report Table field, click Account Index in the Table Fields field, click Add Field , click = under Operators , and then click Add Field .

The restriction is displayed in the Restriction Expression field as follows:

DTA_Transaction_Codes_WORK.Account Index = DTA_Transaction_Codes_WORK.Account Index
5. Click OK .
6. In the Report Restrictions window, click New , type MDA JOURNAL ENTRY in the Restriction Name field, click Transaction Analysis Groups in the Report Table field, click Journal Entry in the Table Fields field, click Add Field , click = under Operators , click Year-to-Date Transaction Open in the Report Table field, click Journal Entry in the Table Fields field, and then click Add Field .

The restriction is displayed in the Restriction Expression field as follows:

DTA_Transaction_Groups_WORK.Journal Entry = GL_YTD_TRX_OPEN.Journal Entry
7. Click OK .
8. In the Report Restrictions window, click New , type SEQUENCE NUMBER in the Restriction Name field, click ( , click Transaction Analysis Groups in the Report Table field, click Sequence Number in the Table Fields field, click Add Field , click = under Operators , click Year-to-Date Transaction Open in the Report Table field, click Originating Sequence Number in the Table Fields field, click Add Field , and then click ) .
9. Click OR , click ( , click Transaction Analysis Groups in the Report Table field, click Sequence Number in the Table Fields field, click Add Field , click = under Operators , click Year-to-Date Transaction Open in the Report Table field, click Sequence Number in the Table Fields field, click Add Field , and then click ) .

The restriction is displayed in the Restriction Expression field as follows:

(DTA_Transaction_Groups_WORK.Sequence Number = GL_YTD_TRX_OPEN.Originating Sequence Number) OR (DTA_Transaction_Groups_WORK.Sequence Number = GL_YTD_TRX_OPEN.Sequence Number)
10. Click OK , and then close the Report Restrictions window.

Step 4: Override the built-in sort that is used by the report

1. In the Report Definition window, click Sort .
2. In the Sorting Definition window, click General Ledger Trial Balance Temporary in the Report Table field, click Account Number in the Table Fields list, and then click Insert .
3. Click Year-to-Date Transaction Open in the Report Table field, click TRX Date in the Table Fields list, click Account Number of table General Ledger Trial Balance Temporary in the Sort By list, and then click Insert .
4. Click Year-to-Date Transaction Open in the Report Table field, click Journal Entry in the Table Fields list, click TRX Date of table Year-to-Date Transaction Open in the Sort By list, and then click Insert .
5. Click Transaction Analysis Groups in the Report Table field, click Sequence Number in the Table Fields list, click Journal Entry of table Year-to-Date Transaction Open in the Sort By list, and then click Insert .
6. Click Transaction Analysis Groups in the Report Table field, click DTA_Group_ID in the Table Fields list, click Sequence Number of table Transaction Analysis Groups in the Sort By list, and then click Insert .
7. Click Transaction Analysis Codes in the Report Table field, click DTA_Code_ID in the Table Fields list, click DTA_Group_ID of table Transaction Analysis Groups in the Sort By list, and then click Insert .

The Sort By list is displayed as follows:

Account Number of table General Ledger Trial Balance Temporary
TRX Date of table Year-to-Date Transaction Open
Journal Entry of table Year-to-Date Transaction Open
Sequence Number of table Transaction Analysis Groups
DTA_Group_ID of table Transaction Analysis Groups
DTA_Code_ID of table Transaction Analysis Codes
8. Click OK .
9. Click OK to close the Report Definition window.

Step 5: Open the report, and then create two calculated fields in the report layout

1. In the Report Definition window, click Layout .
2. In the Toolbox window, click Calculated Fields in the list, and then click New .
3. In the Calculated Field Definition window, type MDA CODE in the Name box, click String in the Result Type field, and then click Conditional in the Expression Type area.
4. Click the Conditional section, click Transaction Analysis Codes in the Resources field on the Fields tab, click DTA_Code_ID in the Field field, and then click Add .
5. Under Operators , click = .
6. Click the Constants tab, click String in the Type field, and then click Add .

The Conditional section is displayed as follows:

DTA_Transaction_Codes_WORK.DTA_Code_ID = ""
7. Click the True Case section, click the Constants tab, select String in the Type field, and then click Add .

The True Case section is displayed as follows:

""
8. Click the False Case section, click the Fields tab, click Transaction Analysis Codes in the Resources field, click DTA_Code_ID in the Field field, and then click Add .

The False Case section is displayed as follows:

DTA_Transaction_Codes_WORK.DTA_Code_ID
9. Click OK .
10. In the Toolbox window, make sure that Calculated Fields is still selected in the list.
11. Click New .
12. Type MDA GROUP in the Name box, click String in the Result Type field, and then click Conditional in the Expression Type area.
13. Click the Conditional section, click Transaction Analysis Groups in the Resources field on the Fields tab, click DTA_Group_ID in the Field field, and then click Add .
14. Under Operators , click = .
15. Click the Constants tab, click String in the Type field, and then click Add .

The Conditional section is displayed as follows:

DTA_Transaction_Groups_WORK.DTA_Group_ID = ""
16. Click the True Case section, click the Constants tab, click String in the Type field, and then click Add .

The True Case section is displayed as follows:

""
17. Click the False Case section, click the Fields tab, click Transaction Analysis Groups in the Resources field, click DTA_Group_ID in the Field field, and then click Add .

The False Case section is displayed as follows:

DTA_Transaction_Groups_WORK.DTA_Group_ID.
18. Click OK .

Step 6: Add new sections to the report

1. With the Report Layout window still appearing, click Tools , and then click Section Options .
2. In the Report Section Options window, click Account Number in the Additional Headers list, and then click New .
3. In the Header Options window, type JOURNAL ENTRY in the Header Name box, click Year-to-Date Transaction Open in the Report Table field, click Journal Entry in the Field list, and then click OK .
4. Click JOURNAL ENTRY in the Additional Headers list, and then click New .
5. In the Header Options window, type MDA GROUP in the Header Name box, click Transaction Analysis Groups in the Report Table field, click DTA_Group_ID in the Field list, click to select the Suppress When Field Is Empty check box, click MDA GROUP in the Calculated Field list, and then click OK .
6. In the Report Section Options window, click MDA GROUP in the Additional Headers list, and then click New .
7. In the Header Options window, type MDA CODE in the Header Name box, click Transaction Analysis Codes in the Report Table field, click DTA_CODE_ID in the Field list, click to select the Suppress When Field Is Empty check box, select MDA CODE in the Calculated Field list, and then click OK .
8. In the Additional Footers section, click New .
9. In the Footer Options window, type SEQUENCE NUMBER in the Footer Name box, click Transaction Analysis Groups in the Report Table field, click Sequence Number in the Field List, and then click OK .
10. In the Report Section Options window, click OK .
11. Drag each field in the B (Body) section of the report to the H2 section.
12. After you move each field to the H2 section, click Tools , click Section Options , click to clear the Body check box, and then click OK .
13. In the Toolbox window, click Calculated Fields in the list, and then drag the MDA GROUP calculated field to the H3 section of the report.
14. In the Toolbox window, click Calculated Fields in the list, and then drag the MDA CODE calculated field to the H4 section of the report.
15. In the Toolbox window, click Transaction Analysis Groups in the list, and then drag the DTA_Group_Amount field to H3 section of the report.
16. In the Toolbox window, click Transaction Analysis Codes in the list, and then drag the DTA_Amount field to the H4 section of the report.
17. To add text fields to the report, click A in the Toolbox window, click the report layout where you want to put the text field, and then type the text of the field.
18. Close the Report Layout window, and then click Save .
19. In the Report Definition window, click OK .
20.

Exit Report Writer. To do this, follow the appropriate step:

• In Microsoft Dynamics GP 10.0 or in Microsoft Dynamics GP 9.0, click Microsoft Dynamics GP on the File menu.
• In Microsoft Business Solutions - Great Plains 8.0, click Microsoft Business Solutions - Great Plains on the File menu.



     

Step 7: Assign security permissions to the modified report

 

Microsoft Dynamics GP 10.0

1. On the Microsoft Dynamics GP menu, point to Tools , point to Setup , point to System , and then click Alternate/Modified Forms and Reports .
2. If you are prompted, type the system password in the Please Enter Password box, and then click OK .
3. In the ID box, type the Alternate/Modified Forms and Reports ID that is associated with the user ID that will print this modified report.
4. In the Product list, click Microsoft Dynamics GP .
5. In the Type list, click Reports .
6. Expand Financials .
7. Expand the folder of the report that you modified.
8. Click Microsoft Dynamics GP (Modified) .
9. Click Save .
10. On the Microsoft Dynamics GP menu, point to Tools , point to Setup , point to System , and then click User Security .
11. In the User list, click a user ID.
12. In the Company list, click a company.
13. In the Alternate/Modified Forms and Reports ID list, click the ID from step 3.
14. Click Save .

Microsoft Dynamics GP 9.0 and Microsoft Business Solutions – Great Plains 8.0

 

Method 1: Use the Advanced Security tool
1. On the Tools menu, point to Setup , point to System , and then click Advanced Security .
2. If you are prompted, type the system password in the Please Enter Password box, and then click OK .
3. In the Advanced Security window, click View , and then click By Alternate, Modified and Custom .
4. Use the appropriate method: • In Microsoft Dynamics GP 9.0, expand Microsoft Dynamics GP .
• In Microsoft Business Solutions - Great Plains 8.0, expand Great Plains .


     
5. Expand Reports , expand Financials , and then expand the report that you modified.
6. Use the appropriate method: • In Microsoft Dynamics GP 9.0, click Microsoft Dynamics GP (Modified) .
• In Microsoft Business Solutions - Great Plains 8.0, click Great Plains (Modified) .


     
7. Click Apply , and then click OK .
Note By default, the current user and the current company are selected when you start the Advanced Security tool. Any changes that you make affect the current user and the current company. However, you can select additional users in the Users area of the Advanced Security window. You can select additional companies in the Company Name area of the Advanced Security window.
Method 2: Use the Standard Security tool
1. On the Tools menu, point to Setup , point to System , and then click Security .
2. If you are prompted, type the system password in the Please Enter Password box, and then click OK .
3. In the User ID list, click the ID of the user who you want to have access to the modified report.
4. In the Type list, click Modified Reports .
5. In the Series list, click Financials .
6. In the Access List box, double-click the report that you modified, and then click OK .

Note An asterisk (*) appears next to the report name.

↑ Back to the top

Keywords: kb, kbhowto, kbinfo, kbnosurvey, kbExpertiseInter, kbMBSPartner, kbMBSMigrate, BemisKB862086

↑ Back to the top

Article Info
Article ID : 862086
Revision : 2
Created on : 3/6/2017
Published on : 3/6/2017
Exists online : False
Views : 397