creating macros for a crosstab.

samsci · Jun 2, 2004

I have 3 fields from a table PS_JOB. They are emplid, deptid and empl_status.

Creating the crosstab is pretty easy. I list the deptids and the total number of employees in each deptartment.

However, I also need the following.

1). Total active employees in each department.
2). Total terminated employees in each department.
3). Percentage of terminated employees in each department.

I thought I could create 3 macros, based on empl_status of A and T.

But not sure how to do it.

Thanks!

Jeff

CharlesCook · Jun 3, 2004

I don't think you would need a macro derived field. You could do a few SQL derived fields and get what you want.

Name this SQL derived field Active

Decode(empl_status, /* <= Dbl Click in the Data field */
'A',1, /* If Active Count Them*/
'L',1, /* If Leave Count Them */
'S',1, /* If Short Term Leave Count Them*/
0) /* Else DO NOT Count Them */

Name this SQL derived field terminated

Decode(empl_status, /* <= Dbl Click in the Data field */
'A',0, /* If Active Do Not Count Them*/
'L',0, /* If Leave Do Not Count Them */
'S',0, /* If Short Term Leave Do Not Count Them*/
1) /* Else Count Them */

Put these SQL Derived fields in your crosstab and use the Sum function to add up the 1's and 0's

You could put them in your crosstab AGAIN with sum and use % or % of total switch to get a %

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

creating macros for a crosstab.

samsci

Programmer

CharlesCook

Programmer

Similar threads

Part and Inventory Search

Sponsor