\r\nFindPrinter(ReportID : Integer) : Text[250]\r\n<\/pre>\nis 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.<\/p>\n
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:<\/p>\n
\r\nif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ReportUsage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)\r\ndrop table [dbo].[ReportUsage]\r\nGO\r\n\r\nCREATE TABLE [dbo].[ReportUsage] (\r\n [ReportID] [int] NOT NULL ,\r\n [UserID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,\r\n [Date] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,\r\n [Time] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,\r\n [Notes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL\r\n ) ON [PRIMARY]\r\nGO\r\n<\/pre>\nIn codeunit 1 we are going to add this code in the first part of FindPrinter().<\/p>\n
\r\nInteger.SETRANGE(Number,ReportID);\r\nInteger.FIND('-');\r\nIF CODEUNIT.RUN(CODEUNIT::"Report Usage",Integer) THEN;\r\n<\/pre>\nThe 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.<\/p>\n
The codeunit itself has this code:<\/p>\n
\r\nOnRun(VAR Rec : Record Integer)\r\nReportUsageToSQL(Number);\r\n<\/pre>\nand the function:<\/p>\n
\r\nReportUsageToSQL(ReportID : Integer)\r\nIF ISCLEAR(ADOConnection) THEN\r\n IF NOT CREATE(ADOConnection) THEN\r\n ERROR('Cannot create ADO Connection automation variable.');\r\n\r\nADOConnection.ConnectionString(\r\n 'Driver={SQL Server};Server=server;Database=database;Uid=user;Pwd=password;');\r\n\r\nADOConnection.Mode := 2;\r\nADOConnection.Open;\r\n\r\nRecordsAffected := '';\r\nRSOption := 0;\r\n\r\nSQLString :=\r\n STRSUBSTNO(\r\n 'INSERT INTO ReportUsage VALUES' +\r\n '(''%1'',''%2'',''%3'',''%4'',''%5'')',\r\n ReportID, \/\/1\r\n USERID, \/\/2\r\n FORMAT(TODAY), \/\/3\r\n FORMAT(TIME), \/\/4\r\n ''); \/\/5\r\n\r\nADOConnection.Execute(SQLString,RecordsAffected,RSOption);\r\nADOConnection.Close;\r\n<\/pre>\nAfter a while you end up with some very useful statistics in your little sql table, that you can query in every imaginable way.<\/p>\n
![\"\"](\"http:\/\/45.63.48.66\/wp-content\/uploads\/2010\/06\/reportdata-247x300.png\" "\\"reportdata\\"")
<\/a>Report Statistics in SQL<\/p><\/div>\n
[dm]3[\/dm]<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[3],"tags":[23,77,22,15,21,24,25],"class_list":["post-230","post","type-post","status-publish","format-standard","hentry","category-navision","tag-ado","tag-navision","tag-object","tag-report","tag-sql","tag-statistics","tag-upgrade"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts\/230","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/comments?post=230"}],"version-history":[{"count":22,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts\/230\/revisions"}],"predecessor-version":[{"id":241,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts\/230\/revisions\/241"}],"wp:attachment":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/media?parent=230"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/categories?post=230"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/tags?post=230"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"http:\/\/45.63.48.66\/wp-content\/uploads\/2010\/06\/reportstack-238x300.jpg\" "\\"reportstack\\"")
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.<\/p>\n