This solution was demoed at the ODTUG APEX Open Mic night
I developed a reporting application in APEX. The goal of the application is to help turn “data into information”. Users should be able to make business decisions based on the reports and charts rather than exporting the report data and using spreadsheets to analyze it.
I’ve been a bit concerned that the application isn’t achieving it’s initial goal and that the reports are just used as data dumps. To validate this hypothesis I needed a way to track how many times a report was downloaded and compare it to the number of times it was viewed.
I can find out how many times a report/page was displayed by looking at the APEX logs:
SELECT * FROM apex_workspace_activity_log WHERE application_id = :app_id AND page_id = :app_page_id
Note: this approach is not 100% accurate since it only logs the page and not the individual regions, however for this purpose it should do. I have tinkered with “Region Logging” and may write about it soon.
I still needed a way to track report downloads. Here’s how I did it. A working demo can be found here: http://apex.oracle.com/pls/apex/f?p=20195:3120
- Create Download Log table:
CREATE TABLE TAPEX_WORKSPLACE_REPORT_DL_LOG ( WORKSPACE VARCHAR2 (255 BYTE) NOT NULL, WORKSPACE_ID NUMBER NOT NULL, APPLICATION_ID NUMBER NOT NULL, APPLICATION_NAME VARCHAR2 (255 BYTE) NOT NULL, APPLICATION_SCHEMA_OWNER VARCHAR2 (30 BYTE) NOT NULL, PAGE_ID NUMBER NOT NULL, PAGE_NAME VARCHAR2 (255 BYTE) NOT NULL, REGION_ID NUMBER NOT NULL, REGION_NAME VARCHAR2 (255 BYTE) NOT NULL, VIEW_DATE DATE NOT NULL, APEX_USER VARCHAR2 (255 BYTE) NOT NULL, APEX_SESSION_ID NUMBER NOT NULL );
- Create procedure to store APEX download record
/** * Insert Download Report Record * @param p_region_id Report Region ID */
CREATE OR REPLACE PROCEDURE sp_log_apex_dl_report (
p_region_id IN apex_application_page_regions.region_id%TYPE)
– Insert into report download log table
INSERT INTO tapex_worksplace_report_dl_log (WORKSPACE,
FROM APEX_APPLICATION_PAGE_regionS aapr, APEX_APPLICATIONS aa
WHERE aapr.application_id = v (‘APP_ID’)
AND AAPr.PAGE_ID = v (‘APP_PAGE_ID’)
AND aapr.region_id = p_region_id
AND aapr.application_id = aa.application_id;
- Trigger the above procedure every time a report is downloaded:
Since Standard Reports (STD) and Interactive Reports (IR) download functionalities are handled differently I can’t use an Applicatiobn Process to trigger the logging function. Instead, I’m going to leverage the VPD section in APEX. For those of you new to APEX, the Virtual Private Database (VPD) section in APEX is a section in APEX where you can put a block of PL/SQL code. This code gets run right at the beginning of the page, after the APP_USER is defined. The label of VPD is a bit misleading since the code doesn’t have to do any VPD tasks. Initially I had planned to handle IRs and STD Reports using a different method but thanks to the guys at Purdue Pharma for reminding me that I can leverage the VPD section in APEX.
- Shared Components
- Security Attributes
- Virtual Private Database
- Security Attributes
DECLARE v_region_id APEX_APPLICATION_PAGE_REGIONS.REGION_ID%TYPE; BEGIN -- If CSV/HTMLD is for IR (does not factor in email IRs). FLOW_EXCEL_OUTPUT is for Standard Report IF :request IN ('CSV', 'HTMLD') OR :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN -- Check for Standard Report IF :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN v_region_id := REGEXP_SUBSTR (:request, '[[:digit:]]+'); ELSE -- Interactive Report SELECT region_id INTO v_region_id FROM APEX_APPLICATION_PAGE_ir WHERE application_id = :app_id AND page_id = :app_page_id; END IF; sp_log_apex_dl_report (p_region_id => v_region_id); END IF; END;
- Comparison report:
Here’s the query that I used to compare the downloads to the report views
SELECT AR.APPLICATION_ID, ar.application_name, ar.page_id, ar.page_name, ar.region_id, ar.region_name, tdl.downloads, al.page_views FROM APEX_APPLICATION_PAGE_REGIONS ar, (SELECT tdl.application_id, tdl.page_id, tdl.region_id, COUNT (tdl.application_id) downloads FROM TAPEX_WORKSPLACE_REPORT_DL_LOG tdl GROUP BY tdl.application_id, tdl.page_id, tdl.region_id) tdl, (SELECT al.application_id, al.page_id, COUNT (al.application_id) page_views FROM apex_workspace_activity_log al GROUP BY al.application_id, al.page_id) al WHERE AR.APPLICATION_ID = :app_id AND ar.source_type_code IN ('SQL_QUERY', 'DYNAMIC_QUERY') -- Downloads AND ar.region_id = tdl.region_id(+) -- Page Views AND ar.application_id = al.application_id(+) AND ar.page_id = al.page_id(+)