Geeks on Campus

anything, everything, geeky.

Design to Deliver

Posted by jeggent on January 8, 2021

Some years ago I started to get concerned about something. My group was becoming responsible for an increasing amount of regular maintenance as we automated more and more processes. I decided that we needed to deliver solutions in a way that empowered the end users and minimized the ongoing involvement of the technical staff. So I came up with an outline/template laying out the items to address to accomplish these goals. I called it Design to Deliver. Having this outline also helps to establish the scope of a project to make sure that everything is included and to correctly estimate a project timeline. The components of Design to Deliver are:

  • Security
  • Flexibility
  • Usability
  • Documentation
  • Training

Security
Does the project have any security requirement? Do all of the users have access to the pages and items included in the solution? Getting security changed can often take some time. It is important to include security changes to have an accurate timeline.

Flexibility
Is the solution flexible enough to handle minor variations without needing rework? For example is the end user able to specify which aid year or term is to be processed? If codes are known to change over time is the selection logic written to account for that, such as using using LIKE or BETWEEN instead of EQUAL or IN?

Usability
Is the design user friendly given the skill sets of the end users? Solutions that include steps that are too complex for the end user will likely continue to require technical support. Good usability also includes being compatible with the users current process. For example, will the next item appear on a screen that is already being used often or within a current workflow? Or will the user have to take additional steps and review different screens?

Documentation
I know. I know. Documentation is not fun. However it can be critical in empowering end users to be more self sufficient. Some processes are only done once a year. Sometimes staff change responsibilities. And in these cases having documented the process in detail can make all the difference. A year from now the user may not remember how to complete the process. It’s likely the developer won’t either. Then time will need to be spent re-learning and re-explaining the steps.

Training
Does the project include time and materials for any training that will be required? Training is another item that is too often overlooked but goes a long way to empowering users. Training can take a while to complete depending on the number and locations of the users.

Posted in Random, Resource | Tagged: | No Comments »

BI Publisher Examples

Posted by jeggent on January 5, 2021

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)

Posted in PeopleSoft | No Comments »

LISTAGG Examples

Posted by jeggent on December 9, 2020

I have a post on PS Query Expressions and I was about to add a LISTAGG example to it. But I thought that instead I would create a new post just for LISTAGG examples since there may end up being many of them. The post Combine Multiple Rows and Fields explains LISTAGG.

FA award short description, status, and amount:
LISTAGG(CONCAT(C.DESCRSHORT, CONCAT(‘ (‘,CONCAT(DECODE(B.AWARD_STATUS,’C’,’Cancelled’,’D’,’Declined’,’Accepted’), CONCAT(‘) ‘,B.DISBURSED_AMOUNT)))), ‘ – ‘) WITHIN GROUP (ORDER BY B.ITEM_TYPE)

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

Academic plan description and code:
LISTAGG(CONCAT(B.DESCR,CONCAT(‘ (‘, CONCAT(B.ACAD_PLAN, ‘) ‘))), ‘ / ‘)

Output:
Electromechanical Technology (ET-AASEEET) / Electr-Mechanical Technology (EMECT-AAS)

FA Load by Term:
LISTAGG(CONCAT(G.STRM,CONCAT(‘ (‘,CONCAT(G.FA_LOAD,’)’))), ‘ / ‘) WITHIN GROUP (ORDER BY G.STRM)

Output:
2205 (L) / 2208 (F)

Posted in PS Query | No Comments »

Excel Formulas

Posted by jeggent on August 20, 2020

Flag if one of two strings is found in a cell.

=IF(AND(ISERROR(FIND(“fall 2020”,AB2)),ISERROR(FIND(“Fall 2020″,AB2))),”N”,”Y”)

IFS, Switch, Min
It was an Excel formula before it was a PS Query Expression

=IFS(X3>0,0,W3>0,SWITCH(V3,”NO NEED”,250,”HIGH”,500,”MODERATE”,500,”LOW”,500,”NO FAFSA”,0,0),W3<1,SWITCH(V3,”HIGH”,MIN(1200,S3),”MODERATE”,MIN(1000,S3),”LOW”,MIN(750,S3),”NO NEED”,MIN(500,S3),0)) 

Convert number (Student ID) into text to replace and keep leading zeros
=TEXT(C2,”0000000000″)

Does a cell contain one or more of these given strings
=IF(OR(ISNUMBER(SEARCH(“2021”,D3)),ISNUMBER(SEARCH(“2020”,D3)),ISNUMBER(SEARCH(“2019″,D3))),”Yes”,”No”)

Explained: SEARCH returns a number indicating which character position your given string (“2021”) starts at in the target cell (D3). If the string is not found it returns #N/A. So ISNUMBER is used to return True if SEARCH returns a number (and False if #N/A). Three of the ISNUMBER SEARCH pairs are grouped with the OR so that if any of them is True than True is returned to the IF statement.

Posted in Excel | No Comments »

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

Decode, Substr, To_number and Least

CASE WHEN L.OFFER_BALANCE > 0
THEN 0
WHEN K.OFFER_BALANCE > 0
THEN DECODE(E.VARIABLE_CHAR4,’NO NEED’,LEAST(250,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘HIGH’,LEAST(500,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘MODERATE’,LEAST(500,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘LOW’,LEAST(500,TO_NUMBER(SUBSTR(A.FLD1,12,5))), 0)
ELSE DECODE(E.VARIABLE_CHAR4,’HIGH’,LEAST(1200,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘MODERATE’,LEAST(1000,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘LOW’,LEAST(750,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘NO NEED’,LEAST(500,TO_NUMBER(SUBSTR(A.FLD1,12,5))), 0)
END

Here is an interesting expression that uses a few different functions. If the student has already been awarded in this term (l.offer_balance > 0) then the result is 0. If the student has previously been awarded (k.offer_balance > 0), then a decode is used to evaluate the value in e.variable_char4. If there is no current or prior offer_balance then a different version of the decode statement is used. The decode works like this:

Look in this fieldIf this is the valueThis is the result
DECODE(E.VARIABLE_CHAR4,‘HIGH’,LEAST(1200….

LEAST(1200,TO_NUMBER(SUBSTR(A.FLD1,12,5)))
The least statement returns whichever is lower, the number (ex. 1200) or the result of TO_NUMBER(SUBSTR(A.FLD1,12,5)). The substr statement is pulling 5 characters out of the field A.FLD1 starting at position 12. Then to_number converts that to a number for comparison in the least function.

MOD and FLOOR for a file extract

This is for an odd case where a file layout calls for no fractional digits on whole number amounts. ex. 123.45 = 123.45 : 678.00 = 678

CASE WHEN MOD(A.DISBURSED_BALANCE,1) > 0
THEN TO_CHAR(A.DISBURSED_BALANCE)
ELSE TO_CHAR(FLOOR(A.DISBURSED_BALANCE))
END

Getting Calendar Year from Term Code

CONCAT(’20’,SUBSTR(C.EXP_GRAD_TERM,2,2))

Calculating age at time of application

MONTHS_BETWEEN(B.ADM_APPL_DT, H.BIRTHDATE) / 12

Removing non-number characters from phone number to compare ISIR phone number to Campus Community

CONCAT(SUBSTR(H.PHONE,1,3),CONCAT(SUBSTR(H.PHONE,5,3),SUBSTR(H.PHONE,9,4)))

Posted in PS Query | No Comments »

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 »