Jun 01

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.

Report Statistics in SQL

[dm]3[/dm]

11 Responses to “How to track report usage in Dynamics NAV”

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

    I would have done:

    Integer.get(ReportID);
    Integer.setrecfilter;
    IF CODEUNIT.RUN(CODEUNIT::"Report Usage",Integer) THEN;
    

    not sure would make a hell of a difference, but I do not like r.find(‘-‘)

    /TheDoubleH

  2. ZH says:

    Hi, having this issue, FindPrinter() doesn’t run anything. No error shown, but no record added. Tried adding a MESSAGE(‘1’); in the first part of FindPrinter() also doesn’t get me any dialog. Any idea?

  3. SNielsen says:

    The FindPrinter() function is called when you run the report.

  4. Peter says:

    http://www.mibuso.com/forum/viewtopic.php?f=32&t=33024
    I seem to recall this problem was fixed in SP1, but not sure…

  5. ZH says:

    Version 5.0 SP1 here. Connecting via Citrix, logs in from an AD account.

    I had found out the reason for the issue:
    – Findprinter() doesn’t work when i’m using database authentication.

    Changed to windows authentication then it works perfectly. Thanks a lot for this great idea. 🙂

  6. SNielsen says:

    Quick update on this post. Had not noticed this absolutely great post by Mark Brummel back in 2009. Great solution with the temporary table!

  7. […] Tracking report usage A post about how to track what reports are being used in your NAV installation by users. […]

  8. Doug Shepard says:

    Any idea how to do something similar for Forms? Is there anything in Codeunit 1 that runs everytime a Form is opened?
    Thanks
    -Doug

  9. Peter says:

    There it nothing simular for Forms. However; I guess it would be possible to setup SQL-Profiler, så that it simply logs whenever a form is loaded from the Object table.

  10. Jeremy Vyska says:

    In case anyone comes here looking for Form, Page and Report object tracking, it is possible, it just requires modifying the objects and having a logging table. I’ve put together just such a customization: https://community.dynamics.com/nav/b/smallsquareservices/archive/2013/05/27/announcing-realtime-object-usage-statistics.aspx

    Søren, this link/post is technically advertising a solution that can be purchased, so if that goes against commenting policy, delete it and my apologies.

Leave a Reply

preload preload preload
pornpants.com pornofri.com kilporn.com