1 <?xml version="1.0" encoding="utf-8"?>
\r
2 <chapter xml:id="create-template" xmlns="http://docbook.org/ns/docbook" version="5.0" xml:lang="EN"
\r
3 xmlns:xi="http://www.w3.org/2001/XInclude" xmlns:xlink="http://www.w3.org/1999/xlink">
\r
6 <title>Creating Templates</title>
\r
9 <para>Once you have created a folder, the next step in building a report is to create or clone a
\r
10 template. Templates allow you to run a report more than once without building it anew every
\r
11 time, by changing definitions to suit current requirements. For example, you can create a
\r
12 shared template that reports on circulation at a given library. Then, other libraries can
\r
13 use your template and simply select their own library when they run the report.</para>
\r
15 <para>It may take several tries to refine a report to give the output that you want. It can be
\r
16 useful to plan out your report on paper before getting started with the reporting tool.
\r
17 Group together related fields and try to identify the key fields that will help you select
\r
18 the correct <emphasis>source</emphasis>. </para>
\r
20 <para>It may be useful to create complex queries in several steps. For example, first add all
\r
21 fields from the table at the highest source level. Run a report and check to see that you
\r
22 get results that seem reasonable. Then clone the report, add any filters on fields at that
\r
23 level and run another report. Then drill down to the next table and add any required fields.
\r
24 Run another report. Add any filters at that level. Run another report. Continue until you’ve
\r
25 drilled down to all the fields you need and added all the filters. This might seem time
\r
26 consuming and you will end up cloning your initial report several times. However, it will
\r
27 help you to check the correctness of your results, and will help to debug if you run into
\r
28 problems because you will know exactly what changes caused the problem. Also consider adding
\r
29 extra fields in the intermediate steps to help you check your results for correctness. </para>
\r
31 <para>This example illustrates creating a template for circulation statistics. This is an
\r
32 example of the most basic template that you can create. The steps required to create a
\r
33 template are the same every time, but the tables chosen, how the data is transformed and
\r
34 displayed, and the filters used will vary depending on your needs. </para>
\r
36 <section xml:id="create-template-choosing">
\r
38 <title>Choosing Report Fields</title>
\r
47 <para>Click on the <guilabel>My Folder</guilabel> template folder where you want
\r
48 the template to be saved.</para>
\r
51 <alt>Screenshot of staff client report interface</alt>
\r
53 <imagedata scalefit="0" fileref="../media/create-template-1.png"/>
\r
64 <para>Click on <guilabel>Create a new Template for this
\r
65 folder</guilabel>.</para>
\r
66 <para> <mediaobject>
\r
67 <alt>Screenshot of staff client report interface</alt>
\r
69 <imagedata scalefit="0" fileref="../media/create-template-2.png"/>
\r
72 </mediaobject></para>
\r
79 <para>You can now see the template creating interface. The upper half of the
\r
80 screen is the <guilabel>Database Source Browser</guilabel>. The top left
\r
81 hand pane contains the database <guilabel>Sources</guilabel> drop-down list.
\r
82 This is the list of tables available as a starting point for your report.
\r
83 Commonly used sources are <guimenuitem>Circulation</guimenuitem> (for circ
\r
84 stats and overdue reports), <guimenuitem>ILS User</guimenuitem> (for patron
\r
85 reports), and <guimenuitem>Item</guimenuitem> (for reports on a library's
\r
89 <alt>Screenshot of staff client report interface</alt>
\r
91 <imagedata width="100%" scalefit="1"
\r
92 fileref="../media/create-template-3.png"/>
\r
97 <para>The <guilabel>Enable source nullability</guilabel> checkbox below the sources
\r
98 list is for advanced reporting and should be left unchecked by default.</para>
\r
106 <para>Select <guimenuitem>Circulation</guimenuitem> in the <guimenu>Sources
\r
107 </guimenu> dropdown menu. Note that the <guimenuitem>Core
\r
108 Sources</guimenuitem> for reporting are listed first, however it is
\r
109 possible to access all available sources at the bottom of this dropdown
\r
110 menu. You may only specify one source per template.</para>
\r
113 <alt>Screenshot of staff client report interface</alt>
\r
115 <imagedata scalefit="0" fileref="../media/create-template-4.png"/>
\r
127 <para>Click on <guilabel>Circulation</guilabel> to retrieve all the field names
\r
128 in the <guilabel>Field Name</guilabel> pane. Note that the <guilabel>Source
\r
129 Specifier</guilabel> (above the middle and right panes) shows the path
\r
130 that you took to get to the specific field. </para>
\r
131 <para> <mediaobject>
\r
132 <alt>Screenshot of staff client report interface</alt>
\r
134 <imagedata width="100%" scalefit="1"
\r
135 fileref="../media/create-template-5.png"/>
\r
138 </mediaobject></para>
\r
147 <para>Select <guimenuitem>Circ ID</guimenuitem> in the middle <guilabel>Field
\r
148 Name</guilabel> pane, and <guimenuitem>Count Distinct</guimenuitem> from
\r
149 the right <guilabel>Field Transform</guilabel> pane. The <guilabel>Field
\r
150 Transform</guilabel> pane is where you choose how to manipulate the data
\r
151 from the selected fields. You are counting the number of
\r
152 circulations.</para>
\r
153 <para><mediaobject>
\r
154 <alt>Screenshot of staff client report interface</alt>
\r
156 <imagedata width="100%" scalefit="1"
\r
157 fileref="../media/create-template-6.png"/>
\r
160 </mediaobject></para>
\r
163 <para><guilabel>Field Transforms</guilabel> have either an
\r
164 <guilabel>Aggregate</guilabel> or <guilabel>Non-Aggregate</guilabel> output
\r
165 type. See <xref linkend="create-template-transforms"/> for more about
\r
166 <guilabel>Count, Count Distinct,</guilabel> and other transform
\r
173 <para>Click <guibutton>Add Selected Fields</guibutton> underneath the
\r
174 <guilabel>Field Transform</guilabel> pane to add this field to your
\r
175 report output. Note that <guimenuitem>Circ ID</guimenuitem> now shows up in
\r
176 the bottom left hand pane under the <guilabel>Displayed Fields</guilabel>
\r
178 <para><mediaobject>
\r
179 <alt>Screenshot of staff client report interface</alt>
\r
181 <imagedata width="100%" scalefit="1"
\r
182 fileref="../media/create-template-7.png"/>
\r
185 </mediaobject></para>
\r
190 <step xml:id="create-template-step8" xreflabel="Create Templates Step 8">
\r
193 <para><guimenuitem>Circ ID</guimenuitem> will be the column header in the report
\r
194 output. You can rename default display names to something more meaningful.
\r
195 To do so in this example, select the <guimenuitem>Circ ID</guimenuitem> row
\r
196 and click <guibutton>Alter Display Header</guibutton>. </para>
\r
197 <para><mediaobject>
\r
198 <alt>Screenshot of staff client report interface</alt>
\r
200 <imagedata scalefit="0" fileref="../media/create-template-8.png"/>
\r
203 </mediaobject></para>
\r
208 <para>Double-clicking on the displayed field name is a shortcut to altering the
\r
209 display header.</para>
\r
214 <para>Type in the new column header name, for example <emphasis>Circ
\r
215 count</emphasis> and click <guibutton>OK</guibutton>. </para>
\r
216 <para><mediaobject>
\r
217 <alt>Screenshot of staff client report interface</alt>
\r
219 <imagedata scalefit="0" fileref="../media/create-template-9.png"/>
\r
222 </mediaobject></para>
\r
228 <step xml:id="create-template-add-field" xreflabel="Step">
\r
229 <para> Add other data to your report by going back to the
\r
230 <guilabel>Sources</guilabel> pane and selecting the desired fields. In this
\r
231 example, we are going to add <menuchoice>
\r
232 <guimenu>Circulating Item</guimenu>
\r
233 <guimenuitem>Shelving Location</guimenuitem>
\r
234 </menuchoice> to further refine the circulation report. </para>
\r
236 <para>In the top left hand <guilabel>Sources</guilabel> pane, expand
\r
237 <guimenuitem>Circulation</guimenuitem>. Depending on your computer you
\r
238 will either click on the + sign or on an arrow to expand the tree. </para>
\r
239 <para><mediaobject>
\r
240 <alt>Screenshot of staff client report interface</alt>
\r
242 <imagedata scalefit="0" fileref="../media/create-template-10.png"/>
\r
245 </mediaobject></para>
\r
253 <para>Click on the + or arrow to expand <guimenuitem>Circulating
\r
254 Item</guimenuitem>. Select <guimenuitem>Shelving Location</guimenuitem>. </para>
\r
255 <para> <mediaobject>
\r
256 <alt>Screenshot of staff client report interface</alt>
\r
258 <imagedata scalefit="0" fileref="../media/create-template-11.png"/>
\r
261 </mediaobject></para>
\r
264 <para>When you are creating a template take the shortest path to the field you need
\r
265 in the left hand <guilabel>Sources</guilabel> pane. Sometimes it is possible to
\r
266 find the same field name further in the file structure, but the shortest path is
\r
267 the most efficient. </para>
\r
274 <para> In the <guilabel>Field Name</guilabel> pane select
\r
275 <guimenuitem>Name</guimenuitem>. </para>
\r
276 <para> <mediaobject>
\r
277 <alt>Screenshot of staff client report interface</alt>
\r
279 <imagedata width="100%" scalefit="1"
\r
280 fileref="../media/create-template-12.png"/>
\r
283 </mediaobject></para>
\r
290 <para>In the upper right <guilabel>Field Transform</guilabel> pane, select
\r
291 <guimenuitem>Raw Data</guimenuitem> and click <guibutton>Add Selected
\r
292 Fields</guibutton>. Use <guilabel>Raw Data</guilabel> when you do not
\r
293 wish to transform field data in any manner. </para>
\r
294 <para><mediaobject>
\r
295 <alt>Screenshot of staff client report interface</alt>
\r
297 <imagedata width="100%" scalefit="1"
\r
298 fileref="../media/create-template-13.png"/>
\r
301 </mediaobject></para>
\r
307 <step xml:id="create-template-step15" xreflabel="Create Templates Step 15">
\r
310 <guilabel>Name</guilabel> will appear in the bottom left pane. Select the
\r
311 <guilabel>Name</guilabel> row and click <guibutton>Alter Display
\r
312 Header</guibutton>. </para>
\r
313 <para> <mediaobject>
\r
314 <alt>Screenshot of staff client report interface</alt>
\r
316 <imagedata scalefit="0" fileref="../media/create-template-15.png"/>
\r
319 </mediaobject></para>
\r
325 <para> Enter a new, more descriptive column header, for example,
\r
326 <emphasis>Shelving location</emphasis>. Click <guibutton>OK</guibutton>. </para>
\r
327 <para><mediaobject>
\r
328 <alt>Screenshot of staff client report interface</alt>
\r
330 <imagedata scalefit="0" fileref="../media/create-template-16.png"/>
\r
333 </mediaobject></para>
\r
339 <para> Note that the order of rows (top to bottom) will correspond to the order
\r
340 of columns (left to right) on the final report. Select <guimenuitem>Shelving
\r
341 location</guimenuitem> and click on <guibutton>Move Up</guibutton> to
\r
342 move <guimenuitem>Shelving location</guimenuitem> before <guimenuitem>Circ
\r
343 count</guimenuitem>.</para>
\r
344 <para><mediaobject>
\r
345 <alt>Screenshot of staff client report interface</alt>
\r
347 <imagedata scalefit="0" fileref="../media/create-template-17.png"/>
\r
350 </mediaobject></para>
\r
357 <para>Return to the <guilabel>Sources</guilabel> pane to add more fields to your
\r
358 template. Under <guilabel>Sources</guilabel> click
\r
359 <guilabel>Circulation</guilabel>, then select <guilabel>Check Out
\r
360 Date/Time</guilabel> from the middle <guilabel>Field Name</guilabel>
\r
362 <para> <mediaobject>
\r
363 <alt>Screenshot of staff client report interface</alt>
\r
365 <imagedata width="100%" scalefit="1" fileref="../media/create-template-19.png"/>
\r
375 <para>Select <guimenuitem>Year + Month</guimenuitem> in the right hand
\r
376 <guilabel>Field Transform</guilabel> pane and click <guibutton>Add
\r
377 Selected Fields</guibutton></para>
\r
378 <para> <mediaobject>
\r
379 <alt>Screenshot of staff client report interface</alt>
\r
381 <imagedata width="100%" scalefit="1"
\r
382 fileref="../media/create-template-20.png"/>
\r
385 </mediaobject></para>
\r
394 <guimenuitem>Check Out Date/Time</guimenuitem> will appear in the
\r
395 <guilabel>Displayed Fields</guilabel> pane. In the report it will appear
\r
396 as a year and month (YYYY-MM) corresponding to the selected tranform.</para>
\r
397 <para><mediaobject>
\r
399 <imagedata scalefit="0" fileref="../media/create-template-21.png"/>
\r
401 </mediaobject></para>
\r
405 <step xml:id="create-template-step22" xreflabel="Create Templates Step 22">
\r
407 <para>Select the <guimenuitem>Check Out Date/Time</guimenuitem> row. Click
\r
408 <guibutton>Alter Display Header</guibutton> and change the column header
\r
409 to <emphasis>Check out month</emphasis>.</para>
\r
410 <para> <mediaobject>
\r
411 <alt>Screenshot of staff client report interface</alt>
\r
413 <imagedata scalefit="0" fileref="../media/create-template-22.png"/>
\r
416 </mediaobject></para>
\r
423 <para>Move <guimenuitem>Check out month</guimenuitem> to the top of the list
\r
424 using the <guibutton>Move Up</guibutton> button, so that it will be the
\r
425 first column in an MS Excel spreadsheet or in a chart. Report output will
\r
426 sort by the first column.</para>
\r
427 <para><mediaobject>
\r
428 <alt>Screenshot of staff client report interface</alt>
\r
430 <imagedata scalefit="0" fileref="../media/create-template-23.png"/>
\r
433 </mediaobject></para>
\r
442 <para> Note the <guibutton>Change Transform</guibutton> button in the bottom left
\r
443 hand pane. It has the same function as the upper right <guilabel>Field
\r
444 Transform</guilabel> pane for fields that have already been added. </para>
\r
448 <para> <mediaobject>
\r
449 <alt>Screenshot of staff client report interface</alt>
\r
451 <imagedata scalefit="0" fileref="../media/create-template-24.png"/>
\r
454 </mediaobject></para>
\r
462 <section xml:id="create-template-filters">
\r
464 <title>Applying Filters</title>
\r
467 <para>Evergreen reports access the entire database, so to limit report output to a single
\r
468 library or library system you need to apply filters. </para>
\r
470 <para>After following the steps in the previous section you will see
\r
471 three fields in the bottom left hand <guilabel>Template Configuration</guilabel> pane.
\r
472 There are three tabs in this pane: <guilabel>Displayed Fields</guilabel> (covered in the
\r
473 previous section), <guilabel>Base Filters</guilabel> and <guilabel>Aggregate
\r
474 Filters</guilabel>. A filter allows you to return only the results that meet the
\r
475 criteria you set. </para>
\r
477 <para><guilabel>Base Filters</guilabel> apply to non-aggregate output types, while
\r
478 <guilabel>Aggregate Filters</guilabel> are used for aggregate types. In most reports you will be using the
\r
479 <guilabel>Base Filters</guilabel> tab. For more information on
\r
480 aggregate and non-aggregate types see
\r
481 <xref linkend="create-template-transforms"/>. </para>
\r
486 <para>There are many available <guilabel>operators</guilabel> when using filters. Some
\r
487 examples are <emphasis>Equals</emphasis>, <emphasis>In list</emphasis>, <emphasis>is
\r
488 NULL</emphasis>, <emphasis>Betwee</emphasis>, <emphasis>Greater than or equal
\r
489 to</emphasis>, and so on. <emphasis>In list</emphasis> is the most flexible
\r
490 operator, and in this case will allow you flexibility when running a report from this
\r
491 template. For example, it would be possible to run a report on a list of timestamps (in
\r
492 this case will be trimmed to year and month only), run a report on a single month, or
\r
493 run a report comparing two months. It is also possible to set up recurring reports to
\r
494 run at the end of each month.</para>
\r
495 <para>In this example we are going to use a <emphasis>Base Filter</emphasis> to filter out
\r
496 one library’s circulations for a specified time frame. The time frame in the template
\r
497 will be configured so that you can change it each time you run the report.</para>
\r
501 <title>Using Base Filters</title>
\r
505 <para>Select the <guilabel>Base Filters</guilabel> tab in the bottom
\r
506 <guilabel>Template Configuration</guilabel> pane. </para>
\r
511 <para> For this circulation statistics example, select <menuchoice>
\r
512 <guimenu>Circulation </guimenu>
\r
513 <guisubmenu>Check Out Date/Time</guisubmenu>
\r
514 <guimenuitem>Year + Month</guimenuitem>
\r
515 </menuchoice> and click on <guibutton>Add Selected Fields</guibutton>. You
\r
516 are going to filter on the time period. </para>
\r
517 <para><mediaobject>
\r
518 <alt>Screenshot of staff client report interface</alt>
\r
520 <imagedata width="100%" scalefit="1" fileref="../media/create-template-25.png"/>
\r
523 </mediaobject></para>
\r
528 <para>Select <guimenuitem>Check Out Date/Time</guimenuitem>. Click on
\r
529 <guibutton>Change Operator</guibutton> and select <guimenuitem>In
\r
530 list</guimenuitem> from the dropdown menu. </para>
\r
531 <para> <mediaobject>
\r
532 <alt>Screenshot of staff client report interface</alt>
\r
534 <imagedata scalefit="0" fileref="../media/create-template-26.png"/>
\r
537 </mediaobject></para>
\r
541 <step xml:id="create-template-filter-step" xreflabel="above">
\r
543 <para>To filter on the location of the circulation select <menuchoice>
\r
544 <guisubmenu>Circulation</guisubmenu>
\r
545 <guisubmenu> Circulating library</guisubmenu>
\r
546 <guimenuitem> Raw Data</guimenuitem>
\r
547 </menuchoice> and click on <guibutton>Add Selected Fields</guibutton>. </para>
\r
548 <para> <mediaobject>
\r
549 <alt>Screenshot of staff client report interface</alt>
\r
551 <imagedata width="100%" scalefit="1" fileref="../media/create-template-27.png"/>
\r
554 </mediaobject></para>
\r
559 <para>Select <guilabel>Circulating Library</guilabel> and click on
\r
560 <guibutton>Change Operator</guibutton> and select
\r
561 <guimenuitem>Equals</guimenuitem>. Note that this is a template, so the
\r
562 value for <emphasis>Equals</emphasis> will be filled out when you run the
\r
564 <para><mediaobject>
\r
565 <alt>Screenshot of staff client report interface</alt>
\r
567 <imagedata scalefit="0" fileref="../media/create-template-28.png"/>
\r
570 </mediaobject></para>
\r
573 <para> For multi-branch libraries, you would select <emphasis>Circulating
\r
574 Library</emphasis> with <emphasis>In list</emphasis> as the operator, so you
\r
575 could specify the branch(es) when you run the report. This leaves the template
\r
576 configurable to current requirements. In comparison, sometimes you will want to
\r
577 hardcode true/false values into a template. For example, deleted bibliographic
\r
578 records remain in the database, so perhaps you want to hardcode deleted=false,
\r
579 so that deleted records don’t show up in the results. You might want to use
\r
580 deleted=true, for a template for a report on deleted items in the last month.
\r
586 <para>Once you have configured your template, you must name and save it. Name
\r
587 this template <emphasis>Circulations by month for one library</emphasis>.
\r
588 You can also add a description. In this example, the title is descriptive
\r
589 enough, so a description is not necessary. Click
\r
590 <guibutton>Save</guibutton>. </para>
\r
591 <para><mediaobject>
\r
592 <alt>Screenshot of staff client report interface</alt>
\r
594 <imagedata scalefit="0" fileref="../media/create-template-29.png"/>
\r
597 </mediaobject></para>
\r
603 <para> Click <guibutton>OK</guibutton>. </para>
\r
604 <para><mediaobject>
\r
605 <alt>Screenshot of staff client report interface</alt>
\r
607 <imagedata scalefit="0" fileref="../media/create-template-30.png"/>
\r
610 </mediaobject></para>
\r
616 <para> You will get a confirmation dialogue box that the template was
\r
617 successfully saved. Click <guibutton>OK</guibutton>. </para>
\r
618 <para><mediaobject>
\r
619 <alt>Screenshot of staff client report interface</alt>
\r
621 <imagedata scalefit="0" fileref="../media/create-template-31.png"/>
\r
624 </mediaobject></para>
\r
630 <para> After saving it is not possible to edit a template. To make changes you will need to
\r
631 clone it and edit the clone</para>
\r
637 <para> The bottom right hand pane is also a source specifier. By selecting one of
\r
638 these rows you will limit the fields that are visible to the sources you have
\r
639 specified. This may be helpful when reviewing templates with many fields. Use <keycombo>
\r
640 <keycap>Ctrl</keycap>
\r
641 <mousebutton>Click</mousebutton>
\r
642 </keycombo> to select or deselect items. </para>
\r
644 <para> <mediaobject>
\r
646 <imagedata scalefit="0" fileref="../media/create-template-32.png"/>
\r
649 </mediaobject></para>
\r