Creating Alma Analytics - Training Script

Owned by Allison Powers
Last updated: Jun 24, 2020 by MinnaVersion comment
25 min read

Trainer Preparation

Verify that all participants have Design Analytics role – in other words, that they can see the list of reports and the Design Analytics option in the Alma Analytics menu.

Introduction

Welcome to Creating Alma Analytics. We will focus on the tools available in Analytics to create reports. I will show you how the data is organized, explain some of the terminology, show you how to navigate the menus in Analytics, and introduce you to the folder structure.  Together we will work through two hands-on exercises. You will learn how to select fields for your analysis, how to create filters, and how to sort and format the output.

The script and all materials for this class are available to review on the wiki, and will continue to be updated with any changes in procedures.

Some quick logistics information:

  • Please ask questions when they come up; I will also pause between sections for questions and to make sure things are clear.

  • The nearest bathrooms & water supply are:

    • 90 Mt Auburn: Two restrooms are on the opposite side of the building on the basement level, water is in the kitchenette and in a fountain by the restrooms

    • 625 Mass Ave: Restrooms are at the opposite end of the building on the 3rd floor, by the water fountain

Let's start by introducing ourselves. (Go around the room and ask everyone to share their name, library/unit, and how this class relates to their work.)

Ok, let’s get going with Analytics.

What is Alma Analytics?

Alma Analytics is a separate platform called Oracle Business Intelligence Enterprise Edition, or OBIEE for short. Oracle provides the interface and features. Ex Libris selects the data to extract from Alma, and determines how it is organized in Analytics.

OBIEE is integrated with Alma, which allows us to access Analytics from Alma, and to run or Subscribe To reports from Alma, and to add widgets, and other Analytics objects to our Alma home screen.

Alma Analytics Menu

[Go to the Analytics menu in Alma]

Let’s start by taking a quick look at the Analytics menu.  Under this menu, you can:

  • Run any of the reports listed under the Reports header

  • Check when the data in Analytics was last updated

  • Access Design Analytics 

Under Reports is a list of analyses created by the Analytics and Reporting Working Group to meet core reporting needs. You can see that a scroll bar appears when you move your mouse to the list. Reports on the menu have been assigned a broad array of roles, and all staff should be able to view these reports.

Reports are run by clicking on the title. As you start to create your own analyses, I encourage you to explore these reports to see the different types of prompts that are available, and how they demonstrate an open design that meets a wide variety of reporting needs.

As you’re running reports, it is useful to know when Alma data was last updated in Analytics. Data is extracted from Alma approximately every 24 hours, usually overnight. At the bottom of the Analytics menu, you can view the status of the Alma data. Note that one Subject Area, Titles, is only updated once a month.

Now let’s open Analytics by clicking on Design Analytics. The Analytics module will open in a new tab or window, depending on your browser settings. I have two important tips before we dive in:

  1. Analytics sometimes opens to a login screen. If that happens, close the browser tab, go back to Alma and select Design Analytics again. There is no separate login for Analytics, so if you see a login screen it means Analytics did not open correctly.

  2. Analytics will time out after a period of inactivity in either Analytics or Alma. I recommend saving your work in Analytics often, and try to remember to perform some action in Alma every 20 minutes. The time out period does not seem to be consistent, and it is very frustrating to lose work, so get in the habit of saving often.  

My Account

Analytics displays your Alma ID in the upper right corner with the label "Signed In As". Hover over your ID and click on My Account. This is where you adjust your Preferences settings. The other tabs are either inactive or disabled in our version of OBIEE.  

Preferences should be set up as follows:

  • Time Zone: Your time zone.This is important because it determines the timestamp on your report output.

  • Subject Area Sort Order: Sort order of the subject areas and subject area folders within the Subject Areas pane. We will select Sort A to Z. You can change it, but for this training I want your display to match mine.

  • Analysis Editor: Specify which editor to use in Analytics. Select Full Editor, and the option Start on Criteria tab when editing Analysis.

Click OK.

Catalog

As we learned in the Analytics Overview class, Catalog opens the directory structure for Analytics. There are two top level folders My Folders, and Shared Folders. 

My Folders

My Folders is a personal folder accessible only to you. This is where you can put analyses that you are developing, or that you create for your own use. You can organize My Folders in whatever way works for you. Let's step through the process of creating a new folder in My Folders. 

Create a Folder

  1. Click on My Folders or a subfolder to highlight the tier where you want to create your new folder
  2. Click on the New icon from the Toolbar
  3. Click on Folder icon
  4. Type a name your new folder 
  5. Click OK

Move a Folder

[Trainer: demonstrate this by moving the folder you just created]

Folders can be moved by dragging and dropping them to different locations within My Folders, or to Shared Folders.

Delete a Folder 

[Trainer: demonstrate this by deleting the folder you just moved]

Folders can be deleted by highlighting the folder and then selecting Delete from the Toolbar or the Tasks Pane. You will then be asked to confirm that you want to delete that folder. Select OK.

Shared Folders

Alma

Analyses in the Alma folder were created by Ex Libris for use by all libraries working in Alma. Ex Libris has tried to anticipate library reporting needs by creating dozens of simple analyses and making them available for libraries to copy, modify, and reuse locally. Most of these analyses require adding a filter or prompt to make them usable with a large data set like Harvard's.

Community

Analyses in the Community Folder were created by other Alma institutions for use by all libraries working in Alma. For example, searching for ‘ARL’ in Community brings up ARL statistics reports developed by the University of Minnesota.

Alma Analytics has a single data model for all customers which allows reports developed at other institutions to run on Harvard data. However, the originating institution might include local data fields, or local coding, etc. that make the report unsuitable for use at Harvard.

The Community folder also provides an environment for collaborating with Analytics experts at other institutions. Reports in Community are accessible by all Analytics users at all Alma institutions. If you are developing a report with someone at another institution, they (and others) will be able to access the report configuration and make changes.

People are welcome to explore the Alma and Community reports, but are advised to look at the Harvard reports first.

Harvard University

Harvard University contains analyses that are only accessible by staff at Harvard who have a Design Analytics role. This is where we create analyses for general use at Harvard, or for use in our individual departments. It is where we can collaborate with others at Harvard in creation of an analyses, or post reports for review by colleagues. There are two types of folders under Harvard University at the moment --and this could change of course. At the top, where the folder name begins with an underscore are folders created for a purpose other than general use. 

  • Shared Folders > Harvard University > _Collaboration - Folder for analyses in development so that you can work on them with another Harvard colleague or colleagues. You may create new folders in this folder as needed.

  • Shared Folders > Harvard University > _Departmental Reports - Folder for analyses developed for use by a particular department. Folders can be added as needed. 

  • Shared Folders > Harvard University > _Training - Folder for analyses that we will be modifying or creating in this class.

Folders that do not start with an underscore contain analyses developed specifically for general use by anyone at Harvard. Many of these analyses are available on the Analytics menu in Alma. Analyses in these general use folders should always have prompts, often they have many prompts. Analyses in these folders should demonstrate an open design where prompted input makes the output useful for any unit. 

  • Shared Folders > Harvard University > [Subject Area Folders] - Folders are organized by Subject Area. Subject areas in Analytics are what we call our data sources.  There is a folder for each Subject Area. As you become familiar with the subject areas you will know where to look for analyses that might meet your needs as is, or that you can modify for your use. Please keep in mind that analyses in the subject area folders are for general use and should never be modified. But any can be used as the basis for a new report by opening and saving to a new location in My Folders or in a Departmental folder. 

Searching the Catalog

Keep in mind that there are already many analyses in the Harvard University shared folders, and it's likely that you can find a report that meets your needs as is or with some modification. Just finding a report that has most of the same fields you want can be a big time saving. It's usually a lot faster to remove fields that you don't want than to click through multiple folders to find and add fields. Existing analysis can be saved to My Folders and then modified to delete fields, apply hard coded filters, change them and adjust the formatting according to your needs. 

But first you have to find an analysis to modify. Let’s walk through searching for an analysis in the Harvard University shared folder. Whatever folder you have highlighted will be the folder for the search.

  1. Click on the Harvard University folder 

  2. Select the Search icon (binoculars) from the toolbar at the top

  3. Notice the path in Location which shows the scope of your search 

  4. Limiting by Type of object is an option

  5. Enter a keyword or phrase, such as loan counts, or cost shares, or encumbrances. You can use asterisks as a wildcard

  6. Let’s type in expend*

  7. Click Search

Results are returned for all objects in Harvard University that contain our term in the title or description.  

  • The file path for every object displays under the title for the object. 
  • The number of results shows in the top right. 
  • Every analysis should have a Description that displays below the title of the analysis. Along with the title, the description should help you to determine whether or not the analysis will meet your needs. 

As you can see, there are already many analyses that deal with expenditures, most in _WG or _Departmental Reports. As you become more familiar with the subject areas, a better search strategy would be to select the folder that corresponds to the subject area you want to use. For example, we can select Funds Expenditure to search for analyses involving fund transactions. Selecting this folder ensures that your results are not already customized to a particular department. That type of information should be included in the Description, but to be sure, you can focus your search on shared analyses in the Subject Area you want.

To return to the Folders Pane, select the Show/Hide Folders Pane icon from the Toolbar.

Do a search now to produce a list of reports in the Harvard University/Funds Expenditure folder that have “expenditure” in the title or description

  1. Click on Funds Expenditure to highlight it
  2. Select the Search icon
  3. Select Search

Description is required for analyses in the general subject area folders. 

Open | Edit | More

Let's now open the _Training folder. We will be working with the following analysis:

Shared Folders / Harvard University / _Training / Exercise 1: Acquisitions - Flexible - Expenditure Transactions (Purchases)

Note: The real report that you would normally use has the same name, is in the Fund Expenditures folder. We made an abridged version in _Training to make the report more manageable and to avoid accidentally editing the shared version. 

Notice that each analysis has links under the description for Open, Edit, and More. 

Open

Select Open to run an analysis right from the Catalog.  If the analysis has prompts, a prompt page will appear. Otherwise selecting Open will launch the analysis. This option is best if you want to preview the analysis first. Note: If the analysis has prompts, you can run the analysis with or without selecting any criteria.  Let's select Open. 

We would like to get a list of purchases made by the ACQ31 POL Owner -- that’s ITS at Langdell -- in FY-2020 to date, and want to see if this report will serve our purposes. So before modifying it, we’re going to run this report as an end-user, and see what, if anything, needs to be changed.

We will fill in the following prompts that are on your handout under Preview an Existing Analysis

  1. In POL Owner Code type or select from drop down: ACQ31
  2. In FUND type or select from drop down: 000001
  3. In Reporting Code (Object Code) type or select from drop down: 7031
  4. in BUDGET_YEAR type or select from drop down: 2020
  5. Click OK

Once the results are generated, you can see our prompt values recorded in the header. We can scroll across and down to see the full output. Columns can be re-sorted using the up and down arrows that appear in the column heading.

At the bottom are the options that you are probably already familiar with from the Overview class. 

The ones I want to draw your attention to are: Edit, Refresh, Print and Export.

  • Edit opens the Layout view, where you can change Criteria and edit the display of Results, among other things.  We will do that shortly. 

WARNING: Remember to save a copy to your My Folders before you make any changes to an existing analysis.   

  • Refresh will refresh the results, if they're generated in real-time or if criteria have been changed.

  • Print will open a PDF (to be printed or saved) or an HTML page that can be printed.

  • Export offers several options for exporting and saving the results.

Looking at the report output, we see that it has most of the information that we need. But since this is a report that I run several times a week, I want to save time by creating a version that is hard coded for my unit. I'm going to create filters for POL Owner Code and for the current Budget Year. This does mean that the hard-coded filter will have to be updated when we arrive at a new Fiscal Year. We could select Edit from this screen to start making modifications, but I want to show you an easier way to access Edit mode. 

Exit the report view and return to the _Training folder by clicking on the word Catalog in the menu at the top. Notice that you have returned to the same reports folder in which you started. 

Edit

To review, Open will run the analysis, first presenting you with a prompt page if there is one. This is the best way to determine if a report either meets your needs or can be used as the basis of a new analysis. You can then select Edit from the bottom of report output screen. If you want to go directly into analytics edit mode, you can select Edit right from the Catalog view.  

Note: If you choose Edit to open an analysis for the first time, you may not realize that the analysis has prompts. For this reason, it is recommended to Open an analysis first to see if it meets your reporting needs or is an analysis you want to customize.

More

Under More are a number of functions for managing analyses, and accessing functions that you might want to select right from the catalog. From here you can: 

  • Rename to change the title of an analysis

  • Add to Favorites so the analysis is accessible directly from the Favorites menu

  • Export to generate report output directly from the Catalog

  • Properties is important because it brings up the screen where you enter the description for your analysis

Notice that when I highlight the row for the analysis, all of the options appear in the Tasks window at bottom left. 

Editing Interface

Now lets go back to your handout for Exercise 1, and follow the steps under Modify an Analysis to create a copy of the analysis. 

Select Edit to open the analysis for editing. It will open in the Criteria tab.

Criteria

This opens the analysis in edit mode. The screen has four tabs:  Criteria (where you choose the fields and set the filters used in your report), Results, Prompts, and Advanced. On the left is the Funds Expenditures subject area, which is the data source for our analysis. 

From the Criteria tab, you can see which:

  • Subject Area is the data source of our analysis

  • The fields selected for the output. You can see the source of the fields, or as we say in Analytics, the Dimension

  • Filters if any, display in the Filters pane 

Subject Areas

As we learned in the Overview class, a Subject Area is a data source in which information about a particular functional area or topic has been collected. We have Subject Areas for Fulfillment, Physical Items, E-Inventory, Leganto course reserves, Lending Requests, Borrowing Requests, Cost Usage, Users, and many others. 

[Look at the subject areas on screen.]

The icon for Subject Area is a blue package. When you click on the plus sign next to a Subject Area, to expand its contents, you will find yellow folders, one for each Dimension in that Subject Area.  For example, in the Funds Expenditure Subject Area, you will find the Dimensions for Fund Ledger, Fund Transaction Details, etc. Some Dimensions, called Shared Dimensions, are available in multiple Subject Areas. Bibliographic Details is a Shared Dimension. When you expand a folder, you will find Fields. Fields are also called Columns.

There are three different types of Fields, each for different type of data: measures, attributes, and hierarchical columns.

  • Measures, or measurements, are fields that contains measurement type data, such as Transaction Amount. Measures have a yellow rectangular icon (a ruler) next to them.
  • Attributes are fields that contain descriptive data, such as Payment Method, or Fine Fee Status. They have a white rectangular icon with blue stripes (a list) next to them. Most fields in most dimensions are Attributes.
  • Hierarchical columns are available only in a few dimensions. They contain hierarchical data, such as a date (a day within a month within a year) or a call number that can be collapsed to just the class number or expanded to the full call number with the cutter (or cutters) and the year, volume, and copy information.

Results tab

Selecting the Results tab will run the analysis and display output. Analyses without filters can take a long time to run. For various reasons, wait times of 10-20 minutes are not uncommon even for seemingly simple reports.

From the results display you can access the Layout tools, and work with different Views such as Pivot Tables, Charts, report headers like the one that captures prompt values, and more. 

Prompts tab

To view the details of an analysis’ prompts, select the Prompts tab. You can view the details or edit the prompts by selecting the Edit icon in the upper right.

From the Display pane, you can see how the prompts will appear as well as edit the display by using the Edit icon .

You can also test the prompts by selecting the Preview icon  in the upper right.

Modifying an Existing Report

Let’s go back to the Criteria tab. Our first step is to create a copy of the analysis in My Folders. Remember that we do NOT want to edit the master copy of the report in the shared folder.

There are different ways to save a copy; I will show you one way.

  1. Click on the Save As icon in the upper right

  2. Click on My Folders to select the location where you want to save the analysis

  3. Change the Name to Expenditure Transactions ACQ31

  4. Enter a brief description in the box for Description

Creating Filters

  1. In the Selected Columns pane, scroll right to find the POL Owner Code column
  2. Click on the downward arrow at the right of the column, and select Filter
  3. As you see, there are a variety of Operators you can choose from. We will leave it as “is equal to / is in”. 
  4. In the Value drop-down choose the POL Owner Code - in this case ACQ31 

    The Value drop-down list is extremely useful for any field that has a controlled vocabulary or set of code values -- this makes it easier to pick the correct value you need, since it shows every value used in that field. It is     not as useful for fields with variable data, such as MMS ID numbers, free text, and bibliographic data, since the number of possible values in the list are in the millions. As we learned in the Overview class, it is possible to search for values if you're not sure what to enter. To search for values select the binoculars icon.

  5. Scroll to FUND
  6. Select Filter and type or select from drop down: 000001
  7. Scroll to Reporting Code (Object Code)
  8. Select Filter and type or select from drop down: 7031
  9. Scroll further right to find Budget Year
  10. Select Filter again, and select 2020 from the list

    If we decide later that we want to leave in the prompt for Budget Year to make it more flexible, we can remove this filter.

    We’re now going to test our selection of filters.

  11. Click on the Results tab to run the analysis.

This is a very nice feature of Analytics in that you can bounce back and forth between your criteria and your results until you fine-tune your analysis. Even after the design process is completed, some report authors prefer to run their analyses via the Results interface, because exporting long reports is easier here.

As you can see, there’s a message that displays when the report is running. If you are testing a report that has a long list of results, and it is taking a long time to run, you may wish to add some additional temporary filters (like a specific MMS ID or a language code) in order to test the report with a smaller results set. You can cancel at any time to add additional criteria.

Once the report has finished running, review the results to see if they fit the desired criteria. These are all purchases by ACQ 31 in Budget Year 2020. Did we need any other limits -- for example, only monographs, or only a particular language?

If you wish to export the report results at this point, you can click on the document with the upward pointing arrow at the top of the screen, rather than having to scroll to the end of what may be many pages of results. [Demonstrate exporting results.]

Modifying the Layout in Results Tab

When you are modifying an existing report for your own purposes you may well want to tweak the layout as well. Perhaps it has fields that you don’t care about, or it doesn’t include a field that you want, or you would like to change the field order, or field formatting. Making the edits that you want isn’t that difficult, but the key step is figuring out what edits you want to make. You may wish to jot them down before proceeding.

Views Pane 

In order to edit the report layout, click on the Table entry in the Views pane in the lower left, and select the pencil icon. Although it's possible to manipulate the results directly to some extent, those changes will not be permanent. Edits should be made in the Layout pane that appears when you click on the pencil icon. 

Delete or exclude fields

In looking at this report, I have decided that my unit doesn’t need the full budget string, just the fund number. So I can either delete or “park” the Tub, Org, Activity, Sub-Activity and Root fields.  To delete a field, click on the document with the arrow icon to the right of the field in question (we’ll try this with the TUB field), and click on Remove Column. This is a good way to get rid of a field that you are absolutely sure you will never want in your report, either to display or to filter. If there is a field that you don’t want to display now, but may wish to restore, you can instead drag and drop it into the Excluded box below the column listing. That way if you want to restore it, you just have to drag it up again. We will try this with the ORG field. 

 Now please either Delete or Exclude the ACTIVITY, SUB-ACTIVITY, and ROOT fields from your report.

Add fields

Although we have decided we don’t need all of the elements of the budget code, in our case, we do want the full name of the funds, rather than just the number.

  1. Open the Fund Ledger dimension folder and find the attribute for Fund Ledger Name. 
  2. Click on Fund Ledger Name and drag it into the Columns and Measures area in Layout. Drop it in the location where you want it to appear in output.

    Note: In some cases it may not be obvious to you what a particular field in Alma is called in Analytics. There are various sources of information, including the Analytics online help, checking field names in other reports, sending an email to the Reporting Discussion List, or sending in a Footprint, if you cannot figure out the field you need. You can also search the Subject area for a term in the name of the field.

  3. This is a good point to save the changes we have made so far. Click on the Save icon at the upper right -- note that the message “Saved” will pop up next to it.

Rename Field 

 We’ve added the “Fund Ledger Name” field, but would like to rename it “Fund Name”. 

  1. Click on the document-with-arrow icon next to the Fund Ledger Name field, select Column Properties
  2. Click on the Column Format tab
  3. Click on the Custom Headings check box, and then you will be able to change the Column Heading box. 

Change Field Format

If you decide you would like to change the format of the field, you can do that at this time as well. For example, perhaps you would like the Fund Name field data to be bold.

  1. To do this, click on the Style tab. To change the font to Bold, select Bold from the Font Style drop-down list
  2. To save the changes, click OK button 
  3. Save the analysis

There are plenty of other formatting changes you can make using this Column Properties box, and we encourage you to explore them. We’ll take a quick look at one other important property that you might want to make use of, which is changing the data format.

Change Data Format

You will notice that the purchase transaction amounts in this report display -- as you would expect -- with a leading dollar sign and a two-place decimal. However, in Analytics, all numeric fields, INCLUDING ones expressing dollar amounts (like purchase, orders, encumbrances, etc.) are expressed as numbers without a leading currency sign and are rounded to the nearest integer. So, particularly if you are adding a currency field to a report, but also if you wish to control the level of rounding, or control the data format in some other way, you can do that on the Data Format tab of the Column Properties menu.

  1. Locate the Transaction Amount field, click on Column Properties
  2. Click on the Data Format tab to see our options 
  3. Change the number of Decimal places from 2 to 0, to round prices to the nearest dollar.

Notice that the format is already Currency. If we had just placed the Transaction Amount field in this report, it would have the format Number and we would have had to first check off Override Data Format and change it to Currency.

Click on the OK button and then save again.

We’re going to make two more formatting changes here

Change field order 

We’ve decided that we would like the PO Line Reference (i.e., the POL Number) to appear in our report with other data from the PO Line dimension.  

  1. Locate PO Line Reference at the end of the report
  2. Click on the block in Layout and drag it to the section for other PO Line fields 

Data Aggregation

 We would also like to add totals to our report, in particular to the Transaction Amount field, so that we know the full amount we’ve spent.

  1. Above the row of columns next to the Columns and Measures label, click on the icon with the Sigma symbol Σ 
  2. Select After so that total display at the end of the report (We don’t have an option to put the total at the beginning of a report.)

    This will add totals to any number, currency, or percentage field automatically. There is also a way to add count totals to text fields, but that’s an advanced skill which we won’t be covering in today’s class.

  3. Click on the Done button at the top of the page to save Layout changes and close the Layout pane
  4. Save the report.

If there’s one lesson that we would like to leave you with, it is to save frequently!

Note: Instead of using Save, you may choose to use Save As instead, and rename the report by changing a version number at the end. This may be useful as you are learning, so that you can go back to an earlier version if you have made changes you don’t like and are unsure how to undo.

Creating a New Analysis 

There may be situations where you search for an existing analysis and don’t find any results that meet your needs. In that case you can create a new analysis by selecting New from the main menu.

Selecting a Subject Area

When you create a new analysis, your first decision is to select the subject area to use.

The choice of subject area is important, because it will determine the data available, and therefore what kind of analysis you can create. Acquisitions reports are most commonly created in the Funds Expenditure subject area and to a lesser extent in the Physical Items subject area; Fulfillment reports in the Fulfillment subject area, etc.

How do you decide what Subject Area to use if you are not familiar with what fields they contain? One strategy is to first create a few dummy reports to explore available fields, or you can check an existing report that is similar to the one that you want to create to see what Subject Area it was created in (check the Subject Area name at the top of the upper left pane on the Criteria or Results screen), or go to the Alma Documentation and review the Subject Areas section:

https://knowledge.exlibrisgroup.com/Alma/Product_Documentation/010Alma_Online_Help_(English)/080Analytics/Alma_Analytics_Subject_Areas

It can be helpful to find an example record in Alma that possesses the attributes you want to explore, filter a new report to show only the MMSID of that record, and then return all the fields from one dimension after another. You can drag all the fields into your report at one time. After you view results, back on the criteria tab there is an ‘x’ in the top right that may be used to remove all the selected columns. Doing this will not affect your filter for MMS ID.

New Analysis

In our second hands-on exercise we will create a new analysis using the Fines and Fees subject area. The first step is to open the New menu and select the subject area. 

  1. Hover your mouse over the New menu

  2. Click on Analysis
  3. Click on Fines and Fees from the list of Subject areas

Fines and Fees subject area opens in the Subject Areas window. Notice that the Criteria tab is highlighted and panes for Selected Columns and Filters are empty. Fines and Fees is comprised of several tables, or dimensions as they are called in Analytics. These tables contain columns. The Fines and Fees dimensions that we will be using are Owning Library, Creation Date, and Fines and Fees Transactions. 

Adding Columns

We will begin by selecting the columns that we want to include in our analysis

  1. Open the Owning Library dimension by double clicking on the folder or clicking on the arrow next to it.
    1. Owning Library Name
  2. Open the Creation Date dimension by double clicking on the folder or clicking on the arrow next to it.
    1. Creation Date
    2. Creation Year
    3. Creation Month
    4. Creation Month Key
  3. Open the Fines and Fees dimension by double clicking on the folder or clicking on the arrow next to it. Notice this table has columns that are measures. Measure columns contain values that can be counted, or aggregated in some way.
    1. Original Amount measure
    2. Fine Fee Type attribute

Now we will save the report.

  1. Click on the Save icon.
  2. Click on My Folders if it's not already open.
  3. Type Fines and Fees by library for the name.
  4. Click in Description box and type information about the purpose of the analysis and fields included in the output [for example, type "Calculates fines and fees original amount. Output includes library name, creation year, and creation month."]
  5. Type your name and the date.

Even though this is My Folders, having a description tells me that I created this report from scratch, and the original date of creation. Having good descriptions will be very helpful weeks or months from now when you want to revise this analysis, or when you want to create something similar and are looking to build on an existing analysis. 

Filters

Now let's apply filters to the analysis. Filters can be temporary. Limiting the results lets you quickly review output from your analysis and make iterative changes in the content or layout.

First, filter by Owning Library Name

  1. Click on the bulleted list on the right side of the column, and select Filter.
  2. Click on the down arrow for Operator to see the options.
  3. Select the is equal to / is in operator.
  4. Click on the down arrow for Value.
  5. Scroll down the alphabetical list of library names to Andover, Baker, and Cabot, and select the checkbox to the left of the name.
  6. Click OK.

The filter displays in the Filters pane.

Next, filter by Creation Year

  1. Open Operator list and click on is greater than
  2. Select 2015 for the Value field
  3. Click OK

Save the changes by clicking on the Save icon.

Let's view results of our analysis so far. Click on the Results tab. Notice the months are not sorting chronologically, but the Creation Month Key field has a numeric value for the month and that can be used for sorting. The fine amount is not in dollars and cents. We also want to see results grouped by Library, Month and Creation Year. 

Column Properties 

Let's change the format of Original Amount, and set a sort order for the analysis.

Format of Original Amount:

  1. Click on the bulleted list by Original Amount
  2. Select Column Properties then click on the Data Format tab
  3. Click the box labeled Override Default Data Format
  4. For Treat Numbers As select Currency
  5. Select $ sign for Currency Symbol
  6. In Decimal Places select 2
  7. Click OK

Sorting

Let's assign a sort order for the output: 

  1. Click on the bulleted list by Owning Library Name and select Sort

  2. Click on Sort Ascending
  3. Click on the list by Creation Year, select Sort
  4. Click on Add Descending Sort
  5. Click on the list by Creation Month Key, select Sort
  6. Click on Add Ascending Sort

Again, save the changes by clicking on the Save icon. 

Click on the Results tab. Notice the arrows and numbers in each column. Results are now sorted with current year first and months in chronological order. Amounts are shown in dollars and cents.

The data format and sorting looks good. Now I want to make some adjustments to the report layout. Although I can change the column order in Results, those changes would not be retained. To make layout changes use the Views pane in the lower left. 

  1. If not already viewing results, select the Results tab to produce output.
  2. You don't need to wait for results to display. In the Views pane (lower left), highlight Table and select Pencil icon for Edit View.

Layout Pane

The Layout pane appears below your results display. We are going to make several changes that will improve the layout of this report. 

First we are going to move Fine Fee Type from the end of the report, to a position right before Original Amount. Place your cursor over the column and when the four-pointed cursor appears, click and drag it into the new position. You can experiment with different positions to see how the results are affected. 
Next let's hide the numeric value for Creation Month Key. We can use the field for sorting, but it's not necessary to have it display in result.

  1. In Layout find the column for Creation Month Key, and click on More Options
  2. Click on Hidden. This causes the column to be grayed out. Notice the change is reflected in the results.

Next, we will create a subtotal for each month and year.

  1. Click on the sigma (Σ) sign next to Creation Month, select After
  2. Click on the sigma next to Creation Year, select After

Toggle off the Layout pane by clicking on Show/Hide Layout pane icon on the toolbar. This allows you to examine your results more easily. There is also a button to toggle the results pane on and off. You also have the arrow to the left of Layout label to open and close the left panes. When you are satisfied with your layout, select Done button in upper right. To back out changes select Revert, then Done. Revert will back out all changes made in Layout since the last time they were saved, not just the last change. 
The last change I want to make to the layout is to eliminate the groupings and instead make values repeat on each row.

  1. In the Compound Layout pane select View Properties icon associated with the Table (next to the pencil)
  2. On Style tab, check Enable alternate styling box under Row styling
  3. Check the box to Repeat cell values
  4. Click OK

Click on the Save icon.

Export results

  1. Select icon for Export this analysis (Sheet with arrow pointing up)
  2. Select Excel 2007
  3. Click on the file to open. (File download should appear in the lower left in the Chrome browser or in the upper right in the Firefox browser.)

If there is time: 

Pivot Table View

Now we are going to use the layout options to create a different View of the output. We have the table view configured, but our results are an aggregation of fines and fees, which might be better represented in a pivot table. 

  1. With results on the screen, go to the Views pane and click on the New View icon, and select Pivot Table. Notice that the Owning Library Name is now a column header. 
  2. In the Layout pane:
    1. Click on the More Options... icon for Creation Month Key, then click Hidden
    2. Order of rows should be: Owning Library Name, Fine Fee Type
    3. Order of columns should be: Creation Year, Creation Month Key, Creation Month
    4. Click on sigma (Σ) next to Owning Library Name, then click After
    5. Click on sigma next to Creation Year, then click After
  3. Click Done
  4. Click the green plus sign for Add View in the Views pane to add pivot table view

Click on the Save icon. 

 

 If there is time: 

Adding Prompts

  1. Click on Criteria tab
  2. In Filters area, click on pencil icon next to Owning Library Name
  3. Change Operator to is prompted
  4. Click on Prompts tab
  5. Click green plus sign to add a Column Prompt for Owning Library Name
  6. In Options select check box for Include "All Column Values"
  7. Click OK
  8. Repeat steps 5-7 for Creation Year
  9. Click on prompt page preview icon (the paper with eyeglasses icon)

Clicking on the Save icon.

Now, let's click on Catalog and Open the analysis. Enter prompt values. Note that the valid fiscal years begin with FY- and are indexed at the end of the prompt list.  

 

Wrap-up

Good luck! Please experiment and ask for help. You won't break anything if you've copied the analysis to your folder first. 

Wait times of 10-20 minutes are not uncommon even for seemingly simple reports.

Make sure you've saved the analysis. You can save even while it's searching on the Results tab, then go do something else. Just make sure that neither Alma nor Analytics will time out before your report is done.

Resources

Please continue to use the LTS Alma Support form to ask questions and report issues. You can find the link in the Notifications widget in Alma, along with the link to Alma documentation on the LTS wiki.

The Analytics and Reporting Working Group hosts monthly drop-in sessions that are an opportunity to ask questions, work on your analysis with others, and get help from experienced Analytics users.


The HL Reporting Discussion Group facilitates sharing ideas and learning from each other about tools and techniques used in reporting. In-person meetings are scheduled about every other month. The HL-reporting-discussion list is used for internal communication between Harvard library staff engaged in activities related to reporting and/or analyses, across Harvard’s available systems, including work done in Alma Analytics and Cognos.