Question

Quick Report Usage

  • 8 December 2019
  • 9 replies
  • 2094 views

Badge +1

Hi,

Does anyone know of a way to monitor Quick Reports in order to detect which reports are effectively used?

Thank you and Regards, Rui Resende.


9 replies

Userlevel 4
Badge +9

Maybe history logging or custom event ? 

Userlevel 7
Badge +19

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?

Userlevel 4
Badge +9

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

 

 

Userlevel 4
Badge +9

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!

Userlevel 6
Badge +15

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

 

 

Userlevel 6
Badge +15

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

Userlevel 5
Badge +10

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

Userlevel 4
Badge +9

Another work around:   IFS stores the Previous Query in users' Personal Profiles.  When a user exits out of IFS, the Personal Profile gets stored in the database.  We can query the Profile tables to see this data.

Create an sql   query that shows the last date the user's profile was saved after running a report, which is probably the same date that the report was last run by that user.  There are a couple of limitations:  It does not show old data for reports that were deleted and recreated, which was done for several reports that had columns added or column names changed.  Also, it does not show reports that have no parameters.  Otherwise, it's a pretty good way of seeing for any given Crystal or SQL report, what users have run it and when was the last time they ran it.

 
Userlevel 5
Badge +14

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.

Reply