Payroll Inquiry Custom Formulas [ New ] Options 06-05-2015 01:57 PM Custom Formulas Payroll Check Summary Inquiry, Add State SUI tax amount on it SELECT [Employer Amount] = SUM(pctAmount) FROM AxPayCheckTax LEFT JOIN AxTax ON pctTax = taxKey LEFT JOIN AxPayCheck ON pctPayCheck = pcKey LEFT JOIN AxPayroll ON pcPayroll = prKey LEFT JOIN AxGLAccount ON glaKey = taxLiabilityAccount LEFT JOIN AxVEC ON vecKey = pcEmployee LEFT JOIN AxEntity ON eKey = vecEntity WHERE taxIsEmployerTax = 1 AND taxType = 7 AND prPayDate BETWEEN '01/01/1900' AND '12/31/2078' AND pcKey = [Pay Check Key] GROUP BY pctPayCheck Payroll Check Summary Inquiry, Add State SUI taxable amount SELECT Taxable = ISNULL(SUM(pctTaxable ),0) FROM AxPayCheck LEFT JOIN AxPayroll ON pcPayroll = prKey LEFT JOIN AxPayCheckTax ON pcKey = pctPayCheck LEFT JOIN AxTax ON pctTax = taxKey LEFT JOIN AxVEC E ON pcEmployee = E.vecKey LEFT JOIN AxVEC S ON S.vecKey = E.vecSupervisor LEFT JOIN AxEntity ON E.vecEntity = eKey WHERE taxKey IS NOT NULL AND taxType = 7 AND pcStatus = 3 AND prPayDate BETWEEN '01/01/2009' AND '12/30/2009' AND pcKey = [Pay Check Key] Total Annual Salary for a company (for hourly and salary employees) for the year on a semi monthly payroll for usage in an Employee Inquiry. Note: it would be slightly different if they had different pay periods. SELECT SalaryAmount = ISNULL(CAST((CASE WHEN empIsHourly = 1 THEN empRegularPayRate * ((SELECT sHoursInSemiMonthlyPayPeriod FROM AxSettings)* 24 /* 24 = Periods per year*/) ELSE empSalary * 24 /* 24 = Periods per year*/ END) as decimal(19,2)),0) FROM Employee WHERE empKey = [Employee Key] Add Hourly/Salary to Paycheck Summary Inquiry Case WHEN (SELECT empIsHourly FROM Employee WHERE empKey = [Employee Key]) = 1 THEN 'hourly' ELSE 'salary' END Add Employee Type to State Quarterly Inquiry SELECT etDescription from employee join EmployeeType on empEmployeeType = etKey WHERE empkey = [Employee Key] Payroll Check Summary Inquiry, Add State Withholding tax amount on it This will work to bring in all state withholdings (in case there are multiple) on the paycheck: SELECT ISNULL(SUM(pctAmount), 0.0) FROM AxPayCheckTax JOIN AxTax ON pctTax = taxKey WHERE pctPayCheck = [Pay Check Key] AND taxType = 5 Or, this will filter it by state if you need a breakout: SELECT ISNULL(pctAmount, 0.0) FROM AxPayCheckTax JOIN AxTax ON pctTax = taxKey WHERE pctPayCheck = [Pay Check Key] AND taxType = 5 AND taxState = 37 Tax State: 0 = na; 1 = AL; 2 = AK; 3 = AZ; 4 = AR; 5 = CA; 6 = CO; 7 = CT; 8 = DE; 9 = FL; 10 = GA; 11 = HI; 12 = ID; 13 = IL; 14 = IN; 15 = IA; 16 = KS; 17 = KY; 18 = LA; 19 = ME; 20 = MD; 21 = MA; 22 = MI; 23 = MN; 24 = MS; 25 = MO; 26 = MT; 27 = NE; 28 = NV; 29 = NH; 30 = NJ; 31 = NM; 32 = NY; 33 = NC; 34 = ND; 35 = OH; 36 = OK; 37 = OR; 38 = PA; 39 = RI; 40 = SC; 41 = SD; 42 = TN; 43 = TX; 44 = UT; 45 = VT; 46 = VA; 47 = WA; 48 = WV; 49 = WI; 50 = WY; 51 = DC; 52 = PR Payroll Check Summary Inquiry, Add individual Deduction amount on it SELECT ISNULL(pcdAmount, 0.0) FROM AxPayCheckDeduction JOIN AxDeduction ON pcdDeduction = dedKey WHERE pcdPayCheck = [Pay Check Key] AND dedDescription = 'Section 125' Specify the deduction by substituting the name of the deduction where it says Section 125. The single quotes are needed around the deduction description. To Use these Custom Formulas Copy the desired Formula into the definition of your Inquiry column.