Login
Register
Search
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Forums
Performance Management
Lawson Business Intelligence/Reporting/Crystal
Query and Report Puzzle
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
LynneB
Past 24 Hours:
0
Prev. 24 Hours:
0
Overall:
5123
People Online:
Visitors:
317
Members:
0
Total:
317
Online Now:
New Topics
Lawson S3 Financials
PO Matched Dollars and Matched Quantity
5/10/2024 6:51 PM
Can anyone share the Lawson calculation to determi
S3 Customization/Development
COBOL CSV file
5/10/2024 2:36 PM
We have a custom Cobol program ZU200 that creates
S3 Systems Administration
Using MFA for Lawson S3 Access
5/8/2024 1:23 PM
Does anyone use Multi Factor Authentication when u
Lawson Landmark
Executing IPA from PR160
5/3/2024 2:46 PM
We're facing a situation where we need to upda
IPA/ProcessFlow
Executing IPA from PR160
5/3/2024 2:46 PM
We're facing a situation where we need to upda
S3 Customization/Development
Cobol - Extract Current Time
4/24/2024 7:21 PM
How do you extract just the Current System Time in
Lawson Landmark
LPL INSTR Functions
4/5/2024 8:32 PM
I'm writing a simple report using the Create R
Infor SCM
Translating 856 to get the ~ REF^CN^ field
4/3/2024 8:24 PM
We are trying to get the tracking number which is
IPA/ProcessFlow
Sample XML file create Flow
4/3/2024 3:43 PM
Hello everyone, I am new to creating XML files
Lawson S3 HR/Payroll/Benefits
bn105 error message
3/26/2024 6:40 PM
I need to change some of the set ups in our Life I
Top Forum Posters
Name
Points
Greg Moeller
4184
David Williams
3349
JonA
3288
Kat V
2984
Woozy
1973
Jimmy Chiu
1883
Kwane McNeal
1437
Ragu Raghavan
1357
Roger French
1311
mark.cook
1244
Forums
Unanswered
Active Topics
Most Liked
Most Replies
Search Forums
Search
Advanced Search
Topics
Posts
Prev
Next
Forums
Lawson Business Intelligence/Reporting/Crystal
Query and Report Puzzle
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
DavidBrianCampbell
Basic Member
Posts: 13
9/18/2007 5:03 PM
Thanks in advance for any clues!
I am from scratch (not DME due to using ODBC) writing a query in Crystal Reports. Below I have 2 tables (Employee & PRDISTRIB). What this is to report is payroll deductions by deduction. So within PRDISTRIB (and there might be a better Lawson table) I have a record for each single deduction (e.g. Check #502 for Mary Brown will have maybe 6 records or more, $10 Dental, $25 Health, $5 Uniform, etc.). So with this query and my corresponding report I get the employee name, number, position, etc. for each deduction. What I would really like is one listing of the Employee (with number, position) on a row with that row also having the six deductions. Also in the Employee table I have Employee.Department, which gives the number, but I need to pick up the Department name from the DEPTCODE table. So I need to do another join and the syntax is puzzling me. Any help would be great!
SELECT EMPLOYEE.EMPLOYEE, EMPLOYEE.LAST_NAME,
EMPLOYEE.FIRST_NAME, PRDISTRIB.EMPLOYEE, PRDISTRIB.POSITION, PRDISTRIB.Ded_Code, PRDISTRIB.CHECK_ID, PRDISTRIB.DIST_AMT, PRDISTRIB.DED_CODE, EMPLOYEE.DEPARTMENT, EMPLOYEE.JOB_CODE, EMPLOYEE.POSITION, EMPLOYEE.JOB_CODE, PRDISTRIB.PER_END_DATE,
trim(last_name) || ', ' || trim(first_name) || ' ' ||
case when trim(middle_init) is null then trim(middle_init) else trim(middle_init) || '.' end
FROM PRDISTRIB PRDISTRIB INNER JOIN EMPLOYEE
EMPLOYEE ON (PRDISTRIB.COMPANY=EMPLOYEE.COMPANY) AND (PRDISTRIB.EMPLOYEE=EMPLOYEE.EMPLOYEE)
John Henley
Posts: 3352
9/18/2007 5:17 PM
Hi Brian,
You have two easy options.
1) Within Crystal, you would create this as a 'crosstab' report, which "pivots" on the deduction code to put the values into columns/buckets.
2) You can also use 'CASE' statements within your SQL to create a "poor man's" crosstab:
... case when ded_code = 'HEAL' then dist_amt else 0 end as health_amt ...
You would then summarize by employee and suppress the details
BTW, you might find that it's easier to create this report from PAYDEDUCTN instead of PRDISTRIB.
k-rock
Veteran Member
Posts: 142
9/18/2007 5:23 PM
In these cases, I find that loading a temp table is the easiest. Do your PRDISTRIB pull into a temp table. Join that table to DEPTARTMENT, update the dept name on your temp table, then pull a crosstab query from the temp table. This should also increase performance because you won't be doing a crosstab off of Lawson tables.
Chris Martin
Veteran Member
Posts: 277
9/18/2007 5:32 PM
The relationship between EMPLOYEE and DEPTCODE is on company/process_level/department.
DavidBrianCampbell
Basic Member
Posts: 13
9/21/2007 6:31 PM
I'm manually coding this due to using ODBC. So I've got to refresh my memory for writing cross-tab queries. Basically what I want to do is search for Employees that meet a certain criteria and list their deductions in the following format, so I'm looking at some rather complex SQL, maybe. The first line is a header and each person listed is a detail "row" resulting from a join, to be determined. Each deduction amount and its deduction code are individual records (coming from the ONETMDED table), that is each person has anywhere from 0-6 records forming their row in the detail.
DED CODE DED AMT DED CODE DED AMT DED CODE DED AMT
Mary Brown HFGD 15.25 HFTS 44.10 JFFC 53.10
401K 80.11 SSHG 16.16 KLL1 90.00
Steve Cash HFGD 19.20 HFTS 18.44 JFFC 55.54
401K 28.74 SSHG 59.15 KLL1 75.11
Pat Lomax HFGD 22.64 HFTS 50.50 JFFC 45.66
401K 30.69 KLL1 75.41
John Henley
Posts: 3352
9/21/2007 10:53 PM
There are a few different ways to tackle this.
One is to use nested subqueries, where each column is a separate SELECT query, JOINed to a larger SELECT query...like this (in this example, I'm using PAYDEDUCTN, you'd be using ONETMDED and could leave out the PAYMASTR fields...):
SELECT
PAY.COMPANY AS COMPANY,
PAY.EMPLOYEE AS EMPLOYEE,
PAY.PROCESS_LEVEL AS PROCESS_LEVEL,
PAY.DEPARTMENT AS DEPARTMENT,
PROD.EMPLOYEE.EMP_STATUS AS EMP_STATUS,
PROD.EMPLOYEE.JOB_CODE AS JOB_CODE,
PROD.EMPLOYEE.SALARY_CLASS AS SALARY_CLASS,
CHAR(PAY.CHECK_DATE,USA) AS CHECK_DATE,
CHAR(PAY.PER_END_DATE,USA) AS PER_END_DATE,
PAY.GROSS_PAY AS GROSS_PAY,
HOURS.HOURS_WORKED,
BONUS_PAY.BONUS_PAY,
OTHER_PAY.OTHER_PAY,
TCFU_DED.DED_AMT AS ER_FUTA_AMT,
TCMC_DED.DED_AMT AS ER_MEDICARE_AMT,
TCSS_DED.DED_AMT AS ER_SOCSEC_AMT,
SUI.DED_AMT AS ER_SUI_AMT
FROM
PROD.PAYMASTR PAY
LEFT OUTER JOIN PROD.EMPLOYEE
ON ( PAY.COMPANY = PROD.EMPLOYEE.COMPANY
AND PAY.EMPLOYEE = PROD.EMPLOYEE.EMPLOYEE )
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID, SUM(WAGE_AMOUNT) AS BONUS_PAY FROM PROD.PRTIME
WHERE PAY_SUM_GRP IN ('ABN','CBN','OBN','QBN','RBN','SBN','SBO','ZBN')
GROUP BY COMPANY,EMPLOYEE,CHECK_ID) BONUS_PAY
ON ( PAY.COMPANY = BONUS_PAY.COMPANY
AND PAY.EMPLOYEE = BONUS_PAY.EMPLOYEE
AND PAY.CHECK_ID = BONUS_PAY.CHECK_ID )
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID, SUM(WAGE_AMOUNT) AS OTHER_PAY FROM PROD.PRTIME
WHERE PAY_SUM_GRP IN ('AUT','OEA','GIF','NSO','QME','REL','SEV','TAX','ZNS','ZRL','ZTF')
GROUP BY COMPANY,EMPLOYEE,CHECK_ID) OTHER_PAY
ON ( PAY.COMPANY = OTHER_PAY.COMPANY
AND PAY.EMPLOYEE = OTHER_PAY.EMPLOYEE
AND PAY.CHECK_ID = OTHER_PAY.CHECK_ID )
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID, SUM(R_HOURS) AS HOURS_WORKED FROM PROD.PRTIME
GROUP BY COMPANY,EMPLOYEE,CHECK_ID) HOURS
ON ( PAY.COMPANY = HOURS.COMPANY
AND PAY.EMPLOYEE = HOURS.EMPLOYEE
AND PAY.CHECK_ID = HOURS.CHECK_ID )
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID, SUM(DED_AMT) AS DED_AMT FROM PROD.PAYDEDUCTN
WHERE DED_CODE = 'TCMC'
GROUP BY COMPANY,EMPLOYEE,CHECK_ID,DED_CODE) TCMC_DED
ON ( PAY.COMPANY = TCMC_DED.COMPANY
AND PAY.EMPLOYEE = TCMC_DED.EMPLOYEE
AND PAY.CHECK_ID = TCMC_DED.CHECK_ID)
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID,SUM(DED_AMT) AS DED_AMT FROM PROD.PAYDEDUCTN
WHERE DED_CODE = 'TCSS'
GROUP BY COMPANY,EMPLOYEE,CHECK_ID,DED_CODE) TCSS_DED
ON ( PAY.COMPANY = TCSS_DED.COMPANY
AND PAY.EMPLOYEE = TCSS_DED.EMPLOYEE
AND PAY.CHECK_ID = TCSS_DED.CHECK_ID )
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID,SUM(DED_AMT) AS DED_AMT FROM PROD.PAYDEDUCTN
WHERE DED_CODE = 'TCFU'
GROUP BY COMPANY,EMPLOYEE,CHECK_ID) TCFU_DED
ON ( PAY.COMPANY = TCFU_DED.COMPANY
AND PAY.EMPLOYEE = TCFU_DED.EMPLOYEE
AND PAY.CHECK_ID = TCFU_DED.CHECK_ID )
LEFT OUTER JOIN
( SELECT SUI.COMPANY,SUI.EMPLOYEE,SUI.CHECK_ID,SUM(DED_AMT) AS DED_AMT FROM PROD.PAYDEDUCTN SUI
LEFT OUTER JOIN PROD.DEDCODE DED
ON ( SUI.COMPANY = DED.COMPANY
AND SUI.DED_CODE = DED.DED_CODE )
WHERE DED.CALC_TYPE = 'T'
AND DED.ADJUST_PAY = 'C'
AND DED.TAX_CATEGORY = 10
GROUP BY SUI.COMPANY,SUI.EMPLOYEE,SUI.CHECK_ID) SUI
ON ( PAY.COMPANY = SUI.COMPANY
AND PAY.EMPLOYEE = SUI.EMPLOYEE
AND PAY.CHECK_ID = SUI.CHECK_ID )
WHERE
(
( PAY.STATUS = 9 )
)
ORDER BY
COMPANY,
EMPLOYEE,
CHECK_DATE
John Henley
Posts: 3352
9/21/2007 10:55 PM
Another way to do it is to use CASE statements to populate the columns (this is probably more efficient) but harder to read...
SELECT EMPLOYEE,
SUM(CASE WHEN DED_CODE = 'DED1' THEN DED_AMT ELSE 0 END) AS DED1_AMT,
SUM(CASE WHEN DED_CODE = 'DED2' THEN DED_AMT ELSE 0 END) AS DED2_AMT
FROM lawson.ONETMDED
GROUP BY EMPLOYEE
HAVING
SUM(CASE WHEN DED_CODE = 'DED1' THEN DED_AMT ELSE 0 END) <> 0
OR
SUM(CASE WHEN DED_CODE = 'DED2' THEN DED_AMT ELSE 0 END) <> 0
DavidBrianCampbell
Basic Member
Posts: 13
9/25/2007 5:06 PM
I chose to keep it simple for now and just do "case when DED_CODE ='FSAH' then DED_AMT AS FSAH" to create a new field that I can place anywhere on the report, but I either get "FROM not in expected place" or "Missing Operator" errors.
SELECT
EMPLOYEE.EMPLOYEE,
EMPLOYEE.FICA_NBR,
EMPLOYEE.LAST_NAME,
EMPLOYEE.ADDR1,
EMPLOYEE.CITY,
EMPLOYEE.ADDR2,
EMPLOYEE.DEPARTMENT,
EMPLOYEE.TERM_DATE,
substr(DEPARTMENT,4,2),
EMPLOYEE.ZIP,
trim(employee.city) || ', ' || trim(employee.state) || ' ' || trim(employee.zip) as city_st_zip,
EMPLOYEE.STATE,
EMPLOYEE.PROCESS_LEVEL,
EMPLOYEE.FIRST_NAME,
trim(last_name) || ', ' || trim(first_name) || ' ' ||
case when trim(middle_init) is null
then trim(middle_init)
else trim(middle_init) || '.' end,
ONETMDED.EMPLOYEE,
ONETMDED.DED_CODE,
ONETMDED.DED_AMT,
case when DED_CODE ='FSAH' then DED_AMT AS FSAH
FROM EMPLOYEE EMPLOYEE INNER
JOIN ONETMDED ONETMDED ON EMPLOYEE.EMPLOYEE = ONETMDED.EMPLOYEE
WHERE ONETMDED.RECORD_TYPE ='A'
John Henley
Posts: 3352
9/25/2007 5:11 PM
You need to complete the CASE statement (you need the ELSE and END) for FSAH:
case when DED_CODE ='FSAH' then DED_AMT ELSE 0 END AS FSAH
Chris Martin
Veteran Member
Posts: 277
9/25/2007 5:12 PM
Your case statement is incomplete. Look again at John's sample code (missing "else" and "end").
You are not authorized to post a reply.