IMPROVING THE USER EXPERIENCE USING RELATED CONTENT

On June 24th, I had the distinct pleasure of being selected to present my UX presentation for the PeopleTools Tech SIG's Open Mic Call.  And when I say "selected",  I really mean I hounded a couple of the officers until the gave in and let me present it.  But nonetheless, I got the June time slot and if you missed it and have a quest membership you can watch the recorded webinar using the link below.

http://www.questdirect.org/content/view.do?contentId=2776328

Presentation Includes:

1. The use of the RLC_QueryDataSource which allows the user to utilize Pagelet Wizard to present up to 25,000 rows of data in Related Content and presents links within the Pagelet Wizard content in a user friendly lightbox API (ColorBox)
2. A demonstration on how to leverage the Query API to generate Query Spreadsheets directly to the user using Related Content.
3. A demonstration on how to generate Spreadsheet reports from related content using David Vandiver's SQR2XLS IScript code in combination with Kevin’s ROWSET_TO_CELLS function to easily generate spreadsheets.
4.  Discuss how UMB is using Related Content to generate Spreadsheets for use with the Excel to CI utility to manage Position Data.

Presentation will include:

1. The use of the RLC_QueryDataSource which allows the user to utilize Pagelet Wizard to present up to 25,000 rows of data in Related Content and presents links within the Pagelet Wizard content in a user friendly lightbox API (ColorBox)
2. A demonstration on how to leverage the Query API to generate Query Spreadsheets directly to the user using Related Content.
3. A demonstration on how to generate Spreadsheet reports from related content using David Vandiver's SQR2XLS IScript code in combination with Kevin’s ROWSET_TO_CELLS function to easily generate spreadsheets.
4. Discuss how UMB is using Related Content to generate Spreadsheets for use with the Excel to CI utility to manage Position Data.

- See more at: http://www.questdirect.org/content/view.do?contentId=2776328#sthash.ZrhJzEFJ.dpuf

Presentation will include:

1. The use of the RLC_QueryDataSource which allows the user to utilize Pagelet Wizard to present up to 25,000 rows of data in Related Content and presents links within the Pagelet Wizard content in a user friendly lightbox API (ColorBox)
2. A demonstration on how to leverage the Query API to generate Query Spreadsheets directly to the user using Related Content.
3. A demonstration on how to generate Spreadsheet reports from related content using David Vandiver's SQR2XLS IScript code in combination with Kevin’s ROWSET_TO_CELLS function to easily generate spreadsheets.
4. Discuss how UMB is using Related Content to generate Spreadsheets for use with the Excel to CI utility to manage Position Data.

- See more at: http://www.questdirect.org/content/view.do?contentId=2776328#sthash.ZrhJzEFJ.dpuf

Presentation will include:

1. The use of the RLC_QueryDataSource which allows the user to utilize Pagelet Wizard to present up to 25,000 rows of data in Related Content and presents links within the Pagelet Wizard content in a user friendly lightbox API (ColorBox)
2. A demonstration on how to leverage the Query API to generate Query Spreadsheets directly to the user using Related Content.
3. A demonstration on how to generate Spreadsheet reports from related content using David Vandiver's SQR2XLS IScript code in combination with Kevin’s ROWSET_TO_CELLS function to easily generate spreadsheets.
4. Discuss how UMB is using Related Content to generate Spreadsheets for use with the Excel to CI utility to manage Position Data.

- See more at: http://www.questdirect.org/content/view.do?contentId=2776328#sthash.ZrhJzEFJ.dpuf

Clean up Company Directory Trees

If you are utilizing the company directory then you are probably building a new company directory tree every morning for the current day's use.  Eventually you are going to need to delete these old trees or it will have a performance effect on your directory or your SES search index for person.  This is the code from the one step Application Engine that I wrote to delete those old trees that have served their purpose.  It finds the max effdt tree and then deletes any historical trees that were created three days before that date.  Please note, if you are building any future dated trees, you will need to modify this code to prevent you from deleting the current effective dated tree.

&Session = %Session;

&MYTREE = &Session.GetTree();

Local Rowset &TREE = CreateRowset(Record.PSTREEDEFN);

SQLExec("SELECT MAX(EFFDT) -3 FROM PSTREEDEFN WHERE TREE_NAME = 'COMPANY_DIRECTORY'", &Max);

&count = &TREE.Fill("Where FILL.effdt < :1 and FILL.TREE_NAME = 'COMPANY_DIRECTORY'", &Max);

For &X = 1 To &count
   If All(&MYTREE) Then
      &RETVALUE = &MYTREE.Delete("", "", "COMPANY_DIRECTORY", &TREE(&X).PSTREEDEFN.EFFDT.Value, "");
   End-If;
End-For;

Get the RLC_QueryDataSource and More

If you have been following my blog, then you know I have been showing ways your organization can improve the UX by using Related Content.  This post is dedicated to sharing that knowledge in the form of a downloadable project.  This project contains the source code for the RLC_QueryDataSource and also contains the source code for using the Query API to generate Excel documents in Related Content.  You will still need to setup your datasource and configure the related content in order to use these examples, but don't fret I show you how do that in the original posts.

https://app.box.com/s/2lc5c2ujlfsfvsp5y4s115cvzhf4wzan

RLC_QueryDataSouce

Query API in From Related Content

Using Query API to Generate XLS documents from Related Content

Recently I demonstrated how to generate Excel reports from IScripts and stream them directly to the user via Related Actions.  Today I am going to continue that theme by showing how you can utilize the Query API and its method FormatBinaryResultsString to run query results to Excel.   In the previous examples I use David Vandiver's SQR2XLS IScript code in combination with my ROWSET_TO_CELLS function to easily generate spreadsheets.  In this example I will show you how you can use the Query API to generate Excel documents from Query definitions and use Related Actions to associate these Queries to a PeopleSoft transactions.


First you will need to create a WEBLIB.  I used one that I have already created called, WEBLIB_ZZ_XLS.  Then add the ISCRIPT1 field and copy the following code to the FieldFormula.

Function IScript_ToExcel();
   
   Local string &QryName, &QryDescr, &PrivateFlag;
   Local ApiObject &QryObj;
   Local Rowset &rsURLList;
   Local Record &rRecordExpr;
   
   
   Local array of string &bindArray = CreateArray(%Request.GetParameter("BIND1"), %Request.GetParameter("BIND2"), %Request.GetParameter("BIND3"), %Request.GetParameter("BIND4"), %Request.GetParameter("BIND5"), %Request.GetParameter("BIND6"), %Request.GetParameter("BIND7"), %Request.GetParameter("BIND8"), %Request.GetParameter("BIND9"), %Request.GetParameter("BIND10"));
   &QryName = %Request.GetParameter("QRYNAME");
   
   &QryObj = %Session.GetQuery();
   &returnStatus = &QryObj.Open(&QryName, True, False);
   
   
   &promptRecord = &QryObj.promptrecord;
   
   
   
   If &promptRecord <> Null Then
      For &i = 1 To &bindArray.Len
         If All(&bindArray [&i]) Then
            Local string &thisBind = "BIND" | String(&i);
            
            SQLExec("SELECT FIELDNAME  FROM PSQRYBIND WHERE OPRID = ' ' AND QRYNAME = :1 AND BNDNAME=:2", &QryName, &thisBind, &fieldName);
            REM MAP BIND VARIABLES TO THE  QUERY prompts;
            &TEMPSTRING2 = "Field." | &fieldName;
            &Prompt = &promptRecord.getfield(@&TEMPSTRING2);
            Evaluate &Prompt.Type
            When "DATETIME"
               
               &Prompt.Value = DateTimeValue(&bindArray [&i]);
               Break;
            When "DATE"
               
               &Prompt.Value = DateValue(&bindArray [&i]);
               Break;
            When "TIME"
               
               &Prompt.Value = TimeValue(&bindArray [&i]);
               Break;
            When-Other
               
               &Prompt.Value = &bindArray [&i];
            End-Evaluate;
            
            &LinkParamList = &LinkParamList | "&" | EncodeURLForQueryString(&fieldName) | "=" | EncodeURLForQueryString(&bindArray [&i]);
         End-If;
      End-For;
   End-If;
   
   %Response.SetContentType("application/vnd.ms-excel;");
   
   &sTempString = "attachment; filename=" | &QryName | "_" | Int(Rand() * 10000) | ".xls";
   %Response.SetHeader("Content-Disposition", &sTempString);
   
   If %Response.CharSet = "UTF-8" Then
      %Response.Write("");
   End-If;
   %Response.Write("");
   
   %Response.Write("");
   
   
   &QryDescr = &QryObj.Description;
   If All(&QryDescr) Then
      %Response.Write("

" | &QryDescr | "
");
   Else
      %Response.Write("

" | &QryName | "
");
   End-If;
   %Response.Write("

" | &LinkParamList | "
");
   
   
   
   
   
   &gQueryRowset = &QryObj.RunTorowset(&promptRecord, 25000);
   %Response.WriteBinary(&QryObj.FormatBinaryResultString(&gQueryRowset, %Query_XLS, 0, 0));
   
   
   &QryObj = Null;
   &gQueryRowset = Null;
   
   Return;
   
End-Function;

Make sure you set security to your new IScript and then create your Related Content Service:

I have added 10 Unique Prompt Names, feel free to add as many as you need!


For my example I have created a simple query called ZZ_EE_BY_JOBCODE, that selects Active Employee's in a Jobcode.  This is the SQL from the query tools:

SELECT A.EMPLID, A.EMPL_RCD, A.JOBCODE,  ':1', A.DEPTID
  FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1
  WHERE ( A.EMPLID = A1.EMPLID
    AND A.EMPL_RCD = A1.EMPL_RCD
    AND A1.OPRID = 'PS'
    AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.JOBCODE = :2
     AND A.HR_STATUS = 'A' ) )

:1 is the Jobcode Description, rather than join the jobcode table to this query, I will pass the description in from the page.

The next step is to create your Related Action on the Jobcode Tbl.

Then configure your new Service to send over the Job Title from the page and use the Jobcode as the Bind :2 prompt.

And finally Test your New Service on the Jobcode:

 Now your business user can associate any PeopleSoft Query to any transactions,

HR_DIRECT_REPORTS:DirectReports

HR_DIRECT_REPORTS is an Application Package used throughout the HCM system to achieve Supervisor Data, Peer Data and Direct Report Data.  The package requires that you set either a Target Position or Target Emplid/Empl Rcd and Navigation Method.  

Navigation Methods:
      (1) "Department Security Tree"
          Returns a flat list of all employee/jobs that are accessible
          to the target employee, based upon the target employee's
          RowSecClass. Note that there is no concept of a reporting
          hierarchy when using this method.
          This method cannot be used to determine an employee/job's supervisor.
          It can only be used to generate a list of "direct reports".

      (2) "Supervisor Id"
          Uses the "Supervisor Id" field on the Job record to determine
          reporting relationships. Note that this method does not differentiate
          between multiple concurrent jobs that the supervisor might hold.

      (3) "Department Manager Id"
          The relationship between an employee/job and a supervisor is
          defined by the Department table. The "Manager Id" defined on
          the Department table for the employee/job's department id is
          considered to be the employee/job's supervisor.
          When determining the supervisor for an employee/job, if there
          is no Manager Id associated with the employee's department, then
          we walk up the Department Tree to get the next highest department
          in the department hierarchy that has a Manager Id assigned.
          Note that this method does not fully differentiate between
          multiple concurrent jobs that the supervisor might hold.

      (4) "Reports To"
          This method requires that Full Position Management be implemented.
          The relationship between an employee/job and a supervisor is
          defined by the "Reports To" position on the Job record.
          When determining the supervisor for an employee/job, if there is
          no incumbent in the "Reports To" position on Job, then we walk the
          Position_Data table to get the incumbent in the next highest position
          in the position hierarchy.

      (5) "Partial Position Management / Supervisor Id"
          This method is a combination of "Reports To" and "Department Manager Id".
          First, the "Reports To" method is executed, and then the "Department Manager Id"
          method is executed. The results of these two methods are combined, giving
          preference to the "Reports To" method.
          This method should be used only when Partial Position Management
          has been implemented.

      (6) "Partial Position Management / Department Manager Id"
          This method is a combination of "Reports To" and "Supervisor Id".
          First, the "Reports To" method is executed, and then the "Supervisor Id"
          method is executed. The results of these two methods are combined, giving
          preference to the "Reports To" method.
          This method should be used only when Partial Position Management
          has been implemented.

      (7) "Group Id"
          This method is similar to the "Department Security Tree" method in that
          a flat list of employee/jobs is presented. There is no sense of a hierarchy
          when using this method. The list of employee/jobs returned is simply a list
          of all group members.
          This method cannot be used to determine an employee/job's supervisor. It
          can only be used to generate a list of "direct reports"

Below is an example of method that utilizes the HR_DIRECT_REPORTS application package to find a person's manager and then it will use the manager's position number if the current manager is not active.  This is useful if you are trying to skip manager's who are on leave.  If the initial call does not return an active manager it will recursively call itself until it finds a manager who is active.

method getManager
   Local integer &x;
   Local Rowset &results;
   Local string &rsltPosition;
   %This.DirRpts = create HR_DIRECT_REPORTS:DirectReports();
   %This.DirRpts.AsOfDate = %This.JobRecord.EFFDT.Value;
   &rsltPosition = &position;
   If All(&rsltPosition) Then
      %This.DirRpts.TargetPosition = &position;
   Else
      %This.DirRpts.TargetEmplid = %This.JobRecord.EMPLID.Value;
      %This.DirRpts.TargetERN = %This.JobRecord.EMPL_RCD.Value;
   End-If;
   %This.DirRpts.UseDirectReportsTables = False;
   %This.DirRpts.ShowName = False;
   %This.DirRpts.NavigationMethod = 4;
   %This.DirRpts.GetSupervisor();
   &results = %This.DirRpts.Supervisor;
   For &x = 1 To &results.RowCount
      If &results(&x).GetRecord(1).EMPL_STATUS.Value = "A" Then
         %This.managerID = &results(&x).GetRecord(1).EMPLID.Value;
         &mgrFound = True;
      End-If;
   End-For;
   
   If Not &mgrFound Then
      &rsltPosition = &results(1).GetRecord(1).POSITION_NBR.Value;
      If All(&rsltPosition) Then
         &position = &rsltPosition;
         %This.getManager();
      End-If;
   End-If;
end-method;

If you want to play around with this API, you an use the following navigation Main Menu>>Set Up HRMS>>Common Definitions>>Direct Reports for Managers>>Invoke Direct Reports API  and play around with the API through an easy to use GUI.

Create XLS Reports From Related Actions

If you want to get the most out of your 8.5x toolset you need to start leveraging the power of related content and actions.  One way to improve UX is to generate on demand reporting right from the transaction.

Related actions can be used to call IScripts and IScripts can be used to generate XLS reports and send them to directly to the user.  Below is an example of a report we generate for Succession Planning, this report exports all the plans within the current users organization and break them up by Line of Business, with each line of business on its own worksheet.

To Generate Speadsheets from Related Actions, start by down loading my example project that can be found here.  (Note, if you cannot get to this content, your firewall is blocking it).  After installing this project, at your own risk, into a development like PeopleSoft environment, then you need to configure your Related Content Service:

After you configured your Related Content Service you need to configure your new service to work on the Company Content Reference.   Make sure you also grant your test user access to your IScript, because it will not show up unless you have explicit security to the content, meaning PeopleSoft Admin role will not cut it.

Then Click into the Configure button above and Map the Menu option to the Company on Level 0 of the component, 

Now you are ready to view the end result.

Generate Excel Reports from IScripts

This is an example Project that demonstrates how you can generate Excel Reports from IScripts.  It is written for HCM, but could easily be adapted to FIN.  You will need to download the project from the link below:

https://app.box.com/s/wlhbppqxncayq7q60evj59l4gwp4vgqn

The IScript uses requires one Querystring parameter, COMPANY.  The company will be used to select all employees from the Employee's record and puts the results into an XML 2003 Spreadsheet and streams it via the browser.



Please note, downloading and installing this project is at your own risk.  With that said please compare the project with your target environment to make sure you are not accidently destroying one of your own custom objects.