Third Report Challenge: A client wants to put together a report to show the cardnumber, first name and second name, limited by patron category.

Third Report Challenge: A client wants to put together a report to show the cardnumber, first name and second name, limited by patron category.

These notes are intended to be used to help answer the associated Report Challenge that was released this week.

Take a look at the question below and think about how you would approach answering it:

A client wants to put together a report to show the cardnumber, first name and second name, limited by patron category

Key points in this challenge:

Format of patron category as an authorised value

We can add a dropdown menu to a report

Difference between a hard coded option and dropdown option

Introduction

We are looking to include options for customising when results should be returned by a report. 

While we are not adding new fields to the SELECT statement, we want to limit the results being brought back by a specific patron category. 

Identify the table(s) and field(s)

As you did in previous lessons, navigate to the database schema and identify the table and the field.

In cases like this, where we are already working with a report with specified tables, it is useful to investigate the identified table to confirm if it contains the field you are looking to include or draw on for report results. If the table doesn't contain the field, then searching the columns in the database as outlined in previous lessons should be used. 

Try to identify the field to be included for patron categories to be included in this report. 

Hint 1

What is the table in the report

Here are details for checking database

 

Hint 2

The field doesn't always have the exact same format as the label in Koha.

 

Hint 3

The field is not patroncategory but it does contain the word category.

 

Which table and which field should

Table: borrowers

Field: categorycode

The WHERE clause

 We have a new clause to learn, this is the WHERE clause.

The WHERE clause allows a parameter to be created which will limit the results the report returns. There are many ways to use the WHERE clause, this challenge covers only one of them.

For more information on the WHERE clause, click here.

Incorporating the WHERE clause into the existing report

Now we have the information, we need to format the report to include the patron categories as a choice, rather than just displaying them all together. 

There are two options for this, I always choose to include all patron categories as options in a dropdown menu, rather than hardcoding a specific entry. The dropdown menu option makes the report more versatile. 

BONUS content: Can you hardcode a specific patron category into a report, rather than providing a dropdown menu of options?

How to hardcode a specific field

Identify patron categories on your system.

Here are details for how to find this information

Identify the code for the patron category to be reported on.

In the report, the WHERE clause will look like this:

WHERE borrowers.categorycode = ""

Add in the code for the patron category to be reported on between the " " and click Save.

 

The next step in using the correct field format, in this case, the format is: borrowers.categorycode. 

BONUS: Can you identify the format for other fields in the different tables?

Now we need to determine how to format the WHERE statement and the identified field. 

The WHERE statement is placed at the end of the FROM statement line.

The report format is now:

SELECT borrowers.firstname, borrowers.surname, borrowers.categorycode
FROM borrowers
WHERE 

The next step is to define the field the report should limit the results by. In this case the field is the patron category.

The report format is now:

SELECT borrowers.firstname, borrowers.surname, borrowers.categorycode

FROM borrowers

WHERE borrowers.categorycode

The next step is to add an operator to tell the report what to do with the defined field for limiting the report results. 

The report format is now:

SELECT borrowers.firstname, borrowers.surname, borrowers.categorycode
FROM borrowers
WHERE borrowers.categorycode =

The next step is to define the label for the field and the authorised value the dropdown menu in the report should pull information from.

In this case, we use the <<>> symbols. 

SELECT borrowers.firstname, borrowers.surname, borrowers.categorycode

FROM borrowers

WHERE borrowers.categorycode = <<Patron Category|categorycode>>

This report format will generate a dropdown menu when the report is run so a specific patron category can be selected to run the report for.