none
SSRS Report - how do I get the proper value of an Option set field?

    Question

  • Hi there. I am have created a simple report using BIDS (Visuall Studio 2008), CRM 2011 On Premise, Contracts table. I have several custom fields on the Contracts form which are of Option Set type which I have included in the report. However, when the results are returned, the value of these fields displays the Value rather than the Label ie I have a field called billingfrequency and instead of returning Monthly, it returns 200,006. How do I get the correct value returned?

    I am not really a developer so any help would be appreciated.

    Thanks


    BM

    Thursday, November 15, 2012 5:01 PM

Answers

  • In your SQL query, you should only ever reference the FilteredEntity Views; NEVER the unfiltered views or underlying base tables.

    The reason for this is (at least) three-fold:

    1. Access only to the FilteredEntity Views is supported
    2. The FilteredEntity Views ensure that CRM privileges afforded by Security Roles and record sharing, are adhered to
    3. The FilteredEntity Views make available additional columns, supplying the textual equivalent of OptionSetValues

    E.g.:

    SELECT billingfrequencycode, billingfrequencycodename FROM FilteredContract;

    returns:

    billingfrequencycode

    billingfrequencycodename

    1

    Monthly

    5

    Annually

    As above; for each OptionSet attribute, the equivalent text column has 'name' appended to the column name of its value column equivalent.


    --pogo (pat) @ pogo69.wordpress.com

    • Marked as answer by BillyBoy9991 Friday, November 16, 2012 9:36 AM
    Friday, November 16, 2012 1:27 AM
  • Ah, I got it now:

    SELECT        FilteredContract.customeridname, FilteredContract.activeon, FilteredContract.expireson, FilteredContract.billingfrequencycodename,
                             FilteredContract.new_contracttypename, FilteredContract.new_contracttermname, ContractExtensionBase.New_PAVContractNo,
                             ContractExtensionBase.new_periodbillingamount1, ContractExtensionBase.new_PeriodBillingAmount, FilteredContract.title
    FROM            FilteredContract INNER JOIN
                             ContractExtensionBase ON FilteredContract.contractid = ContractExtensionBase.ContractId

    Thanks all!


    BM

    • Marked as answer by BillyBoy9991 Friday, November 16, 2012 5:03 PM
    Friday, November 16, 2012 5:03 PM
  • ...and incidently, I also modified the report so that I can run it on selected records in CRM:

    SELECT        CRMAF_FilteredContract.customeridname, CRMAF_FilteredContract.activeon, CRMAF_FilteredContract.expireson, CRMAF_FilteredContract.billingfrequencycodename,
                              CRMAF_FilteredContract.new_contracttypename, CRMAF_FilteredContract.new_contracttermname, ContractExtensionBase.New_PAVContractNo,
                             ContractExtensionBase.new_periodbillingamount1, ContractExtensionBase.new_PeriodBillingAmount, CRMAF_FilteredContract.title
    FROM            FilteredContract AS CRMAF_FilteredContract INNER JOIN
                             ContractExtensionBase ON CRMAF_FilteredContract.contractid = ContractExtensionBase.ContractId


    BM

    • Marked as answer by BillyBoy9991 Monday, November 19, 2012 5:40 PM
    Monday, November 19, 2012 5:39 PM

All replies

  • If you aren't a developer I'd use an switch statement

    =Switch(Fields!billingfrequency .Value 200006, "Monthly", Fields!billingfrequency .Value 200004, "Daily")

    The picklist values can be found by modifying the field.

    if you are on premise you can create a function that returns the display name.  I've used this before in CRM 4.0

    Link to sql function article


    David Withers

    Thursday, November 15, 2012 6:43 PM
  • In your SQL query, you should only ever reference the FilteredEntity Views; NEVER the unfiltered views or underlying base tables.

    The reason for this is (at least) three-fold:

    1. Access only to the FilteredEntity Views is supported
    2. The FilteredEntity Views ensure that CRM privileges afforded by Security Roles and record sharing, are adhered to
    3. The FilteredEntity Views make available additional columns, supplying the textual equivalent of OptionSetValues

    E.g.:

    SELECT billingfrequencycode, billingfrequencycodename FROM FilteredContract;

    returns:

    billingfrequencycode

    billingfrequencycodename

    1

    Monthly

    5

    Annually

    As above; for each OptionSet attribute, the equivalent text column has 'name' appended to the column name of its value column equivalent.


    --pogo (pat) @ pogo69.wordpress.com

    • Marked as answer by BillyBoy9991 Friday, November 16, 2012 9:36 AM
    Friday, November 16, 2012 1:27 AM
  • Hi Pat. Thanks for you help. I have got that working on the billingfrequencycode although I have applied your suggestion to CustomerId as below which works. However, I have added another table (ContractExtensionBase) so I can list another field called new_ContractType but adding "name" on the end of this gives me an error. Here is the current code that displays the Option Set value for new_ContractType rather than the value I want:

    SELECT        ContractBase.CustomerIdName, ContractExtensionBase.New_PAVContractNo, ContractBase.ActiveOn, ContractBase.ExpiresOn,
                             ContractExtensionBase.new_ContractType
    FROM            ContractBase INNER JOIN
                             ContractExtensionBase ON ContractBase.ContractId = ContractExtensionBase.ContractId

    How do I get new_ContractType to list the value I want which should be Hardware, rather than 100000001.

    Thanks


    BM

    • Marked as answer by BillyBoy9991 Friday, November 16, 2012 5:03 PM
    • Unmarked as answer by BillyBoy9991 Friday, November 16, 2012 5:03 PM
    Friday, November 16, 2012 1:37 PM
  • Ah, I got it now:

    SELECT        FilteredContract.customeridname, FilteredContract.activeon, FilteredContract.expireson, FilteredContract.billingfrequencycodename,
                             FilteredContract.new_contracttypename, FilteredContract.new_contracttermname, ContractExtensionBase.New_PAVContractNo,
                             ContractExtensionBase.new_periodbillingamount1, ContractExtensionBase.new_PeriodBillingAmount, FilteredContract.title
    FROM            FilteredContract INNER JOIN
                             ContractExtensionBase ON FilteredContract.contractid = ContractExtensionBase.ContractId

    Thanks all!


    BM

    • Marked as answer by BillyBoy9991 Friday, November 16, 2012 5:03 PM
    Friday, November 16, 2012 5:03 PM
  • ...and incidently, I also modified the report so that I can run it on selected records in CRM:

    SELECT        CRMAF_FilteredContract.customeridname, CRMAF_FilteredContract.activeon, CRMAF_FilteredContract.expireson, CRMAF_FilteredContract.billingfrequencycodename,
                              CRMAF_FilteredContract.new_contracttypename, CRMAF_FilteredContract.new_contracttermname, ContractExtensionBase.New_PAVContractNo,
                             ContractExtensionBase.new_periodbillingamount1, ContractExtensionBase.new_PeriodBillingAmount, CRMAF_FilteredContract.title
    FROM            FilteredContract AS CRMAF_FilteredContract INNER JOIN
                             ContractExtensionBase ON CRMAF_FilteredContract.contractid = ContractExtensionBase.ContractId


    BM

    • Marked as answer by BillyBoy9991 Monday, November 19, 2012 5:40 PM
    Monday, November 19, 2012 5:39 PM
  • You're still (unnecessarily) joining on one of the base tables - this is unsupported.

    The FilteredEntity views are defined as a join between two base tables, EntityBase and EntityExtensionBase.  For the Contract entity, FilteredContract is defined as:

    SELECT
        ...
    FROM
        ContractBase
    JOIN
        ContractExtensionBase on ContractBase.ContractId = ContractExtensionBase.ContractId
    ...

    so your query should be able to be rewritten as:

    SELECT
    	CRMAF_FilteredContract.customeridname, CRMAF_FilteredContract.activeon, CRMAF_FilteredContract.expireson, CRMAF_FilteredContract.billingfrequencycodename,
    	CRMAF_FilteredContract.new_contracttypename, CRMAF_FilteredContract.new_contracttermname, CRMAF_FilteredContract.New_PAVContractNo, 
    	CRMAF_FilteredContract.new_periodbillingamount1, CRMAF_FilteredContract.new_PeriodBillingAmount, CRMAF_FilteredContract.title
    FROM 
    	FilteredContract AS CRMAF_FilteredContract;
    To reiterate; you should NEVER be querying against ANY object other than the FilteredEntity views.


    --pogo (pat) @ pogo69.wordpress.com

    Monday, November 19, 2012 10:25 PM