Oracle PL/SQL: How to Track Which Users Are Running a Report

As anyone managing a large system will tell you it's often not getting the data in that's a problem; it's getting it back out. A Report that is suitable for Finance is probably not exactly what they're looking for in the Warehouse. Pretty soon, especially if you have an end-user Reporting tool (like Business Objects), you're going to have hundreds of reports all solving individual problems out in the business.

Over time peoples needs change, some reports become obsolete and some new ones appear. Of course your users will not actually delete anything "just in case" it is needed in the future.

Now it's upgrade time and you've got 600+ reports and no idea who users what.

The point of the blog post is to suggest a way of writing reports that will build logging into the report so that whenever the report is run a record is kept (and these can then be archived off monthly, or moved into the Data Warehouse, or whatever).


Setting Up The Recording Package
Before we can make any changes to the reports to track their usage we need to define somewhere to store this data. For the purposes of this Knol I'm going to be using the Oracle e-Business Suite (11.5.10.2 to be exact) as the source for my reporting data AND as the storage location for the usage data.

To this end I'm going to create three tables in the APPS schema (yes, I know, it's far from ideal but this is only intended as an example giving the objects their own schema is a lot easier BUT then you have to sort out permissions and things start to get complicated - too complicated for this demo!).

The three tables I'm going to create are called; REPORTLOG, REPORTPARAMLOG, and REPORTRUNLOG. To reduce the volume of data produced I've split the necessary information up into three tables so, for example, if the user runs the same report 20 times with the same parameters I only need to store one copy of them. I hope this makes sense.

Anyway, that table structure is;

Figure 1: Log Information Storage Structure

The scripts to create the objects are;

REPORTRUNLOG
  CREATE TABLE "APPS"."REPORTRUNLOG" 
   ( "ID" NUMBER, 
 "REPORTLOG_ID" NUMBER, 
 "REPORTPARAMLOG_ID" NUMBER, 
 "RUNDATE" DATE, 
 "USERNAME" VARCHAR2(40 BYTE)
   );
REPORTPARAMLOG
  CREATE TABLE "APPS"."REPORTPARAMLOG" 
   ( "ID" NUMBER, 
 "PARAM01" VARCHAR2(80 BYTE), 
 "PARAM02" VARCHAR2(80 BYTE), 
 "PARAM03" VARCHAR2(80 BYTE), 
 "PARAM04" VARCHAR2(80 BYTE), 
 "PARAM05" VARCHAR2(80 BYTE), 
 "PARAM06" VARCHAR2(80 BYTE), 
 "PARAM07" VARCHAR2(80 BYTE), 
 "PARAM08" VARCHAR2(80 BYTE), 
 "PARAM09" VARCHAR2(80 BYTE), 
 "PARAM10" VARCHAR2(80 BYTE)
   );

REPORTLOG
  CREATE TABLE "APPS"."REPORTLOG" 
   ( "REPORTNAME" VARCHAR2(40 BYTE), 
 "ID" NUMBER, 
 "VERSION" VARCHAR2(20 BYTE)
   );

These scripts are taken from Oracle SQL Developer tool (selecting the objects and choosing "Export DDL" from the right-click menu).

I've not chosen to enforce the relationship between the tables in Oracle itself (foreign keys). I'm going to use a package to write the information into the tables and I'm happy that the validation I put into the package will enforce the links. This is really a design decision; you could go either way you probably should use foreign keys but it will depend on your environment.

Next comes the creation of the Oracle package;

CREATE OR REPLACE
PACKAGE REPORTLOGGER
AS
FUNCTION LogReport
  (
    p_ReportName IN VARCHAR2,
    p_Version    IN VARCHAR2,
    p_User       IN VARCHAR2,
    p_Param01    IN VARCHAR2 DEFAULT NULL,
    p_Param02    IN VARCHAR2 DEFAULT NULL,
    p_Param03    IN VARCHAR2 DEFAULT NULL,
    p_Param04    IN VARCHAR2 DEFAULT NULL,
    p_Param05    IN VARCHAR2 DEFAULT NULL,
    p_Param06    IN VARCHAR2 DEFAULT NULL,
    p_Param07    IN VARCHAR2 DEFAULT NULL,
    p_Param08    IN VARCHAR2 DEFAULT NULL,
    p_Param09    IN VARCHAR2 DEFAULT NULL,
    p_Param10    IN VARCHAR2 DEFAULT NULL)
  RETURN VARCHAR2;
END REPORTLOGGER;

This created the package header, the defaults for each parameter will mean that if (say) we only have 3 parameters we don't have to pass in 10 and give them null values in order to make the call. Also if this ever needs to be increased to 20 parameters all the existing modified reports should carry on working!

The next (and larger) part is the package body;

CREATE OR REPLACE
PACKAGE BODY REPORTLOGGER
AS
FUNCTION LogReport
  (
    p_ReportName IN VARCHAR2,
    p_Version    IN VARCHAR2,
    p_User       IN VARCHAR2,
    p_Param01    IN VARCHAR2 DEFAULT NULL,
    p_Param02    IN VARCHAR2 DEFAULT NULL,
    p_Param03    IN VARCHAR2 DEFAULT NULL,
    p_Param04    IN VARCHAR2 DEFAULT NULL,
    p_Param05    IN VARCHAR2 DEFAULT NULL,
    p_Param06    IN VARCHAR2 DEFAULT NULL,
    p_Param07    IN VARCHAR2 DEFAULT NULL,
    p_Param08    IN VARCHAR2 DEFAULT NULL,
    p_Param09    IN VARCHAR2 DEFAULT NULL,
    p_Param10    IN VARCHAR2 DEFAULT NULL)
  RETURN VARCHAR2
AS
  PRAGMA autonomous_transaction;
  v_ExistsCount      NUMBER;
  v_ReportName       VARCHAR2(40);
  v_Version          VARCHAR2(20);
  v_User             VARCHAR2(40);
  v_Param01          VARCHAR2(80);
  v_Param02          VARCHAR2(80);
  v_Param03          VARCHAR2(80);
  v_Param04          VARCHAR2(80);
  v_Param05          VARCHAR2(80);
  v_Param06          VARCHAR2(80);
  v_Param07          VARCHAR2(80);
  v_Param08          VARCHAR2(80);
  v_Param09          VARCHAR2(80);
  v_Param10          VARCHAR2(80);
  v_RowCount         NUMBER;
  v_ReportRunLogId   NUMBER;
  v_ReportLogId      NUMBER;
  v_ReportParamlogId NUMBER;
BEGIN
  -- This section converts the passed in paramters (which could be of any length) to values that can
  -- be safely stored in hte table without worrying about "too big"-type errors!
  v_ReportName := upper(SUBSTR(p_reportname, 1, 40));
  v_Version    := upper(SUBSTR(p_Version, 1, 20));
  v_User       := upper(SUBSTR(p_User, 1, 40));
  v_Param01    := upper(SUBSTR(NVL(p_Param01, ''), 1, 80));
  v_Param02    := upper(SUBSTR(NVL(p_Param02, ''), 1, 80));
  v_Param03    := upper(SUBSTR(NVL(p_Param03, ''), 1, 80));
  v_Param04    := upper(SUBSTR(NVL(p_Param04, ''), 1, 80));
  v_Param05    := upper(SUBSTR(NVL(p_Param05, ''), 1, 80));
  v_Param06    := upper(SUBSTR(NVL(p_Param06, ''), 1, 80));
  v_Param07    := upper(SUBSTR(NVL(p_Param07, ''), 1, 80));
  v_Param08    := upper(SUBSTR(NVL(p_Param08, ''), 1, 80));
  v_Param09    := upper(SUBSTR(NVL(p_Param09, ''), 1, 80));
  v_Param10    := upper(SUBSTR(NVL(p_Param10, ''), 1, 80));
  -- Get the ID for the Report (with Version)
   SELECT COUNT(*)
     INTO v_RowCount
     FROM reportlog nrl
    WHERE nrl.reportname = v_ReportName
  AND nrl.version        = v_Version;
  IF v_RowCount          = 0 THEN
     SELECT NVL(MAX(id), 0)+1 INTO v_ReportLogId FROM reportlog;
     INSERT
       INTO reportlog
      (
        id        ,
        reportname,
        version
      )
      VALUES
      (
        v_ReportLogId,
        v_ReportName ,
        v_Version
      );
  ELSE
     SELECT id
       INTO v_ReportLogId
       FROM reportlog nrl
      WHERE nrl.reportname = v_ReportName
    AND nrl.version        = v_Version;
  END IF;
  -- Get the ID for the Report parameters
   SELECT COUNT(*)
     INTO v_RowCount
     FROM reportparamlog
    WHERE param01 = v_Param01
  AND param02     = v_Param02
  AND param03     = v_Param03
  AND param04     = v_Param04
  AND param05     = v_Param05
  AND param06     = v_Param06
  AND param07     = v_Param07
  AND param08     = v_Param08
  AND param09     = v_Param09
  AND param10     = v_Param10;
  IF v_RowCount   = 0 THEN
     SELECT NVL(MAX(id), 0)+1 INTO v_ReportParamlogId FROM reportparamlog;
     INSERT
       INTO reportparamlog
      (
        id     ,
        param01,
        param02,
        param03,
        param04,
        param05,
        param06,
        param07,
        param08,
        param09,
        param10
      )
      VALUES
      (
        v_ReportParamlogId,
        v_Param01         ,
        v_Param02         ,
        v_Param03         ,
        v_Param04         ,
        v_Param05         ,
        v_Param06         ,
        v_Param07         ,
        v_Param08         ,
        v_Param09         ,
        v_Param10
      );
  ELSE
     SELECT id
       INTO v_ReportParamlogId
       FROM reportparamlog
      WHERE param01 = v_Param01
    AND param02     = v_Param02
    AND param03     = v_Param03
    AND param04     = v_Param04
    AND param05     = v_Param05
    AND param06     = v_Param06
    AND param07     = v_Param07
    AND param08     = v_Param08
    AND param09     = v_Param09
    AND param10     = v_Param10;
  END IF;
  -- Insert a record into the REPORTRUNLOG table
   SELECT NVL(MAX(id), 0)+1
     INTO v_ReportRunLogId
     FROM reportrunlog;
   INSERT
     INTO reportrunlog
    (
      id               ,
      reportlog_id     ,
      reportparamlog_id,
      rundate          ,
      username
    )
    VALUES
    (
      v_ReportRunLogId  ,
      v_ReportLogId     ,
      v_ReportParamLogId,
      SYSDATE           ,
      v_User
    );
  COMMIT;
  RETURN 'OK';
EXCEPTION
WHEN OTHERS THEN
  RETURN SUBSTR
  (
    'ERROR:' || SQLERRM || '(' || SQLCODE || ')', 1, 255
  )
  ;
END LogReport;
END REPORTLOGGER;

Now a quick test will show if the package has been setup correctly;

SELECT reportlogger.LOGREPORT('test', '1', user)
FROM DUAL;

This should return "OK" (anything else and we need to look into the error).

The following sections look at the changes necessary to reports for the individual platforms. Because of the nature of the business I work in I'm only going to be listing reporting tools we actually use!

Adding Logging to a Microsoft SQL Reporting Services (SRS) Report 
The first thing I should probably mention is that this reporting tool comes with some pretty nice "off the shelf" reports when you're tracking usage. It's very new though and if your company is anything like mine only a tiny fraction of reporting is currently done with it. As things stand at the moment I'm trying to get the reporting data in one place and so I'm going to add logging. If you want, when you sit down to look at your data, multiple reports to look at and reconcile and that's your choice.

Open the Reporting Services Project (in Visual Studio).
Open the Individual Report to be logged.
Click on the "Data" tab and then click on the Dataset drop down and select "";

Figure 2: Creating a New Dataset

A dialog will appear, you only need the first tab ("Query"). You need to give the query a name, select your data source and then enter the query string as shown below. If you have multiple parameters you can add them on the end (up to 10 obviously!). You should enter parameters here in exactly the same way as you would in your reporting query. In Figure 3 below I've got no parameters for the report so I'm just adding the Name, Version and UserID);

Figure 3: Editing a dataset

Once this is done go to the "Report" menu item and select "Report Parameters".

Figure 4: Editing the Report Parameters

The first thing to do is to mark all the parameters you are passing to the logging routine as "Hidden". You don't want users to be able to change or to even see them. Then you need to set the "Default" value for the parameter, this will be the value passed to your routine. Reporting Services give you a list of "Globals" you can use, as you can see in Figure 4 there is one called "ReportName" which I'm going to use.

Version can't be populated automatically so I've decided to give this report a version of "1.0" as a static value. I'll need to remember to change that each time I do an update (but that's what pre-Go-Live review processes are there to check!).

Click "OK" to save the parameter settings and then click on the Preview tab and see if it works. you should be able to check in the database and see the fact the report has run is being logged.

Adding Logging to a Business Objects 6.5 Report
Unfortunately this isn't quite as "clean" as adding the logging to Reporting Services; it will add a new Variable to the users list which actually executes the logging. It's not necessary to add this to the report for it to work, but it is visible to the users (i.e. if they add it to the report they will see "OK" displayed). To encourage them not to use the report I've called the field "ZZDONOTUSE".

Open the Report in Business Objects.
Under the "Data" menu item select "New Data Provider" to bring up the "New Data Wizard".

Figure 5: Business Objects 6.5 New Data Wizard

Click "Begin".

Figure 6: Specify Data Access

Make sure "Others" is selected and then in the drop down select "Free-hand SQL" and click "Finish".

Figure 7: Free-Hand SQL

Enter the SQL;

SELECT 
  reportlogger.LOGREPORT('Off-Site Storage', '1', @Variable('BOUSER')) as zzDONOTUSE
FROM DUAL

Business Objects 6.5 does not appear to have a variable for the report name (feel free to comment and correct me if that's wrong!) so it's necessary to enter the name and version number for each report and to make sure you update it when the report is changed (again the importance of a rigorous Development > Production process cannot be underestimated).

If you now run the report and check the logging tables you will see a new record for this report execution.