Can some one please tell me why does the option datatype show up as integer in Sql server? how do i convert it to the string value as it is in Navision? thank you very much in advance.
The datatype “option” is always an integer (0,1,2,3,…,n,n+1) in native and sql database. The NAV client just “show” you the text string “behind” the option.
To be honest, I have no clue how to convert it while using reporting with sql.
Thank you for your prompt response. i appreciate your help. I guess i will have to create another table with the string values corresponding to the integer value.
You can create a view:
CREATE VIEW AS SELECT
… (some fields)
case [Document Type]
when 0 then ‘’
when 1 then ‘Payment’
when 2 then ‘Invoice’
when 3 then ‘Credit Memo’
when 4 then ‘Finance Charge Memo’
when 5 then ‘Reminder’
when 6 then ‘Refund’
end as [Doc Type] ,
… (some fields)
FROM dbo.[CompanyName$G_L Entry]
But you must find yourself corresponding text values before - this sample is for GL Entry table field Document Type
You could also write a function or put some code in the report to handle it, but as Modris pointed out you would have to look up he actual values in NAV first. Ots a bit of a pain but unfortunalty thats how it is I think.
Ian
The option value is not really stored anywhere, so I don’t know how to get it out of the database. Hardcoding it is really the only way to do it. I like the idea of hardcoding it into a view, so at least you only have to adjust it in one place if there’s a change. I’m sure there’s other ways to enumerate values in SQL Server.
You can of course add a table containing all OptionValues with fields (TableName, FieldName, Option_INT, Option_Text) for looking up these values, but then you must use JOIN or “subselection” like this:
SELECT
Field_1,
(SELECT OptionText FROM OptionValues WHERE TableName =xxx AND FieldName=Field_2_Name AND OptionINT =Field_2) AS Field_2,
Field_n
FROM … blablabla
wich IMHO should be slower than CASE structure
Anyway, as in this case you can’t build your reports directly on NAV tables, there will be either VIEW or SELECT or Stored Procedure - so feel free to use the mehod you prefer, all they lead to the same result [:)]
Thank you very much for all your responses. I really appreciate all the help. I have created a table with (columnname,integervalue,textvalue) and placed all the option fields in that table. It is working fine. if someone ever finds out a better way, i would definitely like to know. [:D]
Jolly, I would suggest adding as first field TableName, too - it doesn’t add much overhead, but there may be situations, when it helps, and there will be no need to update if (more likely WHEN [:)] ) it becomes critical.
That makes sense, as my table gets bigger with similar field names. I will add that. Thankyou.
Hello Jolly i read u having problem with ssrs report i am having same problem with flowfield
and i created subquery to count amout of sales header but i want no. of quote and amount sales person wise when i run my query because of innerjoin in my query it gives me wrong data
so can any one have solution plz reply me