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.
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:
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:
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.
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"
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.
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.
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:
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.
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.
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.
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:
TIMESTAMPDIFF function: Returns the total number of specified intervals between two timestamps. Syntax: TIMESTAMPDIFF (interval, timestamp1, timestamp2)
TIMESTAMPADDfunction: Returns the timestamp calculated by adding, or subtracting, intervals.
Syntax: TIMESTAMPADD (interval, count as integer, timestamp)
Example: add one week to the current date
TIMESTAMPADD(SQL_TSI_WEEK, 1, CURRENT_DATE)
Example: return a date that is one week earlier than today
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.
In the Edit Prompt windows, change default selection to SQL Results.
In the SQL statement box, type a complete and simple SQL statement. Complete means the statement must begin with "SELECT", ends with "FROM {subject area)}. You may use "WHERE" condition to limit results. The following statement sets the default "Loan Date" value to a month earlier to the current date.
Example: Set default date value to a month earlier to the latest loan date saved in your database.
SELECT TIMESTAMPADD (SQL_TSI_MONTH, -1, MAX("Loan Date"."Loan Date")) FROM "Fulfillment"
CASE
WHEN EXTRACT(MONTH FROM "Dates"."Date") BETWEEN 7 AND 12 THEN EXTRACT(YEAR FROM "Dates"."Date") +1
WHEN EXTRACT(MONTH FROM "Dates"."Date") BETWEEN 1 AND 6 THEN EXTRACT(YEAR FROM "Dates"."Date")
ELSE NULL
END
First Date of Current Fiscal Year
SELECT
CAST(
TRIM(CAST(YEAR(CURRENT_DATE) AS CHAR)
)||'/07/01' AS DATE)
FROM "Funds Expenditure"
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.
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
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:
There are two methods we can use concatenate columns:
Concatenation function CONCAT
Concatenation operator ||
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 :-)
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.
EVALUATE_AGGR function: "Passes the specified database function with (optional) referenced columns as parameters to the back-end data source for evaluation. This function is intended for aggregate functions with a GROUP BY clause. The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function." (Copied)
Syntax: EVALUATE_AGGR('db_agg_function(%1...%N)' [AS datatype] [, column1, columnN])
LISTAGG function: LISTAGG orders data within each group specified in the ORDERBY clause and then concatenates the values of the measure column.
Syntax: LISTAGG ( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
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")
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".
