I received a request for a Position Data Report. The client called it a Span of Control Report and it was to show how many direct reports each manager had. The client also wanted this report to open in Excel while still being formatted. Here is what I came up with. After a little bit of work I came up with an App Engine that uses a recursive function that gets all Reports To and does some processing. I could have used CONNECT BY but didn't for a reason I don't recall. SQR wasn't an option for me either since the functions aren't naturally recursive (the variables aren't local within the stack) and I wanted to perform some additional logic and it would have made it unnecessarily complex. We then use OLE to open and write to an Excel File.
 |
| Report Output(Obviously modified so totals are off) |
In PeopleSoft, the top position in a Business Unit is defined as one that reports to itself. Within our Org, that is not the case so in order to properly show BUs for this report, I need to give users the ability to define the top per BUSINESS_UNIT. We also allow the user running the report to define the colors they want for the reporting rows and whether they want a full list of employees or only those with Direct Reports. Since we are using OLE, we need an NT Process Scheduler with Excel installed.
 |
| Run Control |
 |
| Report Config Page on Run Control |
 |
| Defaults Configuration |
To achieve the desired affect we are going to instantiate an excel object, use it to open a template file, save it as our new output file and fill it with formatted data. To make things easier I use a template file that is already setup for the report. I set my column header, column formats and even header image in the template file and then upload the Template file to the Process Scheduler at runtime (if one is not uploaded at runtime we use a default that is stored separately.) We allow our clients to upload new Template files on their own through a management component that also sets defaults in case the users does not define them on the Run Control. The management component also allows us to format row colors.
 |
| The Template File |
We begin the process by setting initial AE variables and instantiating record, sql and array objects. We then move on to Open the Excel Workbook.
/* *************************************** */ /* Begin Process */ /* *************************************** */ GetExcelObject();
The GetExcelObject() function instantiates our COM object, opens the template and saves it as a new file. &template_path is the full path and filename of my template file. We use a getattachment to retrieve it from the a File.DB.
/* *************************************** */
/* GetExcelObject() */
/* *************************************** */
Function GetExcelObject()
&OBJ_excel = CreateObject("COM", "Excel.Application");
&OBJ_excel.DisplayAlerts = "False";
&OBJ_excel.UserControl = "False";
&OBJ_workbooks = ObjectGetProperty(&OBJ_excel, "Workbooks");
&OBJ_workbook = ObjectDoMethod(&OBJ_workbooks, "Open", &template_path);
/* SaveAs New File */
SQLExec("SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = :1",
&process_instance, &outputdir);
If Substring(&outputdir, Len(&outputdir), 1) <> "\" Then
&outputdir = &outputdir | "\";
End-If;
&output_path = &outputdir | &EXCEL_FileName;
&OBJ_excel.ActiveWorkBook.SaveAs(&output_path);
End-Function;
I give my users the ability to run 1 or all BUSINESS_UNITs, so i push all into an array and loop through that.
/* Fill Business Unit Array */ If All(&rc_business_unit) Then &ARRAY_business_units.Push(&rc_business_unit); Else &sql8.Execute(&REC_bus_unit_tbl_hr); While &sql8.Fetch(&REC_bus_unit_tbl_hr) &ARRAY_business_units.Push(&REC_bus_unit_tbl_hr.BUSINESS_UNIT.Value); End-While; End-If; /* Loop through BUSINESS_UNIT Array */ &bu_index = 0; While &ARRAY_business_units.Next(&bu_index); &business_unit = &ARRAY_business_units [&bu_index]; If All(&rc_positon_nbr) Then &where_string = "AND PD.POSITION_NBR = '" | &rc_positon_nbr | "'" Else &where_string = "AND PD.REPORTS_TO = PD.POSITION_NBR AND PD.BUSINESS_UNIT = '" | &business_unit | "'"; End-If; &row = 10; GetExcelWorksheet();
For each BUSINESS_UNIT the function GetExcelWorksheet() will copy the template worksheet and save it as a new name.
/* *************************************** */
/* GetExcelWorksheet() */
/* *************************************** */
Function GetExcelWorksheet()
If &bu_index = 1 Then
&OBJ_worksheets = ObjectGetProperty(&OBJ_workbook, "Worksheets");
End-If;
&OBJ_worksheet = ObjectGetProperty(&OBJ_worksheets, "Item", &bu_index);
ObjectDoMethod(&OBJ_worksheet, "Copy", &OBJ_worksheet);
&OBJ_worksheet = ObjectGetProperty(&OBJ_worksheets, "Item", &bu_index);
ObjectSetProperty(&OBJ_worksheet, "Name", &business_unit);
End-Function;
Then we set the Header. I use an array to store my format settings.
/* *************************************** */ /* SetWorksheetHeader() */ /* *************************************** */ Function SetWorksheetHeader() /* Set Output Header */ &OBJ_worksheet.Cells(1, 1).Value = "Temple University Health System"; &OBJ_worksheet.Cells(2, 1).Value = "Span of Control Summary"; &OBJ_worksheet.Cells(3, 1).Value = "Org " | &business_unit | " Supervisors"; &OBJ_worksheet.Cells(4, 1).Value = &asofdate; End-Function;
/* *************************************** */
/* PopulateFormatArray() */
/* *************************************** */
Function PopulateFormatArray()
/** Check Run Control page, if settings is missing use default **/
Local Record &REC_span_cfg = CreateRecord(Record.TU_HR_SPAN_CFG);
SQLExec("SELECT 'Y' FROM %TABLE(:1) WHERE OPRID = :2 AND RUN_CNTL_ID = :3",
&REC_span_cfg, &oprid, &run_cntl_id, &CFG_exist);
If &CFG_exist = "Y" Then
MessageBox(0, "", 0, 0, "Using User Defined configuration
for Level and Color");
&sql8 = CreateSQL("%SELECTALL(:1) WHERE OPRID = :2 AND RUN_CNTL_ID = :3
ORDER BY LEVELNUM ASC", &REC_span_cfg, &oprid, &run_cntl_id);
&ARRAY_properties = CreateArrayRept(CreateArrayRept("", 0), 0);
&lineno = 1;
While &sql8.Fetch(&REC_span_cfg)
&ARRAY_properties.Push(&REC_span_cfg.LEVELNUM.Value);
&ARRAY_properties [&lineno].Push(&REC_span_cfg.FLAG.Value);
&ARRAY_properties [&lineno].Push(&REC_span_cfg.COLOR.Value);
/* Set Header key values */
If &lineno <= 7 Then
If &REC_span_cfg.FLAG.Value = "Y" Then
&OBJ_worksheet.Cells(&lineno, 3).Font.Bold = True;
Else
&OBJ_worksheet.Cells(&lineno, 3).Font.Bold = False;
End-If;
&OBJ_worksheet.Cells(&lineno, 3).Interior.Colorindex =
&REC_span_cfg.COLOR.Value;
End-If;
&lineno = &lineno + 1;
End-While;
Else
Local Record &REC_span_dft = CreateRecord(Record.TU_HR_SPAN_DFT);
MessageBox(0, "", 0, 0, "Use Default configuration for Level and Color");
&sql8 = CreateSQL("%SELECTALL(:1) WHERE ATTACH_SEQ_NBR = :2
ORDER BY LEVELNUM ASC", &REC_span_dft, &ATTACH_SEQ_NBR);
&ARRAY_properties = CreateArrayRept(CreateArrayRept("", 0), 0);
&lineno = 1;
While &sql8.Fetch(&REC_span_dft)
&ARRAY_properties.Push(&REC_span_dft.LEVELNUM.Value);
&ARRAY_properties [&lineno].Push(&REC_span_dft.FLAG.Value);
&ARRAY_properties [&lineno].Push(&REC_span_dft.COLOR.Value);
/* Set Header key values */
If &lineno <= 7 Then
If &REC_span_dft.FLAG.Value = "Y" Then
&OBJ_worksheet.Cells(&lineno, 3).Font.Bold = True;
Else
&OBJ_worksheet.Cells(&lineno, 3).Font.Bold = False;
End-If;
&OBJ_worksheet.Cells(&lineno, 3).Interior.Colorindex =
&REC_span_dft.COLOR.Value;
End-If;
&lineno = &lineno + 1;
End-While;
End-If;
End-Function;
We then can get our Top level Supervisor. In our case we look for POSITION_NBR = REPORTS_TO and when we don't find it we reference our override table. We then output it to our report and then begin our calls to our recursive GetReportingPositions(0, &asofdate, &top_level_supv) function.
/* **************************************************************** */
/* GetReportingPositions(&x As number, &in_date, &in_position_nbr); */
/* **************************************************************** */
Function GetReportingPositions(&x As number, &in_date, &in_position_nbr);
Local string &fill;
Local Record &REC_position_data = CreateRecord(Record.POSITION_DATA);
/** Update order by based on Description **/
Local SQL &sql2 = CreateSQL("%SELECTALL(:1 PD) WHERE PD.EFFDT =
(SELECT MAX(PD_ED.EFFDT) FROM %TABLE(:1) PD_ED WHERE PD_ED.POSITION_NBR
= PD.POSITION_NBR AND PD_ED.EFFDT <= %DATEIN(:2)) AND PD.EFF_STATUS = 'A'
AND PD.POSN_STATUS = 'A' AND PD.REPORTS_TO = :3 AND
PD.POSITION_NBR <> :3 ORDER BY PD.DESCR ASC");
&sql2.ReuseCursor = True;
Local number &in_level = &x + 1;
&fill = "";
While Len(&fill) < &in_level * 4
&fill = &fill | "-";
End-While;
&sql2.Execute(&REC_position_data, &in_date, &in_position_nbr);
While &sql2.Fetch(&REC_position_data)
/* Does Subordinate have Positions reporting to them - if yes output */
If &sql7test = "N" Then
&sql7 = CreateSQL("SELECT 'Y' FROM %TABLE(:1) PD WHERE PD.EFFDT =
(SELECT MAX(PD_ED.EFFDT) FROM %TABLE(:1) PD_ED WHERE PD_ED.POSITION_NBR =
D.POSITION_NBR AND PD_ED.EFFDT <= %DATEIN(:2)) AND PD.REPORTS_TO = :3 AND
D.POSITION_NBR <> :3 AND PD.EFF_STATUS = 'A' AND PD.POSN_STATUS = 'A'",
REC_position_data, &in_date, &REC_position_data.POSITION_NBR.Value);
&sql7.ReuseCursor = True;
&sql7test = "Y";
Else
&sql7.Execute(&REC_position_data, &in_date,
&REC_position_data.POSITION_NBR.Value);
End-If;
If &sql7.Fetch(&test) Or
&rc_show_all_flag = "Y" Then
/* Get Counts of Reports to and add to summary array */
&count = 0;
If &sql9test = "N" Then
&sql9 = GetSQL(SQL.TU_SPAN_POS_INCUMBENT_COUNT, Record.POSITION_DATA,
Record.JOB, &asofdate, &REC_position_data.POSITION_NBR.Value);
&sql9.ReuseCursor = True;
&sql9test = "Y";
Else
&sql9.Execute(Record.POSITION_DATA, Record.JOB, &in_date,
&REC_position_data.POSITION_NBR.Value);
End-If;
While &sql9.Fetch(&report_bu, &report_count);
PopulateSummaryArray(&report_bu, &report_count);
&count = &count + &report_count;
End-While;
&test = "N";
&sql3.Execute(&REC_job, &in_date, &REC_position_data.POSITION_NBR.Value);
While &sql3.Fetch(&REC_job)
GetName(&asofdate, &REC_job.EMPLID.Value, &name);
GetDept(&asofdate, &REC_job.SETID_DEPT.Value,
&REC_job.DEPTID.Value, &dept_descr);
PopulateExcelObject(&in_level, &row
, &fill | &REC_position_data.POSITION_NBR.Value
, &fill | &REC_position_data.DESCR.Value
, &name, &REC_job.BUSINESS_UNIT.Value, &REC_job.DEPTID.Value
, &dept_descr, &count);
&row = &row + 1;
&test = "Y";
End-While;
If &test = "N" Then
MessageBox(0, "", 0, 0, "Vacant: Could not get PS_JOB " |
REC_position_data.POSITION_NBR.Value);
SQLExec("SELECT SETID FROM %TABLE(:1) WHERE SETCNTRLVALUE = :2"
, Record.SET_DEPT_BU_VW, &REC_position_data.BUSINESS_UNIT.Value
, &setid_dept);
GetDept(&asofdate, &setid_dept, &REC_position_data.DEPTID.Value
, &dept_descr);
PopulateExcelObject(&in_level, &row
, &fill | &REC_position_data.POSITION_NBR.Value
, &fill | &REC_position_data.DESCR.Value, "Vacant"
, &REC_position_data.BUSINESS_UNIT.Value, &REC_position_data.DEPTID.Value
, &dept_descr, &count);
&row = &row + 1;
End-If;
End-If;
/* Does Subordinate's reporting positions have Positions reporting to them
- if yes go down another level */
If &sql6test = "N" Then
&sql6 = CreateSQL("SELECT 'Y' FROM %TABLE(:1) PD WHERE PD.EFFDT =
(SELECT MAX(PD_ED.EFFDT) FROM %TABLE(:1) PD_ED WHERE PD_ED.POSITION_NBR =
PD.POSITION_NBR AND PD_ED.EFFDT <= %DATEIN(:2)) AND
PD.REPORTS_TO IN (SELECT PD1.POSITION_NBR FROM %TABLE(:1) PD1
WHERE PD1.EFFDT = (SELECT MAX(PD1_ED.EFFDT) FROM %TABLE(:1) PD1_ED
WHERE PD1_ED.POSITION_NBR = PD1.POSITION_NBR AND PD1_ED.EFFDT <= %DATEIN(:2))
AND PD1.REPORTS_TO = :3 AND PD1.POSITION_NBR <> :3 AND PD1.EFF_STATUS = 'A'
AND PD1.POSN_STATUS = 'A') AND PD.EFF_STATUS = 'A' AND PD.POSN_STATUS = 'A'"
, &REC_position_data, &in_date, &REC_position_data.POSITION_NBR.Value);
&sql6.ReuseCursor = True;
&sql6test = "Y";
Else
&sql6.Execute(&REC_position_data, &in_date
, &REC_position_data.POSITION_NBR.Value);
End-If;
If &sql6.Fetch(&test) Or
&rc_show_all_flag = "Y" Then
GetReportingPositions(&in_level, &in_date
, &REC_position_data.POSITION_NBR.Value);
End-If;
End-While;
&sql2.Close();
End-Function;
/* *************************************** */
/* PopulateExcelObject() */
/* *************************************** */
Function PopulateExcelObject(&level As number, &x As number, &in_pos_nbr
, &in_pos_descr, &in_name, &in_bu, &in_dept, &in_dept_descr
, &in_count As number)
/* Reset Function variables */
&bold = False;
&colorindex = 2;
/* Check Level Properties */
&index = 0;
While &ARRAY_properties.Next(&index);
&test = Value(&ARRAY_properties [&index][1]);
&test2 = &ARRAY_properties [&index][2];
&test3 = &ARRAY_properties [&index][3];
If &level = &test Then
&bold = &ARRAY_properties [&index][2];
&colorindex = &ARRAY_properties [&index][3];
If &bold = "Y" Then
&bold = True;
Else
&bold = False;
End-If;
End-If;
End-While;
&range = "A" | &x | ":G" | &x;
&OBJ_worksheet.Range(&range).Font.Bold = &bold;
&OBJ_worksheet.Range(&range).Interior.Colorindex = &colorindex;
&OBJ_worksheet.Cells(&x, 1).Value = &in_pos_nbr;
&OBJ_worksheet.Cells(&x, 1).Value = &in_pos_nbr;
&OBJ_worksheet.Cells(&x, 2).Value = &in_pos_descr;
&OBJ_worksheet.Cells(&x, 3).Value = &in_name;
&OBJ_worksheet.Cells(&x, 4).Value = &in_bu;
&OBJ_worksheet.Cells(&x, 5).Value = &in_dept;
&OBJ_worksheet.Cells(&x, 6).Value = &in_dept_descr;
&OBJ_worksheet.Cells(&x, 7).Value = &in_count;
End-Function;
After we've populated the report, we need to ensure that we close it and any open cursors since we are re-using them for performance. If we don't close the excel object we will clog the server with open EXCEL.exe instances.
/* *************************************** */ /* CloseExcelObject() */ /* *************************************** */ Function CloseExcelObject() /* Delete "Template" Worksheet */ &OBJ_worksheet = ObjectGetProperty(&OBJ_worksheets, "Item", "Template"); ObjectDoMethod(&OBJ_worksheet, "Delete"); /* Save Excel file and quit */ &OBJ_excel.ActiveWorkBook.Save(); &OBJ_excel.ActiveWorkBook.Close(); &OBJ_excel.DisplayAlerts = "True"; &OBJ_excel.Quit(); End-Function;I've attached a complete copy of the Step if you want to copy it, but you will probably benefit from re-writing it, as it will be tailored to your environment.
Step2 Complete
No comments:
Post a Comment