Evergreen
HomePrev Part XII. Reports Next

Chapter 135. Adding Data Sources to Reporter

Table of Contents

Create a PostgreSQL query, view, or table 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 the reporter

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.

There are 3 basic steps to adding a new data source. Each step will be discussed in more detail in the

  1. Create a PostgreSQL query, view, or table that will provide the data for your data source.
  2. Add a new class to fm_IDL.xml for your data source.
  3. Restart the affected services to see the new data source in Reporter.

There are two possible sources for new data sources:

Create a PostgreSQL query, view, or table for your data source

You need to decide whether you will create your data source as a query, a view, or a table.

  1. 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.
  2. 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.
  3. 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 Evergreen PostgreSQL database at the command line. This is normally the psql application. You can access the Postgres documentation at the 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 Evergreen 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 sure that you have an appropriate version control process for the SQL used to create your 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';

Prev Up Next
Chapter 134. Cloning Shared Templates Home Add a new class to fm_IDL.xml for your data source