CRM 2011 OData Query Designer

This project is superceded by the new application Dynamics XRM Tools which includes improved features http://dynamicsxrmtools.codeplex.com/

I’ve developed the CRM 2011 OData Query Designer as a Silverlight 4 application that is packaged as a Managed CRM 2011 Solution. This tool allows you to build OData queries by selecting filter criteria, select attributes and order by attributes. The tool also allows you to Execute the query and view the ATOM and JSON data returned. Once the managed solution is imported into CRM 2011 it is accessible from the Settings area under Customizations .

You can download the CRM 2011 OData Query Designer from codeplex.

[UPDATE]

Latest Update 8th March 2011
1. Added dropdown list of available OptionSet values for a filter when of type OptionSet, Boolean, Status and State.
2. Attribute lists now show the UserLocalizedLabel Display Name instead of Schema Name.
3. Resolved issues with some attribute types not generating correctly when used in a filter.

Has this Tool saved you time…

If you have benefited from downloading and using this tool, please feel free to donate an amount of your choice. Thankyou

GBP – >

USD – >

Euro – >

43 thoughts on “CRM 2011 OData Query Designer”

Pingback: CRM 2011 OData Query Designer (#CRM2011) « MSCRM Bing'd : : crm
Pingback: CRM 2011 OData Query Designer (#CRM2011) « MSCRM Bing'd – _Hosted UniverseThe reference for the cloud
Pingback: CRM 2011 – OData Query tester « Hosk's Dynamic CRM 2011 Blog
Hosk March 6, 2011 at 6:03 pm

Brilliant tool. I was using an excel spread to record and build up queries.

pingback
http://crmbusiness.wordpress.com/2011/03/06/crm-2011-odata-query-tester/

Reply ↓
Caleb Harrelson March 8, 2011 at 8:41 pm

Thank you for the wonderfully useful tool.

Reply ↓
1. Rhett Clinton Post authorMarch 8, 2011 at 9:30 pm
  
  My pleasure Caleb, I’m glad you find it useful.
  
  Reply ↓
Pingback: CRM 2011 – The Best Weekly CRM articles – March week 2 « Hosk's Dynamic CRM 2011 Blog
Pingback: Wonderful tools to learn Linq and oData Query for CRM 2011 « Nishant Rana's Weblog
bruce March 28, 2011 at 4:14 am

Hey Rhett, This doesnt work for me …. i get the following error message on load:

An exception occurred during the operation, making the result invalid. Check InnerException for exception details.

at System.ComponentModel.AsyncCompletedEventArgs.RaiseExceptionIfNecessary()
at CRM.Tools.ODataQueryDesigner.CrmSdk.ExecuteCompletedEventArgs.get_Result()
at CRM.Tools.ODataQueryDesigner.Readers.MetadataReader.ExecuteRetrieveEntities_ExecuteCompleted(Object sender, ExecuteCompletedEventArgs response)
at CRM.Tools.ODataQueryDesigner.CrmSdk.OrganizationServiceClient.OnExecuteCompleted(Object state)

Reply ↓
1. Rhett Clinton Post authorMarch 28, 2011 at 9:14 am
  
  Hi Bruce,
  
  There has been a couple of users experiencing a cross-domain issue when accessing the tool where by the url used to access CRM (and then the tool) does not corresspond to the url returned by the Client API Xrm.Page.context.getServerUrl(). Have you tried accessing the tool from the server and/or from the client? Also what address did you use to access the server, local host, server name, IP, domain.server.com, etc..?
  
  Thanks,
  Rhett
  
  Reply ↓
  1. bruce March 29, 2011 at 1:50 am
    
    Hey Rhett,
    THanks for the quick response..you were right..i was using localhost but as soon as i put it my server name, it worked 🙂 Cheers for that…fantastic tool!!
  2. bruce March 29, 2011 at 2:25 am
    
    Hey Rhett,
    On a side note…do you know how you could possibly get the int value in the global OptionSet..e.g. activitypointer_activitytypecode This is the option set which keeps the values of default and custom activity entity values(e.g. phone,task, custom_ent, etc).. I can get the ActivityTypeCode from the entity e.g. new_ent but i cant get the value e.g 10,000 .. Reason i need this is for a custom action button (i.e open up custom activity from a form) , the parameter is the integer value:
    
    So sorry if this post is inappropriate or in the wrong place
    Cheers bruce
  3. Christophe Trevisani Chavey November 13, 2011 at 2:29 pm
    
    Hi,
    Very interesting tool, good job!
    
    For avoid this URL problem, I recommend to not use the CRM API Xrm.Page.context.getServerUrl() for getting CRM’s URL, instead use this:
    
    var serverUrl = document.location.protocol + “//” + document.location.host + “/” + Xrm.Page.context.getOrgUniqueName();
    
    Good luck!
  4. Rhett Clinton MVP Post authorNovember 13, 2011 at 7:56 pm
    
    Hi Christophe, thanks for your feedback. I have a new getServerUrl function that is currently being used by this tool and hopefully it has no more problems with cross domain issues. With regards your example, you will have problems with CRM Online and IFD deployment as the org name is apart of the host name not appended.
    
    Cheers,
    Rhett
Thomas Roschinsky March 28, 2011 at 7:06 pm

A stunning piece of work – excellent job!

Reply ↓
1. Rhett Clinton Post authorMarch 28, 2011 at 8:32 pm
  
  Thanks Thomas, glad you like it.
  
  Cheers,
  Rhett
  
  Reply ↓
Ravi April 12, 2011 at 5:17 am

Getting this error after importing the solutiion and launching the designer. Please let me know what needs to be done..

[Async_ExceptionOccurred]
Arguments:
Debugging resource strings are unavailable. Often the key and arguments provide sufficient information to diagnose the problem. See http://go.microsoft.com/fwlink/?linkid=106663&Version=4.0.50917.0&File=System.dll&Key=Async_ExceptionOccurred

at System.ComponentModel.AsyncCompletedEventArgs.RaiseExceptionIfNecessary()
at CRM.Tools.ODataQueryDesigner.CrmSdk.ExecuteCompletedEventArgs.get_Result()
at CRM.Tools.ODataQueryDesigner.Readers.MetadataReader.ExecuteRetrieveEntities_ExecuteCompleted(Object sender, ExecuteCompletedEventArgs response)
at CRM.Tools.ODataQueryDesigner.CrmSdk.OrganizationServiceClient.OnExecuteCompleted(Object state)

Reply ↓
1. Rhett Clinton Post authorApril 12, 2011 at 11:28 am
  
  Please try accessing CRM using the servername and then try the tool.
  
  Reply ↓
2. Rick December 14, 2011 at 3:28 pm
  
  Rhett,
  
  I have the same issue and I am using the server name. I do want to point out that I am using CRM Online. Have you tested this with CRM online? I am able to call the same OData service when I write a C# example with no problems. This seems to be a context issue.
  
  Here’s the exception details:
  
  [Arg_SecurityException]
  Arguments:
  Debugging resource strings are unavailable. Often the key and arguments provide sufficient information to diagnose the problem. See http://go.microsoft.com/fwlink/?linkid=106663&Version=4.0.60831.0&File=mscorlib.dll&Key=Arg_SecurityException
  
  [Arg_SecurityException]
  Arguments:
  Debugging resource strings are unavailable. Often the key and arguments provide sufficient information to diagnose the problem. See http://go.microsoft.com/fwlink/?linkid=106663&Version=4.0.60831.0&File=mscorlib.dll&Key=Arg_SecurityException
  at System.Net.Browser.ClientHttpWebRequest.InternalEndGetResponse(IAsyncResult asyncResult)
  at System.Net.Browser.ClientHttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
  at CRM.Tools.ODataQueryDesigner.QueryDesignerViewModel.c__DisplayClass1a.c__DisplayClass1c.b__19()
  : Server Address –
  
  Reply ↓
  1. Rhett Clinton MVP Post authorJanuary 3, 2012 at 12:16 pm
    
    Hi Rick, when does this error occur? When you start the app or when you execute the OData query?
    
    Thanks,
    Rhett
Alan Whitehouse April 12, 2011 at 1:33 pm

I love the concept of this tool. I am trying to use it within CRM Online and am getting the following error when I press the generate button:

***
[Arg_SecurityException]
Arguments:
Debugging resource strings are unavailable. Often the key and arguments provide sufficient information to diagnose the problem. See http://go.microsoft.com/fwlink/?linkid=106663&Version=4.0.60129.0&File=mscorlib.dll&Key=Arg_SecurityException

at System.Net.Browser.ClientHttpWebRequest.InternalEndGetResponse(IAsyncResult asyncResult)
at System.Net.Browser.ClientHttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
at CRM.Tools.ODataQueryDesigner.QueryDesignerViewModel.c__DisplayClass1a.c__DisplayClass1c.b__19()
***

Any suggestions?

Reply ↓
1. Rhett Clinton Post authorApril 12, 2011 at 9:55 pm
  
  Yes I’ve found an issue with executing a get request with the Silverlight client on CRM Online in that it wants to redirect to the live sign in page but then fails (A Silverlight HttpRequest problem). It shouldn’t actually do this anyhow but if you use Fiddler to check the traffic you can see what is actually happens. At the moment, you have to select Show in Browser to view the results of the query.
  
  Reply ↓
Alan Whitehouse April 12, 2011 at 1:35 pm

Actually meant the “execute” button, not the “generate” button.

Reply ↓
Alan April 12, 2011 at 10:07 pm

Rhett,

So show in browser works. However, one question for you, If I choose “ContactSet” for the EntitySet and then select “Select All” from the Attributes when I view in Browser all I see is the Contact’s name. Should I also be seeing the other attributes? Sorry if this is a dumb question but I am new to Odata and CRM.

Alan

Reply ↓
1. Rhett Clinton Post authorApril 12, 2011 at 10:47 pm
  
  Did you Generate after selecting the Select all so the * character shows in the query URL? I can test it tomorrow but you can also try selecting individual attributes and see what you get. Maybe it depends on what data is available. I’ll test it tomorrow too to see what you’re getting.
  
  Reply ↓
Alan April 13, 2011 at 12:52 pm

I double checked. Seems like the sort works like it should, but no matter what attributes I select (all or individual) in the browser all I see is the Contact full name and none of the other fields.

Reply ↓
Janet April 14, 2011 at 11:33 am

I’m using 2011 Online as well so had same issue. I found if I went to Internet Options, Content, Feeds Settings and untick the Turn on Feeds Reading view, the view in browser is not the full xml. Not very pretty but you can see all the information it is returning.

Reply ↓
Mohamed Cherif April 27, 2011 at 8:36 am

I have one question, how do i create complex OData queries that have multi level expands. I mean i need to create a query which gets data from an entity and go 5 levels down

Reply ↓
Pingback: Working With REST Results in CRM 2011 « Gareth Tucker's Microsoft CRM Blog
Pingback: CRM2011, JavaScript, OData -Kevin's Mocha
henk herkelman December 14, 2011 at 2:39 pm

CRM2011: using OrganizationData.svc in visual studio 2010

We are using a hosted crm2011 environment with using the federal service (ADFS) to authenticate with username and password.
We want to use the following webservice:
https://yourdomain/XRMServices/2011/OrganizationData.svc

How to use this within visual studio 2010? Is there an example to registrate this service (with authentication) and add a record in the accoount entity of mscrm2011?

Reply ↓
1. Rhett Clinton MVP Post authorJanuary 3, 2012 at 12:32 pm
  
  Hi Henk,
  
  The SDK may help with an example of this. This particular one is for Silverlight – http://msdn.microsoft.com/en-us/library/gg309342.aspx
  
  Regards,
  Rhett
  
  Reply ↓
Marcus Miller January 31, 2012 at 7:32 pm

Dear Rhett,

I’ve successfully installed your odata query designer in my Dynamics 2011 OnPremise. Unfortunately when trying to open the tool it always wants to download and install ms silverlight although it is already installed. What can I do to get it run?

Regards,

Marcus

Reply ↓
1. Rhett Clinton MVP February 3, 2012 at 11:47 am
  
  Hi Marcus,
  
  This does seem odd behaviour but more likely it is linked to your browser settings or installed version. Here is a related post mentioning the same behaviour, I hope this helps.
  
  http://forums.silverlight.net/t/141649.aspx/1
  
  Regards,
  Rhett
  
  Reply ↓
theenerd April 25, 2012 at 7:50 am

Hi Rhett,

please where do i find the steps to know how to use the oData query Designer, i ‘ve download the zip file but on the site i found no infos about how to start the designer

Thanks in advance for your response

Reply ↓
1. Rhett Clinton MVP Post authorApril 25, 2012 at 12:24 pm
  
  It should be visible under the Settings area in the sitemap left ahnd navigation pane.
  
  Cheers,
  Rhett
  
  Reply ↓
Pingback: CRM 2011 – Getting started with oData « Hosk's Dynamic CRM 2011 Blog
Pingback: Hosk's Dynamic CRM 2011 Blog
Pingback: Introduction to OData in Microsoft Dynamics CRM 2011 | Metaphorix
Nikhil February 4, 2013 at 6:11 pm

Hi Rhett,

I downloaded and successfully imported the oData query designed tool. I have seen some samples where you can use the tool to query data from one or more CRM entities. But I can’t find any examples that use complex queries with multiple joins & order by, group by clause to retrieve data. Is this something that’s possible using the tool or in general using the Organization.svc services ? I would appreciate if you can shed some light on this.

Thanks in advance for your response.

Reply ↓
1. Rhett Clinton MVP Post authorFebruary 4, 2013 at 8:43 pm
  
  Hi,
  
  The tool allows you to choose attributes to retrieve, including from related entities but the condition selection area does not currently support choosing from related entities. You can manually write this simply by prefixing the attribute name with the associated entity schema name. You need to add the additional join section as seen when selecting related entity attributes but should work. Start by creating a query and select associated entity fields, then add a condition using the same prefix.attribute combinations.
  
  Cheers,
  Rhett
  
  Reply ↓
Administrator June 10, 2014 at 12:27 am

I have install the odata solution and this installs a link in the sitemap. The issue is this is a development environment and I need to export the Sitemap and import the development solution into our Test environment and it errors on import because of oData solution is not included.

How can I remove or exclude odata (managed solution) form the solution sitemap but not uninstall odata solution? I can successfully export/import the solution into test environment when the profiler is installed, so why oData is causing an issue?

Reply ↓
Gashaw Feyisa May 8, 2015 at 6:05 pm

Can you please help on https://community.dynamics.com/crm/f/117/p/160186/382861#382861
Unable to import Managed Solution and unable to delete managed solution (when Site Map is included in the managed solution)
Or Provide the solution that I can import to any CRM 2015 just to delete after import?

Reply ↓