Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Display Checks without Deductions? Post Reply Post New Topic
Page  of 2 Next >>
Author Message
techsupport
Groupie
Groupie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 41
Quote techsupport Replybullet Topic: Display Checks without Deductions?
    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.


Edited by techsupport - 23 Jan 2018 at 7:03am
____________________
Thank you very much!
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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.
IP IP Logged
techsupport
Groupie
Groupie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 41
Quote techsupport Replybullet 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!
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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)
IP IP Logged
techsupport
Groupie
Groupie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 41
Quote techsupport Replybullet Posted: 23 Jan 2018 at 11:49am
Thanks

However, it's still not working
____________________
Thank you very much!
IP IP Logged
techsupport
Groupie
Groupie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 41
Quote techsupport Replybullet 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.


Edited by techsupport - 23 Jan 2018 at 6:30pm
____________________
Thank you very much!
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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?
IP IP Logged
techsupport
Groupie
Groupie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 41
Quote techsupport Replybullet 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!
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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.
IP IP Logged
techsupport
Groupie
Groupie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 41
Quote techsupport Replybullet 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!
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.015 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.