If/Then Macro

[christimess]

In the report I am creating I need to pull the check date and what the month-to-date(MTD) deduction would be within the month that the check date is in. I am not sure if I am going the “easy” way about this, but I can’t think of another way, so I am open to all suggestions.
The If/Then statement I am trying to create is:
If derived field month = report variable month then derived field deduction amount.
The macro I created looks as follows:
Sub MTD()
If DerivedField (“MONTH”) = GetRepVar(“month”) then DerivedField(“DEDCDEE”)
End if
DerivedField(X)
End Sub
There are two errors in the above statement, but I don’t know what they are.
Is it correct to do this as a macro, or can a derived field be done? I tried to create it as a derived field, but could not get it to work either.
My database platform is Oracle.

Derived Field MONTH is as follows:
TO_CHAR(“DED”.”CHECK_DT”,”MM”)
Derived Field DEDCDEE is as follows:
SUM(DISTINCT CASE “DED”.”AL_DEDCD”
WHEN ‘X’ THEN “DED”.”AL_AMOUNT”
WHEN ‘Y’ THEN “DED”.”AL_AMOUNT”
WHEN ‘Z’ THEN “DED”.”AL_AMOUNT”
ELSE 0
END)

Thanks in advance for your help and Happy New Year Everyone

 

[obiwaugh]

Hosted ADP client?

First of all.....it looks like you are trying to add up all the deductions from all of the checks in a given month (the month of a certain check)

If that's the case, then you want to get rid of the DISTINCT from your CASE function. If 'X' was taken out of 4 paychecks, and it was $10 each time...then the $10 will only get added once to your total. Not what you want. Besides, you don't really need that derived field.

Another issue that you haven't taken into account is the year.....after a while the report will start adding up deductions taken in the same month AND previous years.

To get deductions from a specific month, create 2 report variables (CHK_DT & START_DT). Use CHK_DATE to select the specific check date that you are going for. Then use START_DT as the beginning of a date range that is between START_DT and CHK_DT. Then you should just enter the deductions you are going for in selection criteria.

By doing this you've got only the rows you need. Highlight the PAYGROUP field and press the Group/Footer button. Then click on FILE_NBR and press the Group/Footer button. Then highlight the AL_AMOUNT field and press the Sum button.

You are looking at the sum of the desired decutions taken in the month of the selected check.

Hope this helps....

 

[christimess]

Thanks for your help. I do see what you’re saying. Unfortunately that won't work in this situation. In the end I have to have 3 columns, one for current deduction, one for month to date and one for year to date and so I am going to need to figure out a derived field for each column.

 

[obiwaugh]

That's not a problem. I'll give you the code tomorrow morning at the latest.

 

[obiwaugh]

OK....I worked out a pretty neat solution to your report.

1. Add PS_AL_CHK_DATA (alias of CHK_DATA) to the report. Include PAYGROUP, FILE_NBR, CHECK_DT.

2. Select a specific CHECK_DT in Selection Criteria by hard coding a date, using a report variable, or you can even use the following formula to select the most recent check for every employee:

(
SELECT MAX(B.CHECK_DT)
FROM DEMOV3.dbo.PS_AL_CHK_DATA B
WHERE CHK_DATA.FILE_NBR=B.FILE_NBR
AND CHK_DATA.PAYGROUP=B.PAYGROUP
)

The following derived field (all 3 are coded for SQL Server) will give you the current total

(
SELECT SUM(DED.AL_AMOUNT)
FROM DEMOV3.dbo.PS_AL_CHK_DED DED
WHERE DED.FILE_NBR=CHK_DATA.FILE_NBR
AND DED.PAYGROUP=CHK_DATA.PAYGROUP
AND CHK_DATA.CHECK_DT=DED.CHECK_DT
AND DED.AL_DEDCD IN ('X','Y','Z')
)

3. The following derived field will give you MTD deduction amounts:

(
SELECT SUM(DED.AL_AMOUNT)
FROM DEMOV3.dbo.PS_AL_CHK_DED DED
WHERE DED.FILE_NBR=CHK_DATA.FILE_NBR
AND DED.PAYGROUP=CHK_DATA.PAYGROUP
AND (DATEPART(MM,CHK_DATA.CHECK_DT))=(DATEPART(MM,DED.CHECK_DT))
AND (DATEPART(YYYY,CHK_DATA.CHECK_DT))=(DATEPART(YYYY,DED.CHECK_DT))
AND DED.AL_DEDCD IN ('X','Y','Z')
)

4. The following derived field will give you YTD deduction amounts:

(
SELECT SUM(DED.AL_AMOUNT)
FROM DEMOV3.dbo.PS_AL_CHK_DED DED
WHERE DED.FILE_NBR=CHK_DATA.FILE_NBR
AND DED.PAYGROUP=CHK_DATA.PAYGROUP
AND (DATEPART(YYYY,CHK_DATA.CHECK_DT))=(DATEPART(YYYY,DED.CHECK_DT))
AND DED.AL_DEDCD IN ('X','Y','Z')
)


*****NOTE: You will have to change the DATEPART functions to the corresponding TO_CHAR functions. You will also need to change the 'DEMOV3.dbo' prefix to whatever you use to introduce tables in subqueries (sometimes it's your USERID...sometimes you don't even use the prefix, depending on how ReportSmith was installed)

*****ALSO: If you want to get cute, you can even create a report variable that holds a list of the deduction codes you are looking for. Then use the report variable in the derived field, rather than hard coding the specific deductions...more flexibility if it's desired. If it's not necessary, then don't sweat it.

 

[christimess]

Hi,
Thank you for all of your help. I am still working on making these statements work in Oracle. Just one more question for you (I hope): In the statement you state
FROM DEMOV3.dbo.PS_AL_CHK_DED DED
what is the second DED? Is it DEDCD?
Thanks!

 

[obiwaugh]

DED is the alias (or nickname) that you're assigning to PS_AL_CHK_DED. Since we're referencing the table, and not physically adding it to the ReportSmith report, we're just assigning the alias in the derived field. The alias is only local, however.

 

[christimess]

Hi!
This is working great! Thank you for your help. However, I need an else statement so that if there is no value a 0.00 will appear. I have been trying to add an else statement, but it does not seem to be working. Any suggestions?
Thanks Again!

 

[obiwaugh]

Change the derived field to read something like this: When the sum is not equal to zero, show the sum. When the sum does not exist, show a zero.

CASE
WHEN
(
SELECT SUM(DED.AL_AMOUNT)
FROM DEMOV3.dbo.PS_AL_CHK_DED DED
WHERE DED.FILE_NBR=CHK_DATA.FILE_NBR
AND DED.PAYGROUP=CHK_DATA.PAYGROUP
AND CHK_DATA.CHECK_DT=DED.CHECK_DT
AND DED.AL_DEDCD IN ('12','16','B')
)
<> 0
THEN
(
SELECT SUM(DED.AL_AMOUNT)
FROM DEMOV3.dbo.PS_AL_CHK_DED DED
WHERE DED.FILE_NBR=CHK_DATA.FILE_NBR
AND DED.PAYGROUP=CHK_DATA.PAYGROUP
AND CHK_DATA.CHECK_DT=DED.CHECK_DT
AND DED.AL_DEDCD IN ('12','16','B')
)
ELSE 0
END