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.
