How to include ALL as an option for a report dropdown

It is possible to include an ALL option in a report dropdown so that staff have the choice of either running the report separately for each individual entry in the authorised value or running the report for every single entry in the authorised value at once. 

Report example:

Here is an example of a report which uses a dropdown menu. 

This report will show a dropdown menu of all the different patron categories on the site. The staff member running the report will need to select which individual patron category to run the report for, meaning that only entries matching the one selected patron category will be returned.

The image below highlights the section of the report which will cause the patron categories dropdown to display when running a report. 

The significant clauses in this sentence are:

WHERE

This indicates to the report that there are going to be limitations defined for when the information requested earlier in the report should be returned.

categorycode

This is the field name of the entry in the database which holds patron category information. This indicates that the limitations indicated by the WHERE statement are linked to patron categories

=

The equals sign indicates that the limitations should only be applied when the information matches exactly the folllwing.

<< >>

These opening and closing brackets contain details of how the dropdown menu generated from categorycode definition should appear

Patron category

This is the label for the dropdown menu. This can be whatever name would make sense to the staff member running the report. 

|categorycode

This is where the dropdown menu draws the options to display from. The straight line or pipe, separates the label. 

This is how the dropdown menu will appear when the report is run:

Each entry in the patron category table appears as an individual option and it is only possible to run the report for one option at a time.

How to include the ALL option in the report dropdown

This is the same report example, but the SQL has been edited to cause the ALL option to appear.

Below is a comparison between the original report and the edited report:

Before the edit:

This entry uses an equal sign to cause the report to show only exactly the entries that are included in the identified authorised value.

After the edit:

This entry uses the LIKE statement instead of the equals to cause the report to use a selection of the identified authorised value. In this case, that means the authorised value entries and the ALL option. It also includes the parameter :all, which tells the report there are two options to include: 1) every option in the authorised value as an itemised list and 2) the ALL option to gather every entry on the list into one group to be reported on at once.

The ALL option will appear as an additional option on the dropdown. It will still be possible to run the report for a single entry in it, as well as all entries grouped together. 

This is the dropdown menu after the ALL option has been included:

Summary

To include the ALL option in a dropdown menu on a report, use the LIKE statement instead of an equals when defining the runtime parameters and include :all at the end of the runtime parameter within the triangular brackets.

Version	Date	Detail	Author
1.0	22/12/22	Document created	Holly Cooper
1.2	16/01/22	Adding details	Holly Cooper

• Related Articles

• Overview of some SQL Report Functions

  Overview of SQL Report Functions This FAQ is an example of a simple report describing the “anatomy” of the SQL code used.  Details about the different functions are outlined in the table below the report.  Some functions are mandatory to use in order ...

• How to require strong passwords for patron accounts

  A password is required to be able to login to a patron account. This document will outline the system preferences that determine the required length and strength of a password on a patron account, as well as options for setting exceptions to these ...

• Patron attributes on the self registration form

  Extended patron attributes are custom fields that can be added to the patron registration form as the library needs them. They can be free text fields or can have a set number of options for users to select from.  For a tour of the patron attribute ...

• How to add a report of the most checked out items to the OPAC

  This document will outline how to enable a report on the OPAC for the most commonly checked out titles. This feature will be available if the patron is logged in or not, but there will be some slight differences in results based on the branch the ...

• How to create a new patron account

  This document will outline how to manually add a new patron account. Step 1 - Find the tool to add new patrons Go to Patrons module on the homepage. There will be two options, add a new patron or use the Quick add new patron. Clicking on either ...