Print Page | Close Window

Display Checks without Deductions?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22528
Printed Date: 27 Apr 2024 at 7:48am


Topic: Display Checks without Deductions?
Posted By: techsupport
Subject: Display Checks without Deductions?
Date Posted: 23 Jan 2018 at 5:20am
Hello,
I can't seem to figure out how to display ALL checks for the month regardless of having any deductions paid.

This is what I'm thinking? 


({PR_PayrollHistoryHeader.CheckDate} in MonthToDate and

{PR_PayrollHistoryDetail.DeductionCode} in ["000004", "000013", "000020", "000023", "KU", "RU"] )

or

({PR_PayrollHistoryHeader.CheckDate} in MonthToDate and ISNULL({PR_PayrollHistoryDetail.DeductionCode}))


Expected result
CheckDate CheckNo Earnings   Deductions   Amt
1/5/2018 00001    $1500      401k        $10
1/5/2018 00002    $1000      401k        - or $0 (this would be NULL if no deductions were taken out)

NOTE: requester would like to have the Deductions display as columns (cross-tab) and show - or $0 when there were no deductions from a check.


-------------
____________________
Thank you very much!


Replies:
Posted By: kevlray
Date Posted: 23 Jan 2018 at 5:43am
Remember any time you mix AND's and OR's, be sure to put parens around the OR statements that belong together.  Otherwise you will get odd results.

Posted By: techsupport
Date Posted: 23 Jan 2018 at 7:03am
The () around the ones that belong together and still does not result in the way expected.

-------------
____________________
Thank you very much!

Posted By: kevlray
Date Posted: 23 Jan 2018 at 10:24am
So to make sure this is set up correctly.

You want the 
{PR_PayrollHistoryHeader.CheckDate} in the month to date AND
{PR_PayrollHistoryDetail.DeductionCode} to be one of the following - "000004", "000013", "000020", "000023", "KU", "RU"
OR 
{PR_PayrollHistoryHeader.CheckDate}  in the month to date AND
{PR_PayrollHistoryDetail.DeductionCode} is NULL (be sure to have Default Value for Nulls selected in the formula editor.

Sometimes with strings, it seems to work better to do a len check
len({PR_PayrollHistoryDetail.DeductionCode}) = 0 (Thanks to Ken Hamady for that one)

Posted By: techsupport
Date Posted: 23 Jan 2018 at 11:49am
Thanks

However, it's still not working

-------------
____________________
Thank you very much!

Posted By: techsupport
Date Posted: 23 Jan 2018 at 5:21pm
This is the SQL in Crystal Reports 

 SELECT "PR_PayrollHistoryHeader"."CheckDate", "PR_PayrollHistoryHeader"."CheckNo", "PR_PayrollHistoryHeader"."TotalRegularHours", "PR_PayrollHistoryHeader"."TotalRegularEarnings", "PR_PayrollHistoryDetail"."DeductionCode", "PR_PayrollHistoryDetail"."PayAmt", "PR_PayrollHistoryHeader"."EmployeeKey"

 FROM   "PR_PayrollHistoryDetail" "PR_PayrollHistoryDetail", "PR_PayrollHistoryHeader" "PR_PayrollHistoryHeader"

 WHERE  ((("PR_PayrollHistoryDetail"."EmployeeKey"="PR_PayrollHistoryHeader"."EmployeeKey") AND ("PR_PayrollHistoryDetail"."CheckNo"="PR_PayrollHistoryHeader"."CheckNo")) AND ("PR_PayrollHistoryDetail"."HeaderSeqNo"="PR_PayrollHistoryHeader"."HeaderSeqNo")) AND ((("PR_PayrollHistoryDetail"."DeductionCode"='000004' OR "PR_PayrollHistoryDetail"."DeductionCode"='000013' OR "PR_PayrollHistoryDetail"."DeductionCode"='000020' OR "PR_PayrollHistoryDetail"."DeductionCode"='000023' OR "PR_PayrollHistoryDetail"."DeductionCode"='KU' OR "PR_PayrollHistoryDetail"."DeductionCode"='RU')) OR ("PR_PayrollHistoryDetail"."DeductionCode" IS  NULL )) AND ("PR_PayrollHistoryHeader"."CheckDate">={d '2018-01-01'} AND "PR_PayrollHistoryHeader"."CheckDate"<={d '2018-01-23'})

 ORDER BY "PR_PayrollHistoryHeader"."EmployeeKey", "PR_PayrollHistoryHeader"."CheckDate", "PR_PayrollHistoryHeader"."CheckNo"


This is the SQL in Access 
SELECT PR_PayrollHistoryDetail.EmployeeKey, PR_PayrollHistoryHeader.CheckDate, PR_PayrollHistoryDetail.CheckNo, PR_PayrollHistoryHeader.TotalRegularHours, PR_PayrollHistoryHeader.TotalGrossEarnings, Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="000004",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS [Intl Pension], Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="000013",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS 401k, Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="KU",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS [401K Catchup], Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="000020",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS [Intl Loan- MO], Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="RU",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS [ROTH Catch-Up], Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="000023",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS [Roth IRA], Month([CheckDate]) AS [Month], Year([CheckDate]) AS [Year], IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]) AS Amt

FROM (PR_PayrollHistoryDetail INNER JOIN PR_PayrollHistoryHeader ON (PR_PayrollHistoryDetail.HeaderSeqNo = PR_PayrollHistoryHeader.HeaderSeqNo) AND (PR_PayrollHistoryDetail.CheckNo = PR_PayrollHistoryHeader.CheckNo) AND (PR_PayrollHistoryDetail.EmployeeKey = PR_PayrollHistoryHeader.EmployeeKey)) LEFT JOIN PR_DeductionCode ON PR_PayrollHistoryDetail.DeductionCode = PR_DeductionCode.DeductionCode

WHERE (((PR_PayrollHistoryDetail.DeductionCode) In ("000004","000013","000020","000023","RU","KU") Or (PR_PayrollHistoryDetail.DeductionCode) Is Null))

GROUP BY PR_PayrollHistoryDetail.EmployeeKey, PR_PayrollHistoryHeader.CheckDate, PR_PayrollHistoryDetail.CheckNo, PR_PayrollHistoryHeader.TotalRegularHours, PR_PayrollHistoryHeader.TotalGrossEarnings, Month([CheckDate]), Year([CheckDate])

HAVING (((PR_PayrollHistoryHeader.CheckDate) Between #1/1/2018# And #1/31/2018#)

ORDER BY PR_PayrollHistoryDetail.EmployeeKey, PR_PayrollHistoryHeader.CheckDate, PR_PayrollHistoryDetail.CheckNo;


NOTE: it works in Access but NOT in Crystal.

Does anyone know the SQL in Crystal so it works like the Access SQL? I have confirmed Access SQL works as expected.


-------------
____________________
Thank you very much!

Posted By: kevlray
Date Posted: 24 Jan 2018 at 5:28am
Not sure why you have a HAVING clause in the Access query. But I do not think it will affect anything. 

I have never tried to write a command for Access queries.  Have you tried dropping the Access query into a command and see if it works.   I know that all the CR fields will have to be replaced (you might be able to re-map them).

So back to the original question.  What is not working?

Posted By: techsupport
Date Posted: 24 Jan 2018 at 7:34am
Finally got the NULL values to show. It was a placement issue. I had to place the IsNull first.

Now my issue is that in Access it does not duplicate the check details but it does in Crystal Reports.

Although I can suppress it, which I did, I am unable to get the correct SUM due to the details repeating information in Crystal Reports.

NOTE: it does not duplicate / repeat in Access.

-------------
____________________
Thank you very much!

Posted By: kevlray
Date Posted: 24 Jan 2018 at 9:20am
Saw the post on another forum.  As they say 'Duh! I knew that'.

Have you tried selecting Distinct Records?  Not sure why it would not be duplicating in Access.

Posted By: techsupport
Date Posted: 24 Jan 2018 at 9:34am
It is not duplicating in Access but it is in Crystal.

I know the Grouping is different as I have Group on all the fields in Access and SUM on the deduction columns.

-------------
____________________
Thank you very much!

Posted By: kevlray
Date Posted: 25 Jan 2018 at 6:52am
Looks like you got the answer you needed on another forum.  Hope it all works for you now.


Print Page | Close Window