Geeks on Campus

Another amazing bgsu blog

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 | 3 Comments »

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 | 2 Comments »

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 | No Comments »

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 | No Comments »

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 | No Comments »

Removing Duplicates

Posted by jeggent on August 29, 2017

This is part of the Tuesday Excel Tips series.

Check out the listing of all the Tuesday Excel Tips.

You may at times have duplicate values depending on the level of detail in your data.  For example, if you have class enrollments listed, you will have multiple rows for each student within each term.  That is an obvious situation.  But there may be other times where it is not obvious that you would have duplication, such as a student having multiple careers in the same year.  Excel delivers ways to quickly identify duplicate values and eliminate them if needed.

Identify Duplicates

  1. Select a column or group of cells that you wish to identify duplicates among.
  2. Click the Conditional Formatting button on the Home tab.
  3. Select Highlight Cell Rules and click Duplicates Values…
  4. Choose the color you prefer in the box that pops up and click ok.

IdentifyDuplicates

Remove Duplicates

  1. Select column with duplicates values you want to be removed.
  2. Click Remove Duplicates from the Data tab.
  3. You get options to expand the selection and choose other columns or just remove values
    from the selected column. Hit ok when you’ve made your choice

RemoveDuplicates

Posted in Excel | No Comments »

Quick Formulas

Posted by jeggent on August 22, 2017

quickThis is part of Tuesday Excel Tips series.

You probably already know that the real power of Excel is in using formulas.  But you may not know that Excel comes with a few handy formulas already defined to make them easy for you to use.

If you select the cell below or to the right of a series of number, you can use the quick formula button to get the Sum, Average, Count, Max, or Min of the series.  If you just click the Sigma button it will Sum the numbers.  If you click the little down arrow you can select one of the other functions.

You can also do like the example below and select a data series and then use the quick formula.

Check out the listing of all the Tuesday Excel Tips.

 

QuickFormulas

Posted in Excel | No Comments »

Quick Info

Posted by jeggent on August 15, 2017

This is part of Tuesday Excel Tips series.

It’s fitting that I only have a small amount of time today to do this post.  Because this tip is all about getting information quickly.  If you highlight/select a group of cell in Excel it will give you some information about the data in those cell in the status area at the bottom of the window.  If the values are numeric Excel will show you the average, count, and sum of the values.  If the values are non-numeric then it will show you a count.  This is very hand if you just need to get one of those figures quickly and don’t need to keep the calculation on the sheet with the rest of the data.

Check out the listing of all the Tuesday Excel Tips.

quickinfo

Posted in Excel | 1 Comment »

Copy Down

Posted by jeggent on August 8, 2017

This is part of the Tuesday Excel Tips series.

If your spreadsheet needs some kind of list or series of labels such as a count, days of the week, days, months, etc. Excel can help you out with that.  As shown in the example below, a count is being entered into the first column.  We only need to enter the first couple of values so that Excel can determine that we increment the value by 1 each time.  Select those values and then place your mouse pointer on the box in the lower right corner of the selection.  Drag the cursor down to the last row you’d like filled and Excel will fill in the values for you.  If you already have data populated in the adjoining rows, you can just double-click that box and it will fill in the series down to your last row of data.  I think you will be pleasantly surprised how often Excel will figure out what values you’d like filled in.  This also works with formulas.  If in cell A1 you have the formula “=B1+C1” you can copy that down to the following rows and Excel will automatically update the references to B2+C2, B3+C3, and so on.

Check out the listing of all the Tuesday Excel Tips.

CopyDownSeries

Posted in Excel | No Comments »

Column Width

Posted by jeggent on August 1, 2017

This is part of the Tuesday Excel Tips series.

Adjusting Column Widths – When you first open Excel all of the columns are the same width.  If the entries in a column are longer than the column is wide you may want to adjust the column width to fit.  Or if you’re like me and have lots of columns, you may want to make some columns small to get more on the screen.

There are LOTS of ways to adjust the width of a column or a group of columns, here are some of them:

  1. Put your mouse pointer between the columns and drag the divider to the right or left.
  2. Double click the divider to automatically widen the column enough to fit your longest entry.
  3. Right click on a column header, choose column width, and specify a value.
  4. Click the upper left most corner (above 1 and left of A) to select the entire worksheet, then double click any column divider to adjust all columns to their longest entry.

Check out the listing of all the Tuesday Excel Tips.

AdjustColumnWidth

Posted in Excel | 2 Comments »