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,