From c3e3c2923cd8036c977032c5787fb6c20f2baa54 Mon Sep 17 00:00:00 2001 From: Robert Soulliere Date: Thu, 19 Jul 2012 13:23:18 -0400 Subject: [PATCH] Documentation: Migrate reports documentation from 2.1 Content reviewed by Jenny Turner. I converted to asciidoc and tested processing. I also added index terms. Signed-off-by: Robert Soulliere --- docs/reports/reporter_add_data_source.txt | 239 +++++++++++++++++ docs/reports/reporter_cloning_shared_templates.txt | 42 +++ docs/reports/reporter_create_templates.txt | 291 +++++++++++++++++++++ docs/reports/reporter_daemon.txt | 64 +++++ docs/reports/reporter_export_usingpgAdmin.txt | 56 ++++ docs/reports/reporter_folder.txt | 76 ++++++ docs/reports/reporter_generating_reports.txt | 87 ++++++ .../reports/reporter_running_recurring_reports.txt | 42 +++ docs/reports/reporter_template_terminology.txt | 107 ++++++++ docs/reports/reporter_view_output.txt | 41 +++ docs/root.txt | 20 ++ 11 files changed, 1065 insertions(+) create mode 100644 docs/reports/reporter_add_data_source.txt create mode 100644 docs/reports/reporter_cloning_shared_templates.txt create mode 100644 docs/reports/reporter_create_templates.txt create mode 100644 docs/reports/reporter_daemon.txt create mode 100644 docs/reports/reporter_export_usingpgAdmin.txt create mode 100644 docs/reports/reporter_folder.txt create mode 100644 docs/reports/reporter_generating_reports.txt create mode 100644 docs/reports/reporter_running_recurring_reports.txt create mode 100644 docs/reports/reporter_template_terminology.txt create mode 100644 docs/reports/reporter_view_output.txt diff --git a/docs/reports/reporter_add_data_source.txt b/docs/reports/reporter_add_data_source.txt new file mode 100644 index 0000000000..199314a572 --- /dev/null +++ b/docs/reports/reporter_add_data_source.txt @@ -0,0 +1,239 @@ +Adding Data Sources to Reporter +------------------------------- + +indexterm:[reports, adding data sources] + +You can further customize your Evergreen reporting environment by adding +additional data sources. + +The Evergreen reporter module does not build and execute SQL queries directly, +but instead uses a data abstraction layer called *Fieldmapper* to mediate queries +on the Evergreen database.Fieldmapper is also used by other core Evergreen DAO +services, including cstore and permacrud. The configuration file _fm_IDL.xml_ +contains the mapping between _Fieldmapper_ class definitions and the database. +The _fm_IDL.xml_ file is located in the _/openils/conf_ directory. + +indexterm:[fm_IDL.xml] + +There are 3 basic steps to adding a new data source. Each step will be discussed +in more detail in the + +. Create a PostgreSQL query, view, or table that will provide the data for your +data source. +. Add a new class to _fm_IDL.xml_ for your data source. +. Restart the affected services to see the new data source in Reporter. + +There are two possbile sources for new data sources: + +indexterm:[PostgreSQL] + +indexterm:[SQL] + +* An SQL query built directly into the class definition in _fm_IDL.xml_. You can +use this method if you are only going to access this data source through the +Evergreen reporter and/or cstore code that you write. +* A new table or view in the Evergreen PostgresSQL database on which a class +definition in _fm_IDL.xml_. You can use this method if you want to be able to +access this data source through directly through SQL or using other reporting tool. + +Create a PostgreSQL query, view, or table for your data source +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +indexterm:[PostgreSQL] + +You need to decide whether you will create your data source as a query, a view, +or a table. + +. Create a query if you are planning to access this data source only through the +Evergreen reporter and/or cstore code that you write. You will use this query to +create an IDL only view. +. Create a view if you are planning to access this data source through other +methods in addition to the Evergreen reporter, or if you may need to do +performance tuning to optimize your query. +. You may also need to use an additional table as part of your data source if +you have additional data that's not included in the base Evergreen, or if you +need to use a table to store the results of a query for performance reasons. + +To develop and test queries, views, and tables, you will need + +* Access to the Evergree PostgreSQL database at the command line. This is +normally the psql application. You +can access the Postgres documentation at the +http://http://www.postgresql.org/docs/[Official Postgres documentation] for +more information about PostgreSQL. +* Knowledge of the Evergreen database structure for the data that you want to +access. You can find this information by looking at the Evergreen schema +http://docs.evergreen-ils.org/2.2/schema/[Evergreen schema] + +indexterm:[database schema] + +If the views that you are creating are purely local in usage and are not intended +for contribution to the core Evergreen code, create the Views and Tables in the +extend_reporter schema. This schema is intended to be used for local +customizations and will not be modified during upgrades to the Evergreen system. + +You should make that you have an appropriate version control pocess for the SQL +used to create you data sources. + +Here's an example of a view created to incorporate some locally defined user +statistical categories: + +.example view for reports +---------- +create view extend_reporter.patronstats as +select u.id, +grp.name as "ptype", +rl.stat_cat_entry as "reg_lib", +gr.stat_cat_entry as "gender", +ag.stat_cat_entry as "age_group", +EXTRACT(YEAR FROM age(u.dob)) as "age", +hl.id as "home_lib", +u.create_date, +u.expire_date, +ms_balance_owed +from actor.usr u +join permission.grp_tree grp + on (u.profile = grp.id and (grp.parent = 2 or grp.name = 'patron')) +join actor.org_unit hl on (u.home_ou = hl.id) +left join money.open_usr_summary ms + on (ms.usr = u.id) +left join actor.stat_cat_entry_usr_map rl + on (u.id = rl.target_usr and rl.stat_cat = 4) +left join actor.stat_cat_entry_usr_map bt + on (u.id = bt.target_usr and bt.stat_cat = 3) +left join actor.stat_cat_entry_usr_map gr + on (u.id = gr.target_usr and gr.stat_cat = 2) +left join actor.stat_cat_entry_usr_map gr + on (u.id = gr.target_usr and gr.stat_cat = 2) +left join actor.stat_cat_entry_usr_map ag + on (u.id = ag.target_usr and ag.stat_cat = 1) +where u.active = 't' and u.deleted <> 't'; +----------- + +Add a new class to fm_IDL.xml for your data source +-------------------------------------------------- + +Once you have your data source, the next step is to add that data source as a +new class in _fm_IDL.xml_. + +indexterm:[fm_IDL.xml] + +You will need to add the following attributes for the class definition + +* *id*. You should follow a consistent naming convention for your class names +that won't create conflicts in the future with any standard classes added in +future upgrades. Evergreen normally names each class with the first letter of +each word in the schema and table names. You may want to add a local prefix or +suffix to your local class names. +* *controller=”open-ils.cstore”* +* *oils_obj:fieldmapper=”extend_reporter::long_name_of_view”* +* *oils_persist.readonly=”true”* +* *reporter:core=”true”* (if you want this to show up as a “core” reporting source) +* *reporter:label*. This is the name that will appear on the data source list in +the Evergreen reporter. +* *oils_persist:source_definition*. If this is an IDL-only view, add the SQL query +here. You don't need this attribute if your class is based on a PostgreSQL view +or table. +* *oils_persist:tablename="schemaname.viewname or tablename"* If this class is +based on a PostgreSQL view or table, add the table name here. You don't need +this attribute is your class is an IDL-only view. + +For each column in the view or query output, add field element and set the +following attributes. The fields should be wrapped with _ _ + +* *reporter:label*. This is the name that appears in the Evergreen reporter. +* *name*. This should match the column name in the view or query output. +* *reporter:datatype* (which can be id, bool, money, org_unit, int, number, +interval, float, text, timestamp, or link) + +For each linking field, add a link element with the following attributes. The +elements should be wrapped with _ _ +* *field* (should match field.name) +* *reltype* (“has_a”, “might_have”, or “has_many”) +* *map* (“”) +* *key* (name of the linking field in the foreign table) +* *class* (ID of the IDL class of the table that is to be linked to) + +The following example is a class definition for the example view that was created +in the previous section. + +.example class definition for reports +---------- + + + + + + + + + + + + + + + + + + + + + +--------- + +NOTE: _fm_IDL.xml_ is used by other core Evergreen DAO services, including cstore +and permacrud. So changes to this file can affect the entire Evergreen +application, not just reporter. After making changes fm_IDL.xml, it is a good +idea to ensure that it is valid XML by using a utility such as *xmllint* – a +syntax error can render much of Evergreen nonfunctional. Set up a good change +control system for any changes to fm_IDL.xml. You will need to keep a separate +copy of you local class definitions so that you can reapply the changes to +_fm_IDL.xml_ after Evergreen upgrades. + +Restart the affected services to see the new data source in the reporter +------------------------------------------------------------------------ + +The following steps are needed to for Evergreen to recognize the changes to +_fm_IDL.xml_ + +. Copy the updated _fm_IDL.xml_ Update _/openils/conf/fm_IDL.xml_ to +_/openils/var/web/reports/fm_IDL.xml_ ++ +------------- +cp _/openils/conf/fm_IDL.xml /openils/var/web/reports/fm_IDL.xml +------------- ++ +. Run Autogen to to update the Javascript versions of the fieldmapper definitions. ++ +------------- +/openils/bin/autogen.sh +------------- ++ +. Restart C services ++ +------------- +osrf_ctl.sh -l -a restart_c +------------- ++ +. Restart the Evergreen reporter. You may need to modify this command depending +on your system configuration and pid path ++ +------------ +opensrf-perl.pl -l -action restart -service open-ils.reporter \ +-config /openils/conf/opensrf_core.xml -pid-dir /openils/var/run +------------ ++ +. Restart the Evergreen application or _use Admin --> For Developers --> Clear +Cache_ + + + diff --git a/docs/reports/reporter_cloning_shared_templates.txt b/docs/reports/reporter_cloning_shared_templates.txt new file mode 100644 index 0000000000..f4cfc68f16 --- /dev/null +++ b/docs/reports/reporter_cloning_shared_templates.txt @@ -0,0 +1,42 @@ +Cloning Shared Templates +------------------------ + +indexterm:[reports, cloning] + +This chapter describes how to make local copies of shared templates for routine +reports or as a starting point for customization. When creating a new template +it is a good idea to review the shared templates first: even if the exact +template you need does not exist it is often faster to modify an existing +template than to build a brand new one. A Local System Administrator account is +required to clone templates from the _Shared Folders_ section and save them to _My +Folders_. + +The steps below assume you have already created at least one _Templates_ folder. +If you haven’t done this, please see <>. + +. Access the reports interface from the _Admin_ (-) menu under _Local +Administration --> Reports_ +. Under _Shared Folders_ expand the _Templates_ folder and the subfolder of the +report you wish to clone. To expand the folders click on the grey arrow or +folder icon. Do not click on the blue underlined hyperlink. +. Click on the subfolder. +. Select the template you wish to clone. From the dropdown menu choose _Clone +selected templates_, then click _Submit_. ++ +NOTE: By default Evergreen only displays the first 10 items in any folder. To view +all content, change the Limit output setting from 10 to All. ++ +. Choose the folder where you want to save the cloned template, then click +_Select Folder_. Only template folders created with your account will be visible. +If there are no folders to choose from please see +<>. + +. The cloned template opens in the template editor. From here you may modify +the template by adding, removing, or editing fields and filters as described in +<>. _Template Name_ and +_Description_ can also be edited. When satisfied with your changes click _Save_. + +. Click _OK_ in the resulting confirmation windows. + +Once saved it is not possible to edit a template. To make changes, clone a +template and change the clone. diff --git a/docs/reports/reporter_create_templates.txt b/docs/reports/reporter_create_templates.txt new file mode 100644 index 0000000000..5b3dc3b2ce --- /dev/null +++ b/docs/reports/reporter_create_templates.txt @@ -0,0 +1,291 @@ +[[reporter_creating_templates]] +Creating Templates +------------------ + +indexterm:[reports, creating templates] + +Once you have created a folder, the next step in building a report is to create +or clone a template. Templates allow you to run a report more than once without +building it anew every time, by changing definitions to suit current +requirements. For example, you can create a shared template that reports on +circulation at a given library. Then, other libraries can use your template and +simply select their own library when they run the report. + +It may take several tries to refine a report to give the output that you want. +It can be useful to plan out your report on paper before getting started with +the reporting tool. Group together related fields and try to identify the key +fields that will help you select the correct source. + +It may be useful to create complex queries in several steps. For example, first +add all fields from the table at the highest source level. Run a report and check +to see that you get results that seem reasonable. Then clone the report, add any +filters on fields at that level and run another report. Then drill down to the +next table and add any required fields. Run another report. Add any filters at +that level. Run another report. Continue until you’ve drilled down to all the +fields you need and added all the filters. This might seem time consuming and +you will end up cloning your initial report several times. However, it will help +you to check the correctness of your results, and will help to debug if you run +into problems because you will know exactly what changes caused the problem. +Also consider adding extra fields in the intermediate steps to help you check +your results for correctness. + +This example illustrates creating a template for circulation statistics. This is +an example of the most basic template that you can create. The steps required to +create a template are the same every time, but the tables chosen, how the data +is transformed and displayed, and the filters used will vary depending on your +needs. + +Choosing Report Fields +~~~~~~~~~~~~~~~~~~~~~~ + +indexterm:[reports, creating templates, choosing reports fields] + +. Click on the My Folder template folder where you want the template to be saved. ++ +image::media/create-template-1.png[create-template-1] ++ +. Click on Create a new Template for this folder. ++ +image::media/create-template-2.png[create-template-2] ++ +. You can now see the template creating interface. The upper half of the screen +is the _Database Source Browser_. The top left hand pane contains the database +_Sources_ drop-down list. This is the list of tables available as a starting point +for your report. Commonly used sources are _Circulation_ (for circ stats and +overdue reports), _ILS User_ (for patron reports), and _Item_ (for reports on a +library's holdings). ++ +image::media/create-template-3.png[create-template-3] ++ +The Enable source nullability checkbox below the sources list is for advanced +reporting and should be left unchecked by default. ++ +. Select _Circulation_ in the _Sources_ dropdown menu. Note that the _Core +Sources_ for reporting are listed first, however it is possible to access all +available sources at the bottom of this dropdown menu. You may only specify one +source per template. ++ +image::media/create-template-4.png[create-template-4] ++ +. Click on _Circulation_ to retrieve all the field names in the Field Name pane. +Note that the _Source_ Specifier (above the middle and right panes) shows the +path that you took to get to the specific field. ++ +image::media/create-template-5.png[create-template-5] ++ +. Select _Circ ID_ in the middle _Field Name_ pane, and _Count Distinct_ from the +right _Field Transform_ pane. The _Field Transform_ pane is where you choose how +to manipulate the data from the selected fields. You are counting the number of +circulations. ++ +indexterm:[reports, field transform] ++ +image::media/create-template-6.png[create-template-6] ++ +_Field Transforms_ have either an _Aggregate_ or _Non-Aggregate_ output type. +See the section called <> for more about +_Count, _Count Distinct_, and other transform options. ++ +. Click _Add Selected Fields_ underneath the _Field Transform_ pane to add this +field to your report output. Note that _Circ ID_ now shows up in the bottom left +hand pane under the _Displayed Fields_ tab. ++ +image::media/create-template-7.png[create-template-7] ++ +. _Circ ID_ will be the column header in the report output. You can rename +default display names to something more meaningful. To do so in this example, +select the _Circ ID_ row and click _Alter Display Header_. ++ +image::media/create-template-8.png[create-template-8] ++ +Double-clicking on the displayed field name is a shortcut to altering the +display header. ++ +. Type in the new column header name, for example _Circ count_ and click _OK_. ++ +image::media/create-template-9.png[create-template-9] ++ +. Add other data to your report by going back to the _Sources_ pane and selecting +the desired fields. In this example, we are going to add _Circulating Item --> +Shelving Location_ to further refine the circulation report. ++ +In the top left hand _Sources_ pane, expand _Circulation_. Depending on your +computer you will either click on the _+_ sign or on an arrow to expand the tree. ++ +image::media/create-template-10.png[create-template-10] ++ +Click on the _+_ or arrow to expand _Circulating Item_. Select +_Shelving Location_. ++ +image::media/create-template-11.png[create-template-11] ++ +When you are creating a template take the shortest path to the field you need in +the left hand Sources pane. Sometimes it is possible to find the same field name +further in the file structure, but the shortest path is the most efficient. ++ +In the Field Name pane select Name. ++ +image::media/create-template-12.png[create-template-12] ++ +In the upper right _Field Transform_ pane, select _Raw Data_ and click _Add Selected_ +Fields. Use _Raw Data_ when you do not wish to transform field data in any manner. ++ +image::media/create-template-13.png[create-template-13] ++ +Name will appear in the bottom left pane. Select the Name row and click _Alter +Display Header_. ++ +image::media/create-template-15.png[create-template-15] ++ +. Enter a new, more descriptive column header, for example, _Shelving location_. +Click _OK_. ++ +image::media/create-template-16.png[create-template-16] ++ +. Note that the order of rows (top to bottom) will correspond to the order of +columns (left to right) on the final report. Select _Shelving location_ and click +on _Move Up_ to move _Shelving location_ before _Circ count_. ++ +image::media/create-template-17.png[create-template-17] ++ +. Return to the _Sources_ pane to add more fields to your template. Under +_Sources_ click _Circulation_, then select _Check Out Date/Time_ from the middle +_Field Name_ pane. ++ +image::media/create-template-19.png[create-template-19] ++ +. Select _Year + Month_ in the right hand _Field Transform_ pane and click _Add +Selected Fields_ ++ +image::media/create-template-20.png[create-template-20] ++ +. _Check Out Date/Time_ will appear in the _Displayed Fields_ pane. In the report +it will appear as a year and month _(YYYY-MM)_ corresponding to the selected tranform. ++ +image::media/create-template-21.png[create-template-21] ++ +. Select the _Check Out Date/Time_ row. Click _Alter Display Header_ and change +the column header to _Check out month_. ++ +image::media/create-template-22.png[create-template-22] ++ +. Move _Check out month_ to the top of the list using the _Move Up_ button, so +that it will be the first column in an MS Excel spreadsheet or in a chart. +Report output will sort by the first column. + +image::media/create-template-23.png[create-template-23] + +[NOTE] +====== +Note the _Change Transform_ button in the bottom left hand pane. It has the same +function as the upper right _Field Transform_ pane for fields that have already +been added. + +image::media/create-template-24.png[create-template-24] +====== + + +Applying Filters +~~~~~~~~~~~~~~~~ + +indexterm:[reports, applying filters] + +Evergreen reports access the entire database, so to limit report output to a +single library or library system you need to apply filters. + +After following the steps in the previous section you will see three fields in +the bottom left hand _Template Configuration_ pane. There are three tabs in this +pane: _Displayed Fields_ (covered in the previous section), _Base Filters_ and +_Aggregate Filters_. A filter allows you to return only the results that meet +the criteria you set. + +indexterm:[reports, applying filters, base filter] + +indexterm:[reports, applying filters, aggregate filters] + +_Base Filters_ apply to non-aggregate output types, while _Aggregate Filters_ are +used for aggregate types. In most reports you will be using the _Base Filters_ tab. +For more information on aggregate and non-aggregate types see the section called +“Field Transforms”. + +There are many available operators when using filters. Some examples are _Equals_, +_In list_, is _NULL_, _Between_, _Greater than_ or _equal to_, and so on. _In list_ +is the most flexible operator, and in this case will allow you flexibility when +running a report from this template. For example, it would be possible to run a +report on a list of timestamps (in this case will be trimmed to year and month +only), run a report on a single month, or run a report comparing two months. It +is also possible to set up recurring reports to run at the end of each month. + +In this example we are going to use a Base Filter to filter out one library’s +circulations for a specified time frame. The time frame in the template will be +configured so that you can change it each time you run the report. + +Using Base Filters +^^^^^^^^^^^^^^^^^^ + +indexterm:[reports, applying filters, base filter] + +. Select the _Base Filters_ tab in the bottom _Template Configuration_ pane. + +. For this circulation statistics example, select _Circulation --> Check Out +Date/Time --> Year + Month_ and click on _Add Selected Fields_. You are going to +filter on the time period. ++ +image::media/create-template-25.png[create-template-25] ++ +. Select _Check Out Date/Time_. Click on _Change Operator_ and select _In list_ +from the dropdown menu. ++ +image::media/create-template-26.png[create-template-26] ++ +. To filter on the location of the circulation select +_Circulation --> Circulating library --> Raw Data_ and click on _Add Selected Fields_. ++ +image::media/create-template-27.png[create-template-276] ++ +. Select _Circulating Library_ and click on _Change Operator_ and select _Equals_. +Note that this is a template, so the value for _Equals_ will be filled out when +you run the report. ++ +image::media/create-template-28.png[create-template-28] ++ +For multi-branch libraries, you would select _Circulating Library_ with _In list_ +as the operator, so you could specify the branch(es) when you run the report. This +leaves the template configurable to current requirements. In comparison, sometimes +you will want to hardcode true/false values into a template. For example, deleted +bibliographic records remain in the database, so perhaps you want to hardcode +_deleted=false_, so that deleted records don’t show up in the results. You might +want to use _deleted=true_, for a template for a report on deleted items in the +last month. ++ +. Once you have configured your template, you must name and save it. Name this +template _Circulations by month for one library_. You can also add a description. +In this example, the title is descriptive enough, so a description is not necessary. +Click _Save_. ++ +image::media/create-template-29.png[create-template-29] ++ +. Click _OK_. ++ +image::media/create-template-30.png[create-template-30] ++ +. You will get a confirmation dialogue box that the template was successfully +saved. Click OK. ++ +image::media/create-template-31.png[create-template-31] ++ +After saving it is not possible to edit a template. To make changes you will +need to clone it and edit the clone + +[NOTE] +========== +The bottom right hand pane is also a source specifier. By selecting one of these +rows you will limit the fields that are visible to the sources you have specified. +This may be helpful when reviewing templates with many fields. Use *Ctrl+Click* to +select or deselect items. + +image::media/create-template-32.png[create-template-32] +========== + + + diff --git a/docs/reports/reporter_daemon.txt b/docs/reports/reporter_daemon.txt new file mode 100644 index 0000000000..c5d784ba01 --- /dev/null +++ b/docs/reports/reporter_daemon.txt @@ -0,0 +1,64 @@ +Starting and Stopping the Reporter Daemon +----------------------------------------- + +indexterm:[reports, starting server application] + +indexterm:[reporter, starting daemon] + +Before you can view reports, the Evergreen administrator must start +the reporter daemon from the command line of the Evergreen server. + +The reporter daemon periodically checks for requests for new reports or +scheduled reports and gets them running. + +Starting the Reporter Daemon +~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +indexterm:[reporter, starting] + +To start the reporter daemon, run the following command as the opensrf user: + +---- +clark-kent.pl --daemon +---- + +You can also specify other options: + +* *sleep=interval*: number of seconds to sleep between checks for new reports to +run; defaults to 10 +* *lockfile=filename*: where to place the lockfile for the process; defaults to +/tmp/reporter-LOCK +* *concurrency=integer*: number of reporter daemon processes to run; defaults to +1 +* *boostrap=filename*: OpenSRF bootstrap configuration file; defaults to +/openils/conf/opensrf_core.xml + + +[NOTE] +============= +The open-ils.reporter process must be running and enabled on the gateway before +the reporter daemon can be started. + +Remember that if the server is restarted, the reporter daemon will need to be +restarted before you can view reports unless you have configured your server to +start the daemonautomatically at start up time. +============== + +Stopping the Reporter Daemon +~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +indexterm:[reports, stopping server application] + +indexterm:[reporter, stopping daemon] + +To stop the reporter daemon, you have to kill the process and remove the +lockfile. Assuming you're running just a single process and that the +lockfile is in the default location, perform the following commands as the +opensrf user: + +---- +kill `ps wax | grep "Clark Kent" | grep -v grep | cut -b1-6` + +rm /tmp/reporter-LOCK +---- + diff --git a/docs/reports/reporter_export_usingpgAdmin.txt b/docs/reports/reporter_export_usingpgAdmin.txt new file mode 100644 index 0000000000..8c22c7eb71 --- /dev/null +++ b/docs/reports/reporter_export_usingpgAdmin.txt @@ -0,0 +1,56 @@ +Exporting Report Templates Using phpPgAdmin +------------------------------------------- + +indexterm:[reports, exporting templates] + +Once the data is exported. Database Administrators/Systems Administrators can +easily import this data into the templates folder to make it available in the +client. + +Dump the Entire Reports Template Table +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +The data exported in this method can create issues importing into a different +system if you do not have a matching folder and owner. This is going to export +report templates created in your system. The most important fields for importing +into the new system are _name_, _description_, and _data_. Data defines the actual +structure of the report. The _owner_ and _folder_ fields will unique to the system +they were exported from and will have to be altered to ensure they match the +appropriate owner and folder information for the new system. + +. Go to the *Reporter* schema. Report templates are located in the *Template* table +. Click on the link to the *Template* table +. Click the *export* button at the top right of the phpPgAdmin screen +. Make sure the following is selected +.. _Data Only_ (checked) +.. _Format_: Select _CSV_ or _Tabbed_ did get the data in a text format +.. _Download_ checked +. Click _export_ button at the bottom +. A text file will download to your local system + +Dump Data with an SQL Statement +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + + +The following statement could be used to grab the data in the folder and dump it +with admin account as the owner and the first folder in your system. + +------------- +SELECT 1 as owner, name, description, data, 1 as folder FROM reporter.template +------------- + +or use the following to capture your folder names for export + +-------------- +SELECT 1 as owner, t.name, t.description, t.data, f.name as folder + FROM reporter.template t + JOIN reporter.template_folder f ON t.folder=f.id +-------------- + +. Run the above query +. Click the *download* link at the bottom of the page +. Select the file format (_CSV_ or _Tabbed_) +. Check _download_ +. A text file with the report template data will be downloaded. + + diff --git a/docs/reports/reporter_folder.txt b/docs/reports/reporter_folder.txt new file mode 100644 index 0000000000..d9b8d92fc8 --- /dev/null +++ b/docs/reports/reporter_folder.txt @@ -0,0 +1,76 @@ +[[reporter_folders]] +Folders +------- + +indexterm:[reports, folders] + +There are three main components to reports: _Templates_, _Reports_, and _Output_. +Each of these components must be stored in a folder. Folders can be private +(accessible to your login only) or shared with other staff at your library, +other libraries in your system or consortium. It is also possible to selectively +share +only certain folders and/or subfolders. + +There are two parts to the folders pane. The _My Folders_ section contains folders +created with your Evergreen account. Folders that other users have shared with +you appear in the _Shared Folders_ section under the username of the sharing +account. + +image::media/folder-1.png[folder-1] + +[[reporter_creating_folders]] +Creating Folders +~~~~~~~~~~~~~~~~ + + +indexterm:[reports, folders, creating] + +Whether you are creating a report from scratch or working from a shared template +you must first create at least one folder. + +The steps for creating folders are similar for each reporting function. It is +easier to create folders for templates, reports, and output all at once at the +beginning, though it is possible to do it before each step. This example +demonstrates creating a folder for a template. + +. Click on _Templates_ in the _My Folders_ section. +. Name the folder. Select _Share_ or _Do not share_ from the dropdown menu. +. If you want to share your folder, select who you want to share this folder +with from the dropdown menu. +. Click _Create Sub Folder_. +. Click _OK_. +. Next, create a folder for the report definition to be saved to. Click on +_Reports_. +. Repeat steps 2-5 to create a Reports folder also called _Circulation_. +. Finally, you need to create a folder for the report’s output to be saved in. +Click on _Output_. +. Repeat steps 2-5 to create an Output folder named _Circulation_. + + +TIP: Using a parallel naming scheme for folders in Templates, Reports, +and Output helps keep your reports organized and easier to find + +The folders you just created will now be visible by clicking the arrows in _My +Folders_. Bracketed after the folder name is whom the folder is shared with. For +example, _Circulation (BNCLF)_ is shared with the North Coast Library Federation. +If it is not a shared folder there will be nothing after the folder name. You +may create as many folders and sub-folders as you like. + +Managing Folders +~~~~~~~~~~~~~~~~ + +indexterm:[reports, folders, managing] + +Once a folder has been created you can change the name, delete it, create a new +subfolder, or change the sharing settings. This example demonstrates changing a +folder name; the other choices follow similar steps + +. Click on the folder that you wish to rename. +. Click _Manage Folder_. +. Select _Change folder name_ from the dropdown menu and click _Go_. +. Enter the new name and click _Submit_. +. Click _OK_. +. You will get a confirmation box that the _Action Succeeded_. Click _OK_. + + + diff --git a/docs/reports/reporter_generating_reports.txt b/docs/reports/reporter_generating_reports.txt new file mode 100644 index 0000000000..fc30bad510 --- /dev/null +++ b/docs/reports/reporter_generating_reports.txt @@ -0,0 +1,87 @@ +[[generating_reports]] +Generating Reports from Templates +---------------------------------- + +indexterm:[reports, generating] + +Now you are ready to run the report from the template you have created. + +. In the My Folders section click the arrow next to _Templates_ to expand this +folder and select _circulation_. ++ +image::media/generate-report-1.png[generate-report-1] ++ +. Select the box beside _Circulations by month for one library_. Select _Create a +new report_ from selected template from the dropdown menu. Click _Submit_. ++ +image::media/generate-report-2.png[generate-report-2] ++ +. Complete the first part of report settings. Only _Report Name_ and _Choose a +folder_... are required fields. ++ +image::media/generate-report-3.png[generate-report-3] ++ +1) _Template Name_, _Template Creator_, and _Template Description_ are for +informational purposes only. They are hard coded when the template is created. +At the report definition stage it is not possible to change them. ++ +2) _Report Name_ is required. Reports stored in the same folder must have unique +names. ++ +3) _Report Description_ is optional but may help distinguish among similar +reports. ++ +4) _Report Columns_ lists the columns that will appear in the output. This is +derived from the template and cannot be changed during report definition. ++ +5) _Pivot Label Column_ and _Pivot Data Column_ are optional. Pivot tables are a +different way to view data. If you currently use pivot tables in MS Excel it is +better to select an Excel output and continue using pivot tables in Excel. ++ +6) You must choose a report folder to store this report definition. Only report +folders under My Folders are available. Click on the desired folder to select it. ++ +. Select values for the _Circulation > Check Out Date/Time_. Use the calendar +widget or manually enter the desired dates, then click Add to include the date +on the list. You may add multiple dates. ++ +image::media/generate-report-8.png[generate-report-8] ++ +The Transform for this field is Year + Month, so even if you choose a specific +date (2009-10-20) it will appear as the corresponding month only (2009-10). ++ +It is possible to select *relative dates*. If you select a relative date 1 month +ago you can schedule reports to automatically run each month. If you want to run +monthly reports that also show comparative data from one year ago, select a +relative date 1 month ago, and 13 months ago. ++ +. Select a value for the _Circulating Library_. +. Complete the bottom portion of the report definition interface, then click +_Save_. ++ +image::media/generate-report-10.png[generate-report-10] ++ +1) Select one or more output formats. In this example the report output will be +available as an Excel spreadsheet, an HTML table (for display in the staff +client or browser), and as a bar chart. ++ +2) If you want the report to be recurring, check the box and select the +_Recurrence Interval_ as described in <>. +In this example, as this is a report that will only be run once, the _Recurring +Report_ box is not checked. ++ +3) Select _Run_ as soon as possible for immediate output. It is also possible to +set up reports that run automatically at future intervals. ++ +4) It is optional to fill out an email address where a completion notice can be +sent. The email will contain a link to password-protected report output (staff +login required). If you have an email address in your Local System Administrator +account it will automatically appear in the email notification box. However, +you can enter a different email address or multiple addresses separated by commas. ++ +. Select a folder for the report's output. +. You will get a confirmation dialogue box that the Action Succeeded. Click _OK_. ++ +image::media/generate-report-14.png[generate-report-14] ++ +Once saved, reports stay there forever unless you delete them. diff --git a/docs/reports/reporter_running_recurring_reports.txt b/docs/reports/reporter_running_recurring_reports.txt new file mode 100644 index 0000000000..d68467ebf5 --- /dev/null +++ b/docs/reports/reporter_running_recurring_reports.txt @@ -0,0 +1,42 @@ +[[recurring_reports]] +Running Recurring Reports +------------------------- + +indexterm:[reports, recurring] + +Recurring reports are a useful way to save time by scheduling reports that you +run on a regular basis, such as monthly circulation and monthly patron +registration statistics. When you have set up a report to run on a monthly basis +you’ll get an email informing you that the report has successfully run. You can +click on a link in the email that will take you directly to the report output. +You can also access the output through the reporter interface as described in +<>. + +To set up a monthly recurring report follow the procedure in <> but make the changes described below. + +. Select the Recurring Report check-box and set the recurrence interval to 1 month. +. Do not select Run ASAP. Instead schedule the report to run early on the first +day of the next month. Enter the date in _YYYY-MM-DD_ format. +. Ensure there is an email address to receive completion emails. You will +receive an email completion notice each month when the output is ready. +. Select a folder for the report’s output. +. Click Save Report. +. You will get a confirmation dialogue box that the Action Succeeded. Click OK. + +You will get an email on the 1st of each month with a link to the report output. +By clicking this link it will open the output in a web browser. It is still +possible to login to the staff client and access the output in Output folder. + +*How to stop or make changes to an existing recurring report?* Sometimes you may +wish to stop or make changes to a recurring report, e.g. the recurrence interval, +generation date, email address to receive completion email, output format/folder +or even filter values (such as the number of days overdue). You will need to +delete the current report from the report folder, then use the above procedure +to set up a new recurring report with the desired changes. Please note that +deleting a report also deletes all output associated with it. + +TIP: Once you have been on Evergreen for a year, you could set up your recurring +monthly reports to show comparative data from one year ago. To do this select +relative dates of 1 month ago and 13 months ago. + diff --git a/docs/reports/reporter_template_terminology.txt b/docs/reports/reporter_template_terminology.txt new file mode 100644 index 0000000000..5158266c58 --- /dev/null +++ b/docs/reports/reporter_template_terminology.txt @@ -0,0 +1,107 @@ +Template Terminology +-------------------- + +Data Types +~~~~~~~~~~ + +indexterm:[reports, data types] + +The central column of the _Database Source Browser_ lists _Field Name_ and _Data +Type_ for the selected database table. + ++ +image::media/view-output-2.png[view-output-2] ++ + +Each data type has its own characteristics and uses: + +[options="header,footer"] +|==================================== +|Data Type |Description |Notes +|id |Unique number assigned by the database to identify a +record |A number that is a meaningful reference for the database but not +of much use to a human user. Use in displayed fields when counting records or +in filters. +|text |Text field |Usually uses the Raw Data transform. +|timestamp |Exact date and time |Select appropriate date/time transform. +Raw Data includes second and timezone information, usually more than is required +for a report. +|bool |True or False |Commonly used to filter out deleted item or patron records. +|org_unit |A number representing a library, library system, or +federation |When you want to filter on a library, make sure that the field +name is on an org_unit or id data type. +|link |A link to another database table |Link outputs a number that is a +meaningful reference for the database but not of much use to a human user. You +will usually want to drill further down the tree in the Sources pane and select +fields from the linked table. However, in some instances you might want to use +a link field. For example, to count the number of patrons who borrowed items you +could do a count on the Patron link data. +|int |Integer +|money |Number (in dollars) +|===================================== + +Field Transforms +~~~~~~~~~~~~~~~~ + +indexterm:[reports, field transforms] + +A _Field Transform_ tells the reporter how to process a field for output. +Different data types have different transform options. + +indexterm:[reports, field transforms, raw data] + +*Raw Data*. To display a field exactly as it appears in the database use the +_Raw Data_ transform, available for all data types. + +indexterm:[reports, field transforms, count] + +indexterm:[reports, field transforms, raw distinct] + +*Count and Count Distinct*. These transforms apply to the _id_ data type and +are used to count database records (e.g. for circulation statistics). Use Count +to tally the total number of records. Use _Count Distinct_ to count the number +of unique records, removing duplicates. + +To demonstrate the difference between _Count_ and _Count Distinct_, consider an +example where you want to know the number of active patrons in a given month, +where ``active" means they borrowed at least one item. Each circulation is linked +to a _Patron ID_, a number identifying the patron who borrowed the item. If we use +the _Count Distinct_ transform for Patron IDs we will know the number of unique +patrons who circulated at least one book (2 patrons in the table below). If +instead, we use _Count_, we will know how many books were circulated, since every +circulation is linked to a _patron ID_ and duplicate values are also counted. To +identify the number of active patrons in this example the _Count Distinct_ +transform should be used. + +[options="header,footer"] +|==================================== +|Title |Patron ID |Patron Name +|Harry Potter and the Chamber of Secrets |001 |John Doe +|Northern Lights |001 |John Doe +|Harry Potter and the Philosopher’s Stone |222 |Jane Doe +|==================================== + +indexterm:[reports, field transforms, output type] + +*Output Type*. Note that each transform has either an _Aggregate_ or +_Non-Aggregate_ output type. + +indexterm:[reports, field transforms, output type, non-aggregate] + +indexterm:[reports, field transforms, output type, aggregate] + +Selecting a _Non-Aggregate_ output type will return one row of output in your +report for each row in the database. Selecting an Aggregate output type will +group together several rows of the database and return just one row of output +with, say, the average value or the total count for that group. Other common +aggregate types include minimum, maximum, and sum. + +When used as filters, non-aggregate and aggregate types correspond to _Base_ and +_Aggregate_ filters respectively. To see the difference between a base filter and +an aggregate filter, imagine that you are creating a report to count the number +of circulations in January. This would require a base filter to specify the +month of interest because the month is a non-aggregate output type. Now imagine +that you wish to list all items with more than 25 holds. This would require an +aggregate filter on the number of holds per item because you must use an +aggregate output type to count the holds. + diff --git a/docs/reports/reporter_view_output.txt b/docs/reports/reporter_view_output.txt new file mode 100644 index 0000000000..2aa391dcfe --- /dev/null +++ b/docs/reports/reporter_view_output.txt @@ -0,0 +1,41 @@ +[[viewing_report_output]] +Viewing Report Output +--------------------- + +indexterm:[reports, output] + +indexterm:[reports, output, tabular] + +indexterm:[reports, output, Excel] + +indexterm:[reports, output, spreadsheet] + +When a report runs Evergreen sends an email with a link to the output to the +address defined in the report. Output is also stored in the specified Output +folder and will remain there until manually deleted. + +. To view report output in the staff client, open the reports interface from +_Admin (-) --> Local Administration --> Reports_ +. Click on Output to expand the folder. Select _Circulation_ (where you just +saved the circulation report output). ++ +image::media/view-output-1.png[view-output-1] ++ +. View report output is the default selection in the dropdown menu. Select +_Recurring Monthly Circ by Location_ by clicking the checkbox and click _Submit_. ++ +image::media/view-output-2.png[view-output-2] ++ +. A new tab will open for the report output. Select either _Tabular Output_ or +_Excel Output_. If Bar Charts was selected during report definition the chart +will also appear. +. Tabular output looks like this: ++ +image::media/view-output-4.png[view-output-4] ++ +. If you want to manipulate, filter or graph this data, Excel output would be +more useful. Excel output looks like this in Excel: ++ +image::media/view-output-5.png[view-output-5] + + diff --git a/docs/root.txt b/docs/root.txt index bc295d35ee..b3f037c0df 100644 --- a/docs/root.txt +++ b/docs/root.txt @@ -265,6 +265,26 @@ Introduction Learn how to create and use reports in Evergreen. +include::reports/reporter_daemon.txt[] + +include::reports/reporter_folder.txt[] + +include::reports/reporter_create_templates.txt[] + +include::reports/reporter_generating_reports.txt[] + +include::reports/reporter_view_output.txt[] + +include::reports/reporter_cloning_shared_templates.txt[] + +include::reports/reporter_add_data_source.txt[] + +include::reports/running_recurring_reports.txt[] + +include::reports/reporter_template_termionology.txt[] + +include::reports/reporter_export_usingpgAdmin.txt[] + Using the Public Access Catalog =============================== -- 2.11.0