Skip to Main Content

Alma Analytics Resource Guide

This guide covers how to use the Alma Analytics platform to produce robust reports. Included are instructions on accessing the Oracle-based service as well as how to use its menu driven service to build custom reports.

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

by Hisham Makki on February 12th, 2025 in Analytics, Reports | 0 Comments

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:

Space: %20 or +

Open parenthesis ((): %28

Exclamation mark (!): %21

Close parenthesis ()): %29

Double quote ("): %22

Asterisk (*): %2A

Hash (#): %23

Plus (+): %2B

Dollar sign ($): %24

Comma (,): %2C

Percent (%): %25

Hyphen (-): %2D

Ampersand (&): %26

Period (.): %2E

Single quote ('): %27

Slash (/): %2F

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:

m: Modify (Analyze) Request

d: Export and Download file

f: Printer Friendly

r: Refresh button

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:

lime

fusionFX

alta

redwood

blafp

skyros

fuse

skyroscloud

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:

A screenshot of a computer

AI-generated content may be incorrect.

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
    A screenshot of a computer

AI-generated content may be incorrect.
  • 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
    A screenshot of a computer

AI-generated content may be incorrect.
  • 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
    A screenshot of a computer

AI-generated content may be incorrect.

 

  • 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 External link

[2] HTML URL Encoding - W3Schools External link

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


How to Join two subject areas in Data Visualization

by Hisham Makki on March 19th, 2024 in Analytics | 0 Comments

Joining Two Subject Areas
in Data Visualization

Author: Hisham Makki, Assistant Director for Data Quality.
Required knowledge: Basic Analytics; Basic DV; Basic SQL.

Contents:

 

 
 

Introduction:

Let’s dive into using Data Visualization (DV) join tables in Alma Analytics. This powerful feature allows you to combine data from multiple related subject areas, enabling more comprehensive and insightful reporting.

Understanding DV "Join" 

  1. What is a "Join"?
    • In database terms, a join combines columns from two or more tables based on related values. Typically, the related columns are the primary key column(s) of one table and the foreign key column(s) of another table.
    • Joins allow you to retrieve data from multiple tables and create meaningful connections between them.
  2. Subject Areas in Alma Analytics
    • Alma Analytics provides various subject areas, each representing a specific aspect of your library data (e.g., Fulfillment, Physical Items, etc.).
    • However, sometimes you need to combine data from different subject areas to gain a holistic view of your library operations.
  3. Using DV Join Tables
    • Suppose you want to create a report that combines information about Fulfillment (e.g., Patron Group) and Physical Items (e.g., Num. of Items).
    • Additionally, you want to group this data by Classification Code.
Here’s how you can achieve this using DV join tables:  

Instructions:

 

Use Alma menu to access Data Visualization:

 

 

 

 

 

 

Click on the "Create" button, then "Dataset":

 
 
 
 
 
 
 
 
 
 
 
 
 
 

Click on “Local Subject Area” icon.

 

Drag the subject areas you’d like to add to the dataset to the right of the screen.

A screenshot of a computer

Description automatically generated

 

Double click on Physical Items.

Drag the columns you need to Select Columns.

Make sure to add the join dimension "Classification Code" to the list.

Click OK and save the changes.

Double click on Fulfillment.

Drag the columns you need to "Select Columns".

Make sure the "Classification Code" is on the list.

Click OK and save the changes.

 

***Here is how you can make changes to columns headers or add database functions

Click on "Enter Logical SQL" and modify the SQL statement.

A screenshot of a computer

Description automatically generated

 

Go back to "Join Diagram" tab. Scroll down to see the tabs.

 

Right click on Physical Items box.

Select "Join to -> Fulfillment".

 

Choose your Join type (in this case I suggest joining all from Fulfillment)

Join both subject areas on "Classification Code".

 

Save your dataset.

BTW, you should be able to see the DV datasets in Analytics. Just scroll down the list of subject areas.

A screenshot of a computer

Description automatically generated

 


 

Resources:


Date-Time Functions in a Nutshell

by Hisham Makki on November 16th, 2023 in Analytics | 0 Comments

Alma Analytics
Date-Time Functions in a Nutshell

Author: Hisham Makki, Assistant Director for Data Quality.
Required knowledge: Basic Analytics.

Contents:

 

 
 

Introduction:

Analytics comes with many built-in functions that allow you to manipulate data. Date and time functions give you numerous options on how to modify, calculate, and extract date and time data. In this post, you will learn about Analytics date and time functions and how they work. This post is meant to be your quick reference guide to all datetime functions.  

Time Extraction Functions:

 

Function Note
SECOND (Time expr) Returns the number (between 0 and 59) corresponding to the seconds for a specified time expression.
MINUTE(Time expr) Returns the number (between 0 and 59) corresponding to the seconds for a specified time expression.
HOUR(Time expr) Returns a number (between 0 and 23) corresponding to the hour for a specified time expression. For example, 0 corresponds to 12 a.m. and 23 corresponds to 11 p.m.
CURRENT_TIME(Time expr) Returns the current time. The time is determined by the system in which the Oracle Analytics Server is running.

Example: Extract the hour from the current time

HOUR (CURRENT_TIME) 

 

Date Extraction Functions:

Day

Function Note
DAY (Time expr) Returns the number corresponding to the day of the month for a specified date expression. DayOfMonth also works.
DayOfWeek(Time expr) Returns a number between 1 and 7 corresponding to the day of the week for a specified date expression. For example, 1 always corresponds to Sunday, 2 corresponds to Monday, and so on through to Saturday which returns 7.
DAYNNAME(Time expr) Returns the name of the day of the week for a specified date expression.
DayOfYear(Time expr) Returns the number (between 1 and 366) corresponding to the day of the year for a specified date expression. Day_of_Year() also works.
DAY_OF_QUARTER(Time expr) Returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date expression.
CURRENT_DATE Returns the current date. The date is determined by the system in which the Oracle Analytics Server is running.

Daytime:

Function Note
NOW() Returns the current date/timestamp. The timestamp is determined by the system in which the Oracle Analytics is running. Time expr is any integer representing the number of digits of precision with which to display the fractional second. The argument is optional; the function returns the default precision when no argument is specified. CURRENT_TIMESTAMP also works.
Week:
Function Note
WEEK_OF_QUARTER (Time expr) Returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date expression.
WEEK_OF_YEAR(Time expr) Returns a number (between 1 and 53) corresponding to the week of the year for the specified date expression.
Month:
Function Note
MONTH (Time expr) Returns the number (between 1 and 12) corresponding to the month for a specified date expression.
MONTHNAME(Time expr) Returns the name of the month for a specified date expression.
MONTH_Of_QUARTER(Time expr) Returns the number (between 1 and 3) corresponding to the month in the quarter for a specified date expression.
Quarter:
Function Note
QUARTER (Time expr) Returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date expression.
Year:
Function Note
YEAR (Time expr) Returns the year for the specified date expression.

Calculate Date-Time Functions:

You can use datetime functions to calculate datetime values for columns, filters, or dashboard prompts. There are two functions allow you to calculate datetime values:


  Add Time Functions to Prompts

Sometimes you need to dynamically set a default date value in your date prompt. For example, you may need to set a prompt value to one week earlier to current date or set a default date to a month earlier to the most recent loan date in your database. 


 

Add Time Functions to Filters

To add a time function to a filter,



Concatenations: Concatenate columns and rows in Alma Analytics

by Hisham Makki on October 3rd, 2023 in Analytics | 0 Comments

Concatenations

[kənˌkadəˈnāSHən]

Author: Hisham Makki, Assistant Director for Data Quality.
Required knowledge: Advanced Analytics.

Contents:

 

 
 
Concatenation combines string data values where each value is joined together in one string value. You need to convert non-string data values to string values before concatenation. The easiest way to convert data types is to use CAST function. Oracle supports ANSI string data types plus a set of built-in data types such as: CHAR , VARCHAR2 ,NCHAR , and NVARCHAR2.Please notice that the built-in data types have limitations on size.
Data Type Description *
CHAR [(size [BYTE | CHAR])]

Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.

BYTE and CHAR have the same semantics as for VARCHAR2.

VARCHAR2 [(size [BYTE | CHAR])]

Variable-length character string having maximum length size bytes or characters. You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum size is:

  • 32767 bytes or characters if MAX_STRING_SIZE = EXTENDED

  • 4000 bytes or characters if MAX_STRING_SIZE = STANDARD

Refer to Oracle Database SQL Language Reference for more information on the MAX_STRING_SIZE initialization parameter.

BYTE indicates that the column will have byte length semantics. CHAR indicates that the column will have character semantics.

NCHAR [(size)]

Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.

BYTE and CHAR have the same semantics as for VARCHAR2.

NVARCHAR2 [(size)]

Variable-length Unicode character string having maximum length size characters. You must specify size for NVARCHAR2. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of:

  • 32767 bytes if MAX_STRING_SIZE = EXTENDED

  • 4000 bytes if MAX_STRING_SIZE = STANDARD

Refer to Oracle Database SQL Language Reference for more information on the MAX_STRING_SIZE initialization parameter.

 

Columns Concatenation

The CONCAT() function accepts, only, two arguments whose data types can by any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. However, the concatenation operator allows concatenating more than two strings in a more readable statement. So, I'm using the operator only on this page :-)  

String || String || String

Examples:

Column name || Column name

"Bibliographic Details"."Publication Place" || ' ' || "Bibliographic Details"."Publisher" || ', ' || "Bibliographic Details"."Publication Date"

Results:

Albany, N.Y. : Denver, Colo. : Books and Open-File Reports distributor; Dept of the Interior US Geological Survey, 1988.

Albany, N.Y. : State University of New York Press, c1997.

Albany, N.Y. : University of the State of New York State Education Dept Bureau of Curriculum Development, 1985.

Example of simple SQL statement:

SELECT 
   "Bibliographic Details"."Publication Place" || ' ' || "Bibliographic Details"."Publisher" || ', ' || "Bibliographic Details"."Publication Date" saw_0
 FROM "Physical Items"
 WHERE 
"Item Creation Date"."Item Creation Year" > '2020'

 

Rows Concatenation

Without further ado, the straightforward way to concatenate rows is to use LISTAGG function. However, LISTAGG function is not available in Alma Analytics. In order to use LISTAGG function, we need to use an embedded database function to run LISTAGG. The embedded DB functions directly call database functions from Oracle BI. We will use EVALUATE_AGGR function to embed LISTAGG in the report.

Here is the statement I used to combine all rows of library codes in one row. This is the only statement that works for the NZ.

EVALUATE_AGGR('LISTAGG(DISTINCT  %1,%2 ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY %3)' as varchar(550), "Library Unit"."Library Code (Active)",',',"Library Unit"."Library Code (Active)")

statement Description
EVALUATE_AGGR The function to embed an aggregate DB function.
LISTAGG The function to concatenate rows.
DISTINCT Limit values to distinct values to eliminate duplicates.
%1,%2 %1 refers to the first column ("Library Unit"."Library Code (Active)")
%2 refers to the delimiter ','.
ON OVERFLOW TRUNCATE Returns a truncated list of values if the size of returned list is larger than the specified size.
ORDER BY %3 order by the third column in the statement ("Library Unit"."Library Code (Active)")
as varchar(550) Convert returned values to varchar. Size is optional.

Example of simple SQL statement:

SELECT 
   "Institution"."Institution Name" saw_0,
   EVALUATE_AGGR('LISTAGG(DISTINCT  %1,%2 ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY %3)' as varchar(550), "Library Unit"."Library Code (Active)",',',"Library Unit"."Library Code (Active)") saw_1,
   "Physical Item Details"."Num of Items (In Repository)" saw_2,
   DESCRIPTOR_IDOF("Physical Items"."Institution"."Institution Name") saw_3
 FROM "Physical Items"
 WHERE 
"Item Creation Date"."Item Creation Year" > '2020'

 

Tip 😎 You can concatenate columns and rows in one statement:

Using System Events subject area, the following example concatenates two columns ("Event Key Name" and "Event Key Value"), and the rows of concatenated columns.

EVALUATE_AGGR('LISTAGG(DISTINCT  %1,%2 ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY %3)' as varchar(550), ("Event Details"."Event Key Name" || ' : ' || "Event Details"."Event Key Value"), ' - ', "Event Details"."Event Key Value")

 



Add Barcode Font to Analytics

by Hisham Makki on August 25th, 2023 in Analytics | 0 Comments

Add Barcode Font to Analytics

*Add Barcode Font to Analytics*

Author: Hisham Makki, Assistant Director for Data Quality.
Required knowledge: Basic Analytics; HTML.

Contents:

 

 
 
Unfortunately, Alma Analytics does not come with a built-in barcode font. However, we have been asked by some libraries in our consortium to find a way to add barcode fonts on Alma Analytics. After investigating, I found that it is possible to add a barcode font using the custom CSS style feature. But, with every work around come few issues. One of these issues is you cannot export the barcode font to Excel or PDF files. Yet, we still can print the barcode. Here is how:

 

Add Barcode to An Analysis

  • First Drag a barcode column to an analysis.
  • Change the barcode column formula and add an asterisk to the beginning of the barcode and an asterisk to the end of it. You can use the concatenation operator  ||  like this:
    '*' || "Physical Item Details"."Barcode" || '*'  or CONCAT ('*', CONCAT ("Loan Details"."Barcode",'*' ))    

Change Column CSS:

  • Click on Results then click on the edit icon.
  • Click on the gear icon then click on Format Value from the popup menu.

     

  • Expand "Custom CSS Style Options (HTML Only)"

     

  • Check "Use Custom CSS Style" and add the following line:
    font-family: 'Libre Barcode 39 Extended Text', cursive;font-size:50px;

     
  • Check "Use Custome CSS Class" and add the following line:
    <Head> <link href="https://fonts.googleapis.com/css2?family=Libre+Barcode+39+Extended+Text&display=swap" rel="stylesheet"> <STYLE TYPE="text/css"> .barcode39 {   font-family: 'Libre Barcode 39 Extended Text', cursive;   font-size: 40px; }  </STYLE>  </Head>

 


 Add Barcode to A Dashboard Analysis

I found that the barcode font does not work if added to a dashboard unless we remove the CSS Class. So, if you want to add your analysis to a dashboard, you need to uncheck the "Use Custom CSS Class".
 

Resources:


  Subscribe



Enter your e-mail address to receive notifications of new posts by e-mail.


  Archive



  Subjects



Analytics
Reports