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.
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
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
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.