KB01-023

For additional reports in Comprehensive Leave Manager, you can use you existing SmartList functionality in Microsoft Dynamics GP to create custom reports. We will look in detail at building the Leave Summary report. You can then follow the same steps to build the other three reports.

• Leave Summary – Displays data for all employees and leave codes from the Employee Leave Maintenance and Employee Leave Summary window.
• Leave Transactions – Displays transactional data for all employees and leave codes from the Employee Leave Transactions window.
• Leave summary during payroll – Displays employees and leave codes being processed this pay period and the leave balance amounts. This report only contains data during payroll processing. Run this report after calculate checks to verify leave earned, taken, pending and available amounts for this pay period.
• Leave Transactions during payroll – Displays data for all employees and leave codes being processed this pay period and the leave balance amounts. This report only contains data during payroll processing. Run this report after calculate checks to verify leave earned, taken, pending and available amounts for this pay period.

To open SmartList Designer for Microsoft Dynamics GP select Microsoft Dynamics GP > SmartList > New Button. If you do not have the New button on your SmartList window login to Microsoft Dynamics GP as ‘sa’ to grant security for this functionality. See additional instructions on granting security below.

To create the Leave Summary report we will first enter the List Name, Leave Summary and select Comprehensive Leave Manager as the product. In Database View, we can expand the Comprehensive Leave Manager product, Tables and 3^rd Party. Here you will see all the Leave Tables.

For the Leave Summary report, we will want to find the Leave Employee Code Master table and check the checkbox.

If you want to filter the report by Department, Position or other Employee specific information in Microsoft Dynamics GP then you can also add the Payroll Master table by expanding Microsoft Dynamics GP, Tables and Payroll then select Payroll Master.

Next, we want to create a relationship between our two tables, within the Relationship section we can select Leave Employee Code Master as the TableName, select Employee ID as the FieldName, select Left Join, and select Payroll Master as the TableName2 and Employee ID as FieldName2.

The system now will display all the fields in the Comprehensive Leave Manager Leave Code Master and Microsoft Dynamics GP Payroll Master table. You may not want some of these fields in your report. In the Selected Fields section, you can delete any fields you do not want on the report.

Once you believe your report is ready to view you can select Execute Query and review your results in the Result’s Preview section at the bottom of the SmartList Designer window.

When you are finished, select Ok to close SmartList Designer and view your new report in SmartList.

You can complete this process to create the other three reports. Except when you select your tables use the following tables for each report you want to create.

Leave Transactions report: Leave Employee Transactions

Leave Summary during Payroll report: Leave Employee Code WORK

Leave Transactions during payroll report: Leave Employee Transactions WORK

How to setup security for SmartList Designer

1. Open the Security Task Setup window.
   
2. Select a current task ID assigned to the user or create a new one
3. Select SmartList for the Product List
4. Select Windows for the Type
5. Select Company for the Series
6. After the SmartList windows appear in the Access List pane, click to select the check box for SmartList Designer.
7. Click Save

If you opened an existing task in Step 2, you can skip the rest of the steps below. If you created a new task in Step 3, proceed to step 8

8. Open the Security Roles Setup window.
   
9. Open an existing role or create a new role.
10. Find the task you created in Step 2 and select the check box next to it.
11. Click Save.

If you opened an existing role in Step 8, you can skip the rest of the steps below. If you created a new role in Step 9, proceed to Step 12.

12. Open the User Security Setup window.
    
13. Select the user and company.
14. Find the role you created in Step 10 and select the check box next to it.
15. Click Save.

Questions/Resources

If you have any questions or comments please email Support@integrity-data.com.

• Knowledge Base: https://www.integrity-data.com/hrp/knowledge-base/ 
• Videos: https://www.integrity-data.com/hrp/videos/

Additional reporting KB articles:

• https://www.integrity-data.com/faq-items/kb-smartlist-comprehensive-leave-manager/
• https://www.integrity-data.com/faq-items/kb-comprehensive-leave-manager-sql-views/

Last Review:       July 29, 2015 – Revision: 1.0

Applies To:

• Microsoft Dynamics GP 2015 R2
• Microsoft Dynamics GP 2013 R2