Geeks on Campus

As I am not sure how long BGSU will be kind enough to host this blog, the contents have been migrated to a new site https://geeksoncampus.wordpress.com/.

New posts will only be made at the new link. Sorry for the trouble.

All the best.

John

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!

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.

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.

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) 

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)

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.

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 field	If this is the value	This 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).

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.

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.