401K Report with Benefit and Deductions on One Report

1. From the Tools menu select Customize >> Report Writer.If you have more than one dictionary loaded you will have to select Dynamics as the Product.

2. Click on the Reports button.

3. Select the New button.

4. The Report Definition window will open and enter the Report Name on the Report Definition window as" 401K Report" .Select Payroll as the Series and Payroll Transaction History as the Main Table.

5. Unmark Skip Blank Records in the Format Options section.

6. Select Landscape as the Page Orientation.

7. Unmark First Page Header and mark Use RF For Last PF in the Printing Options section.

8. Select the Tables button and the Report Table Relationships window will open.

9. Select the New button on the Report Table Relationship window and the Related Tables window opens.

10. Highlight the Payroll Master table and select the OK Button on the Related Tables window.

11. Highlight the Payroll Master on the Report Table Relationships window and select the New button again.

12. Highlight the Payroll Employee Summary table and select the OK button on the Related Tables window

13. Select Close on the Report Table Relationships window.Select the Sort button on the Report Definition window and the Sorting Definition window opens

14. Select the Sort button on the Report Definition window and the Sorting Definition window opens.

15. Highlight Employee ID in the Table Fields list box and Insert it into the Sort By list box.

16. Select OK on the Sorting Definitions window.

17. Select the Restrictions button on the Report Definition window and the Report Restrictions window opens.

18. Select the New button on the Report Restrictions window and the Report Restriction Definition window opens.

19. Enter the Restriction Name of Pay Period Ending Date.

20. Leave the Payroll Transaction History Report Table selected in the Fields section and select TRX Ending Date in the Table Fields list.Select Add Field.

21. Select the " = " (equals) button in the Operators section.

22. Select the Type of Date in the Constants section and enter your ending date for the pay run.***Please be aware that you will have to change the date restriction for the new pay period ending date each time you run the report.Select Add Constant.

23. Select OK on the Report Restriction Definition window.

24. Select the New button again on the Report Restrictions window and the Report Restriction Definition window opens.

25. Enter the Restriction Name of 401K Benefits and Deductions Only.

26. Leave the Payroll Transaction History table selected in the Fields section and select Payroll Code in the Table Fields list.Select Add Field.

27. Select the " =" (equals) button in the Operators section.

28. Select the Type of String in the Constants section and enter your 401k deduction code.My deduction code is called 401K for anything up to 8%.Select Add Constant.

29. Select the OR button in the Operators section.

30. The Payroll Transaction History table should still be selected in the Fields section so you can select Add Field again from the Fields section.

31. Select the " =" (equals) button in the Operators section.

32. The Type of String in the Constants section should still be selected and enter your additional 401k deduction code.I have an additional 401k deduction for anything over 8% and it is called 401KB.Select Add Constant.

33. Select the OR button in the Operators section.

34. The Payroll Transaction History table should still be selected in the Fields section so you can select Add Field again from the Fields section.

35. Select the" = " (equals) button in the Operators section.

36. The Type of String in the Constants section should still be selected and enter your 401k benefit code.My benefit code is called 401K just like my deduction.Select Add Constant.
    
    
    Your expression in the Restriction Expression should read:
    UPR_Transaction_HIST.Payroll Code = " 401K" OR UPR_Transaction_HIST.Payroll Code =" 401KB" OR UPR_Transaction_HIST.Payroll Code =" 401K"
    
    
    ***Remember that your deduction and benefit code names may vary from the ones listed above.Please substitute the names of your codes.If you do not have more than one deduction for 401k you can eliminate the middle part of the equation.
    
    

37. Select OK on the Report Restriction Definition window.

38. Close the Report Restrictions window using the close box.

39. Select the Layout button on the Report Definitions window and the Report Layout window opens.

40. Select Report Section Options from the Tools menu.

41. In the Report Section Options window mark Page Header, Report Header, unmark Body, Report Footer, and Page Footer.

42. Select the New button in the Additional Footers section and the Footer Options window opens.

43. Enter the Footer ID of Employee ID.Leave the Payroll Transaction History Report Table selected in the Print When Field Changes section and select Employee ID in the Fields list.

44. Select the OK button on the Footer Options window.

45. Select the OK button on the Report Section Options window.

46. From the Toolbox window (Layout >> Toolbox) in the Layout tab select the drop down list that currently says Payroll Transaction and select Calculated Fields.

47. Select the New button on the Toolbox and the Calculated Field Definitions window will open.(We are going to create eight different calculated or conditional fields to use on the report.)

48. Enter Employee Name as the Name and select String as the Result Type.

49. Select the Functions tab and select the Function of Strip from the drop down list.Select Add.

50. Select the Fields tab and Payroll Master as the Resource and Last Name as the Field.Select Add.

51. In the Calculated field Expression on the bottom of the window click to the right of the" )" so the cursor focus is after the " )".

52. Select the CAT button in the Operators section.

53. We need to tell it that we want a comma after the last name and a space before the first name.Select the Constants tab and Type of String.With the cursor in the Constant field enter a comma and then the space bar.Select Add.

54. Select the CAT button in the Operators section.

55. Select the Functions tab and select the Function of Strip from the drop down list.Select Add.

56. Select the Fields tab and Payroll Master as the Resource and First Name as the Field.Select Add.
    
    
    Your expression in the Calculated Expression should read:
    STRIP(UPR_MSTR.Last Name) # " , " # STRIP(UPR_MSTR.First Name)
    
    

57. Select the OK button on the Calculated Field Definition window to save the new calculated field.Employee Name should appear in the Toolbox Calculated Fields list box.

58. Select the New button on the Toolbox again and the Calculated Field Definitions window will open.(This is our second of seven calculated fields.)

59. Enter 401K Deduction as the Name and select Currency as the Result Type.Mark Conditional as the Expression Type.

60. In the Fields tab select Payroll Transaction History as the Resource and Payroll Record Type as the Field.Select Add.

61. Select the " = " (equals) button in the Operators section.

62. Select the Constants tab and Type of Integer.Enter 2 in the Constant field. Select Add.

63. Select the " AND " button in the Operators section.

64. Select the Fields tab Resource of Payroll Transaction History and Field of Payroll Code.Select Add.

65. Select the " = " (equals) button in the Operators section.

66. Select the Constants tab and Type of String.Enter your 401k deduction code in the Constant field.Select Add.

67. Click once in the True Case Expression putting focus in that field.

68. Select the Fields tab Resource of Payroll Transaction History and Field of UPR TRX Amount.Select Add.

69. Click once in the False Case Expression putting focus in that field.

70. Select the Constants tab and Type of Currency.The Constant field should default with 0.00000, do not change and Select Add.
    
    
    Your expression in the Conditional Expression should read:
    UPR_Transaction_HIST.Payroll Record Type=2 AND UPR_Transaction_HIST.Payroll Code=" 401K"
    True Case:
    UPR_Transaction_HIST.UPR_TRX_Amount
    False Case:
    0.00000
    
    ***Remember that your deduction code names may vary from the ones listed above.Please substitute the name of your code.
    
    

71. Select the OK button on the Calculated Field Definition window to save the new calculated field.401K Deduction should appear in the Toolbox Calculated Fields list box above Employee Name.

72. Select the New button on the Toolbox again and the Calculated Field Definitions window will open.(This is our third of seven calculated fields.)

73. Enter 401KB Deduction as the Name and select Currency as the Result Type.Mark Conditional as the Expression Type.If you do not have more than one deduction for 401k you can eliminate creating this calculated field.

74. In the Fields tab select Payroll Transaction History as the Resource and Payroll Record Type as the Field.Select Add.

75. Select the " =" (equals) button in the Operators section.

76. Select the Constants tab and Type of Integer.Enter 2 in the Constant field. Select Add.

77. Select the " AND" button in the Operators section.

78. Select the Fields tab Resource of Payroll Transaction History and Field of Payroll Code.Select Add.

79. Select the " =" (equals) button in the Operators section.

80. Select the Constants tab and Type of String.Enter your additional 401k deduction code in the Constant field.Select Add.

81. Click once in the True Case Expression putting focus in that field.

82. Select the Fields tab Resource of Payroll Transaction History and Field of UPR TRX Amount.Select Add.

83. Click once in the False Case Expression putting focus in that field.

84. Select the Constants tab and Type of Currency.The Constant field should default with 0.00000, do not change and Select Add.
    
    
    Your expression in the Conditional Expression should read:
    UPR_Transaction_HIST.Payroll Record Type=2 AND UPR_Transaction_HIST.Payroll Code=" 401KB"
    
    True Case:
    UPR_Transaction_HIST.UPR_TRX_Amount
    
    False Case:
    0.00000
    
    
    ***Remember that your deduction code name may vary from the ones listed above.Please substitute the name of your code.If you do not have more than one deduction for 401k you can eliminate this calculated field.
    
    

85. Select the OK button on the Calculated Field Definition window to save the new calculated field.401KB Deduction should appear in the Toolbox Calculated Fields list box below 401K Deduction.

86. Select the New button on the Toolbox again and the Calculated Field Definitions window will open.(This is our forth of seven calculated fields.)

87. Enter 401K Benefit as the Name and select Currency as the Result Type.Mark Conditional as the Expression Type. Mark Conditional as the Expression Type.

88. In the Fields tab select Payroll Transaction History as the Resource and Payroll Record Type as the Field.Select Add.

89. Select the" =" (equals) button in the Operators section.

90. Select the Constants tab and Type of Integer.Enter 3 in the Constant field. Select Add.

91. Select the " AND" button in the Operators section.

92. Select the Fields tab Resource of Payroll Transaction History and Field of Payroll Code.Select Add.

93. Select the " =" (equals) button in the Operators section.

94. Select the Constants tab and Type of String.Enter your 401k benefit code in the Constant field.Select Add.

95. Click once in the True Case Expression putting focus in that field.

96. Select the Fields tab Resource of Payroll Transaction History and Field of UPR TRX Amount.Select Add.

97. Click once in the False Case Expression putting focus in that field.

98. Select the Constants tab and Type of Currency.The Constant field should default with 0.00000, do not change and Select Add.
    
    
    Your expression in the Conditional Expression should read:
    UPR_Transaction_HIST.Payroll Record Type=3 AND UPR_Transaction_HIST.Payroll Code=" 401K"
    
    True Case:
    UPR_Transaction_HIST.UPR_TRX_Amount
    
    False Case:
    0.00000
    
    
    ***Remember that your benefit code name may vary from the ones listed above.Please substitute the name of your code.
    
    

99. Select the OK button on the Calculated Field Definition window to save the new calculated field.401K Benefit should appear at the top in the Toolbox Calculated Fields list box.

100. Select the New button on the Toolbox again and the Calculated Field Definitions window will open.(This is our fifth of seven calculated fields.)If you do not have an additional 401K deduction you can eliminate creating this calculated field.

101. Enter Total 401K Deductions as the Name and select Currency as the Result Type.Mark Calculated as the Expression Type.

102. In the Fields tab select Calculated Fields as the Resource and 401K Deduction as the Field.Select Add.

103. Select the " + " (plus or addition) button in the Operators section.

104. In the Fields tab select Calculated Fields as the Resource and 401KB Deduction as the Field.Select Add.
     
     
     Your expression in the Calculated Expression should read:
     401K Deduction + 401KB Deduction
     
     
     ***Remember that your deduction codes names may vary from the ones listed above.Please substitute the names of your codes.If you do not have an additional 401K deduction you do not have to create this expression.
     
     

105. Select the OK button on the Calculated Field Definition window to save the new calculated field.Total 401K Deductions should appear in the Toolbox Calculated Fields list box below Employee Name.

106. Select the New button on the Toolbox again and the Calculated Field Definitions window will open.(This is our sixth of seven calculated fields.)

107. Enter Total 401K as the Name and select Currency as the Result Type.Mark Calculated as the Expression Type.

108. In the Fields tab select Calculated Fields as the Resource and Total 401K Deductions as the Field.Select Add.

109. Select the " + " (plus or addition) button in the Operators section.

110. In the Fields tab select Calculated Fields as the Resource and 401K Benefit as the Field.Select Add.
     
     
     Your expression in the Calculated Expression should read:
     Total 401K Deductions + 401K Benefit
     
     
     ***If you did not create the Total 401K Deductions calculated field then you add the 401K Deduction calculated field to the 401K Benefit instead.
     
     

111. Select the OK button on the Calculated Field Definition window to save the new calculated field.Total 401K should appear in the Toolbox Calculated Fields list box below Employee Name.

112. Select the New button on the Toolbox again and the Calculated Field Definitions window will open.(This is our seventh of seven calculated fields.)

113. Enter YTD Wages as the Name and select Currency as the Result Type.Mark Calculated as the Expression Type.

114. In the Fields tab select Payroll Employee Summary as the Resource and Gross Wages as the Field.Select Add.A window will come up asking to " Enter the array index" and enter 1.Select OK

115. Select the" + " (plus or addition) button in the Operators section.

116. In the Fields tab Payroll Employee Summary and Gross Wages should still be selected.Select Add and again a window will come up asking to " Enter the array index " .Enter 2 and Select OK.

117. Repeat the last two steps incrementing the array index by one each time up to 12.
     
     
     Your expression in the Calculated Expression should read:
     UPR_Employee_SUM.Gross Wages[1]+ UPR_Employee_SUM.Gross Wages[2]+ UPR_Employee_SUM.Gross Wages[3]+ UPR_Employee_SUM.Gross Wages[4]+ UPR_Employee_SUM.Gross Wages[5]+ UPR_Employee_SUM.Gross Wages[6]+ UPR_Employee_SUM.Gross Wages[7]+ UPR_Employee_SUM.Gross Wages[8]+ UPR_Employee_SUM.Gross Wages[9]+ UPR_Employee_SUM.Gross Wages[10]+ UPR_Employee_SUM.Gross Wages[11]+ UPR_Employee_SUM.Gross Wages[12]
     
     

118. Select the OK button on the Calculated Field Definition window to save the new calculated field.YTD Wages should appear at the bottom in the Toolbox Calculated Fields list box.

119. From the same Toolbox drop down list that you selected Calculated Fields, select Globals.Find Company Name and drag the field to the page header area in the layout.You can leave it the size that it defaults to or you can highlight it and select Tools >> Report Drawing Options and change how the field will print on the report.I have select Arial Font size 16, bold, and alignment Center.

120. From the Globals list, drag Company Name to the report header area in the layout.Repeat the same steps as above if you want to change the display.

121. Fields like For Period Ending, Empl ID, Name, User Date, etc use the Text tool from the Toolbox.

122. The following fields come from the Payroll Transaction History table: TRX Ending Date and Employee ID.

123. Social Security Number comes from the Payroll Master.

124. The following fields come from the Calculated Fields list: Employee Name, Total 401K Deduction, 401K Benefit, Total 401K, and YTD Wages.

125. System Date and Page Number come from the tools on the Toolbox.

126. Once you have all of the fields on the layout window, click once on the Total 401K Deduction field in footer one and then select Tools >> Report Field Options.The Report Field Options window will open and in the bottom left corner select the Field Type of Sum (you may have to scroll to the bottom of the list box).Select OK.

127. Click once on the 401K Benefit field in the layout window and select Tools >> Report Field Options.In the Report Field Options window select the Field Type of Sum.Select OK.

128. Click once on the Total 401K field in the layout window and select Tools >> Report Field Options.In the Report Field Options window select the Field Type of Sum.Select OK.

129. The other fields in the footer one area should all have defaulted correctly to the Field Type of Last Occurrence in the Report Field Options window; however, you can verify each of those fields have the correct selection by following the same steps and highlighting Last Occurrence.

130. When you have all of the fields laid out in the order you would like close the Report Layout window.Select to save the changes.

131. Click OK on the Report Definition window.

132. From the File menu select Great Plains Dynamics/eEnterprise.

133. Verify the users that need this report have access to the custom report in Security.Remember that all users have access to custom reports automatically so you will need to take away access to those users that should not be able to print this report.
     
     
     If you would like this document with screen prints,pleasecallGreat Plains Payroll Technical Support at 1-(888) GPS-SUPP (888-477-7877), or email at
     http://www.microsoft.com/dynamics/gp/default.mspx.

This article was TechKnowledge Document ID:21190