Opened 9 years ago

Closed 8 years ago

#51 closed defect (fixed)

Reporting system

Reported by: demeler Owned by: gegorbet
Priority: normal Milestone: future
Component: ultrascan3 Version: 1.0
Keywords: review Cc:


Result reporting in US3 will be implemented as follows:

All results files will be deposited in the LIMS DB in a uniform structured result table with the following table structure:

int resultID
int experimentID
int resultGUID
int requestGUID
int globalID (link table entry, could be null if not global)
int wavelength (could be null if global)
int cellNumber (could be null if global)
char channel (could be null if global)
string runID
string filename
text analysis (vHW, dcdt, 2ndmoment, 2DSA, GA, GA-MC, GA-MW-MC, etc...)
text subAnalysis (report, combined, s-distribution, residuals, etc)
text type (spreadsheet, plot, png, svg, html, etc)
longblob contents (gzip compressed)

For all types, I would not create a controlled language list, but add sensible types, fully spelled out, so they could just be read by the user and understood. This way it is easy to just add new types when they come along in our programming process. I would just make sure that similar types, subtypes always get the same name even if they are from different methods (e.g., sedimentation coefficient (molecular weight, diffusion coefficient, frictional coefficient, frictional ratio, etc) distribution).

in addition to this table, and possibly a link table for global data analyses, we will need the following:

  1. stored procedure called for updating this record
  2. Qt library routine to call stored procedure for populating DB
  3. Qt library routine to call/query stored procedure for

downloading info in DB

  1. application for letting user choose items to be represented in report

Attachments (1)

report_tables.sql (3.4 KB) - added by dzollars 9 years ago.
Report tables

Download all attachments as: .zip

Change History (11)

Changed 9 years ago by dzollars

Report tables

comment:1 Changed 9 years ago by dzollars

I've attached a first pass at representing the report tables in the database. My thought is to have a structure that will be the same whether it's a global report or not. Briefly, there would be a single record in the report table for each report. Then there would be one or more reportDetail records---a non-global report would have one, a global report would have more than one. A global report would have a different reportDetail record for each unique combination of runID and triple.

Now this is where it gets tricky. We can have zero or more png's, svg's, or whatever in the reportDocument table for each reportDetail record, but we can also have a single reportDocument that ties back to multiple reportDetail records in the case of global reports. I have introduced a link table to try to handle this, but I'm still not sure if we have it. Thoughts?

comment:2 Changed 9 years ago by gegorbet

At first blush, this looks good to me. It would be handy if there was an API to get the reportDetailID for any runID-triple pair, so in writing a reportDocument record, an app could determine whether a new reportDetail record is also needed.

Small point: I would replace the documentType enum "spreadsheet" with "html". There would not likely actually be a spreadsheet record, but rather a text data table (type "dat") which might be used to import to a spreadsheet program (externally to Ultrascan). There is a need for "html" for the main report markup.

comment:3 Changed 9 years ago by bdubbs

  • Status changed from new to assigned

I agree that the documentType enum needs a little more thought. I would change it to:

'spreadsheet' -> 'csv'
'rpt' -> 'html' 
'dat' -> 'text'

I don't think we would be saving other types (jpeg, etc) but I'd add to the enums as needed. One think for the future might be type 'movie', but the composition of that would have to be discussed.

comment:4 Changed 9 years ago by gegorbet

In terms of what is actually produced by applications at this time, we have:

  • 'html' - the main report in markup form;
  • 'svg' - most plots;
  • 'png' - some plots, primarily those that are more raster than vector;
  • 'rpt' - report text in plain text form (some reports lend themselves to plain text);
  • 'dat' - data table text, space-delimited tables, often corresponding to plots.

No harm in 'csv', although no such is produced right now; same with 'avi' or 'movie'.

comment:5 Changed 9 years ago by bdubbs

  • Owner changed from bdubbs to gegorbet
  • Status changed from assigned to new

comment:6 Changed 9 years ago by gegorbet

  • Milestone changed from alpha to beta
  • Status changed from new to assigned

I will be putting together a document with further design specifications.

comment:7 Changed 9 years ago by gegorbet

  • Milestone changed from beta to 1.0

A reporting system exists for local files. Extending the system to the database is a post-Workshop task.

comment:8 Changed 9 years ago by bdubbs

  • Milestone changed from 1.0 to future

Changing to future (After workshop)

comment:9 Changed 8 years ago by gegorbet

  • Keywords review added

Changes to the source code, database additions, and a new web-based reporter through LIMS have completed the basics of this item.

There are likely more changes needed in analysis programs to save reports and will no doubt be new types of reports created. But the essential mechanism for database reports is functioning.

Review-ready. The ticket is closely related to ticket #267.

comment:10 Changed 8 years ago by demeler

  • Resolution set to fixed
  • Status changed from assigned to closed

The basic infrastructure for online reports is in place and is working well. This ticket can be closed.

Note: See TracTickets for help on using tickets.