Geeks on Campus

Another amazing bgsu blog

PS Query Expressions

Posted by jeggent on June 29, 2020

Get the username from from an e-mail address

UPPER(SUBSTR(A.EMAIL_ADDR,0,(INSTR(A.EMAIL_ADDR,’@’)-1)))
I’m using this to join to the roll name on PSROLEUSER  so I can then get the user’s actual name.

Expression for First Generation (C = ISIR_PARENT)

CASE WHEN (C.FATHER_GRADE_LVL IN (‘ ‘, ‘4’) AND C.MOTHER_GRADE_LVL IN (‘ ‘, ‘4’)) THEN ‘Unknown’
WHEN (C.FATHER_GRADE_LVL IS NULL AND C.MOTHER_GRADE_LVL IS NULL) THEN ‘Unknown’
WHEN (C.FATHER_GRADE_LVL = ‘3’ OR C.MOTHER_GRADE_LVL = ‘3’) THEN ‘No’
ELSE ‘Yes’
END

Posted in PS Query, Uncategorized | Comments Off on PS Query Expressions

Programming Languages

Posted by jeggent on April 7, 2020

For the last couple of weeks I have been learning Java programming. I’m doing this using a course called Java Programming Masterclass for Software Developers on Udemy.com. My first purpose of writing this post is to recommend this course. There is over 80 hours of video. I have been working on it for a couple of weeks and I’m only about a third of the way through. I’m really happy with the quality of the instruction and the platform. Check it out if you think you might be interested (wait for a sale, don’t pay full price).

The second purpose of this post is to document all of the different programming languages I’ve used over the years, mostly just for my own record.

Approx. 1985, 1988 – BASIC
All of the dates on this list are approximate, but this one especially. My first computer was the Radio Shack TRS-80. I would check books out from the library and re-type BASIC programs line-by-line from them. I later (don’t remember which grade) had a “computer” class in school where we did BASIC programming.

1992 – Turbo Pascal
This was a high school class. I was on a team that competed in programming competitions with this language.

1995 & 1996 – Quick Basic, C++ and Visual Basic
Programming classes in college. I did use Quick Basic some in my first IT job.

1998 – “Classic” Active Server Pages(ASP) & HTML
It wasn’t “Classic” back then. I wrote several ASP applications that were used on the company public website and our intranet. I also used ASP to support the web applications developed in our office (BG) when we were between programmers. I guess HTML is technically a mark-up language and not a programming language. But I really wanted to mention some of the HTML editors I used over the years.

Notepad, HoTMetaL, GoLive, CoffeeCup HTML Editor, Frontpage, Dreamweaver, Notepad++ (what I mostly use now). I’m sure there were others.

1999 – Visual Basic for Applications
This seems like an odd thing to include. But I was really proud of some of the things I developed with VBA. I had a really great book on it from Microsoft Press that I wish I still had. I created a purchase order system that would retrieve the next purchase order number from a central dataset so that all purchase orders in the company could use a sequential set of numbers instead of having to assign blocks of numbers to users.

2000 – Delphi
Hey look, it’s the PASCAL language again. I used Delphi Developer to program a Louver Specification Wizard software that American Warming and Ventilating (AWV) distributed to architects and manufacturer representatives to include AWV louvers in building specifications. The user would select a product and respond to a series of questions to generate a 10200 formatted specification on screen. The specification could also be saved to an RTF file.

2001 – Visual Basic .NET
I worked in Visual Basic .NET in collaboration with our corporate office on an online product catalog and order placing system.

2002 – FOCUS
I used FOCUS to write reports on our mainframe student information system. There was a short time where I think I might have been the most advanced FOCUS programmer on campus (after Janice retired and Erik graduated). I took over training new staff on FOCUS programming for some time.

2009 – PHP
I learned PHP because that is what our new web applications were being written in. I did re-write the AWV louver specification writer in PHP and put it online. The most interesting part of that was the code to generate an RTF file download. It also used a MySQL database back-end.

2020 – JAVA
This takes us back to Java.

Others
I’m not sure where to put JavaScript. I wrote some pretty complex web applications using JavaScript for interactivity and dynamic content.

Posted in Programming | Comments Off on Programming Languages

Optional Query Prompts

Posted by jeggent on January 13, 2020

How to make PS Query prompts optional.

I add (Optional) to the heading so that the user knows they do not need to provide a value. There is also a checkbox to indicate that the prompt can be left blank.

Add the criteria for the target field as equal to the prompt as usual, but then also group it with another criteria with an OR statement allowing for the prompt to be Null.

Query Criteria

Posted in PS Query | Comments Off on Optional Query Prompts

Max, Yet Prior To

Posted by jeggent on October 25, 2019

Sometimes you need to find which row of data in a table would have been the latest/greatest/current/effective row as-of a specified date or term.  Describing this in plain language would be something like:

Show me the row with the latest date that is prior to August 17, 2019.
Or
Which row would have been in effect on August 17, 2019?

Here is some example data from the FA Term table:

So, given the question above, we would be looking for the 8/15/19 row.  Of course FA term has delivered MAX Effective Date logic, but that isn’t helpful here and lots of other tables have dates that are not Effective Dates.  Typically when you want to find the MAX date on a non-effective date table you just filter on the date and specify a criteria of equal to Subquery. Then in the subquery you Select the date field and use the Max Aggregate.  But if you try to add a HAVING of Not Greater Than 8/17/19 it won’t return any results. If you try to add that same logic as a criteria at the top level you won’t get any results. So the way that I have found to do this is as follows:

  1. At the top level of the query filter on the date field and set it Equal to Subquery.
  2. In the subquery use the same record as the top level.
  3. Specify whichever criteria are needed to limit the selection the the correct group (ID, Term, ect.)
  4. Include a criteria on the date (or term) field such as Not Greater Than and specify a constant, a prompt, or another field.
  5. Create an expression in the subquery that is the same type and length as the field you are filtering at the top level (ex. date, term).
  6. Select the Aggregate Function checkbox.
  7. The Express Text will be “MAX(field)” where field is the field in the subquery record that mirrors the field being filtered at the top level. So for this example it would be “MAX(B.EFFDT)”.
  8. Select “Use as Field” for this expression.

The query should now return just the row of data that you are looking for.  Looking at the SQL generated by the initial attempt using the standard Select MAX and the revised query using an expression, the only difference is the final (outer) right parenthesis on the A.EFFDT subquery moves down one line to also encompass the Not Greater Than criteria.

I am 100% sure there are other ways to accomplish this.  I’m pretty sure that I had gotten this to work in the past using another method. But this is what I got to work now and I wanted to document it for the future (and others).

All the best. I hope this helps someone.

Cheers, John @ BGSU

 

Posted in PS Query | Comments Off on Max, Yet Prior To

Blog Comments

Posted by jeggent on July 18, 2019

I don’t at all understand how blog comment spam works, but sometimes it’s funny.  Every once in a while an old post on here will get a bunch of spam comments. Right now that post is Drilling URL.  That post is from 2011! Why/how is it being targeted now for comment spam? You all will never see those comments because I have my blog set to not post any comments that are not approved by me. Just for fun I’m going to post some snips from the recent group of spam comments.

  • I have been surfing online more than three hours today, yet I never found any interesting article like yours.
    It’s pretty worth enough for me. In my opinion, if all website owners and bloggers made good content as you did, the net will be a lot more useful than ever before.
  • hello!,I like your writing so a lot! proportion we communicate more approximately your article on AOL?
    I need an expert on this area to resolve my problem.
    Maybe that’s you! Having a look ahead to peer you.  ** AOL huh?  Cool. **
  • We would like to thank you all over again for the beautiful ideas you offered Jeremy when preparing a post-graduate research and, most importantly, with regard to providing the many ideas in one blog post. In case we had been aware of your web site a year ago, we would have been saved the useless measures we were taking.  ** Who’s Jeremy? **
  • Hello.This article was extremely interesting, particularly since I was investigating for thoughts on this subject last Saturday.  ** Really, last Saturday you were looking for drilling URL thoughts? **
  • Good article over again! I am looking forward for your next post.  ** Good news, this post is 8 years old. There have been others since. **
  • Merely to follow up on the up-date of this issue on your web page and would want to let you know how much I valued the time you took to write this beneficial post.
    In the post, you spoke on how to definitely handle this concern with all comfort.
    It would be my own pleasure to accumulate some more thoughts from your blog and come as much as offer some others what I learned from you.
    Thanks for your usual great effort.  ** I did indeed speak on how to definitely handle this concern with all comfort. Thanks for noticing. **
  • ** So very many comments about prescription drugs **
  • …. appreciate the knowledge you shared with others, the content is lit …..  ** lit 🙂 **
  • We Samish Leather assisting our customers since a decade, providing them the best of the best product in cheap rates, do not waste your time, and visit our web store. ** saved by the comma **
  • … I care for such information much. I was seeking this particular information for a very long time. ** 8 years is a long time **

Thanks for your usual great effort.  Cheers. John

Posted in Random | Comments Off on Blog Comments

Jump N Buddies

Posted by jeggent on January 30, 2019

I just realized that I never posted anything about the video game I made, Jump ‘n Buddies.

I say “made” because it’s online and technically playable, but I certainly wouldn’t call it complete.  It’s been a work-in-progress hobby for several years.  I’m using the software Stencyl.  It’s pretty easy to use, but it doesn’t have near the community of something like Unity. Because of that there aren’t nearly as many resources available.  So it’s a trade-off; fewer tutorials but easier to get started with.  Check it out if you’d like and have a browser you can trick into running Flash.

Have fun.

Posted in Fun | 1 Comment »

Five Years Later: Drawdio

Posted by jeggent on January 28, 2019

Well, it only took about 5 years from the initial post. But I finally built the Drawdio.

As mentioned in another post, I had trouble finding a couple of components.  Radio Shack closed their stores and I gave up for a few years.  Then I started playing  around with the Ardiuno Mega 2560 I got for Christmas and my interest was renewed in completing the Drawdio.  My Drawdio looks quite a bit different than the one from the link above.  I did not want to have to solder the components. I don’t have good soldering equipment and I’m way out of practice.  So I found these mini breadboards on Amazon and it worked great.  I also got the last couple of components on Amazon. FYI a 300K Ohm resistor works just fine in place of the 270K from the spec.  🙂

I wonder what other projects from the past I could dig up and finish…..

Posted in Fun, Random | Comments Off on Five Years Later: Drawdio

Conditional Text in COMGEN E-mails

Posted by jeggent on November 26, 2018

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!

Posted in PeopleSoft, PS Query | Comments Off on Conditional Text in COMGEN E-mails

Combine Multiple Rows and Fields

Posted by jeggent on June 28, 2018

TLDR;

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

To make this:

Student Term Award Amount
A. Person Fall 18 BG Scholarship 1000
A. Person Fall 18 Federal Grant 2000
A. Person Fall 18 Federal Loan 3500
B. Clever Fall 18 BG Scholarship 1000
B. Clever Fall 18 Outside Scholarship 2500

 

Look like this:

Student Term Awards
A. Person Fall 18 BG Scholarship $1000 – Federal Grant $2000 – Federal Loan $3500
B. Clever Fall 18 BG Scholarship $1000 – Outside Scholarship $2500

 

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 revisit this post quite often to copy the syntax above. Here is another example that I just used in a report. I’m posting this mostly for my own future reference, but maybe someone else will get some use out of it.

Expression:
LISTAGG(CONCAT(C.DESCRSHORT, CONCAT(‘ (‘,CONCAT(DECODE(B.AWARD_STATUS,’C’,’Cancelled’,’D’,’Declined’,’Accepted’), CONCAT(‘) ‘,B.DISBURSED_AMOUNT)))), ‘ – ‘) WITHIN GROUP (ORDER BY B.EMPLID, B.ITEM_TYPE)

Output:
SubLn (Accepted) 1732 – UnsubLn (Accepted) 990 – UnsubLn8 (Accepted) 1979 – Plus Ln (Cancelled) 0

Posted in PeopleSoft, PS Query | Comments Off on Combine Multiple Rows and Fields

Filters

Posted by jeggent on September 5, 2017

This is part of the Tuesday Excel Tips series.

Check out the listing of all the Tuesday Excel Tips.

A quick way to organize a data table in Excel is with Filters.  You can use filters to hide the rows of data that do not match the parameters you specify.  These parameters could be lists of values in one or multiple columns or greater-than less-than or ranges for number values.  You can also use filters for sorting.  To get started, select the column headers that you want included in the data table.  Click the Filter icon on the Home or Data menus.  Click the new icon next to the column header to see your options.  Once you have filtered a table you will see the icon change to a filter and the status bar at the bottom will show how many matches there were.  The example below shows “4 of 7 records found”.  You can re-select all of the items or click the filter clear icon to un-filter the list.

Filter

Posted in Excel | Comments Off on Filters