The Reports domain in OLIB has been re-designed to enable it to be used as the primary reporting solution for OLIB.
The significant changes to the Reporting domain include:
- A Keyword Search to enable you to search for a report using a word or words anywhere in the report name, description, parameter prompt fields, email subject line, email body and more.
- Drop-down lists to aid selection of report parameters.
- Ability to preview and run the report on screen.
- Ability to record the previous report output.
- Scheduling of reports.
- To be able to generate a GETStream URL with the click of a button based on the report parameters.
- Availability of a selection of output formats, including as the email body or as an attachment in PDF, MS-Word, comma separated (for MS-Excel), or HTML.
- Incorporates rolling date capabilities and academic year configuration.
- Simplifies the method of delivering new reports via a Reports Repository.
The reporting features documented will only work in OLIB Web. WorldView has not been changed to support them.
The OLIB Reports section is located just below User Management in the OLIB Web Menu:
Where to find OLIB Reports
The OLIB Reports section has been moved and is now located below the User Management domain in the OLIB Web Menu:
The default search for the Reports domain is All OLIB Reports. This is a simple left-truncated, case-insensitive search on the reports’ names. For example, entering ca will list all reports whose report name begins with CA, the standard cataloguing reports.
Search options are available to list all reports in a specific domain such as Cataloguing, Circulation, Acquisitions, Collection Management, etc.
There is also a new Keyword Search to enable you to search for a report. The keyword search looks for the word or words entered anywhere in the report name, description, parameter prompt fields, email subject line, email body, header output, SQL To Run field, and footer output.
The fields that contribute to the OLIB Reports keyword index are defined in the OLIB Reports Index keyword index in the System Administration-Keyword Indexes domain. The contents of the OLIB Reports Index keyword index can be modified using the standard keyword index maintenance facilities in this domain.
Running a Report
If you know that the report you want to run is configured correctly and has all the runtime parameters correctly set, you can run the report from the Reports hitlist.
To do this, select the report to run and then choose the Run Report option on the Other Actions drop-down list.
This will run the report using the report parameters and output type already saved. The report will be emailed directly to the addresses present in the report.
To set or modify the report parameters, open the report record in modify mode. This can be achieved directly from the hitlist using the modify icon.
The report parameters are displayed on the Run Report sheet of the Reports layout.
Parameters can be entered either by typing directly into the parameter value field on the right, or by selecting from the parameter selection date picker or drop-down list field on the left.
Selecting a value in a parameter selection date picker/drop-down list will automatically copy the selected value into the parameter value field. Once the value is copied into the respective parameter value field, you can amend it manually.
If the parameter selection field is a date picker, the date you select will be copied into the parameter value field in the format required by the report and as indicated in the field label, e.g. Start Date (DD/MM/YY).
If the parameter selection field is a drop-down list, selecting an option from the list will add the key ID to the parameter value field. If the report is designed to allow multiple values, the drop-down selection will add the selected value to a comma separated list of values in the parameter value field.
Mandatory parameter value fields will be highlighted as such. In the above example, they are highlighted with a yellow background, although note that this will vary depending on how your OLIB system has been configured to highlight mandatory fields. You must enter a value in these fields before previewing or running the report.
All drop-down lists include a Clear option at the bottom to enable you to clear the parameter value field with a single click.
Some reports include parameters that are free text value fields, for example, CA013c – Sortable Accession List (for exporting to Excel) asks for a Start Shelfmark and End Shelfmark range. The parameters should be entered directly into these fields.
Some reports, for example, CI011 Hourly Circulation report, use a rolling date, such as Current Month Start, Current Academic Year Start, that can be selected from a drop-down menu. This avoids you having to edit the report parameters at the end of each month or year.
Selecting an option from the rolling dates drop-down list will present a parameter value in the form of an SQL clause, together with a description enclosed by SQL comment tags.
The date format specified here will be suitable for the report query and should not be changed.
Academic Year options
The default academic year runs from September through August inclusive. The default values are 01-09 and 31-08 respectively.
These dates can be configured if the Academic Year Start and End for your library are different. A new Reports / Email Configuration sheet is available in OLIB Defaults.
These dates can be configured in the Academic Year Start and End fields if they are different from the default values.
When the start date is changed, you will see that the end date defaults to the day before the new start date. Also, the displays immediately to the right of the fields are updated.
The end date can be amended independently of the start date.
Choosing an Output Format
The Output Type field is used to specify how the report is exported.
Here the report can be exported as a HTML attachment or an HTML formatted email. Only targets suitable for the report are available to select.
Preview a report
To preview a report, click on the Preview Report button.
If this is the first time this report is being previewed, then you may need to click the Refresh option:
The report preview will be displayed in the field immediately in the preview panel.
A Preview Only entry will appear in the Report Run History field on the Report History tab. Repeated Preview actions will overwrite the existing Preview entry rather than generating a separate entry for each preview.
Adding email addresses
Before running a report, enter the recipient’s email address either straight into the Email(s) field.
This field is on both the Run Report sheet of the report layout
and the Report Scheduling sheet.
Alternatively, you can select an Email List from the Email List field on the Report Scheduling
Email Lists can be created from the OLIB Reports menu.
If you are using the Email(s) field, multiple recipients should be entered with a semi-colon between email addresses. Recipients can be CC’d or BCC’d using the cc: or bcc: prefix.
In this example, the report will be sent to email@example.com and Richard.firstname.lastname@example.org in the To list, with email@example.com and firstname.lastname@example.org in the CC list.
To run a report, click the Run Report button. The report will be run and sent to the specified recipients. In addition, an Emailed OK entry will be included in the Report Run History field on the Report History tab.
Generating a GETStream URL
Clicking on the GETStream URL button will populate the GETStream URL field with the GETStream URL for this report.
This will allow you to output the report directly to Excel using the GetStream functionality available from OLIB Service Pack 7.
Parameters with dynamic values (e.g. rolling date options such as “Previous Calendar Year Start” and “Previous Calendar Year End”) are not included in the GETStream URL as their values will be generated at runtime.
The GETStream URL button will also update the GETStream URL in any Excel Template that is attached to the Report Definition.
GETStream reports in a browser
To run a GETStream report in a browser, copy the GETStream URL to your clipboard and paste it into your browser’s address bar.
This allows you to view the content of the GETStream report quickly and easily.
Running a report with an Excel template
If the report has an Excel template attached, this can be found at the top of the attachments list:
Clicking on the GETStream URL button will not only populate the GETStream URL field, but also update the Excel template with your GETStream URL and the currently set static parameter values.
Downloading and opening the Excel template will then require acceptance of two security measures, firstly to enable editing:
and secondly to enable external data connections:
Once these have been accepted, the spreadsheet can be used to retrieve the data from OLIB by selecting Refresh All from the Data ribbon:
You may then be prompted for suitable login details.
The spreadsheet can also be saved to your PC for simple re-use or circulated to colleagues.
Running a report in Excel without an Excel template
If the report does not have an Excel template attached, you can still run the report in Excel. To do this, copy the GETStream URL to your clipboard from the GETStream URL field.
Once copied, the report can be viewed in Excel by using the From Web option on Excel’s Data
Depending on the browser you are using, a New Web Query wizard may be launched with your browser homepage pre-selected.
Replace this address with the URL copied to the clipboard from OLIB Web and click Go. If this report requires authentication, it will be requested.
Enter the OLIB Web login details.
After a few moments, the report content will be previewed. The following example shows the result from the CA013c – Sortable Accessions List report:
Clicking on Import will then ask for a location in the spreadsheet in which to start the data.
Select a suitable starting point and click OK.
The data will then be imported into the spreadsheet. The data source is saved with the spreadsheet so that it can be subsequently refreshed by simply clicking on the Refresh All button on the toolbar.
Optionally the data can be set to refresh automatically when the spreadsheet is opened.
Scheduling a report
The new Report Scheduling sheet on the Reports layout allows you to schedule a report to be run at a particular time, for example, daily, weekly or monthly.
Before scheduling a report, enter the recipient’s email address either straight into the Email(s)
field or as a mail list in the Email List field.
Note that if there are any email addresses in the Email(s) field, the report will only be sent to these recipients. Thus, if you enter email address in Email(s) and if you also specify an email list in Email List, the email list will be ignored.
Next, select the required frequency (Daily, Weekly or Monthly) in the Frequency Type field and enter a value in the Frequency Value field. For example, select Monthly and 1 to set the report to run every (1) month.
The Once option in the Frequency Type drop-down list can be used to schedule a report to run once. Either set the date on which it should run in the Start/Next Run On field, or leave this blank to schedule the report to be run the next time the background report job runs. Once a report that is scheduled as a Once report has been run, Enabled will be set to No.
Start/Next Run On: select a date from the calendar on which to start the report generation schedule.
If this field is left blank, the report will be run the next time the background report job runs (usually every 15 minutes) after the report is saved as Enabled.
Enabled: set to Yes to activate the report schedule.
Email Body: enter some text to include a covering note in the email that is generated.
Viewing the Report History
Each time a report is run or previewed, it is stored in the database. Previous versions of the report can therefore be viewed at any time in one of two ways:
As an attachment: The report’s Attachments list displays the 13 most recent reports.
To view a previous report, simply select it from this list, and depending on the report type, it will be opened in Word, your PDF viewer or your default browser.
Note: The Attachments option is only displayed when the report is displayed in modify mode.
From the Report History Sheet: The Report Run History field on the Report History sheet lists the previous executions of the report.
Details included are the date and time the report was run, the user who requested the report, the status and the report parameters.
You can click through to the Report History record to see a more detailed view of the report parameters, with links to the Object, User and/or Email History record pertinent to this execution of the report.
Configuring the Report History
A Library can also decide for how long they wish to maintain the report history. This is configured on the Report History sheet of the Report layout.
The No. Results To Retain field specifies how many past executions of this report should be retained.
By default, the last 53 executions will be retained.
The No. Attachments To List field indicates how many attachments are listed in the report’s Attachments list.
If nothing is entered in this field, the default number of attachments to list is 13.
OLIB Reports Designer
If you have experience in SQL and report writing, new reports can be written from scratch using the OLIB Reports Designer.
When creating a new report that is not available to retrieve from another system, click New Record from the Reports domain hitlist. A report Parameter Type will need to be selected to change the report layout to the appropriate layout for the parameter type you selected.
Next the report definitions will need to be configured. This includes creating the following:
Report description fields
A separate How-To guide is available on OLIB Reports which describes in detail how to run, schedule and create reports from the Reporting domain.
OLIB Reports Exchange
An OLIB Reports Repository will be hosted and maintained by OCLC. A list of the reports in this repository will be maintained and made available to OLIB customers, together with URLs to retrieve the report definitions.
The list of reports in the repository can be viewed in your browser using the following link: https://reports.olib.oclc.org/repository/GETReport.jsp?reportno=-33