Ad Hoc Reports On-The-Fly: A Dream Fulfilled

Author: Hisham Makki, Assistant Director for Data Quality.
Required knowledge: Advanced Alma Analytics; HTML; URL; SQL

Contents:

Did you know that you can run a report on-the-fly, and without the need to save it in your Analytics folder? Let me show you how.

In this blog, I will walk you through the essential steps of creating a simple report by just passing some URL parameters. The parameters we use in this blog are SQL, format, options, and style.

Go (URL Command)

The Go URL command can also be used to pass context, such as filters, to a destination request by adding additional parameters to the call. For example, you can use it to issue SQL commands and return tabular results. Here are some instructions on how to use some GO parameters.

SQL Parameter

You can use the following SQL command to generate a report:

SELECT 

   "Institution"."Institution Name", 

   "Portfolio"."Material Type", 

   "Portfolio"."No. of Available Portfolio" 

FROM "E-Inventory"

WHERE 

   ("Portfolio"."Material Type" = 'Book') 

   AND ("Portfolio Activation Date"."Portfolio Activation Year" = '2024')

To run this command via a URL, use the following format:

https://{Your Analytics server}/analytics/saw.dll?Go&SQL=SELECT "Institution"."Institution Name", "Portfolio"."Material Type", "Portfolio"."No. of Available Portfolio" FROM "E-Inventory" WHERE ("Portfolio"."Material Type" = 'Book') AND ("Portfolio Activation Date"."Portfolio Activation Year" = '2024')

URL Encoding

URL parameters often need to be encoded to ensure they are transmitted correctly over the internet. This process, known as URL encoding or percent encoding, converts characters into a format that can be safely included in a URL.

Here is the URL-encoded version of the above command:

https://{Your Analytics server}/analytics/saw.dll?Go&SQL=SELECT%20%22Institution%22.%22Institution%20Name%22,%20%22Portfolio%22.%22Material%20Type%22,%20%22Portfolio%22.%22No.%20of%20Available%20Portfolio%22%20FROM%20%22E-Inventory%22%20WHERE%20(%22Portfolio%22.%22Material%20Type%22%20=%20%27Book%27)%20AND%20(%22Portfolio%20Activation%20Date%22.%22Portfolio%20Activation%20Year%22%20=%20%272024%27)

Here are some common punctuation characters and their encoded forms:

For a complete reference of URL encoded characters, you can check resources like W3Schools[1][2].

Format Parameter

This controls the format of the results. The format can be xml, html, PDF, txt (tab separator), or csv (comma separator). Add this URL Parameter &format= to the end of the previous URL:

https://{Your Analytics server}/analytics/saw.dll?Go&SQL=SELECT%20%22Institution%22.%22Institution%20Name%22,%20%22Portfolio%22.%22Material%20Type%22,%20%22Portfolio%22.%22No.%20of%20Available%20Portfolio%22%20FROM%20%22E-Inventory%22%20WHERE%20(%22Portfolio%22.%22Material%20Type%22%20=%20%27Book%27)%20AND%20(%22Portfolio%20Activation%20Date%22.%22Portfolio%20Activation%20Year%22%20=%20%272024%27)&format=pdf

Options Parameter

This controls the options displayed at the end of the report. The following values can be used for the options parameter:

Example:

https://{Your Analytics server}/analytics/saw.dll?Go&SQL=SELECT%20%22Institution%22.%22Institution%20Name%22,%20%22Portfolio%22.%22Material%20Type%22,%20%22Portfolio%22.%22No.%20of%20Available%20Portfolio%22%20FROM%20%22E-Inventory%22%20WHERE%20(%22Portfolio%22.%22Material%20Type%22%20=%20%27Book%27)%20AND%20(%22Portfolio%20Activation%20Date%22.%22Portfolio%20Activation%20Year%22%20=%20%272024%27)&options=mfdr

Style Parameter

This shows the results using a specified style. If the style does not exist, the default is used. Available styles include:

Example:

https://{Your Analytics server}/analytics/saw.dll?Go&SQL=SELECT%20%22Institution%22.%22Institution%20Name%22,%20%22Portfolio%22.%22Material%20Type%22,%20%22Portfolio%22.%22No.%20of%20Available%20Portfolio%22%20FROM%20%22E-Inventory%22%20WHERE%20(%22Portfolio%22.%22Material%20Type%22%20=%20%27Book%27)%20AND%20(%22Portfolio%20Activation%20Date%22.%22Portfolio%20Activation%20Year%22%20=%20%272024%27)&style=lime

No what?
  Now all what you need to do is to save the URL you created as a link on any webpage and share it with your work buddies. The following example shows you how you can do it.

Example Report:

    Let's generate a simple report of duplicate ISBNs using the following SQL statement in the Advanced tab:

SELECT 
   "Bibliographic IDs"."ISBN valid single (Normalized)" saw_0,
   "Bibliographic Details"."Title Author Combined and Normalized" saw_1,
   "Title Measures"."Num of Titles (Active)" saw_2
 FROM "Titles"
 WHERE 
("Title Measures"."Num of Titles (Active)" > 1) AND ("Bibliographic IDs"."ISBN valid single (Normalized)" IS NOT NULL)

Add Action Link:

• Click on the gear icon on the ISBN column
• Select Column Properties
• Go to Interaction tab
• Under Value, change the Primary interaction list to Action Link
• Click on the plus sign to add an Action Link
• Click on Create New Action
  
• Select Navigate to a Web page
• Enter this URL in the URL box
  https://{YOUR ANALYTICS SERVER}/analytics/saw.dll?Go&SQL
• Click Define Parameters button and add parameters
• Type SQL in the Prompt box
• Click the little triangle in the Value field and select column value.
• Click the little triangle to the right of Column Value and select the ISBN column.
• Your screen should look like this
  
• Click OK. Ignore any message you may get.
• Click on the “>>” to edit the action we just created
• Paste the following URL in the URL box. Make sure your ISBN column is the first prompt in the report. Otherwise, you have to change the number after the @ sign.

https://{YOUR ANALYTICS SERVER}/analytics/saw.dll?Go&SQL=SELECT "Titles"."Bibliographic IDs"."ISBN valid single (Normalized)", "Titles"."Bibliographic Details"."MMS Id", "Titles"."Bibliographic Details"."Title Author Combined and Normalized" FROM "Titles" WHERE ("Titles"."Bibliographic IDs"."ISBN valid single (Normalized)"='@{1}')

Run and Test:

• Run the report. The report should look like this
  

• Click on any ISBN to execute the link.

Wrap-Up and Summary

By following these steps, you can easily run reports without saving them in your Analytics folder. This method allows you to quickly generate and view reports by simply passing URL parameters, making the process efficient and flexible. Whether you need to filter data, format results, or apply specific styles, the Go URL command provides a powerful way to customize your reports on the fly.

Happy reporting! If you have any questions or need further assistance, feel free to reach out.

References

[1] HTML URL Encoding Reference - W3Schools 

[2] HTML URL Encoding - W3Schools 

[3] OBIEE 10G/11G - The Go URL