Allowing Users to Annotate Interactive Reports

At the last place I worked the company staff (especially managers) were keen on using spreadsheets for everything – almost to the point of obsession. Anything that looks like a list or any kind of table was put in a spreadsheet. Sound familiar?

Many of these spreadsheets were actually just csv output from queries of the database and the only reason they were put into spreadsheet form was so that the user would be able to annotate each row with a comment or to sign off that an action was completed.  Anyone would think it was 1985.

So even after I’d converted the csv extracts to lovely APEX interactive reports, end users still wanted to download the data to csv so that they could look at it in Excel. Not so that they could perform any analysis, the reason for this was purely so they could tick off each row to show they had completed their actions.

I thought it would be useful if the interactive reports themselves could be annotated and the annotations retained so that each time the report is refreshed with new source data, the annotations would still be there.

In addition there was a fair amount of MI manually compiled from these spreadsheets – weekly stats on how much work is to be done, how much work has been completed and by whom etc. etc. Having these annotations in the database means that these types of metrics can be generated automatically.

Method

To enable annotations on interactive reports I created a new table to hold the annotations and some meta data tables to hold both the definitions of the annotation columns and the relationship between the report rows and the annotation rows.  I used the metadata tables because all my report pages are automatically generated from metadata (see here ) .

Interactive Report Source

I use pl/sql to generate my apex pages and so the report source is generated automatically from these meta data tables when the page is auto-generated.  However you can still create the report source manually.

For example, I have a report based on the view BASE_DATA_VIEW with user annotations defined as using metadata is as follows:

Category = BASE_DATA_VIEW

Link Columns PK1 = TRACKING_ID, char

PK2 = PEP_NAME, char

PK3 = PEP_DOB, date

Labels :

Checkbox = Completed

Option = Result

Comments = Comments

The report source looks something like this  :

select 
     -- my normal interactive report data
     R.*
     -- ANNOTATION data
     -- checkbox displayed as a tick
     , case when A.checkbox = 1
             then ''<span class="fa fa-check"></span>''
             else null
        end                                          as COMPLETED
     , X.description                                 as RESULT
     , A.comments                                    as COMMENTS
     , 'link'                                        as LINK
     , A.id
     , A.created_by                                  as COMPLETED_BY
     , A.created_date                                as COMPLETED_DATE
from 
      BASE_DATA_VIEW R
 
left join 
      ANNOTATIONS A
-- join criteria defined in the SETTINGS table
   on c.category    = 'PEP_RETURNS'
  and R.TRACKING_ID = A.link1_CHAR
  and R.PEP_NAME    = A.link2_CHAR
  and R.PEP_DOB     = c.link3_DATE

-- LOVS table used to decode the options
left join 
      LOVS X
  on X.code= A.option_code
 and X.category = A.category

Entering Annotations

On the report page, there is a column LINK that opens the modal data entry page, passing in the category and the primary key values. Dynamic actions on the report page will auto refresh the report page when the dialog is closed.

The data entry modal page is a generic page that can be linked to any number of interactive reports. The modal page items are conditional based on the SETTINGS metadata which is queried at runtime.

Actually there is no Select List item shown in the above image as its not required for PEP_RETURNS annotations.  The item on the generic modal page is conditional based on the presence or absence of its definition in the settings table. If there had been an option column defined, the select list item would be shown with its label obtained from the settings table and its list of values would be based on the LOVS table for the given category:

select option_name ,option_code from LOVs where category = :px_category

Whenever a user adds or edits an annotation record, their username and the currrent date is automatically recorded.

Job done. No more spreadsheets required and the annotation data is now stored in the database and so can be queried in its own right. Managers can now check work progress, see who is doing what and provide other bits of MI and auditing based on the annotation data rather than trawling through numerous spreadsheets.

Of course I could have used interactive grids to save the annotations but my users were very familiar with interactive reports and prefer the modal page method.

Leave a Reply

Your email address will not be published. Required fields are marked *