How to Create an Auditor Report for Non-GP Users

Subject:

The steps here will describe how to create an Auditor report for non-GP Users.

Resolution:

Here are the steps to follow:

1. Deploy Excel Reports in Microsoft Dynamics GP.
   1. For more information on how to do this, see the Excel report deployment section of the System Setup PDF file.
   2. The typical location of this PDF file is: C:\Program Files (x86)\Microsoft Dynamics\GP2016\Documentation.
2. Create a SQL View in Microsoft SQL Server Management Studio.
   1. Open Microsoft SQL Server Management Studio.
   2. Connect to your Microsoft Dynamics GP Server.
   3. Expand the Server name, expand Databases, and then expand DYNAMICS (or the name of the DYNAMICS database).
   4. Right-click on Views, and then click New View.
   5. In the Add Table window, click Close.
   6. In the section with SELECT FROM, replace SELECT FROM with the following:
   7. select * from RSAS015
   8. Note: If you would like to narrow down the results, you can add a where clause. For example, if you would like to only see the Audit Records for the PM Vendor Master File (PM00200), you can use this select statement: select * from RSAS015 where RSA_Table_Physical_Name = ‘PM00200’
   9. Once the statement is in the section, right-click in that section, and then click Execute Statement.
   10. Click File, and then click Save.
   11. Enter a name for the View. For example, Auditor Records for Excel, Vendor Audits (if you are viewing just the results for the PM00200 table), Auditor SQL View, etc. Note the name you enter for the View.
   12. Click OK.
   13. Close the View window.
3. Create a SmartList Designer report in Microsoft Dynamics GP.
   1. Log into Microsoft Dynamics GP as sa or a POWERUSER.
   2. Go to Microsoft Dynamics GP | SmartList.
   3. Click New.
   4. Enter Auditor Records for Excel, for example, in the List Name field. Note the List Name you enter.
   5. For the results to appear in the Auditor section, select Auditor for Product.
   6. Expand Views, and then expand System.
   7. Mark the checkbox for the SQL View you created in Step 2.
   8. Note: If you want to only see several of the columns from the table in your SmartList report, expand the View and mark the appropriate checkboxes. Here is a list of the recommended columns: User ID, Date, Time, Record Key, Table Name, Window Name, Field Name, Old Field Value, New Field Value, Note, Company Name, Product Name, and Event Type.
   9. You can then add a Relationship to another table and a Filter if needed. Or you can modify the report afterwards to add these options.
   10. Click OK.
4. Publish the SmartList Designer report in Microsoft Dynamics GP.
   1. In SmartList, expand the Folder for the List Name you created in Step 3, and then click the Asterisk.
   2. Click Search to add any restrictions to the results and increase the Maximum Records from 1,000 to 1,000,000,000.
   3. Once you have the desired results, click Favorites to create a Favorite.
   4. Then click Publish.
5. View the Excel report with real-time data.
   1. You will be able to see and click on the report in the location you specified for the Excel reports in Step 1. If you are not sure what the path is set to for saving them, go to Microsoft Dynamics GP | Tools | Setup | System | Reporting Tools Setup. Click Excel Reports. For example, my location is C:\ExcelReports\. I found my reports in this location: C:\ExcelReports\Reports\TWO.
   2. Note: The Auditor records in the RSAS015 table are for all companies even though the location has you going to the path with the Company name where you selected to publish the report from.