This post is intended to be a resource for myself and others with links and examples of BI Publisher template coding. Like the LISTAGG, Excel Formula, and PS Query Expression posts I intend on continuing to update this post as I encounter useful examples.

Links
Report Designer’s Guide for BI Publisher
Oracle Doc for Creating an RT Template
PeopleBooks on Creating Report Definitions

Example code

<?if:number(TOT_HOUS_ON)>0?> (compare a field to a number)

<?if@inlines: number(ISIR_TXN_NBR)>0 and fld_SCC_LETTER_CD=’FVE’?>  (example with AND to check multiple things, @inlines suppresses NEWLINE so that you don’t get blank lines if the test fails)

<?if:number(SCCquery_BG_FA_SOAR_DATA_AWARDS/@numrows)>0?>    (check to see if an additional data query returned any rows)

<?xdoxslt:set_variable($_XDOCTX,’COUNT’,0)?>  (create a variable called COUNT that can be updated and checked)

<?xdoxslt:set_variable($_XDOCTX,’COUNT’,xdoxslt:get_variable($_XDOCTX,’COUNT’) + ISPLUS)?>  (update the variable COUNT with the sum of the current value and the field ISPLUS)

<?if:xdoxslt:get_variable($_XDOCTX,’COUNT’)>0?>  (check the value of the created variable COUNT)

<?if:substring(FIELDNAME,1,1)!= C?> (Check the first character of a field – note “!=” means does not equal)

<?xdofx:TUITION+472.55+ROOMAMT+PLANAMT?>  (doing math with field values and static numbers)

<?html2fo:D.DESCRLONG?> (the field D.DESCRLONG contains HTML tags, this will display formatted by the tags instead of the HTML code)

<?xdofx:sysdate()?> – For System Date/time (select format in box) 

This post is going to be a super high-level overview of how to do conditional text in a COMGEN e-mail template.  Admittedly I am mostly writing this for myself as a reference for the next time I go to do one of these. I am likely the most regular visitor to this blog as I try to remember exactly how I’ve done things in the past.

What is needed

1. All the usual setup for a communication (letter code, context, category, etc)
2. A query that will pull the data you want used in the communication and to check for the conditional text areas
3. A Communication Data Source
4. The BI Publisher Design Helper to create the template
5. A template to use with the Report Definition
6. A Report Definition

Left-Outer Expression Trickery

We want this communication to say one thing if the student has a campus e-mail address (exists) and something else if they do not (not exist). So we are passing in a flag in a field to indicate this. To accomplish this we are doing a left-outer join to the e-mail address table, then setup an expression to output a “Y” if they have an address and a “N” if they do not.  Here is what that expression looks like:

Click the “Use as Field” link to add this to the output fields then click “Edit” on the Fields tab and give it a user friendly name in the Unique Field Name box.

Query Prompts

In order to use any query with a BI Publisher Data Source it needs to include the correct prompts.  This is how the BI Publisher process passes values to the query to pull the correct data.  This is described in the PeopleBooks article “Setting Up the Communication Generation Process” if you need more details than what I’ll put here.

All additional data queries must include a prompt for Person ID or Org ID.  Other prompts will depend on which Admin Function your communication is for.  I am using FINA, and since the variable data required for FINA is Aid Year, I have to include a prompt for Aid Year.  To do this, you create prompts in the query and change the “*Unique Prompt Name” in the prompt properties.  For Person ID it has to be exactly “PERSON_ID” and for Aid Year it is “AID_YEAR”.  Put any additional criteria in the query and select any fields that you will want to output or use as criteria in the conditional text checks.

Add the Query to the Data Source

The data source for this communication (BI Publisher Report Definition) will need to be an XMLDoc Object, which means you’ll need to create it from Main Menu > Campus Community > Communications > Set up Communications > Communication Data Source.  Select your Administrative Function and in the Custom Extract Data section select PS Query and enter the name of your query.  Register the data source, then click the “View/Download Sample Data File” link and save the XML file for use in creating the template.

Creating the Template

If you haven’t already, install the Design Helper found at Main Menu > Reporting Tools > BI Publisher > Setup.

Then open MS Word and hopefully you have a BI Publisher menu tab at the top. Click the “Sample XML” button and open the XML file saved from the Data Source screen. Now start typing the body of your e-mail.

Dynamic Values from Query

To insert a value from the query, select the “Field” button. WARNING: Don’t click the “All Fields” button!  It will add all available fields from your XML file to the template, it’s a mess and it takes a while.  The Field box that opens up will contain the fields available to insert. Some fields are there with any data source, some a specific to your admin function, and at the bottom will be the fields from your query.  Select the field and click Insert.

Conditional Text Areas

To setup areas on the template that will output different text depending on values in the data source, you will use the Conditional Region button.  Position your cursor where you want the conditional text and click the Conditional Region button. In the Data field drop down select the field you want to check, specify Number or Date/Text, select an operator (=, >, <), then specify a constant or field to compare against.  Then click the Advanced tab.  You will see that it has built the Code for you.  Now enter some text to display, otherwise it will just show the letter “C” and if you have multiple conditional areas it will get confusing.

Here is an example where I want to output a sentence if the student is a new admit:

The template will now have two blocks with grey backgrounds, Is-New and EC.  The EC is for End Conditional (region). Any text and/or dynamic fields between those blocks will only be output if the condition is met.

Once you are done with the template, save it and upload it to the Report Definition.  The preview from there isn’t very helpful with dynamic data and the conditional regions.  I found the best way to test is to assign the communication to a group that will represent the possible combinations for the conditions then use the “Online Preview” within COMGEN to have the emails generated and sent to you.

Good Luck!

TLDR;

Use LISTAGG(CONCAT(D.DESCR, CONCAT(‘ $’,C.OFFER_AMOUNT)), ‘ – ‘) WITHIN GROUP (ORDER BY B.EMPLID, C.PKG_SEQ_NBR)

To make this:

Look like this:

The Details

The LISTAGG function can be used in an expression to combine multiple rows of data.  The field(s) in the LISTAGG are grouped and then ordered by student.  A more simple version would be:

LISTAGG(D.DESCR,  ‘ – ‘) WITHIN GROUP (ORDER BY B.EMPLID)

This would give you a field with all of the awards separated by a space, dash, and space.  “Scholarship – Grant – Loan”

You can combine the LISTAGG function with the CONCAT function to combine multiple fields (Description and Amount) and/or fields and strings (Amount and “$”).

You can also specify multiple fields in the ORDER BY  to sort on more than one field. In the top example it is sorting by student and then within the student sorting the awards in the order they appear on the Assign Awards to a Student screen.

LISTAGG is (hopefully obviously) an aggregate function, so be sure to check the Aggregate Function checkbox on the “Edit Expression Properties” box and specify a length that will be enough to accommodate everything after combining.

I hope you find this useful. I think it’s really cool and makes for some much nicer looking output.

* Edit *

I’ve created a post just for LISTAGG Examples.

This is just a public service announcement to save network traffic and file storage space.

When you run a query to Excel from PeopleSoft it will initially be in an Excel 2010 .xls file format.  The query results that I just download had 39,181 rows and 10 columns of data.  As an xls file it had a file size of 6,733 KB.  Without making any other changes I saved the file as an Excel 2013 .xlsx file and it reduced the file size to 1,756 KB.  That is almost 75% smaller.

Thank you for your attention.  You may now return to your memes and cat videos.

I’ve been meaning to write a blog post about unions in PS query for some time now, but I didn’t have a good example to work with until today.  So the scenario is that you want to list out all of the items from record (table) A and the count of their occurrences in record B.  As long as each entry in record A has at least one match in record B that’s fine.  You would just use a standard join and put the COUNT aggregate on a field in record B.  But what to do when there are no matching rows in record B and you don’t want to lose any items from A?

The first idea is to just use a left outer join from A to B and count a field in B.  That works mostly fine except you will get a count of 1 for items that have no rows in record B.  To mitigate the confusion this would cause you can output an additional field from the B record.  That way the additional field from B will only display with the items that have at least one row in B and the others with a count of 1 you will know are really 0.  Given the example of counting the occurrences in STDNT_AWARDS (B) of the item types in ITEM_TYPE_FA (A):

Item    Descr          Count   Year
1234   Pell Grant   1,234   2014
2345  ABC Grant            1
3456  Fed Loan     2,345  2014

You can see that no details from the A  record are lost and the “ABC Grant” has no matching B rows because the year field is blank.

Or you could use a Union

I think a cleaner solution would be to use a Union.  You would create the query first using a standard join and count.  That would result in output such as:

Item    Descr          Count
1234   Pell Grant   1,234
3456  Fed Loan     2,345

Then you would add a Union to the query.  This is done using the “New Union” link at the bottom of the Query Manager page (left of the Return to Search button).  It will look like a new query has been started, but it will say “Working on selection Union 1” at the top.  Select your A record and choose the same fields in the same order as the original query.  * An important point is that the union must have the same number of fields of the same type and in the same order as the main query or you will get an error *  Then create an Expression with a type of number and in the Expression Text box just enter the number 0.  Use this Expression as a field.  Finally add a Does not Exist subquery to the union to only select A rows that have no match in the B record.  Then your output will look like this:

Item    Descr          Count
1234   Pell Grant   1,234
2345  ABC Grant            0
3456  Fed Loan     2,345

*Note that the query will use the field names and sorting specified in the Top Level Query.

That should do it.  Hopefully knowing how to use Unions in PS Query will help you write better queries.

*Update* Do note that adding a Union to your query has the effect of checking the “Distinct” box, you won’t get duplicate output.

I just submitted my first session proposal for a conference!  I submitted a session called “Fast SAP processing with jobsets, 3Cs, service indicators, online appeals, and web services” for the Alliance13 conference this March.  I will be attending the conference regardless of if I am presenting or not.  This will be my second Alliance, having attended the 2011 conference in Denver.  Wish me luck!

I’m working on a project to (pop) update a field in PeopleSoft based on a file from an outside source.  The goofy part is that the file does not have a student ID (not surprising), and the first 5 characters of the SSN are masked (more surprising).  So then how do I do a one-to-one match in order to do the update?  What I’ve come up with had a couple of Excel and PS Query tips that I thought were worth sharing.  The first thing I did was create some new columns in the Excel file that is the source data.  To get just the 4 “good” characters of the SSN, I used the RIGHT function.

Equation:  =RIGHT(B4,4)
Translation:  Give me the last 4 characters from the string (text) in cell B4
Example:  ***-**-1234  = 1234

The name in the source data is listed in this format, Last (comma) (space) first (space) middle initial.  To get just the last name from that string I first used the FIND function to get which character position the “,” is in.

Equation:  =FIND(“,”,C4,1)
Translation:  This looks weird because it is a comma that I’m looking for.  The syntax is FIND then what you are looking for in quotes, what cell to look in, and what character position to start looking from.  So this equation says find the comma in cell C4 and start looking at the first character.
Example:  Student, John A = 8  (the comma is the 8th character in the string (name))

Then I use the LEFT function to get the last name.

Equation:  =LEFT(C4,G4-1)
Translation:  In this example G4 has the results of the Find function.  So this says give me the beginning (Left) characters of the string in cell C4 and stop one character before the number G4 (G4 -1).
Example:  G4 = 8 (see above)  Student, John A = Student (left 7 (8-1) characters in the name)

Next I wanted to use the first three characters in the first name.  I only wanted the first three because of the variability of how first names are recorded.  I used the MID function for this.

Equation:  =MID(C4,G4+2,3)
Translation:   MID is like LEFT and RIGHT except it lets you specify a starting point and an ending point.  This equations says: starting two characters after the comma (remember G4 holds the position of the comma and a space follows the comma, G4+2) give me the next three characters.
Example:  G4 = 8  Student, John A = Joh (three characters starting after the comma and the space)

So, those are fields that I am going to match on in PeopleSoft; the last four of the SSN, the last name, and the first three letters of the first name.

We (thankfully) have a process that allows us to upload a file to PeopleSoft to use in a query.  I uploaded this input file and used some criteria with expressions to do the matching from the file to the PEOPLE_SRCH record to get the student ID.  The last name field is a simple EQUAL TO criteria then I compare the last four of the SSN in the input file to the expression SUBSTR(B.NATIONAL_ID,-4).  The function SUBSTR typically works like the LEFT function in Excel where you specify a field and a number to get the first given number of characters in that text.  However (HOT TIP) if you use a negative number, as I did, it will work like the RIGHT function in Excel and give you the last given number of characters in that text.

For the first name comparison I used the expression SUBSTR(B.FIRST_NAME_SRCH,1,3).  When used with two number parameters SUBSTR works like Excel’s MID, so this expression gets the first (because the first number parameter is 1) three characters of the first name.

I double checked the results and using these three criteria I am able to get the student ID for those in the input file.  While you may never have this same exact situation, hopefully you’ll find parts of this post useful in other projects.

Cheers,
John

Around two months ago a Firefox update caused the browser to stop working well with PeopleSoft.  Sadly the same thing had happened with Chrome not long before that.  IE and Safari both appear to still work well with their current versions.  If you, like me, would like to continue to use Firefox there is a work-around.  You can download Firefox version 3.6 from portableapps.com and use it with PeopleSoft.  Since this is not a current version I would not recommend using the browser with external web sites.  This “portable” 3.6 version will not harm your “current” Firefox version, but they cannot be running at the same time.  I use Firefox 3.6 as my primary PeopleSoft browser, IE as my secondary browser (when logged into two environments at the same time), and Chrome for external sites.

** I just checked the new Firefox 12 and it is the same story of super slow page loads.  A random update broke it and I keep hoping a random update will fix it.

** Update 2:  Make sure you turn off auto-updates in your 3.6 version in order to stay at that version and not get bugged about updates.

** Update 3:  This issue has been fixed for us.  Thank you Casey!  I’m told the issue had to do with SSL ciphers.  PeopleSoft is now working with the current versions of Firefox and Chrome.

Are you tired of .log files that look like this:

Do you want .log files that look like this:

You just need to change your default program for .log files from Notepad to WordPad.  Here are some links with instructions:

Windows XP

Windows 7

You likely already know that you can use an Aggregate like SUM to reduce multiple query results rows to a single row.  But did you ever think about using it with a couple of functions to display multiple values in a single row?

Examples

A simple example of a SUM to reduce rows, would be summing the offer amounts of all of a student’s awards to display their total offers.

A more advanced example using SUM with functions would be to display the Summer, Fall, and Spring semester amounts on a single row.  For this example I will be using the STNDT_DISB_AWD record.  For you non-Financial Aid people I will clarify that in this record the award amounts are listed by term, so without any aggregate function the amounts would be shown on separate rows.  The next step will be to create three functions, one for each term:

*note: ‘2115’, ‘2118’, and ‘2122’ are our terms for Summer, Fall, and Spring

Use those functions as fields and select the SUM aggregate for them.

Without the functions and aggregate the data could have looked like this:

EMPLID             TERM    OFFER
0012345678   2115      1000.00
0012345678   2118      2000.00
0012345678   2122      2000.00

With the functions and aggregates the data could look like this:

EMPLID             Summer Offer   Fall Offer   Spring Offer
0012345678    1000.00             2000.00     2000.00

I hope you find this useful.

John