PINES Documentation

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Advanced Reports Guide ======

===== Introduction =====

The information in this section builds upon the information discussed in the [[reports:egreports:basic|Basic Reports Guide]] and [[https://my.nicheacademy.com/pines/course/9061|Basic Reports Class]]. An [[https://my.nicheacademy.com/pines/course/48526|Advanced Reports Class]] is also available in the PINES Learning Center. Before continuing with advanced reports, you may wish to consult the basic reports resources if you have not done so, unless you are comfortable running reports in Evergreen and manipulating the output.

Though it is not necessary to create templates in Evergreen, some knowledge of relational databases will come in handy, as the reports interface can be overwhelming. It may be helpful to review a brief [[reports:egreports:intro_db|introduction to databases]].

==== Data Types, Transforms, and Operators ====

The terms “data type,” “field transform,” and “operator” are used throughout template creation. Getting familiar with each set will be helpful when determining what fields and filters to add to your template.

//Data type// indicates what kind of information the piece of stored data is. The data type tells the system how to treat the piece of information and determines how that information can be used or viewed. For example, when Evergreen needs to store a time, such as a checkout time for a circulation, it will store it as a timestamp. Timestamp is a specific data type that tells the system to treat that piece of data as a specific time and indicates what options are available for interpretation of that data. For a list of available data types and their descriptions, review the [[reports:egreports:data_types|list of data types]].

//Field transforms// determine how data is interpreted when it is retrieved from the database. Each data type has a specific set of transforms available. The 'Raw Data' transform is available to every data type; it indicates that the data should be treated exactly as it is stored. For a list of available transforms and their descriptions, review the [[reports:egreports:data_types#transforms|list of field transforms]].

//Operators// describe the ways two pieces of data can be compared to one another and are used when creating filters to determine which database records should be included in a report. For example, when comparing two numbers, they can be equal to one another, the first can be greater than the second, etc. One helpful and commonly used operator is 'In list,' which can be used to provide a list of the organizational units in a template. For a list of available operators and their descriptions, review the [[reports:egreports:data_types#operators|list of operators]].

Below shows these three fields within the report template filters tab:

{{:reports:egreports:data_op_ft.png?400|}}

==== Data Type Key ====
Within the template editor, data fields are associated with the following icons, which notate what type of data the field contains:

{{:reports:egreports:pasted:20220520-144107.jpeg}}text field\\
{{:reports:egreports:pasted:20220520-144115.jpeg}}boolean (results in a true (1) or false (0))\\
{{:reports:egreports:pasted:20220520-144204.jpeg}}links to a different table of data\\
{{:reports:egreports:pasted:20220520-144210.jpeg}}organizational unit (tip: to show library names in Display Fields, choose Short (Policy) Name instead; this is better to use for filters)\\
{{:reports:egreports:pasted:20220520-144215.jpeg}}money/dollar amount\\
{{:reports:egreports:pasted:20220520-144223.jpeg}}timestamp (ex: date)\\
{{:reports:egreports:pasted:20220520-144230.jpeg}}ID (ex: circulation ID, hold request ID)\\
{{:reports:egreports:pasted:20220520-144238.jpeg}}integer\\
{{:reports:egreports:pasted:20220520-144303.jpeg}}time interval (ex: circulation duration lengths)\\
===== Modifying a Cloned Template =====

If you find a template that you like, but you would like to customize certain fields and filters to better fit the data you are looking for, you can clone the template and make modifications. To do so, follow the steps for [[reports:egreports:basic#cloning_existing_templates|cloning an existing template]] until you reach the template editor view.

{{:reports:egreports:pasted:20220426-144001.jpeg}}

Once in the template editor, update the Template Name and Template Description fields at the top of the screen. (You can wait to update the Template Description until you have finished modifying the template, but this step is important to track the paths and labels of the fields and filters you used to create the template).

Note that any of the display fields or filters may be changed or removed, and new fields or filters may be added. However, the Core Source cannot be changed without losing the current display fields and filters. Changing the Core Source will force you to start the template creation process from scratch. (If you do attempt to change the Core Source, an alert will pop up to prompt you to click Continue or Cancel.)



==== Editing Existing Display Fields ====
The following adjustments can be made to existing entries in the Display Fields tab, which will reflect in your output report.

{{:reports:egreports:reports_display_field_actions_menu.png?400|}}

**Change Column Label**
  * Right-click the appropriate row in the Display Fields tab.
  * Click Change Column Label.
  * Enter the header name you would like to use in the pop-up window. (For example, you may want to change "Item Barcode" to "Barcode")
  * Click OK/Continue.

**Change Column Documentation**
This function allows you to add additional information, or hints, that will show up next to the column name in the report template. The hints will not show in the output, however, so this function may not be used often for display fields.
  * Right-click the appropriate row in the Display Fields tab.
  * Enter text in field.
  * Click OK/Continue.

**Change Field Transform**
  * Right-click the appropriate row in the Display Fields tab.
  * Click Change Transform.
  * Choose from the options in the dropdown menu.
  * Click OK/Continue.

**Move Field Up**
  * Right-click the appropriate row in the Display Fields tab.
  * Click Move Field Up.

**Move Field Down**
  * Right-click the appropriate row in the Display Fields tab.
  * Click Move Field Down.
//(Please note that you must move fields up or down one level at a time.)//

**Remove Field**
  * Right-click the appropriate row in the Display Fields tab.
  * Click Remove Field.

Another way to edit fields is by clicking on the row in the Display Fields tab, clicking Actions, and choosing an option from the dropdown menu.

{{:reports:egreports:pasted:20220426-144025.jpeg}}

**Notes on editing fields:**
  * The appearance of the displayed data is dictated by the field’s [[reports:egreports|data type]] and the field [[reports:egreports:data_types#transforms|transform]] that is applied to the field. The data type cannot be changed, but a different field transform can be applied, which will format the output data in different ways.
  * If a field causes data to be broken down into smaller chunks, removing that field will aggregate the data on a single line. For example, say you have a template using Classic Item List as the Core Source with Display Fields “Owning Library” (as //Raw Data//) and “Copy ID” (with //Sum// field transform). This report will give you a count of copies (that meet the conditions specified in the filters) for each owning library on separate lines. If “Owning Library” is removed from the Display Fields, the output will contain a single line with a count of all copies that meet the conditions specified in the filters, rather than breaking them out by owning library.

==== Adding New Display Fields ====

For each display field you would like to add, follow these steps: 

**1. Select the Display Field.** Navigate to the field that you want to include via the Core Source and Source options and highlight that field.

**2. Select the Field Transform.** In the Field Transform box, choose the transform you’d like to apply to the field; this transform determines how the field will appear in the output. If you aren't sure which transform to use, consult the [[reports:egreports:data_types#transforms|field transforms list]].

**3. Add to Display Fields.** Under the Display Fields tab, click Add Fields.

**4. Rename the Field. (optional)** You may want to change the column label that will appear in the output. To do so, right-click on the appropriate row in the Display Fields tab and type in the preferred header name.

{{:reports:egreports:pasted:20220427-135858.jpeg}}

**Tips:**

  * You can add more than one field at a time to the Display Field tab. This can be done by selecting multiple fields and then clicking Add Fields. Once selected, field names are bolded, as shown in the screenshot below. (To deselect a bolded field, click on the field again so it becomes unbolded.) They will be added to the Display Field tab in the order they are selected.
{{:reports:egreports:pasted:20220426-144521.jpeg}}

  * If a field under Source has a link symbol, this means this links to another table of information and will not show as data if selected there. If you would like to use a linked field, return to the main Core Source dropdown to click on the arrow and find the name of that corresponding field/table.
{{:reports:egreports:pasted:20220429-161445.jpeg}}

  * It’s best to stay as high up on the “tree” of field options as possible, as getting further into the weeds may cause reports to run in a loop and not produce an output.
{{:reports:egreports:pasted:20220429-162023.jpeg}}


==== Editing Existing Filters ====

Existing fields in the Filters tab can be edited just as in the Display Fields tab: by right-clicking on the row and selecting the option from the dropdown menu, OR by clicking Actions and choosing from the dropdown menu.

The options for editing Filters are slightly different, however. Edits can only be made to the Operator, Field Transform, and Filter fields. Filters can be removed, though the Move Up and Move Down functions are not available, as the order of fields is not relevant to filters. The column documentation is also a bit different.

**Change Column Documentation**

In Filters, this function can be used to document any notes or helpful hints that will appear in the report template. For example, if you have a filter to choose the Owning Library and another filter to choose the Circulating Library, you can label these filters using column documentation. This shows up on the template form when you’re running the report. To add column documentation:
  * Right-click the appropriate row in the Display Fields tab.
  * Enter text in field.
  * Click OK/Continue.

The highlighted text in the screenshot below shows what filter column documentation looks like when running a report:

{{:reports:egreports:column_doc.png?400|}}

==== Adding New Filters ====

The process of adding a new filter is similar to the process of adding a field to the Display Fields tab. 

{{:reports:egreports:pasted:20220427-135126.jpeg}}

The difference is that you will want to designate an Operator as well as a Filter Value (if applicable).

For example, if you are creating a report where you want to filter by a date range for Check In Date/Time, once you add the field to your Filters tab, you can change the operator to //Between// and the Field Transform to //Date.// If you want the report to show data from a specific permission group, you could go to Main (Profile) Permission Group, add Group ID, and set Operator as //In list//.

Once you have made all of your necessary adjustments, click Save Template. Your altered template is now ready for use.


===== Creating a Template from Scratch =====

Before you start creating your template, it's a good idea to ask yourself exactly what data you’re trying to retrieve. You may want to write down the answers to the following questions, especially for the first few templates you create; you’ll use these answers throughout the template creation process.
  - What is the general topic of the data you’re looking for? Do you need to collect data about patrons, items, circulations, transits, holds, or monetary transactions?
  - What kind of data are you looking for? Do you need to know the count of items or patrons that fit some criteria? Do you need to report a sum of money? Do you need to find a list of patrons or titles? 
  - How should you filter your results? For example, if you’re looking for a list of patrons, you probably don’t want to list all the patrons in your consortium. Do you want to restrict your list to just patrons of your library? You may also want to restrict it by the patron’s birthday, ZIP code, and/or demographic.

Now that you’ve defined the data you’re looking for, it’s time to start building the template. There are five basic steps of template creation, which we’ll describe in detail below.

  - **Create a new template.** You will create a new template in the appropriate subfolder under Templates.
  - **Select the Core Source.** You will determine which main source you use as your starting point for the template.
  - **Set up Display Fields.** You will decide what data will be included in the output and how it will appear.
  - **Set up Filters.** Here, you set up restrictions on the output so you only retrieve the data you need.
  - **Name and save the template.** Once you've defined the filters and displays, it's time to name the template and save it for future use.

Steps 3 and 4 are interchangeable. For some people, it makes more sense to set up filters before choosing which data should be included in the output; for others, it is easier to set up the displays first. You should perform these steps in the order that you find most intuitive.

**Step 1: Create a New Template**
  * Click on the name of the subfolder where you would like the template to live.
  * Click ‘Create a new Template for this folder.’

{{:reports:egreports:pasted:20220426-160808.png}}

  * This will bring you to the blank template editor screen.
  * Fill in your Template Name and Template Description.

<note>The Template Description can be updated before, during, or after you create your report, however, it is very important to notate which fields, filters, and column labels you used. It is also helpful to add which Core Source you used. For a guide, please refer to report descriptions in Quick Reports or in any of the Shared Templates in the staff client.</note>

**Step 2: Select the Core Source**

The Core Source determines what data you'll have available for your report. It is very important that you pick the source that makes the most sense. To select the source you want, choose it from the dropdown box located to the right of Core Source.

{{:reports:egreports:pasted:20220426-162451.png}}

Most everything you need will be under the ‘Core sources’ category in the dropdown. Options listed under ‘Non-Core sources’ are very likely not relevant to any data you are looking for.

To determine which is the proper source, look back at your answer to the first question: What is the general topic of the data you're looking for? Choose the Core Source that is most closely related to the topic.

If you are uncertain which source you should use, consult the [[reports:egreports:core_src|Core Sources list]].

**Step 3: Set Up Filters**

Now that you've selected a source, you can start setting up the filters. Look back at your answer to question 3: How do you want to restrict your report to show only the data you want?

The first step for setting up filters is to determine what filters you actually need. This is the most difficult step, since there is no formulaic method. To get a feel for this process,  you can review descriptions of reports you are comfortable using to see what filters are included.

Pay special attention to filters for 'Is Deleted' and 'Is Active' for Items and Patrons, respectively. If you’re trying to get a list of holdings, it’s important to make sure that you only report on items that haven’t been deleted; for patrons, you may or may not want to filter out inactive patrons, depending on your report’s needs or your local policies.

The filters themselves will vary according to your needs, and there are far too many possibilities to list here, so you may need to experiment a bit. However, the basic steps are the same for all filters. 

For each filter in your template, repeat the following steps. You may also want to add column documentation if you feel appropriate.

//**1. Select the Filter Field**//

Click on the Core Source name next to the arrow to populate the Source fields. If you click on the arrow, more categories will populate underneath the Core Source.

//**2. Select the Transform**//

Once you click on a Source field, the Field Transform box will populate with the available options.

//**3. Add the Field to the Filters Tab**//

Once you have the field and field transform highlighted, click the Add Fields button, which is directly under the Filters tab. (If you do not choose a transform, it will default to //Raw Data// and can be changed after adding the field by right-clicking on the row and clicking Change Transform.

//**4. Select Operator/Filter Value**//

//Equals// is the default operator for a filter. If you want to choose a different operator for a filter, right-click the row and click the Change Operator button from the pop-up menu at the bottom of the screen. Choose the correct operator from the list. If you aren't certain which operator to use, refer to the [[reports:egreports:data_types#operators|list of operators]].

You also may want to specify a value for the filter at this time. To do so, right-click the row and click Change Filter Value. A pop-up will allow you to specify a value that will be used whenever a report is run based off of this template.

**Step 4: Set Up Display Fields**

The first part of setting up the Display Fields is determining what they are. Look back at your answer to question 2: What kind of data are you looking for? Your answers will help you determine which fields you’ll need to display and how you'll display them. 

Once you’ve decided what you'll want to display, follow the steps for [[reports:egreports:advanced:#adding_new_display_fields|adding a Display Field]], making sure the Display Fields tab is clicked so entries populate in the correct tab.

  - Select the Display Field.
  - Select the Field Transform.
  - Click Add Fields to add to the Display Fields tab.
  - Update Column Label (if applicable).

**Step 5: Review and Save**

Check back over your filters and display fields to make sure you haven't missed anything. Once you’re satisfied that everything is included, review your template name and description to make sure everything has been updated accordingly.

Now, click Save Template. You can now create reports based on your new template.

===== Sharing Reports =====

Templates and reports can be shared between libraries within a system, though they cannot be shared across systems. Only GPLS staff can share across PINES. This is because the assumptions that you make in your library or library system may be very different from the assumptions a different library or library system makes. For example, there may be variances in vocabulary, which may endanger the situation that you are using the data for, and you may be depending on a report that may not be appropriate for your system. Anything you would want shared is ideally already provided in Quick Reports, or there is a template in the provided PINES staff template folders. 

Think of sharing reports as publishing a report. Subfolders should be weeded as much as possible to rid of junk or bad reports that you may have used while testing a new report. Be mindful when naming reports as well, to ensure titles and descriptions are useful and relevant to any who may access the report(s).

//Source: {{ :reports:egreports:advanced_reporting_guide.doc |Advanced Reporting Guide}}//