Hurry! Act Now! Report Transformation for NAV is Coming!

“Hurry! Act Now!” It’s a statement used and overused by television marketers to instigate buying their product on impulse.  They don’t want you to think about whether you need it or not – just BUY! Buy NOW!  They know that if you wait, you will figure out you really don’t need that lighted collar for your dog, the lettuce spin dryer, the leaf taco, or even the bug zapper!

For that reason I hesitate to say it, because I feel the statement has lost value.  But …well…here goes…“Hurry! Act Now!” But this time, it will save you both time and money!

Many of you are facing the inevitable upgrade to RTC reporting.  Yet with possibly hundreds of custom reports in your database, the thought of upgrading them is like a black cloud hanging over your head, following you about every day.  But it doesn’t have to be.  Do you know why?  Well, listen carefully and I’ll whisper it to you: Not all those reports are being used.  In fact, probably MOST of them are not being used.

Wouldn’t it be nice if you knew which you needed to upgrade, and which were most important to your users?  There is a way to find out, and I’m going to give you the instructions to create the code to do it.  (Note: I’m not giving you the code, I’m showing you how to code it yourself.  I’d rather teach you to fish than give you a fish. smiley)

Now, lest you think I’m some sort of code genius, we should thank Mark Brummel for sharing this solution.  Yes, I’ve tweaked it a bit, but all the thanks should go to Mark.  Lots of you may have already seen it, but maybe not implemented it.  But now is the time to reconsider.  The history you will build up as your users run reports between now and the day you have to analyze your report usage is only as good as the length of time you have it in place.  If you’ve got a year before you upgrade – you’re going to cover a lot of ground.  Six months, not as much.  Next week…stop reading now…it’s too late my friend…R.I.P. your weekends.  For the rest of you that are being proactive, let’s get started! 

The coding is very simple.  You’ll need two small code additions to Codeunit  1 “Application Management”, a custom table, and a custom codeunit. That’s it!  After the code is in production, you just sit back, sip a glass of sweet tea, and wait for the day to come when you harvest your report usage data!   

How does it work? When a report runs in NAV, it automatically calls the function from CodeUnit 1 called “Find Printer” (in RTC as well as Classic client).  This is the point at which we will call code from our new codeunit to write to our new table the report being printed, and a few statistics.  But because we’re writing to a table in Codeunit 1, it must be a temporary table.  Sorry…that’s just how it is.  But the temporary table will be held in memory until the user signs off because we open it in a single-instance codeunit.  So in the “LogInEnd” (a.k.a . “Log Out”) function, we’ll extract the data from our temporary table out to the new “permanent” table we’ve created. 

First, let’s create our table to store our report usage.  There’s no code on the table itself, just these fields:

 

 

 

 

You may want to add other statistics, but we’re going to just store the report number, who ran it last, when they ran it, and how many times the report has been run.  That’s enough for you to determine what reports are most important to users in your database.  And if you have a custom report that never shows up in usage, you know it’s not being used, and you can archive the report object.

Secondly, let’s create our codeunit.  The codeunit has two functions: AddReport, and SaveUsage.  ReportUsageTemp is a temporary variable referring to the table just created, and ReportUsage is the actual table (not temporary).  ReportID is an integer, which references the report number being run.

Pay close attention to this little statement or it won’t work:  THE CODEUNIT PROPERTY SINGLE INSTANCE MUST BE MARKED YES.  Otherwise your temporary table will not be held in memory until you log out.

 

 

 

 

 

 

All that remains is to hook our codeunit into Codeunit 1 so we process the report usage to the tables.  This is very simple.  First, we go to the “FindPrinter” function and record the usage using the “AddReport” function we just created.

 

 

 

 

 

 

 

Then we go to the “LogInEnd” function and at the very end, right before the session is ended, we call the “SaveUsage” function, and clear our codeunit variable.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Don’t forget to make RptUsageMgt a local variable referencing the new codeunit in both functions.

And that’s it!  To test it, go ahead and sign out and back in, and run a few reports.  Then look at your table.  You should see them listed.  You won’t notice a performance hit on this either – it’s very noninvasive.  

The point in all this is, as we say in the south, to “put a bug in your ear”.  I’m not sure what the origin of that idiom is, but it is meant to call you to action.  So…HURRY, ACT NOW! Upgrade time is coming, and you can save time and money if you know which reports you have to upgrade. 

Comments

Comment: 

http://dynamicsuser.net/blogs/mark_brummel/archive/2009/12/01/tip-20-save-report-usage.aspx
The link to the NAV Teamblog descibes how to do this with a hotfix for the classic client. This only works if you use this hotfix. My solution works in almost all versions.
Thanks for sharing my idea's.

Comment: 

I knew someone would have seen this! Armonds and Mark, I reviewed both sets of code, and Mark, it is EXACTLY the code that you blogged in 2009.  I commend you my friend! Thank you for coming forward.  This code will help reduce the workload for  thousands of our fellow developers that are facing report transformation. 

And friends, I would encourage you to go to Mark Brummels blog and subscribe: http://dynamicsuser.net/blogs/mark_brummel/rss.aspx  In case you're not familiar with Mark's work, it bears noting that he has written a wonderful book about NAV development called "Microsoft Dynamics NAV 2009 Application Design" which was reviewed by ArcherPoint's very own Daniel Rimmelzwaan.  You can find it on Amazon at: http://www.amazon.com/Microsoft-Dynamics-2009-Application-Design/dp/1849680965

Comment: 

When this code is implemented do not forget to update security roles with indirect permissions on both tables and codeunit. And add it to the ALL / BASIC roles  :)
If not done this will lead to error messages when printing and logging out..
 

Comment: 

Good tip Bennn!  Any time we add a new table, we should add the table to the ALL role of security.  Thanks!

Comment: 

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

Comment: 

Doug,
 
You cannot do this with forms. But an effective method that shows which forms are uses is to remove the 0 Forms line in the ALL role and replace this with the forms you are sure you need to use. You can leave out the forms you think are not used.
As soon as you receive a mail or phonecall that someone cannot open a form you know that it is used.

Comment: 

Doug - Mark is right.  There is no way to do this same thing using CodeUnit 1.  But there are a couple of things I can suggest.  These are not a clean cut as putting the history into a table for you.

First, if you can get your users to run Code Coverage (classic client only), then it will compile a list of the forms they use.  Of course, the downfall of this is that they have to remember to start code coverage in the morning, and  check code coverage before signing off and copy out the forms used for you.  Since Code Coverage writes to a virtual table, there's no way that I know of to capture that usage and send it to a file. (If anyone else knows how and where to do this - please chime in!)

Secondly, take a look at how MergeTool's security recording works with the SQL Trace: http://youtu.be/AVno5lP5Q4o  Per Mogensen used SQL Profiler to capture usage to then import into Easy Security as a baseline for setting permissions.  You might could use this same functionality to capture the usage of forms via SQL Profiler, provided your database is SQL and not C/Side.

Forms do present a large task when moving to NAV2013 in that the object type FORM no longer exists.  To make it even more of a hastle, the tool to convert them isn't in NAV 2013, so you have to upgrade to NAV 2009, transform, and then go to NAV 2013. 

Doug, if you get either method to work for you, please reply back and let us know.  You've asked a very pertinent question for the workload many in the NAV community are facing.

Comment: 

Another option is to create you own logging table and fuction. I sometimes use this when analyzing a database before upgrading en recoding objects.
In the OnOpenForm trigger call a function that writes a record in a table. You can log who used a form and when it was accessed.
You can also use this for other objects and individual function. Handy if there are a lot of custom made functions of which you are not sure if they are used and worth upgrading.
And yes, don't forget to grant permission on this new table.

Comment: 

Hi Maarten,

Yes, adding a function call to code simular to the report usage but on the OnFormTrigger would work, but in an environment where there are many custom forms, this could be a tedious process, and then when the analysis was done we'd have to remove all that code as well. 

But in an environment without only a few custom forms, this would be solution.

Thanks!

Faithie Robertson

Comment: 

Friends, to be sure we're clear, this method for collecting report usage history will only work on printed reports.  A processing only report does not retrieve a printer selection record, and will not be written to the report history table.  I just thought I'd better point that out in clear terms.