Reports with subqueries

From PTAGISWiki

The 'gendecode' problem

It is easy to create a report including codes for species, run, and rear type (ie. 1, S, H). It is more difficult to create a report that has verbose names for species, run, and rear type (ie. Chinook, Spring, Hatchery).

SAIC solved this problem with a complex script that ran against each row returned by the report to swap out the result fields verbose names. It worked, with a few flaws. It was inefficient. It did not include the verbose name in the report legend. It was difficult to use in other reports.

After working with an Inetsoft consultant, we replaced this with another solution that involves attaching scripts to report elements to do the work. This is faster, but it uses hard-coded data that can be out of sync with the database. The verbose names are lost when the report is exported to CSV format. (An unrelated bug exists that makes export to Excel format difficult: numbers are quoted so they cannot be used in calculations.)

 

The data model solution

SREE has the ability to work with a data model that would in effect join the tables with verbose names with the interrogation tables to allow a clean and efficient solution. However, we are hampered by our non-normalized validtbl which contains the verbose names for species, runs, and rear types. It looks like there are normalized views of run, rear type, and species now, so that should make it a simple matter to create a data model. It remains difficult to get the data model to come together.

Data models seem to need to be composed of tables and not views.

Here is the view definition of sum_obs_by_day:

View Definition:
create view  sum_obs_by_day as select distinct obs_date = o.obs_day ,
o.obs_site , h.rel_site , h.tag_site , h.file_id , h.migr_yr ,
h.rel_date , h.tag_date , h.coord_id , h.org , h.epa_reach ,
h.river_km , species = t.t_species , run = t.t_run , rear_type =
t.t_rear_type , o.tag_id 

from "pittag". obs_by_day o , "pittag". tag_data t , "pittag". tag_hdr h 

where o.tag_id = t.tag_id and t.tag_file = h.file_id

So to create a data model that uses a view like sum_obs_by_day, we have to construct the model from the original tables in that view: obs_by_day, tag_data, tag_hdr. And since our motivation here is to add verbose names for things like run and species, we need to add the valid_run, valid_reartype, and valid_species tables. In order to get verbose site names, we'll also include the site table.

 

The next task is to create the relationships between the tables.

These edits made in the Query Builder are stored in datasource.xml and so that is what needs to be deployed. However, the information is quite verbose and is not simple to cut and paste. It may be better to copy the development datasource.xml to the local workstation, make the edits within the gui, and copy the file back to development.

Don't rely on the query limit of Maximum rows returned and Query timeout. Apparently they are applied after the full query runs to completion.

After linking to a parameter sheet and a CSS, the resulting report looks like this: