|
Open PeopleSoft Queries from Excel |
|
|
Saturday, 01 April 2006 |
Here's a cool use of David Vandiver's Excel XML libraries when mashed up with an the PeopleCode Query API, iScript, and XML Link.
Imagine launching Excel, hitting File > Open, and entering a URL with your query name and prompt values embedded in it, and your query opens in Excel. No code, nVision plug in, or PeopleSoft ODBC driver required.
Here's how it looks to an end-user:
Launch Excel.
Then go to File > Open.
Enter this URL into the Filename text box: http://laptop.ok.cox.net:6600/xmllink/hdmo89/ExecQuery?userid=BRENT&pwd=BRENT &runservice=yes&qryname=PT_OBJSEC_USER&type=public&OPRID=BRENT
You'll notice that your PeopleSoft username and password are parameters in the URL, along with the query name (qryname=PT_OBJSEC_USER), the query type (type=public or type=private), and any bind parameters (oprid=BRENT).
Then hit open and your query will show up in Excel. Look mom - No code on the client! Nice if you hate PeopleSoft's ODBC driver as much as I do.
In addition to David Vandiver's Excel XML libraries for PeopleCode, here's the PeopleCode that makes it possible:
Function IScript_ExecQuery() Returns boolean;
rem Local string &sOutFile;
Local string &QryName;
Local string &RunCntlID;
Local Record &rcdQryRunParms;
Local Record &rcdQryPrompts;
Local SQL &sqlSelectQryParms;
Local number &cnt, &i, &Result;
Local ApiObject &aRunQry, &QryRunTimePrompts;
Local boolean &bPublic;
Local number &nOrigPSMessagesMode;
Local Rowset &aRowSet;
Local XmlDoc &inXMLDoc;
Local boolean &ret;
rem Open the query the user specifies with the QRYNAME parameter on the URL;
&aRunQry = %Session.GetQuery();
&QryName = %Request.GetParameter("QryName");
&bPublic = True;
If %Request.GetParameter("TYPE") = "PRIVATE" Then
&bPublic = False;
End-If;
If &aRunQry.Open(&QryName, &bPublic, False) = 0 Then
rem Get the query prompts, and assign them to parameter values specified in the URL;
&rcdQryPrompts = &aRunQry.PromptRecord;
If &rcdQryPrompts <> Null Then
&QryRunTimePrompts = &aRunQry.RunTimePrompts;
For &i = 1 To &rcdQryPrompts.FieldCount
&rcdQryPrompts.GetField(&i).Value = %Request.GetParameter(&rcdQryPrompts.GetField(&i).Name);
End-For;
End-If;
Else
rem Query didn't open cleanly, return from the function without doing anything;
Return True;
End-If;
rem Run the query to a rowset;
&aRowSet = &aRunQry.RunToRowset(&rcdQryPrompts, 0);
rem Now parse the rowset and create valid XML tags;
If &aRowSet <> Null Then
rem Looks like a good query -- Initialize the Excel worksheet;
xml_init_file(0, 0, 0, 0);
Rem now initialize title, header, and row styles;
xml_init_style(0);
xml_style_font("Arial", 14, 1);
xml_style_alignment("Left", "Bottom", 0, 0);
xml_init_style(1);
xml_style_font("Arial", 12, 1);
xml_style_alignment("Left", "Bottom", 0, 0);
xml_style_border("Bottom", "Continuous", 1);
xml_init_style(2);
xml_style_font("Left", 10, 0);
xml_finalize_style();
xml_init_worksheet(&QryName, 0, 0);
xml_init_col(1, 0, 50, 0, 0);
xml_init_col(2, 0, 125, 0, 0);
xml_init_col(3, 0, 135, 0, 0);
xml_init_col(4, 0, 50, 0, 0);
xml_init_col(5, 0, 110, 0, 0);
xml_init_row(0, 0, 25, 0, 0);
xml_cell_char(&aRunQry.Description, 0, 0);
&aRec = &aRowSet(1).GetRecord(1);
&QrySelOutputFldCol = &aRunQry.QuerySelect.QueryOutputFields;
If &aRowSet.RowCount > 0 Then
rem Rows are being returned. Write them to an Excel sheet;
rem First, write the header row;
xml_init_row(1, 0, 0, 0, 1);
For &j = 1 To &QrySelOutputFldCol.Count
xml_cell_char(&QrySelOutputFldCol.Item(&j).HeadingText, 0, 0);
End-For;
For &i = 1 To &aRowSet.ActiveRowCount
&aRow = &aRowSet(&i);
xml_init_row(1, 0, 0, 0, 2);
For &j = 1 To &aRow.GetRecord(1).FieldCount
If Left(&QrySelOutputFldCol.Item(&j).Format, 4) = "CHAR" Or
Left(&QrySelOutputFldCol.Item(&j).Format, 9) = "LONG CHAR" Or
Left(&QrySelOutputFldCol.Item(&j).Format, 5) = "IMAGE" Then
xml_cell_char(&aRow.GetRecord(1).GetField(&j).Value, 0, 0);
End-If;
If Left(&QrySelOutputFldCol.Item(&j).Format, 6) = "NUMBER" Or
Left(&QrySelOutputFldCol.Item(&j).Format, 6) = "SIGNED" Then
xml_cell_number(&aRow.GetRecord(1).GetField(&j).Value, "99999999999.99", 0, 0);
End-If;
If Left(&QrySelOutputFldCol.Item(&j).Format, 4) = "DATE" Or
Left(&QrySelOutputFldCol.Item(&j).Format, 4) = "TIME" Or
Left(&QrySelOutputFldCol.Item(&j).Format, 9) = "DATE/TIME" Then
xml_cell_datetime(&aRow.GetRecord(1).GetField(&j).Value, 0, 0);
End-If;
End-For;
End-For;
Else
xml_init_row(0, 0, 25, 0, 0);
xml_cell_char("No records retrieved", 0, 0);
End-If;
Else
xml_init_row(0, 0, 25, 0, 0);
xml_cell_char("Failed to retrieve result set", 0, 0);
End-If;
xml_finalize_worksheet_pagesetup("Landscape", "", 0.5, 0.23, 0.5, 0.26, "", "");
xml_finalize_worksheet_print(1, 1000, 0, 75);
xml_finalize_workbook();
&aRunQry.Close();
%Session.PSMessagesMode = &nOrigPSMessagesMode;
If %Session.PSmessages.Count = 0 Then
Return True;
Else
Return False;
End-If;
End-Function; /* ExecQuery */
Once you get the PeopleCode in place, make sure you add it to the web libraries on a permission list, and register it as an XML Link function.
|
|
Last Updated ( Wednesday, 05 April 2006 )
|
|
|
Online Excel Reporting in PeopleSoft |
|
|
Thursday, 30 March 2006 |
I've been a little busy lately, so I asked my friend and fellow PeopleSoft expert David Vandiver if he would like to contribute an article to the blog. He came back with a very interesting post about generating Excel reports from PeopleCode using ISCripts (see below).
Incidentally, if you have a great PeopleSoft approach or tip you'd like to share but don't want to start your own blog, feel free to send it to me (
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
) and I'll do my best to get it posted.
And now, here's David's article:
Want to add some cool features to PeopleSoft while you wait for Fusion? Want to start producing Excel documents well before XML Publisher is available? Then this blog entry is for you.
I have built a free PeopleCode library that allows custom Excel reports to be built via a PeopleSoft web page. The end result allows an end-user to click a link or button and a separate PeopleSoft window will be launched giving an Excel document. And not just any plain Excel format, but a rich Excel document with fonts, color, formulas, and multiple worksheets. The code is part of an open source project at http://sourceforge.net/projects/sqr2xml, which also has a solution for producing Excel documents from SQR.
How it works: PeopleSoft has a function called iScript (starting in Tools 8.1) that allows a developer to launch a new web window and build the HTML. It is a great tool to build a quick report (Purchase Order or Voucher Coversheet). Instead of creating HTML, I built XML data that Excel expects. Once the new window opens, it sees the XML schema in the file and will launch Excel.
Any developer can code the XML, just like the HTML, using iScript. But to simplify the process, I created a library of functions the developer can call to write the XML. By using the library (which is in PeopleCode as well), the developer doesn't have to write XML or even understand what XML is. All the XML writing is done by the library of functions.
I also wrote a manual that accompanies the free code. In the manual, you'll find more info on how this works, as well as all the functions you can call and their syntax. Some of the features are:
- Cell Styles (font, color, number format, alignment)
- Page formatting (margins, header/footer)
- Row styles (hidden, height)
- Column styles (hidden, width)
- Cell types (character, number, dates, etc)
- Formulas (simple to complex)
- Multiple Worksheets
- Page Breaks and Auto Filter
This code is already in use in several companies and universities in America and abroad, including the University of Houston (my current client). My hope is that the code can help customers get more out of PeopleSoft as they wait for Fusion. Feel free to write me if you want more information or want to share your successes.
Code location: http://sourceforge.net/projects/sqr2xml
Enjoy
David Vandiver
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
VandiverHouse Consulting Inc.
|
|
Last Updated ( Friday, 31 March 2006 )
|
|
|
