Geeks on Campus

anything, everything, geeky.

Keyboard Shortcuts

Posted by jeggent on August 5, 2022

Welcome to another blog post which is mostly just me documenting things I want to remember later. Here are a few useful keyboard shortcuts in Excel. I’m maybe a little old-fashioned and like to keep my hands on the keyboard as much as possible, so I like using these keyboard shortcuts instead of right clicking or using a button on the ribbon (menu). I will return to this post to add others as I think of them.

Forgive listing these: Ctrl+C – copy, Ctrl+X – cut, Ctrl+V – paste

Navigation:

  • Home – move to the first column of the current row
  • Ctrl+Home – move to the upper-left corner (A1)
  • End – move to the last column with data of the current row
  • Ctrl_End – you guessed it, last row, last column of data
  • Ctrl+arrow – move the start (left), end (right), top, or bottom of the data
  • Adding Shift to any of these will select the range from the current cell to the destination. Ex. you are on cell G20 and press Ctrl+Home, it will select all the cells from column A to column G in rows 1 – 20

Other Handy Shortcuts

  • F2 – Cell edit. Normally when you start typing it replaces what is in the cell. Press the F2 function key first and you can append/edit what is in the cell without clicking into the formula bar. Use with Home, End, and arrows to move the cursor within the existing contents.
  • Esc – exit without changing. Oops, you hit a key and it’s replacing the contents of a cell, just click Esc to revert the cell contents back.
  • Ctrl+; – enter today’s date

Bonus

  • F2 also works for renaming files in Windows!

Posted in Excel | No Comments »

A better CONCAT?

Posted by jeggent on May 19, 2022

In past posts (example, another, yet another) I’ve demonstrated using the CONCAT function to combine strings/fields. However CONCAT is limited to two arguments and I have often had to string multiple CONCATs together to accomplish a desired output. While the resulting expression looks kind of cool, it isn’t easy to write. I have just found a better way.

Instead of using the CONCAT function, you can just use the concatenation operator “||”. So to get an output like F23OTT (I), what would have been:

CONCAT(A.CHKLST_ITEM_CD, CONCAT( ‘ (‘, CONCAT(A.ITEM_STATUS, ‘)’)))

Can now be:

C.CHKLST_ITEM_CD || ‘ (‘ || C.ITEM_STATUS || ‘)’

That may not look hugely different, but it is much easier to write without all of the additional commas and parentheses.

That’s it. That’s the post. Cheers.

Posted in PS Query | No Comments »

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: | Comments Off on Design to Deliver

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)

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

Posted in PeopleSoft | Comments Off on BI Publisher Examples

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 | Comments Off on LISTAGG Examples

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 | Comments Off on Excel Formulas

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)))

Reformatting a 2020/11/06 date to 11/06/2020
TO_CHAR(TO_DATE(A.DT_APP_RECEIVED),’MM/DD/YYYY’)

Compare a date field to a date last year
I.COMM_DT > TO_DATE(CONCAT(TO_NUMBER(:1)-1,’-02-01′),’YYYY-MM-DD’)

Get the date from a DateTime field
TO_CHAR(CAST((A.COMM_DTTM) AS TIMESTAMP), ‘MM/DD/YY’)
Also check out the EXTRACT function if you just need one element (day, month, etc).

String Theory
CONCAT(SUBSTR(D.FLD4, 1, INSTR(D.FLD4, ‘ ‘)), ‘Meal Plan’)
Using CONCAT, INSTR, and SUBSTR to turn the input string “Silver BOTH swipes & falcon dollars (custom)” to the output string “Silver Meal Plan”.
CONCAT attaches two strings together. SUBSTR is used to extract just the beginning of the input string up to the first space (found by INSTR).

Posted in PS Query | 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. Delete automatic effective date criteria if there is any.
  2. At the top level of the query filter on the date field and set it Equal to Subquery.
  3. In the subquery use the same record as the top level.
  4. Specify whichever criteria are needed to limit the selection the the correct group (ID, Term, ect.)
  5. Include a criteria on the date (or term) field such as Not Greater Than and specify a constant, a prompt, or another field.
  6. 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).
  7. Select the Aggregate Function checkbox.
  8. The Expression 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)”.
  9. 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

Update: If looking for the effective dated row just prior to the Max effective row try this from Michael K. on HEUG.

To get the second highest effective date, add another copy of the same table to the query (this one would be labeled as B) it will auto add the same effective date and effective sequence criteria. Go to update the B.EFFDT criteria to be EFFDT < (not just < ) A.EFFDT. If done right when looking at the sql it should have the max criteria just like the one above but instead of being < SYSDATE it’ll be < A.EFFDT[.

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