Geeks on Campus

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!