Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
how to select and read Option set value in sql for reporting? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Dears!

    I was building a report in ssrs but when i used option set values to show on the report unfortunately the caption did not show and just the value (code) of option set item did show!!!!!

    my question is : how to select and read Option set value in sql for reporting?

    Thanks

    Wednesday, October 30, 2013 11:48 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Something like this:

    select AttributeValue, Value
from StringMap
where ObjectTypeCode = 1 -- entity type code, 1 = account
and AttributeName = 'new_fieldname --fieldname

    Jason Lattimer
    My Blog -  Follow me on Twitter -  LinkedIn

    Wednesday, October 30, 2013 1:07 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Jason i used the below code:

    SELECT     dbo.StringMapBase.Value


FROM         dbo.SalesOrderDetailExtensionBase INNER JOIN
                      dbo.SalesOrderDetailBase ON dbo.SalesOrderDetailExtensionBase.SalesOrderDetailId = dbo.SalesOrderDetailBase.SalesOrderDetailId INNER JOIN
                      dbo.SalesOrderBase ON dbo.SalesOrderDetailBase.SalesOrderId = dbo.SalesOrderBase.SalesOrderId LEFT OUTER JOIN
                      dbo.StringMapBase ON dbo.SalesOrderDetailExtensionBase.new_Color = dbo.StringMapBase.AttributeValue


WHERE     (dbo.StringMapBase.AttributeName = 'new_Color') AND (dbo.StringMapBase.ObjectTypeCode = 1089) AND (dbo.StringMapBase.LangId = 1065) AND 
                      (dbo.SalesOrderBase.OrderNumber LIKE @OrderNumber)

    fortunately it works, but the other thing is this way can work for one Option set field nut multiple one!

    as i select value from StringMapBase it is just for one field that filtered by Where Command.

    What do u thing is it possible to select 2 or more option set in a same time?

    thanks


    Wednesday, October 30, 2013 6:47 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    You are working directly with the database tables, which is an unsupported method. For example, your reports will not work in CRM 2013 because the base and extension tables are merged together.

    The supported approach is to use SQL and fitlered views to retrieve data for reports: http://msdn.microsoft.com/en-us/library/gg328467.aspx. One of the nice things about filtered views is that you don't have to create joins to retrieve option set labels, the labels are included in the view.

    Neil Benson, CRM Addict and MVP at Slalom Consulting. Find me on Twitter. Join over 20,000 other CRM professionals on the Microsoft Dynamics CRM group on LinkedIn.

    Thursday, October 31, 2013 12:24 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Use filtered view

    Example: consider state is the backend name of your optionset field and it is in account table.use statename instead of state in query

    Use below query:

    select statename from filteredaccount

    • Proposed as answer by SravaniRN459 Tuesday, November 5, 2013 7:20 AM
    Tuesday, November 5, 2013 6:00 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    You should use the filtered view for CRM reporting.

    When you want to show the label of the Option set or Lookup field you will have to append name with your field. Suppose your option set field name is new_optionset then you will have to include new_optionsetname in the query. it is valid for lookup field as well.

    Hope this helps!

    Thanks!

    • Proposed as answer by SravaniRN459 Tuesday, November 5, 2013 7:20 AM
    Tuesday, November 5, 2013 6:51 AM