Macro creationg

[carlos139]

Looking for examples or documentation on writing macros with Report Smith. I want to display current number of employees even though my report spans multiple pay periods counting the employee multiple times.

 

[brianewo]

Are you tracking total wages over multiple weeks?

If so you can use this derieved field to sum the wages over and then only have one row of data for the emp.


SUM("PS_AL_CHK_DATA"."CK_GROSS&quot

that should get you what you want

 

[CharlesCook]

This request is more complex then just doing a simple sum. If you did a count (the 123 button) you really are counting the records in the group. So your grand total count of employees would be #of employees * #of pays best case. If anyone got an extra check for smoething that would be counted also.

So what you need to do is look at each record and only count each person one time. Lets assume the data is grouped by pay period and there is a footer for this group. In this footer you would place a macro derived field. The macro of this derived field would be run when the footer is displayed. Because the footer is displayed after all the detail records for this pay period are displayed and before any of the next pay period records are displayed your current record is the last record for the pay period. What you would do is store the pay period and emplid/file# of the employee add one to a counter and get the previous record. If this record is in the same pay period and is a different emplid/file# you save the emplid/file# and add one to the counter. When the pay period changes OR YOU GET TO THE FIRST RECORD you exit.

So it would look something like this.

Sub PayPeriodEmployeeCount()

Group$ = Field$(The same data field as pay period group)
Emplid$ = ""

REM This loop will process all data except record 1
While ((Group$ = Field$(DB Field)) and (Current() > 1))

REM Have I counted this person yet?
If Emplid$ <> Field$(DB Field) Then
Emplid$ = Field$(DB Field)
Total = Total + 1
End If

REM Get the next previous record
GetPrevious
WEnd

REM Process record 1
If ((Group$ = Field$(DB Field)) and (Current() = 1)) Then
If Emplid$ <> Field$(DB Field) Then
Total = Total + 1
End If
End If

REM All data comming into and out of a Macro DerivedField REM must be text
DerivedField Str(Total)

End Sub

For a grand total count use this derived field. This Derived field will add up the results of the prev macro one time for each group

Sub GrandTotalEmployeeCount()

Group$ = &quot;&quot;

While (Current() > 1)
If (Group$ <> Field$(DB Field)) Then
Group$ = Field$(DB Field)
Total = Total + Val(Field$(&quot;PayPeriodEmployeeCount&quot)
End If

Get Previous
WEnd

If (Current() = 1) Then
If (Group$ <> Field$(DB Field)) Then
Total = Total + Val(Field$(&quot;PayPeriodEmployeeCount&quot)
End If
End If

DerivedField Str(Total)

End Sub CharlesCook.com
ADP - PeopleSoft
ReportSmith - Crystal Reports - SQR - Query
Reporting - Interfaces - Data Mining

 

[carlos139]

Thanks Guys.... Charles worked great!!!

 

[gtarrant]

How would you modify the above macro to not only count all employees one time, but to add an additional column that would count the employees one time who are exempt?

 

[CharlesCook]

Sub PayPeriodEmployeeCount()

Group$ = Field$(The same data field as pay period group)
Emplid$ = &quot;&quot;

REM This loop will process all data except record 1
While ((Group$ = Field$(DB Field)) and (Current() > 1) and (SOMEFIELD = SOMEVALUE))

REM Have I counted this person yet?
If Emplid$ <> Field$(DB Field) Then
Emplid$ = Field$(DB Field)
Total = Total + 1
End If

REM Get the next previous record
GetPrevious
WEnd

REM Process record 1
If ((Group$ = Field$(DB Field)) and (Current() = 1) and (SOMEFIELD = SOMEVALUE)) Then
If Emplid$ <> Field$(DB Field) Then
Total = Total + 1
End If
End If

REM All data comming into and out of a Macro DerivedField REM must be text
DerivedField Str(Total)

End Sub CharlesCook.com
ADP - PeopleSoft
ReportSmith - Crystal Reports - SQR - Query
Reporting - Interfaces - Data Mining