Updated: How to log report usage

Updated: How to log report usage

Rate This
  • Comments 27

Updated: Now with links to both NAV 5.0 and NAV 2009 hotfixes

 /Claus Lundstrøm

******

In several partners meetings I have heard this requests over and over again.

Claus, when we are planning an upgrade of Classic reports to RDLC reports, we do not always know which reports are actually being used at the customer site. It would be great to be able to log which reports are used so we know exactly which reports we need to upgrade to RDLC.

Well, if you download below hotfix you will now have the capability to log report usage at a customer site.

Dynamics NAV 5.0: KB2575296

Dynamics NAV 2009: KB2558650
Find links to all NAV 2009 Platform Hotfixes here:
CustomerSource: Overview of Released Platform Hotfixes for Microsoft Dynamics NAV 2009 SP1 and Microsoft Dynamics NAV 2009 R2
PartnerSource: Platform Hotfixes for Microsoft Dynamics NAV 2009 SP1 and Microsoft Dynamics NAV 2009 R2

 

In the following steps I have outlined which steps you need to do to get this log file. Remember above hotfix is required to perform the following steps.

1. Create new table to be used for Log report usage:

clip_image002

OBJECT Table 50000 Report Log
{
OBJECT-PROPERTIES
{
Date=22-06-11;
Time=14:19:58;
Modified=Yes;
Version List=CLAUSL;
}
PROPERTIES
{
}
FIELDS
{
{ 1 ; ;No. ;Integer ;AutoIncrement=Yes;
MinValue=1 }
{ 2 ; ;User ID ;Code50 ;TableRelation="User Role"."Role ID";
CaptionML=ENU=User ID }
{ 3 ; ;Report ID ;Integer ;CaptionML=ENU=Report ID }
{ 4 ; ;Report Name ;Text249 ;FieldClass=FlowField;
CalcFormula=Lookup(AllObjWithCaption."Object Caption" WHERE (Object Type=CONST(Report),
Object ID=FIELD(Report ID)));
CaptionML=ENU=Report Name }
{ 5 ; ;Date Time ;DateTime }
}
KEYS
{
{ ;No. ;Clustered=Yes }
}
FIELDGROUPS
{
}
CODE
{
BEGIN
END.
}
}

2. Now with the table created for our Report Usage log please open Codeunit 1

3. Open “C/AL Globals”

4. Navigate to ”Functions”

clip_image004

5. Create new function with Name=”OnReportRun”

clip_image006

6. Open Properties and change ID to 120

clip_image008

7. Now open “Locals” and create parameter= ReportId with Type=Integer

clip_image010

8. Select Variables tab and create ReportLog

clip_image012

9. Now all we need is to write the code for this new trigger. Open C/AL Editor and navigate to the end.

10. In OnReportRun write the following code:

ReportLog."User ID" := USERID;

ReportLog."Report ID" := ReportId;

ReportLog."Date Time" := CURRENTDATETIME;

ReportLog.INSERT;

clip_image014

11. Now Restart Classic Client

12. Run a couple of reports

13. And then at last run the Report Log table to see the result:

clip_image016

Yes I’m aware that this solution only works for Classic Reports, and yes I also would like a feature so it’s possible for you to log all objects being used at a customer site. For now we do not get this, but let’s see what the future brings.

/Claus Lundstrøm

Leave a Comment
  • Please add 1 and 3 and type the answer here:
  • Post
  • Thanks Claus! :-)

    But do we really need a new codeunit 1 function for that? Couldn't we use FindPrinter instead, at least for all reports that are not ProcessingOnly?

  • FindPrinter is not visited if UseSystemPrinter=Yes

    /Claus Lundstrøm

  • Claus, you have new big plus on my list... :-D

  • a nice solution, of course.

    but what about "a another user blocked the Table. please while the user ends is work"-Problem on a SQL-Server?!

    i'm using a similar solution to log about 1000 - 2000 printed documents a day. and i got this "blocked table"-Problem in average 1 or 2 times a day. sometimes up to 10 times. depending on the operating behavior of the user.

    do you have any practical insights with your solution?

    and by the way, what's the mystery about the ID 120 ?! Are there more like these and for what?!

    .... certainly ;-)

    regards,

    hannes

  • complete: "please wait while the user ends his work"

    where is my coffee..??? ;-)

  • First let me say, THANK YOU!!!!!  I've been wishing for this the last 4 years.  That said, does this trigger occur outside of the normal transaction?  In other words, if a report is run that errors out, will the rollback affect the tracking entry as well?  If so, that would greatly diminish the usefulness of this trigger.

  • Also, if it does occur outside of the transaction that the report runs within, then I believe any locking concerns can be dismissed since any change from the OnReportRun procedure would be committed rather quickly (assuming you don't do anything time intensive within the procedure).  Although I'm pretty sure Claus's example code would have no locking issues anyway since it simply inserts new records and it is letting SQL handle the auto-incrementing.

  • OnReportRun is called before the first trigger of the report. So it will commit its inserts no matter what happens inside report run (of course if you are running a report inside an outer transaction – then everything will be committed only when the outer transaction commits.)

    /Claus Lundstrøm

  • I found no Dynamics NAV 2009: KB2556639.

    blogs.msdn.com/.../buildnummern-bersicht-microsoft-dynamics-nav-2009-sp1.aspx

  • Dynamics NAV 2009: KB2556639 should be released according to the article above from the NAV Team, but it seems to be found nowhere - It should be released on the following site, Overview of Released Application Hotfixes for Microsoft Dynamics NAV 2009 SP1 and Microsoft Dynamics NAV 2009 R2

    :

    mbs.microsoft.com/.../General.aspx

    I believe a mistake from the NAV Team, so waiting a couple of days hopefully helps :-)

  • The KB article itself will take some time to produce. While we wait for that you can just request KB2556639 from support.

    For me it was important that you could actually get the hotfix. Should I have waited to blog about this before the KB article was ready as well?

    /Claus Lundstrøm

  • "but what about a another user blocked the Table"

    To prevent this you can add another field with "GUID" to the log table. Change the clustered index to this GUID field. So if another record will be inserted this is always done in another (random) place. So the risk of blocking decreases.

  • Updated blog with link to the KB article for NAV 2009.

    /Claus Lundstrøm

  • Dear Claus,

    at first: Tank you for this great post. Very good that you get this done!

    One suggestion: You created within your Report Log table a Table Relation to User Role by User ID, wich makes no sense. User Role table is used for the permission system. I think you want to link the Field to table ID 91 "User Setup" instead.

    Anyways, damn good implementation.

    @Hannes Holst: The "mystery" about the method ID 120 is: The .exe tries to find the method for report usage logging by id. If you hack-down the exe by reading the executable with a hex-editor, you'll get it more clearly ;-)

    I start now with some more test. If I'll finde some more interesting topics, you'll hear it here :)

    digital greetingx

    Jascha

  • This seems to work great for reports that are not run modally, however any REPORT.RUNMODAL causes the "the following C/AL functions can be used only to a limited degree during write transactions because one or more tables will be locked." error. An example is trying to print form the posted purchase receipt or posted sales invoice form.

    Anything wrong with putting a COMMIT after the insert to the Report Log? It solves the error.

Page 1 of 2 (27 items) 12