In Part 1 we created a table and a processing report in Dynamics NAV that would enable us to later access every Dynamics NAV option string value in a SQL Reporting Services report. Now, I will show how I handled the query to get option string values into an SSRS report. This is a little less complex than Part 1.
With all the steps of Part 1 complete, we now need a simple and consistent way to query for those values. I solved this problem by created a User Defined Function in my database.
Which looks like:
CREATE FUNCTION [dbo].[OptionString] (@TableName AS NVARCHAR(30), @FieldName AS NVARCHAR(30), @FieldInteger AS INT)
RETURNS NVARCHAR(250) AS
FROM [CRONUS USA, Inc_$Option Strings]
WHERE [TableName] = @TableName AND [FieldName] = @FieldName AND [FieldInteger] = @FieldInteger
Each time I want my SSRS report to return the option text value instead of the integer value, I insert this line into my query:
dbo.OptionString(‘Sales Header’,'Document Type’,[Document Type]) AS ‘Document Type Option String’
I’m passing into the UDF the table, column name (the first and second parameters and should be known if you’re writing the report) and the query itself is supplying the integer value which is required to return the option value string from the table we created in Part 1.
I’ve provided a SQL query in the zip file below that queries table 36, the Sales Header table. The UDF is also included as well as the SSRS RDL. The query can go directly into an SSRS report resulting in the picture below if everything has been put together correctly:
You now have a simple and consistent way to generate and query for NAV option string values. Hope this helps someone!
I’m frequently creating new databases in NAV. One annoyance was NAV’s insistence on the default file location. In my dev environment this was always where I had installed SQL.
Not where I want my databases.
I spent some time investigating and after watching file system and registry access on both server and client I wasn’t able to see it grab any relevant values from those spots. From what I can tell after watching packets between client and server with a packet sniffer, the NAV client is parsing the file location from the sp_helpfile stored procedure. The solution? NAV is essentially following the Master database.
Code Coverage in Navision (available off of the tools menu -> debugger -> Code Coverage) is useful if you want to run through a process and see exactly what code is being hit. Helpful from time to time in identifying spots where things are happening in code or where you can hook new functionality into. I’m sure people are already doing this and saying “Duh” to themselves as they read this. I didn’t really think about it and put up with scrolling or trying to do searches whenever I used code coverage. I decided it must be keeping track of more than just the code to be able to color code what was hit. Indeed.
After starting code coverage and running through a process you’ll have a list of objects whose code was touched. You can go to each object and hit the code button to see what happened.
This view isn’t very helpful depending on what you’re looking for. Black for code that was hit and red if it wasn’t hit.
I edit this form and add some additional columns that exist in the code coverage table. I add columns for “Line No.”, “No. of Hits”, “Object Type”, “Object ID” and “Line Type”.
I now have a list that I can filter on “No. of Hits” > zero and quickly see any code that was used in that object. The “Object Type” and “Object ID” columns are useful if you remove all of the filters and have all the code showing across all objects.
Not sure where I originally saw this tip but it’s been so handy for me I think it needs to exist in more than one location. If you’ve done a NAV backup and need to grab a couple objects out of the backup, instead of doing a full restore and then exporting select objects, you can point the object designer import process at the .fbk backup file. NAV will scan through and present a list of conflicting objects, which should be all objects. Skipping the full restore is a HUGE time saver.
Shift + F12 to open object designer. File -> Import. You’ll have to change the file type to ‘All Files (*.*)’.
It’ll scan through the .fbk and present the Import Worksheet.
Here you can CTRL+F1 to select individual objects that you’d like to replace or import. On the menu View -> ‘Marked Only’ will show the marked objects, or you can save them as .fob files.