Return all attributes without having to list them all in the query

Sometimes you might want to return all attributes on an entity without having to list every single one of them in the FetchXML, to do this is very simple in FetchXML, just add in <all-attributes /> where you would normally add in the attributes. If you have a linked entity then you need to add this into the linked entity as well otherwise it will only bring back the primary entities fields.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
   <entity name='account'>
      <all-attributes />
      <filter type='and'>
         <condition attribute='createdon' operator='today' />
      </filter>
   </entity>
</fetch>

How to display Images that are attached to Notes for CRM 2011 Online – Using FetchXML

How to display Images that are attached to Notes for CRM 2011 Online – Using FetchXML

Create a Query using the Advanced Find in CRM 2011, download the FetchXML from it and edit the file. The actual image is stored in a field which isn’t visible in the CRM GUI, it’s called documentbody. Here’s an example with it in, if you add this code below to your SSRS Dataset then it will show you all notes created this month with the image. Then simple add an <Image> from within BIDS and set the image properties field to the documentbody and MIME type to jpg, gif, png etc..

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

  <entity name="annotation">
    <attribute name="subject" />
    <attribute name="notetext" />
    <attribute name="filename" />
    <attribute name="annotationid" />
    <attribute name="overriddencreatedon" />
    <attribute name="documentbody" />
    <attribute name="ownerid" />
    <attribute name="annotationid" />
    <attribute name="filesize" />
    <order attribute="subject" descending="false" />
    <filter type="and">
      <condition attribute="createdon" operator="this-month" />
    </filter>
  </entity>
</fetch>

Works a treat J

Reporting Services - Custom Sort Orders in Tables

SSRS allows you by default to sort on a value  either by A to Z or Z to A, which is fine for most scenarios however what happens if you want to sort in a specific way.

Example, you have 3 values and you want them sorted as per below:

Low

Medium
High

A to Z would give you

High

Low
Medium

Z to A would be a reverse so neither of these would achieve the required sort order.

The answer is so add a IFF Statement to the Sort Expression and select A to Z as the Order, so for example

=iif(value=”Low”,”1”, iif(value=”Medium”,”2”, iif(value=”High”,”3”,””)))

 

The order is then sorted on the numeric value as opposed to the Alphabetical value, thus giving you your desired sort order J

Error when Suming values from CRM2011 - String not Dec

If you try to sum a value that you’ve queries through FetchXML from CRM 2011 then you need to make sure you choose the FieldNameValue.Value and not the FieldName.Value, the first is the actual value stored as a decimal/integer etc. and the second is simply stored as a string. If you try to sum a string you will see an error similar to this:

Error:

Warning  1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘extendedamountValue8.Paragraphs[0].TextRuns[0]’ contains an error: Conversion from string "£‎1,614.13" to type 'Decimal' is not valid.      C:\SSRS Reports\Sales Pipeline.rdl           

Correct Answer for the Purposes of Totaling:
=sum(Fields!new_weightedestsalesValue.Value)

Wrong, unless you simply want to display the value as a string.:
=sum(Fields!new_weightedestsales.Value)

             

Fetch XML limitations

There are some limitations which I've come accross which are hopefully MS will change for future releases.

• You cant specify left outer join, i.e Show me all Accounts without an Activity.
• Group/Sum isnt supported i.e Select Account, count(accountid), sum(estimated value) from FilteredAccount Group by Account

If these are show stoppers then you will need to look at one of the following alternatives

• Move to CRM On Premise
• Create a plugin to store a date field on the record of the last activity etc.
• Retrieve data using a webservice to store the data locally and then query that instead

Let me know if you find anymore that I havent listed here. 

How to Group using FetchXML

In Microsoft Dynamics CRM 2011 FetchXML includes grouping and aggregation features which let us to use aggregate functions like sum, count etc.

Using Group by we can calculate sum, avg, min, max, count. But, Group by clause is not supported through LINQ in CRM.

You can only specify one aggregate attribute in a query and you cannot use the distinct keyword. To create an aggregate attribute, set the keyword aggregate to true, then specify valid entity name, attribute name and alias(variable name). You must also specify the type of aggregation you want to perform.

<fetch distinct='false' mapping='logical' aggregate='true'>

<entity name='entity name'>
<attribute name='attribute name' aggregate='count' alias='alias name'/>
</entity>
</fetch>"

Below is the example to get sum of total amount of all won quotes:

string quotes = @"

<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='quote'>
<attribute name='totalamount' alias='totalamount_sum' aggregate='sum'/>
<attribute name='statecode' groupby='true' alias='state' />
<filter type='and'>
<condition attribute=' statecode ' operator='eq' value='won' />"+
"</filter> "+
"</entity> "+
"</fetch>";

EntityCollection quotes_result = _service.RetrieveMultiple(new FetchExpression(quotes));

foreach (var q in quotes_result.Entities)
{
Decimal wonQuoteAmount = ((Money)((AliasedValue)q["totalamount_sum"]).Value).Value;
}

courtesy of:
http://community.dynamics.com/product/crm/crmtechnical/b/crminogic/archive/2011/04/23/group-by-using-fetchxml.aspx

CRM2011 FetchXML Row Count

How to find the row count in CRM2011 using fetchxml, just add returntotalrecordcount='true' into the first tag as per below.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" returntotalrecordcount='true'>

enjoy!

Top 10/x Records

Bringing back the top 10 records in CRM2011 using fetchxml is extremly simple, you just need to add in count="10" into the first fetch tag as per below

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" count="10">
  <entity name="bc_renewal">
    <attribute name="subject" />
  </entity>
</fetch>

Fetchxml CRM 2011 Pre-Filtering

How to turn CRM 2011 Pre-filtering on for Reporting Services Reports.

Open the dataset, edit the fetchxml query and add in the enableprefiltering and prefilterparametername box, this description can be whatever you like.

<fetch  version="1.0"
            output-format="xml-platform"
           mapping="logical"
           distinct="false">

<entity name="opportunityproduct"
                       enableprefiltering="true"
                       prefilterparametername="OpportunityProdFilter">

fetchxml conditions, what are they, how do they work

fetchxml conditions, what are they?

Conditions in fetchxml as very straight forward, a full list of them are below.

Example usage for a simple firstname equals robert

<filter type='and'>
      <condition attribute='firstname' operator='eq' value='robert'/>
</filter>

Example usage for a simple firstname equals robert or simon

<filter type='or'>
      <condition attribute='firstname' operator='eq' value='robert'/>
      <condition attribute='firstname' operator='eq' value='simon'/>
</filter>

Example usage firstname is a match to one of the values in a multivalue paramater called @FN (firstname)

<filter type='and'>
      <condition attribute='firstname' operator='in' value='@FN'/>
</filter>

Shows any records where the createdon date equals today or within the last 7 days, note: this only looks at the date, so if your looking at a datetime field then no matter what the time is it will show you todays records.

<filter type='or'>
      <condition attribute = 'createdon' operator='today'/>
      <condition attribute = 'createdon' operator='last-seven-days'/>
</filter>

Condition	Actual comparison	Value
equals x	eq	x
does not equal x	ne	x
is greater than x	gt	x
is greater than or equal to x	ge	x
is less than x	lt	x
is less than or equal to x	le	x
begins with x	like	x%
does not begin with x	not-like	x%
ends with x	like	%x
does not end with x	not-like	%x
contains x	like	%x%
does not contain x	not-like	%x%
exists	not-null
does not exist	null
anytime	not-null
yesterday	yesterday
today	today
tomorrow	tomorrow
in next 7 days	next-seven-days
in last 7 days	last-seven-days
next week	next-week
last week	last-week
this week	this-week
this month	this-month
last month	last-month
next month	next-month
this year	this-year
next year	next-year
last year	last-year
on x	on	x
on or after x	on-or-after	x
on or before x	on-or-before	x
in between	between
not between	not-between
in	in
not in	not-in
equals user id	eq-userid
does not equal user id	ne-userid
equals business id	eq-businessid
does not equal business id	ne-businessid