How to create an inventory report in Microsoft Dynamic GP that lets report readers determine the items that have low turnover

INTRODUCTION

This article describes how to use Report Writer in Microsoft Dynamic GP or in Microsoft Business Solutions - Great Plains to create a customized report that does the following:

• Lists all items that are set up in inventory
• Shows the date of the last sale for each item

By using this report, readers can determine the items that have low turnover.

MORE INFORMATION

To create the customized report, follow these steps.

Step 1: Open Report Writer

Microsoft Dynamics GP 10.0

1.	On the Microsoft Dynamics GP menu, point to Tools , point to Customize , and then click Report Writer .
2.	In the Product list, click Microsoft Dynamics GP .

Microsoft Dynamics GP 9.0

1.	On the Tools menu, point to Customize , and then click Report Writer .
2.	In the Product list, click Microsoft Dynamics GP .

Microsoft Business Solutions - Great Plains 8.0

1.	On the Tools menu, point to Customize , and then click Report Writer .
2.	In the Product list, click Great Plains .

Step 2: Create the relationship between the Item Master table and the Sales Transaction History table

1.	In Report Writer, click Tables , and then click Tables .
2.	In the list of tables, click IV_Item_MSTR , and then click Open . Note This table is the Item Master table.
3.	In the Table Definition window, click Relationships .
4.	In the Table Relationship window, click New .
5.	In the Table Relationship Definition window, click the ellipsis button (...) to the left of the Secondary Table field.
6.	In the Relationship Table Lookup window, click Sales Transaction Amounts History , and then click OK .
7.	In the Secondary Table Key field, click SOP_LINE_HIST_Key3 .
8.	In the first line of the Primary Table list, click POP Receipt Number .
9.	In the Primary Table list, click Item Number , and then click OK .
10.	Close the Table Relationships window.
11.	In the Table Definition window, click OK , and then close the Tables window.

Step 3: Create the report

1.	In Report Writer, click Reports , and then click New .
2.	In the Report Definition window, type the name of the report, click Inventory in the Series list, click Item Master in the Main Table section, and then click IV_Item_MSTR_Key1 in the Using Key list.
3.	In the Format Options section, click to select the Text Report check box.

Step 4: Create the table relationships

1.	In the Report Definition window, click Tables .
2.	In the list of tables, click Item Master , and then click New .
3.	In the list of tables, click Sales Transaction Amounts History , and then click OK .
4.	In the list of tables, click Sales Transaction Amounts History , and then click New .
5.	In the list of tables, click Sales Transaction History , and then click OK .
6.	In the list of tables, click Sales Transaction Amounts History , and then click New .
7.	In the list of tables, click Item Quantity Master , and then click OK .
8.	Click Close to close the Report Table Relationships window.

Step 5: Create the sorting options and restrictions

1.	In the Report Definition window, click Sort .
2.	In the Sorting Definition window, click Sales Transaction History in the Report Table list, click Document Date in the Table Fields section, and then click Insert .
3.	Click OK to close the window.
4.	In the Report Definition window, click Restrictions .
5.	In the Report Restrictions window, click New .
6.	In the Report Restriction Definition window, type Invoices Only in the Restriction Name field.
7.	In the Fields section, click Sales Transaction Amounts History in the Report Table list, and then click SOP Type in the Table Fields list.
8.	Click Add Field .
9.	In the Operators section, click the = (equal sign) button.
10.	In the Constants section, click Integer in the Type list, and then type 3 in the Constant field.
11.	Click Add Constant . Your restriction expression should be as same as the following: SOP_LINE_HIST.SOP Type = 3
12.	Click OK to close the Report Restriction Definition window.

Step 6: Make the layout of the report

1.	In the Report Definition window, click Layout .
2.	On the Tools menu, click Section Options .
3.	In the Report Section Options window, click to clear all check boxes except the Page Header check box.
4.	In the Additional Footers section, click New .
5.	In the Footer Options window, type Item Number in the Footer Name field, click Item Master in the Report Table list, and then click Item Number in the Field section. Make sure that the Item Number field is highlighted.
6.	Make sure that all check boxes are cleared.
7.	In the Footer Options window, click OK .
8.	In the Report Section Options window, click OK .
9.	In the toolbox, click Item Master in the unnamed drop-down list.
10.	In the list of fields, click Item Number , and then drag it to the F1 section of the report.
11.	In the list of fields, click Item Description , and then drag it to the F1 section of the report.
12.	In the unnamed drop-down list, click Sales Transaction Amounts History
13.	In the list of fields, click Document Date , and then drag it to the F1 section of the report.
14.	In the unnamed drop-down list, click Item Quantity Master .
15.	In the list of fields, click Last RCPT Date , and then drag it to the F1 section of the report.
16.	In the list of fields, click QTY on Hand , and then drag it to the F1 section of the report.
17.	Double-click the QTY on Hand field to open the Report Field Options window.
18.	Make sure that the following conditions are true: • The Visibility field is set to Visible . • The Format field is set to DLR11_U2 . • The Display Type field is set to Last Occurrence .
19.	Click OK , and then save the report.

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 Alternative/Modified Forms and Reports ID that is associated with the user who will print this modified report.
4.	In the Product list, click Microsoft Dynamics GP .
5.	In the Type list, click Reports .
6.	Expand the Inventory folder.
7.	Expand the folder of the report that you modified.
8.	Click to select the Microsoft Dynamics GP (Modified) option.
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 that you used in step 7.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 to take the following action: • 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 Inventory , and then expand the report that you modified.
6.	Use the appropriate method to take the following action: • In Microsoft Dynamics GP 9.0, click to select the Microsoft Dynamics GP (Modified) option. • In Microsoft Business Solutions - Great Plains 8.0, click to select the Great Plains (Modified) option.
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, and 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, type the Alternative/Modified Forms and Reports ID that is associated with the ID of the user who will print this modified report.
4.	In the Type list, click Modified Reports .
5.	In the Series list, click Inventory .
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.

APPLIES TO

•	Microsoft Dynamics GP 10.0
•	Microsoft Dynamics GP 9.0
•	Microsoft Business Solutions–Great Plains 8.0
•	Inventory Control
•	Report Writer

How to create an inventory report in Microsoft Dynamic GP that lets report readers determine the items that have low turnover

INTRODUCTION

MORE INFORMATION

Step 1: Open Report Writer

Microsoft Dynamics GP 10.0

Microsoft Dynamics GP 9.0

Microsoft Business Solutions - Great Plains 8.0

Step 2: Create the relationship between the Item Master table and the Sales Transaction History table

Step 3: Create the report

Step 4: Create the table relationships

Step 5: Create the sorting options and restrictions

Step 6: Make the layout of the report

Step 7: Assign security permissions to the modified report

Microsoft Dynamics GP 10.0

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

Method 1: Use the Advanced Security tool

Method 2: Use the Standard Security tool

APPLIES TO

Need more help?

Want more options?

Was this information helpful?

Thank you for your feedback!