Quick Report Usage

Maybe history logging or custom event ? 

To detect which reports are effectively used, what parameters you are looking for? The number of times that a particular report executed? Or something different?

Quick Report Log was added in Apps 10 UDP 4. This is by default disabled, and has to be enabled via the System Parameters.

This is excellent to hear.  Does anyone have a workaround for Apps9 ?    We just upgraded last year so Apps10 will be a few years away.    Would be nice to see the usage of SQL Quick reports in order to determine if any are no longer required, etc.  Thanks!

Here is a workaround…

This will log:

1.Which report

2.Last user to run report

3.Last date report was used

4.Total number of times the report has been executed

Note: I created this so there will only by one line per report in the table - you could modify it to capture every single time the report is executed and by whom - but this seems overkill for that you are trying to do.

Step 1: Create a Custom Logical Unit - (4 Attributes USER, REPORT, DATE, COUNT)

Step 2: Create a custom API / create a new Function in an existing one

Header:

CREATE OR REPLACE PACKAGE REPORT_LOGGING_API IS

FUNCTION Log_Report(reportName_ VARCHAR2, lastUser_ VARCHAR2) RETURN VARCHAR2;

END REPORT_LOGGING_API;

Body:

CREATE OR REPLACE PACKAGE BODY REPORT_LOGGING_API IS

FUNCTION Log_Report(reportName_ VARCHAR2, lastUser_ VARCHAR2) RETURN VARCHAR2 IS

PRAGMA AUTONOMOUS_TRANSACTION;

info_ VARCHAR2(32000);
attr_ VARCHAR2(32000);
objversion_ VARCHAR2(2000);
objkey_ VARCHAR2(50);
count_ NUMBER;

BEGIN

 SELECT a.objkey, a.objversion, a.CF$_COUNT into objkey_, objversion_, count_
 FROM IFSAPP.REPORT_LOGGING_CLV a
 WHERE a.CF$_REPORT = reportName_;

 Client_SYS.Clear_Attr(attr_);
 Client_SYS.Add_To_Attr('CF$_USER',lastUser_,attr_);
 Client_SYS.Add_To_Attr('CF$_DATE',sysdate,attr_);
 Client_SYS.Add_To_Attr('CF$_COUNT',count_+1,attr_);
 IFSAPP.REPORT_LOGGING_CLP.Modify__(info_,objkey_,objversion_,attr_,'DO');

 COMMIT;

 RETURN 'LOGGED';

EXCEPTION
 WHEN no_data_found THEN

 Client_SYS.Clear_Attr(attr_);
 Client_SYS.Add_To_Attr('CF$_REPORT',reportName_,attr_);
 Client_SYS.Add_To_Attr('CF$_USER',lastUser_,attr_);
 Client_SYS.Add_To_Attr('CF$_DATE',sysdate,attr_);
 Client_SYS.Add_To_Attr('CF$_COUNT',1,attr_);
 IFSAPP.REPORT_LOGGING_CLP.New__(info_,objkey_,objversion_,attr_,'DO');

 COMMIT;

 RETURN 'LOGGED';

END Log_Report;

END REPORT_LOGGING_API;

Step 3: Add to your reports in the where clause - ensuring your change the report name in the parameters

Step 4: Select from the custom view

I have found a solution to get this to work for Crystal reports as well… not the neatest though

(You cannot use a normal SQL Expression as it will be executed for the number of rows the query returns and then you will see the count going up by 100’s etc… instead of just 1)

Steps:

Database → Database Expert → My Connections → #DATABASE# → Add Command

select IFSAPP.REPORT_LOGGING_API.Log_Report('ISO Country Report - Crystal', '') from dual

Press OK and then click on the newly created command and press F2 to rename and add the alias (Right click and rename doesn’t appear for some reason)

I didn't bother linking it

Add to the report header and suppress

Just to note: this will not log the last user who run the report - just the report, count and date

P.s. Make sure the API is granted to IFSPRINT and IFSSYS

Morning,

Just a follow up to this topic, I just want to ensure there is no standard logic or SQL query that can be run using Apps 8 to determine how often quick reports are used?

I know it was possible pre-Apps 8 using a SQL statement.

I note the custom solution suggested above by Callum.

Regards

Shaun

Here is a workaround…

 

This will log:

1.Which report

2.Last user to run report

3.Last date report was used

4.Total number of times the report has been executed

 

Note: I created this so there will only by one line per report in the table - you could modify it to capture every single time the report is executed and by whom - but this seems overkill for that you are trying to do.

 

Step 1: Create a Custom Logical Unit - (4 Attributes USER, REPORT, DATE, COUNT)

 

 

Step 2: Create a custom API / create a new Function in an existing one

 

Header:

CREATE OR REPLACE PACKAGE REPORT_LOGGING_API IS

FUNCTION Log_Report(reportName_ VARCHAR2, lastUser_ VARCHAR2) RETURN VARCHAR2;

END REPORT_LOGGING_API;

 

Body:

CREATE OR REPLACE PACKAGE BODY REPORT_LOGGING_API IS

FUNCTION Log_Report(reportName_ VARCHAR2, lastUser_ VARCHAR2) RETURN VARCHAR2 IS

PRAGMA AUTONOMOUS_TRANSACTION;

info_ VARCHAR2(32000);
attr_ VARCHAR2(32000);
objversion_ VARCHAR2(2000);
objkey_ VARCHAR2(50);
count_ NUMBER;

BEGIN

 SELECT a.objkey, a.objversion, a.CF$_COUNT into objkey_, objversion_, count_
 FROM IFSAPP.REPORT_LOGGING_CLV a
 WHERE a.CF$_REPORT = reportName_;

 Client_SYS.Clear_Attr(attr_);
 Client_SYS.Add_To_Attr('CF$_USER',lastUser_,attr_);
 Client_SYS.Add_To_Attr('CF$_DATE',sysdate,attr_);
 Client_SYS.Add_To_Attr('CF$_COUNT',count_+1,attr_);
 IFSAPP.REPORT_LOGGING_CLP.Modify__(info_,objkey_,objversion_,attr_,'DO');

 COMMIT;

 RETURN 'LOGGED';

EXCEPTION
 WHEN no_data_found THEN

 Client_SYS.Clear_Attr(attr_);
 Client_SYS.Add_To_Attr('CF$_REPORT',reportName_,attr_);
 Client_SYS.Add_To_Attr('CF$_USER',lastUser_,attr_);
 Client_SYS.Add_To_Attr('CF$_DATE',sysdate,attr_);
 Client_SYS.Add_To_Attr('CF$_COUNT',1,attr_);
 IFSAPP.REPORT_LOGGING_CLP.New__(info_,objkey_,objversion_,attr_,'DO');

 COMMIT;

 RETURN 'LOGGED';

END Log_Report;

END REPORT_LOGGING_API;

 

Step 3: Add to your reports in the where clause - ensuring your change the report name in the parameters

 

Step 4: Select from the custom view

 

 

That was what I think to solve the problem when I see the post. Great solution. Congratulations.

i have tried this method in ifs 8 and all works fine when executing the report in ifsapp user, meanwhile when end user execute that, i got error user not allowed to access database object, meanwhile i have granted execute on that package to ifssys and user related role. how to solve this?