Geeks on Campus

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

Microsoft Excel Tips and Tricks

I’ve created this document (link above) containing some of my favorite Microsoft Excel tips and tricks.  I’m going to give a staff training on Excel and will demonstrate these items and others.  I thought that I would share this document here for everyone’s reference.  The topics covered are:

• Format Painter
• Adjust Column Width
• Copy Down Series/Formulas
• Filter and Sort
• Selecting Cells
• Add Rows/Columns
• Highlight Cells for Quick Info
• Using Formulas

Today I am using my new Windows 7 PC that ITS brought over Friday. I just wanted to let you all know of an issue that I’ve experienced with the setup as delivered and the solution that I’ve found. Firefox was included which is good as that is my primary web browser. However, the menus were flickering and very difficult to use. I did some web searching and found the solution at this link: http://lifeonubuntu.com/firefox-dropdown-menus-are-flickering-on-2nd-monitor/. Spoiler alert, the answer is to turn off hardware acceleration.  This has completely solved the problem for me and does not appear to be effecting the performance at all.

Before I begin the long process of creating the next aid year in PeopleSoft, lets talk about something more fun.  Image editing and creating!  Whether or not you have experience with the software package Photoshop you are likely familiar with the name.  Photoshop or Photoshopped or just shopped, has come to mean a picture that has been digitally altered.  Photoshop and programs like it can be used to create digital images or touch up images such as digital photographs.  I needed to create a logo for a web site recently and I turned to my favorite Photoshop alternative Paint.NET.  The last I checked, Photoshop costs hundreds of dollars to purchase and Paint.NET is completely free.  Paint.NET has many of the features of Photoshop and those lacking can often be added to the program through plugins.  For example, I needed to have text arch around a circular image.  A quick Google search identified a Paint.NET plugin called Circle Text which does just that.  I find Paint.NET easy enough to use and recommend checking it out if you are in need of this type of program.  Good Luck!

**Update**
Well, check it out.  The day after I post this, one of my favorite web sites (lifehacker) posts about the same topic.  The Best Image Editing App for Windows

**Another Update**
Here is one of the logos I created with Paint.Net and the circle text plug in.  I’m happy with how it turned out.  I also did some web site updates with Paint.Net.  You can check out Byrne Road and Adrian Vein Clinics to see the site.  Check them out for treatment of spider and/or varicose veins.

Happy New Year to you all!

I hope 2011 treated you well.

Well it’s over.  I’ve graduated.  Hopefully I will be able to stick with my plan of blogging more now that I am not taking classes anymore.  I’m not going to make any grand promises though.  I have a pretty bad track record at staying with blogs.

To kick things back off with this blog I going to talk about the COUNTIF() function in Excel.  This is a very useful function with which you can count of the number of occurrences of something in a range. For example the formula =COUNTIF(A1:A20,B2) will tell you how many of cells in the range of A1 to A20 are equal to the cell B2.  The problem for me has been the way the function is presented by the function wizard (or whatever it’s called) in Excel.  The wizard makes it easy to select the range to check and specify a criteria cell, however it doesn’t give you the syntax to use inequalities (i.e. > or <).

Recently I was working with a list which included students’ enrollment in the upcoming Spring term and wanted a count of how many were enrolled.  In other words, how many had enrollment greater than zero.  The formula for this value is =COUNTIF(A1:A20,”>0″). The secret here is the quote marks around the inequality.  This formula shows how many cells in the range A1 to A20 have values greater than zero.

I hope this helps.

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

As expected, the previous semester was very busy.  The next semester will likely be even busier.  However it will also be my last semester.  Assuming I am able to complete the two classes, I will graduate this December.

Today I have used the DECODE function for the first time in a PeopleSoft query.  I am not sure which databases this function works with, we are on an Oracle DB.  Decode allows you to display a specific value based on the contents of a field.  For example, if a field contained integers you could output the corresponding letter of the alphabet.  The syntax of the function is like:

DECODE(Field, Value 1, Output 1, Value 2, Output 2, Else)

Alphabet example:

DECODE(A.Field, 1, ‘A’, 2, ‘B’, 3, ‘C’, 4, ‘D’, …., 36, ‘Z’, ‘other’)

I am using this function in a report for repackaging aid.  I am “decoding” the currently packaged item type to the item type it needs to be replaced with.  If there are multiple values that translate to the same output then you may want to consider a CASE statement with an IN list.  Maybe I’ll go over that next.  Hopefully before Fall semester starts 🙂

Good Luck.

John

Cool New Tool: Drilling URL

In starting loan origination for 2011-2012, I wanted to know what status all of the loans were left in after running the process.  So I created a really quick query to just count the IDs by loan status for the aid year.  When I saw that some loans were in a “Hold” status I wanted to then get the listing of those IDs so that I could go and look at those accounts.  This reminded me of the drilling URL example from the Maximizing the use of PS Query for Financial Aid session from the Alliance 2011 conference.  After setting up the Drilling URL, I am able to just click on the loan status and see a listing of all the loans in that status.  There are lots of examples that I can think of that this technique would be useful for.  A department could show the count of students in their classes for a semester and click the class name to show a roster of students in the class.  I will now step you through setting up a query with a Drilling URL.

A drilling URL actually connects two different queries.  So the first step is to create what I will refer to as the detail query, the query with the specific listing that will be reached by clicking the link in the top level query.  In my example this is the BG_FA_LOAN_STATUS_DETAIL query.

For my detail query I am just going to output a few fields from the LOAN_ORIG_DTL record along with the student’s name.  The first important step is to create the Prompts in this query that will be part of the linking process from the top level query.  I want to list the details for all of the loans in a given status for a given aid year, so my two prompts will be for Aid_Year and LOAN_PROC_STAT.

Of course these prompts must be in the Criteria of the query in order to be used.

Next, save this query and begin a new query.  My top level query is called BG_FA_LOAN_STATUS.  It will display a count of records in each loan status by aid year (prompting for aid year).  You’ll see that I have a “Count” on A.EMPLID and I am displaying LOAN_PROC_STAT twice.  This is because I want to display the long translate of the field for the user (note the ‘L” in XLAT) but I also need the one character code to use in the prompt of the detail query.  To do this I just created an expression that is simply A.LOAN_PROC_STAT, displayed that expression and used the translate on the actual field.  The aid year is also displayed and will be used for the detail prompt.

Now you need to setup the Drilling URL.  In “Expressions”, click the Add Expression button and select “Drilling URL” from the Expression Type drop-down.  (the expression text will get filled in for you after completing the following steps).

There are different Drilling URLs that you can use, but we want a Query URL, so click the Query URL link.  You can now search for the detail query that you will be linking to.  It will bring in the prompts from that query (URL Keys) and you choose the fields from your query to populate those prompts.  You also choose (in Map URL to Query Columns) which value or values to turn into clickable links, I am only using the translate of the LOAN_PROC_STAT as the hyperlink, but any or all of the fields can be used to the same effect.

That’s it!  Now when you run this query the Status field will be a link that you can click to get the detailed listing.  Let me know if you try this and have any questions.  Enjoy!

I titled this post Grad School because that is why I haven’t been posting.  I’m pretty sure the next 8 weeks are going to be pretty bad too, but I should have a bit of a break after that.  Since the last posting we did get our award letters out on time, hurray!  I’ve also done some kind of interesting things with Pop Update, particularly with having terms building new budgets when an ISIR comes in after a budget already exists.  We’ve also started making much more use of External Award, including “Process SF External Award” and “Process External Award File”.  Along with External Awards, we have begun using the new Repackaging functionality.  Finally, we are now sending e-mails out over night in a jobset using 3C engine and ComGen.  It wasn’t nearly as difficult as I was afraid and wish we would have started it sooner.  When I next come back to post I’ll likely break-down one of these processes and share our setup and implementation.

Cheers,

John

Not that I’m ever the poster boy for reliable blog posting, but I’m probably not going to be posting much until award notification go out in mid-March.  I just wanted to give that warning so you don’t think the blog has been abandoned if there are a couple of weeks without an update.  I do have one little PS Query trick for you today though.  In creating a new Pop Select query for aid year activation I wanted to check for an award offer in the prior aid year.  Sure I could have just prompted for the prior aid year and used that, but I was already prompting for the new aid year and didn’t think both should have to be entered.  The solution was to use the following criteria in an “Exists” subquery:

Best of luck!