Table of Contents
The USMAI Data Warehouse Reporting Environment uses a tool called JasperReports Server (a.k.a. "Jaspersoft"). In addition to this guide, users may find some of their documentation useful. (Note that not all features described will be relevant due to permissions, server configurations, etc.)
- User Guide: https://docs.tibco.com/pub/js-jrs/7.2.0/doc/pdf/JasperReports-Server-User-Guide.pdf
- How-to Videos: https://community.jaspersoft.com/wiki/jasperreports-server-how-videos-playlist
- Community Wiki: https://community.jaspersoft.com/wiki/jaspersoft-community-wiki-0
The Reporting Environment allows users to query data that is in the USMAI Data Warehouse in order to build views, reports, and dashboards from available data.
Data from the data warehouse is organized into topics in the Reporting Environment to make the query and report-building process easier.
Currently, the following reporting topics are available:
- Collections (Current) - item-level collection data from Aleph that is refreshed monthly.
- BIBs and HOLs (Current) - holdings-level collection data from Aleph that is refreshed monthly.
- EZproxy Sessions Snapshot - snapshots of the current number of EZproxy sessions taken at 15-minute intervals.
For assistance, please contact the CLAS team at email@example.com.
Access the Reporting Environment
To login, you will need to enter your library's 2-character code in the "Organization" field on the login screen, as well as your Reporting Environment username and password.
Rather than entering in your library's 2-character code, you can use the following relevant link to pre-populate this value:
Each USMAI library has the ability (and responsibility) to manage their users and their permissions/access. There are three primary components involved in managing users and access: (1) Users, (2) Roles, and (3) Organizations. The JasperReports Server Admin Guide (chapters 1-3) covers these topics in detail.
A user with administrative privileges for their library can create, update, and delete users by selecting Users from the Manage menu.
Jaspersoft has two primary roles that control functionality in the Reporting Environment: (1) ROLE_ADMINISTRATOR and (2) ROLE_USER. THESE ROLES SHOULD NEVER BE DELETED! Users who are assigned assigned ROLE_ADMINISTRATOR have the ability to create additional roles under Manage -> Roles.
From the Manage -> Users page, an administrator can modify the roles assigned to any individual user. In this way, an administrator can assign permissions related both to functionality (i.e. creating and modifying users) and access (i.e. ability to view, edit, and/or delete Repository assets). By default users who are assigned ROLE_USER will have both read and write privileges for their library's content. Users who are assigned ROLE_ADMINISTRATOR can adjust these permissions for all resources, specific folders, and even specific items.
Each USMAI library is an "organization" in Jaspersoft with a separate set of administrators, users, and reports. Within that, a library can create suborganizations to further partition. Note: Users created in a suborganization cannot be given permission to reports and other assets in the parent organization.
Users can change their own passwords if they know their current password. On the login screen, there is a 'Change password' link that will reveal additional fields for setting a new password.
If a user forgets their password, a user with the Admin role will need to change it in the Manager -> Users section. Users should contact identified Admins at their library to have their password reset if they forget it.
Controlling Access to Resources
Restricting access to specific reports and other assets can be done using either Organizations or Roles. Currently, it is recommended to do this using Roles. For instance, if a set of reports needs to be restricted to a circulation department, a new role (e.g. ROLE_CIRC_DEPT) could be created and assigned to specific users.
There are three primary ways to view data in the Reporting Environment:
- Ad Hoc Views: A drag-and-drop environment for building custom views of the data, including defining filters, specifying sorting order, and choosing view modes (e.g. table, crosstab, or chart).
- Reports: Reports are built using Ad Hoc Views and contain a more limited set of options. Fields available for display, sorting, and filtering are defined in the Ad Hoc View and available for manipulation in the report.
- Dashboards: A combined view of multiple new and/or existing Ad Hoc Views and Reports. Usually used for 'quick views' of key data.
Ad Hoc Editor
Using the Ad Hoc Editor
When you choose "Create Ad Hoc View," you will be prompted to choose a data source and then a new window will open. This is the Ad Hoc Editor, and it is where you select the particular Fields and Measures you wish to include in your ad hoc views (and subsequently, in reports based on those views). The Fields and Measure available to you appear in the left-hand window, and you can drag-and-drop or double-click them into the Columns or Groups spaces.
However, before you do this, you may wish to make your selection from the two drop-down menus on the top line. One controls the format (or "Visualization Type") of the report (Table/Chart/Crosstab) and the other the data included (Sample Data/Full Data/No Data).
You also have an option to select (using the "gears" icon) what is displayed: Detailed Data, Totals Data, or Details and Totals.
Fields and Measures
The Fields which appear in the left-hand window are grouped into sets. You may drag the whole set to the right-hand window if you wish. For example, if you drag "Basic Bibliographic Info" to the Columns line, you will see columns for Title, Author, Imprint, and Pub Year. You can remove any of these fields from Columns by clicking on the "X" next to the name.
Alternately, you can open each Field group (by clicking on the +) and drag only those fields you want to the Columns line. For example, "Item Volume/Issue Design" has 17 different fields in the group, and you may only wish to have one of these such as "Volume/Issue" in your ad hoc view. It's more efficient to open the group and drag just one field than to drag the whole group and then remove those fields you don't want.
Measures are numerical attributes that might be summed or have other calculations (i.e. averages, modes, etc.) performed on them.
Calculated Fields and Measures
New, "calculated" attributes can be added to your ad hoc views by performing calculations on values in other attributes. This option is available from the three dots to the right of the heading for either Fields or Measures.
Select 'Create Calculated Field…' or 'Create Calculated Measure.'
The Reporting Environment has a formula builder to help you construct and validate Calculated Measures and Fields.
Filters allow you to narrow the results in an ad hoc view, which, by default, includes all of an institution's items. For example, you can add a filter to display only items with material type "BOOK".
To create a filter, right-click on the desired column, or right-click on the Field or Measure on the left, and click Create Filter (you need not display the field/measure as a column in order to filter by it). You can also drag the field or measure you want from the Fields/Measures section on the left to the Filters section (at the far-right on the screen). You can then decide how you want to filter the data.
There are a number of types of filters. Filters for data fields include:
- is one of
- is not one of
- is not equal to
- does not contain
- starts with
- does not start with
- ends with
- does not end with
"Is one of" is generally preferable to "equals" because it gives you the flexibility of choosing one value or many in the Report you create from the Ad Hoc View.
Filters for measures (numerical counts, for example) have some of the above, but also have number- or amount-specific possible filters:
- is less than
- is greater than
- is greater or equal to
- is less or equal to
- is between
- is not between
You must click the blue APPLY button at the bottom of the Filters section to filter your data.
Filters appear as Options in reports that are generated from an ad hoc view (the icon is on the top row of the Reports view page). These allow you to change the input controls: for example, if your ad hoc view was filtered by Collection Code is one of, you have the option to limit your report to one or more Collection Codes. However, you only have options that apply to the filters created in the original ad hoc view. In the Report view, you always have the option to filter to a subset of data based on values in the displayed columns.
However, the filters you set up in the Ad Hoc View become Input Controls in the Report editor, which can give the report creator the ability to change or expand the selected data, not just make it narrower.
You have the ability to sort a column in the Ad Hoc View or the Report, but only the Ad Hoc View allows you to sort on a value that is not displayed in the report. One case in which you would want to do this is when sorting by call number. The call number that is suitable for display is not suitable for sorting. There is a special field called, Sort by Call Number, that should be used for sorting, but not display. In the Ad Hoc View, right-click on the Field, Measure, or Column and select Use for Sorting…
A dialog window will open, which will allow you to sort by multiple fields:
Saving Ad Hoc Views & Creating Reports
When you have completed your creation/editing of the Ad Hoc View, you may click on the "floppy disk" icon on the top line to perform one of three actions:
- Save Ad Hoc View (this saves it as "Ad Hoc View" if it is a new ad hoc view or as the previously-given name if it is a previously-saved ad hoc view)
- Save Ad Hoc View As… (choose if you are modifying one ad hoc view to create another)
- Save Ad Hoc View and Create Report (the report will be named "Ad Hoc View Report" or whatever new name you have given your Ad Hoc View or you can name the report separately)
Note: you have the option to "Create a Title" for your Ad Hoc View (click next to the "gears" icon) but this is only for display purposes, it does not automatically transfer as the saved file name.
There is also an option to Export an Ad Hoc View or a Report. The icon for this is next to the "Save" icon. There are multiple format options, including Excel, PDF, DOCX, CSV, etc.
Reports can be created by choosing Reports from the Create menu.
You will be asked to choose an ad hoc view from which your report will be derived. Reports are always derived from ad hoc views.
There are two ways of manipulating the data that appears in a report:
- Clicking the Options icon and limiting by any filters that were set up in the ad hoc report
- Clicking the header for a column, which will give you options to format the column, hide the column, or sort/filter results based on values in the column.
Note: currently the limit in the Reporting Environment for reports is 200,000 rows. If your report exceeds this total, you will get an error message. Thus it's probably best to avoid creating reports that you know will have a very large number of items (don't do a shelflist for your entire library, for example). Such reports would probably be very unwieldy even if the Reporting Environment could produce them.
Why create reports?
Reports have much more limited functionality than ad hoc views, which might make one wonder what benefits reports have.
There are two main benefits:
- Reports can be scheduled to run on a regular basis and delivered to individuals (even those who don't have access to the Reporting Environment) via email.
- The minimal options in reports can make it much quicker to get the information you need, especially for users less familiar with the Reporting Environment.
Dashboards allow you to bring together multiple ad hoc views and reports. These can be useful for creating quick visual snapshots around topics of interest.
To create a new dashboard, choose Dashboard from the Create menu. This will open the Dashboard Designer. To open an existing dashboard for editing, right-click on the name (in either the Library or the Repository and choose 'Open in Designer…')
Dashboards can use existing content or include new content created specifically for that dashboard.
The various ad hoc views, reports, and other assets that are included in a dashboard are called dashlets. To edit a dashlet, right-click anywhere on the dashlet and choose 'Edit…'. Note that there are also a number of options in the 'Properties' menu option for each dashlet.
Once created and saved, dashboards can be linked to directly using the URL that displays in the address bar when viewing a dashboard (not when editing the dashboard).
Multipurpose Ad Hoc Views
Rather than creating many ad hoc views with distinct use cases, it is recommended to try to build ad hoc views that will satisfy a number of reporting scenarios. This can be achieved using filters. For instance, rather than setting up an ad hoc view specifically for reviewing items in a Stacks collection, set up a filter where a user can choose any collection they want for their report.
Build Ad Hoc Views using "No Data"
By default, when you're building an ad hoc view, your view will automatically populate using "Sample Data" - a subset of the entire data set. However, even with sample data some actions (like sorting, grouping, etc.) may require the Reporting Environment to query the entire data set. This can take some time and slow you down. Setting this to "No Data" allows you to add in the attributes you want and get them organized. Once you think you have what you want, set it back to "Sample Data" to review the results.
Calculated Fields and Measures
Because these types of attributes require the Reporting Environment to perform real-time calculations on each item, they can slow down the loading of your ad hoc views. It is recommended to use these sparingly.
Finding Reports: Repository and Library
There are two primary ways to find reports and other assets in the Reporting Environment: (1) the Repository and (2) the Library.
The Repository is a hierarchical structure of folders and files that a user can browse.
When browsing the repository users will only be able to see, and act upon, those folders and files to which they have the appropriate permissions. By right-clicking on a folder or file, a user can see the actions that they are able to take. Users with appropriate permissions can also add/remove access to specific folders and files by right-clicking and choosing Permissions.
Additionally, the Repository can be searched, and search results can be filtered by a number of characteristics, including file type and modification date.
The 'Public' Folder
When browsing the Repository, all users have access to a folder named Public. Folders and files in the Public folder are accessible by users at all USMAI libraries. This is where CLAS-developed reports and other resources will be stored.
The Public folder contains six subfolders, each correlating to a type of content/resource available in the Reporting Environment: Ad Hoc Views, Dashboards, Reports, Topics, Assets, and Templates. The Assets subfolder contains miscellaneous files that may be of common interest, like the USMAI logo. The Templates subfolder contains files that control the formatting of reports created from ad hoc views. The other subfolders are further organized by reporting topics.
If you write an ad hoc view, dashboard, or report that you think other libraries will find useful, CLAS can add it to the Public folder!
The Library is a view of all reports, ad hoc views, and dashboards in the Repository to which a user has access. It is a flat list that can be sorted by Name or Modified Date.
Reports can be scheduled to run at a specified point in the future either as a one-time operation or on a recurring basis. The reports can be placed in a specified folder in the Repository, sent via email, or both.
To schedule a report
- Find the report in either the Library or Repository
- Right click on the report
- Select Schedule…
- Click Create Schedule
On the first screen, you'll be given options for when and how often to run the report. Additional tabs will allow you to set the values for any filters in the report, output options like file type, and email recipients (if any) along with the method by which they'll access the report.
Note: By default, the Output Options enables an option to "Output to Repository", which will store a copy of the report in a default location unless you specify otherwise. If your sole intention is to email a report, then it is a good idea to uncheck this box. Otherwise you may unintentionally end up with a large number of reports that clog up your Repository!
Understanding the Data
Descriptions of all attributes available for reporting can be viewed in the Reporting Environment by hovering your cursor over the attibute's label.
Missing and invalid values
Many attributes in the data warehouse are checked for errors. If a value is required but not available, there are indicators that the value is missing. If the value is there but not valid, there are indicators that the value is invalid.
The following attributes are assigned a special code if missing or invalid:
- Item Sublibrary Code
- Item Collection Code
- HOL Sublibrary Code
- HOL Collection Code
- IPS Code
- Item Status Code
- Item Material Type Code
If a particular value is invalid or missing, it will affect the values assigned to other attributes for that item. For instance, if an item has an invalid "Item Collection Code", then the "Item Collection Name" will also have a corresponding value of "Invalid Value in Source".
This approach allows you to create filters that include/exclude invalid and/or missing values from your reports.
Aleph stores many dates related to an item. These are available in the Reporting Environment in the group labeled Item Action Dates.
Dates are also checked to make sure they are valid. In the majority of cases, if the Reporting Environment receives an invalid date (e.g. 2019-02-30) from Aleph, the value is set to 'null'. The exception to this is the Item Create Date. If an invalid date is encountered (which should never happen), a date value of 0001-01-03 is assigned. If the date value is missing or set to '0' in Aleph, it will have a date value of 0001-01-02 in the Reporting Environment.
Additional attributes exist for the Item Create Date that are intended to make it easier to filter, sort, and group your results by this date. For instance, you can quickly filter your results to only those items created in a certain year using the Item Create Date Year attribute. These attributes are available in the Item Create Date Filters group in ad hoc views.
View your reports on the go! There is a mobile app for both iOS and Android called JasperMobile.
- iOS: https://apps.apple.com/us/app/tibco-jaspermobile/id467317446
- Android: https://play.google.com/store/apps/details?id=com.jaspersoft.android.jaspermobile&hl=en_US
To use it, set the Server URL field to https://data.usmai.org/jasperserver-pro. The Organization is your library's two-letter campus code.