none
CRM 2011 reporting - how to access picklist values via fetchxml for a parameter

    Question

  • I want to use fetchxml to create a dataset in a custom reporting services report for CRM 2011 online that lists all possible values for a picklist field.  This dataset will be used as the default values for a parameter.

    In CRM 4 I could access this directly using either the stringmap or filteredstringmap.  This table doesn't seem to be accessible now.

    Thursday, February 24, 2011 2:40 AM

All replies

  • Hello Adrian,

    To retrieve all the values of a certain picklist, you must use the CRM SDK and develop a web service to do that. Here's a post that explains the classes you need to use: http://social.microsoft.com/Forums/en/crmdevelopment/thread/12b7176c-6d05-412e-a317-7028506dc361. When your web service is done, you can query it using JavaScript.


    Cornel Croitoriu - Senior Software Developer & Entrepreneur

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    CWS SoftwareBiz-Forward.comCroitoriu.NET

    Thursday, February 24, 2011 7:05 AM
  • Hi Adrian,

    Accessing tables directly is not a good way and CRM 2011 changes the picklist with global OptionSet. Using CRM 2011 SDK IOrganizaitonService web service to retrieve the metadata of the picklist attribute - if the attribute uses global Option Set - you need retrieve the metadata of the global Option Set, be sure to use the localized label for display.

    --------------------------------------------------------------------------------------------------------------------------------

    If this post answers your question, please click "Propose As Answer" on the post or "Vote as Helpful"

    Thursday, February 24, 2011 8:54 AM
  • This "solution" is for CRM4 not 2011.  My question related to accessing data via fetchxml in a reporting services report. 

    It is a normal practice to create a Reporting Services dataset from filteredstringmap in CRM4 to use as a parameter filter.  I certainly don't want to have to resort to creating a web service to do that in 2011 if it can be avoided as I don't have the skills.


    Adrian
    Wednesday, March 09, 2011 12:50 AM
  • AdrianI,

    Unfortunately, you cannot access the list of picklist possible values using the fetch xml. Also, you would not be able to access the picklist value using the Iorganizationservice as suggested by kevin from the report.

    Currently, you might want to hardcode the default value in your report.

    You can open a suggestion bug  through MSConnect, so that we can consider this for our next release.

     

    Thanks

    Kunal

    Wednesday, March 09, 2011 4:34 AM
  • Hi Kunal,

    Are you refering to CRM 4 or CRM 2011?Yes, Adrian refers to CRM4 and there is no IOrganizationService in CRM4, only in CRM 2011; It's doable in CRM 2011 from CRM 2011 SDK:

    RetrieveAttributeRequest retrieveAttributeRequest =
                            new RetrieveAttributeRequest
                        {
                            EntityLogicalName = Contact.EntityLogicalName,
                            LogicalName = "new_picklist",
                            RetrieveAsIfPublished = true
                        };

                        // Execute the request.
                        RetrieveAttributeResponse retrieveAttributeResponse =
                            (RetrieveAttributeResponse)_serviceProxy.Execute(
                            retrieveAttributeRequest);

                        // Access the retrieved attribute.
                        PicklistAttributeMetadata retrievedPicklistAttributeMetadata =
                            (PicklistAttributeMetadata)
                            retrieveAttributeResponse.AttributeMetadata;

                        // Get the current options list for the retrieved attribute.
                        OptionMetadata[] optionList =
                            retrievedPicklistAttributeMetadata.OptionSet.Options.ToArray();

                        // Change the order of the original option's list.
                        // Use the OrderBy (OrderByDescending) linq function to sort options in 
                        // ascending (descending) order according to label text.
                        // For ascending order use this:
                        var updateOptionList =
                            optionList.OrderBy(x => x.Label.LocalizedLabels[0].Label).ToList();

    If you are referring CRM 4.0, is there going to be update release for CRM 4.0 after CRM2011 already out :-)?


    View Kevin Dan's LinkedIn profileView Kevin Dan's profile Please click "Mark As Answer" on the post if this post answers the question or "Vote as Helpful" when it helps.
    Wednesday, March 09, 2011 5:40 AM
  • Kevin,

     

    I am referring to CRM 2011 online. CRM 4 doesn't had support for fetch xml based reports and has been introduced in crm 2011. You might be able to access the picklist metadata using crm 2011 sdk, but it will not be possible to do it from reports.  Reports just provide fetch xml support to access the data.

     

    Thanks

    Kunal

    Wednesday, March 09, 2011 5:52 AM
  • Thanks for clarification, Kunal.

     

    I am wondering if the following could work with CRM 2011 Online to create reports:

    1.  use SQL Server BIDS (business intelligence development studio) to create a custom report , with a report parameter of picklist, fill the report parameter picklist using IOrganizationService in the code as SDK does in the Report constructor.

    2.  Based on the user selection, construct FetchXML with filtering to fetch the report data

     


    View Kevin Dan's LinkedIn profileView Kevin Dan's profile Please click "Mark As Answer" on the post if this post answers the question or "Vote as Helpful" when it helps.
    Wednesday, March 09, 2011 6:17 AM
  • Kevin,

    Where are you planning to execute the sdk message to retrieve the sdk metadata and then hook it into the report parameter.  As far as my understanding goes, Report Parameter either accepts values from a DataSet or an expression within the Dataset.

    Please note that you could not write any such thing at least in the report RDL.

     

    Thanks

    Kunal

     

     

    Wednesday, March 09, 2011 12:50 PM
  • This is how I do it... 'new_category' is a Picklist.


    EntityCollection coll = context.RetrieveMultiple(new FetchExpression(@"<fetch version='1.0' output-format='xml-platform' page='" + i + @"' paging-cookie='" + encodedString.ToString() + @"' count='5000' mapping='logical' distinct='false'>; <entity name='opportunity'> <attribute name='name' /> <attribute name='new_product'/> <attribute name='new_category'/> <attribute name='new_projectdescription'/> <attribute name='new_projectscope' /> <order attribute='name' descending='false' /> <filter type='and'> <condition attribute='statecode' operator='eq' value='0' /> </filter> <link-entity name='account' from='accountid' to='customerid' visible='false' link-type='outer' alias='account'> <attribute name='name' /> <attribute name='new_accountcode' /> </link-entity> <link-entity name='new_supplier' from='new_supplierid' to='new_supplierid' visible='false' link-type='outer' alias='new_supplier'> <attribute name='new_name' /> </link-entity> </entity> </fetch>"));

    foreach (Entity entity in coll.Entities)
    {
    string category = entity.GetFormattedAttributeValue("new_category");

    }




    • Proposed as answer by naterchip Tuesday, March 27, 2012 8:45 PM
    • Unproposed as answer by naterchip Tuesday, March 27, 2012 8:47 PM
    • Proposed as answer by Alexander Nosiara Wednesday, June 06, 2012 4:15 AM
    • Unproposed as answer by Alexander Nosiara Wednesday, June 06, 2012 4:15 AM
    Tuesday, March 27, 2012 8:44 PM
  • Actually, you can show the option set values as a parameter to a FetchXML report.

    You must use the distinct clause, and create a seperate dataset to fetch only the option set field like this.

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
      <entity name="account">
        <attribute name="accountratingcode" />
      </entity>
    </fetch>

    This actually returns 2 columns to BIDS -

    accountratingcode
    accountratingcodevalue

    Create a report parameter for the main report and in the Available Values tab specify:

    Get Values from a query
    Dataset: Seperate Dataset with the Fetch XML above
    Value Field: accountratingcodeValue
    Label Field: accountratingcode

    Wednesday, June 06, 2012 4:41 AM