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!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

creating macros for a crosstab.

Status
Not open for further replies.

samsci

Programmer
Nov 25, 2003
3
US
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top