PeopleSoft Development: 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("" | &QryName | "");
   
   %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.