I was quoting an upgrade proposal for a client that had a lot of custom reports. Trying to identify which ones were actively being used today, the client did not have a definitive answer. So we needed an strategy to identify the reports that was being used. And at the same time we would like to know who used them, and how often – that way we could make a more qualified decision about the reports to upgrade.

Of course every report could be modified so it wrote to a report usage table (but that would require you to modify every report in the system). Fortunately there is an easier way to handle this. In codeunit 1 Application Management, the function FindPrinter():

Update 9-23-2010: Embarrasing! Trying out some searches on Bing.com i found this on Marq’s blog: http://dynamicsuser.net/blogs/mark_brummel/archive/2009/12/01/tip-20-save-report-usage.aspx

Nice trick with the temporary table. In case you dont have any tables available keep reading this post :).

FindPrinter(ReportID : Integer) : Text[250]

is always called for a report you run. Well at least for any report that is not ProcessingOnly=Yes. And since it is called with the report id, we already know the userid of the logged in user as well as the current date and time, we have all the information needed for our log.

Unfortunately you can’t start any transactions in the database at the point this function is called, as it is called right after the OnIniReport() trigger. To get around this we can just have our report usage table reside in another database. In this example, I have setup a table on a SQL server:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ReportUsage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ReportUsage]
GO

CREATE TABLE [dbo].[ReportUsage] (
  [ReportID] [int] NOT NULL ,
  [UserID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [Date] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [Time] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [Notes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
  ) ON [PRIMARY]
GO

In codeunit 1 we are going to add this code in the first part of FindPrinter().

Integer.SETRANGE(Number,ReportID);
Integer.FIND('-');
IF CODEUNIT.RUN(CODEUNIT::"Report Usage",Integer) THEN;

The function is on the OnRun trigger of the codeunit, that way we can avoid any errors, like if users don’t have connectivity to the external database etc.

The codeunit itself has this code:

OnRun(VAR Rec : Record Integer)
ReportUsageToSQL(Number);

and the function:

ReportUsageToSQL(ReportID : Integer)
IF ISCLEAR(ADOConnection) THEN
  IF NOT CREATE(ADOConnection) THEN
    ERROR('Cannot create ADO Connection automation variable.');

ADOConnection.ConnectionString(
  'Driver={SQL Server};Server=server;Database=database;Uid=user;Pwd=password;');

ADOConnection.Mode := 2;
ADOConnection.Open;

RecordsAffected := '';
RSOption := 0;

SQLString :=
  STRSUBSTNO(
    'INSERT INTO ReportUsage VALUES' +
    '(''%1'',''%2'',''%3'',''%4'',''%5'')',
    ReportID,                                                   //1
    USERID,                                                     //2
    FORMAT(TODAY),                                              //3
    FORMAT(TIME),                                               //4
    '');                                                        //5

ADOConnection.Execute(SQLString,RecordsAffected,RSOption);
ADOConnection.Close;

After a while you end up with some very useful statistics in your little sql table, that you can query in every imaginable way.

[dm]3[/dm]