For example, in table Sales Header (#36) the very first field Document Type (field #1) is of type “Option”.
This particular option string, “Quote,Order,Invoice,Credit Memo,Blanket Order,Return Order” is listed above.
If you looked at the table in NAV, with the Cronus USA database you’d might see something like:
Option strings use a zero based indexing. So, Quote = 0, Order = 1, Invoice = 2, etc. sitting in SQL. If you compare the two pictures the integer values match the string value order.
If you looked at the same table in SQL:
These values returned might not necessarily make sense to the end user. 1, 2, 3, etc. instead of Order, Invoice, or Credit Memo.
Unfortunately, there’s no way to get these in a SQL query. They are stored in the NAV Objects themselves which reside in the SQL table “Objects”. The text of the objects is compressed and possibly encrypted before being stored as a BLOB value. This isn’t really well documented anywhere so I don’t know for sure. Regardless, querying the Option String value from SQL is not possible, leaving you with workarounds to get the text of these fields into your SSRS reports.
You could use a CASE statement in the SSRS SQL code but that gets rather tedious after a while and the code is rather static. If you had 50 reports using the same CASE code and you add another option string value, that’s 50 places you need to change code. Maybe easier than that you could push that logic into a stored procedure or user defined function. Better yet if you actually had a table of values you could query. Yet, creating a user populated table is problematic because there’s a high initial cost, additional maintenance for new option fields and possible errors.
Better idea: Let’s have NAV make the table!
We’ll need a table to store these values as well as a processing report which fill the table with useful option string values.
First, the table. I created a table “Option Strings” with the fields below and a key comprised of the TableNo, No., and Integer columns to enforce unique table, field and option values.
Most of these fields are based off of the NAV virtual table “Field”. This table doesn’t exist on disk but similar to tables like “Date” and “Table Information” is assembled on the fly and can be used in Forms and Reports.
The “Field” virtual table provides an always up to date listing of every single field in the NAV database with helpful attributes that we’ll use to help us populate the “Option Strings” table.
Second, I created a report with “Field” as the first DataItem.
I’ve set the table filter to filter on Type = Option. The report will loop through each field of type option and uses RecordRef and FieldRef options to retrieve the entire OptionString. The report then takes the OptionString, copies the string value up to each comma, assigns that to a new “Object Strings” record and assigns an Integer value.
After running the report you should have a populated table.
Now we can reference this table in SSRS, query it for the OptionString value based on the Table Name, Field Name and the SQL integer value (which the SQL query will provide).
