Pete Moss

ADFS Timeout on Server 2012 for CRM 2011

2013-11-08T14:44:00.000+00:00

This follows on from this Microsoft Guide

http://social.technet.microsoft.com/wiki/contents/articles/7681.setting-the-adfs-timeout-for-crm-2011-internet-facing-deployments-ifd.aspx

This only seems to work on PowerShell v2.0 however. Server 2012 has v3.0 and It wouldn't accept the first Add-PSSnapin command. To get around this I ran PowerShell ISE as an Administrator and then skipped this step. Everything else works as per the guide.

You can just then type in

PS C:\Windows\system32> Set-ADFSRelyingPartyTrust -Targetname "CRM Claims Replying Party" -TokenLifeTime 720

PS C:\Windows\system32> Set-ADFSRelyingPartyTrust -Targetname "CRM External Relying Party " -TokenLifeTime 720
:)

Microsoft Dyamics CRM 2013 is GA

2013-10-08T16:06:00.000+01:00

The last few months for Microsoft Dynamics have been buzzing with news about CRM 2013, new features that are being introduced, and when it’d be generally available to the masses.

The good news is you don’t have to wait anymore. The links are officially live to download the Server and Client components of the latest version of Microsoft’s Dynamics CRM suite:

Microsoft Dynamics CRM Server 2013

http://www.microsoft.com/en-us/download/details.aspx?id=40341

Microsoft Dynamics CRM 2013 for Microsoft Office Outlook (Outlook Client)

http://www.microsoft.com/en-us/download/details.aspx?id=40344

In addition to the On Premise links above, you can also sign up for a new trial account for Dynamics CRM 2013 Online.

Trial: http://www.microsoft.com/en-gb/dynamics/crm-free-trial-overview.aspx

ADFS / IE Issues, Timeouts, CRM 2011 Outlook Client keeps logging on

2013-08-15T11:38:00.002+01:00

ADFS / IE Issues, Timeouts, CRM Client keeps logging on

Came across an annoying but simple to fix issue recently, Users on CRM 2011 with CBA/IFD were being asked to login again during the day for no apparent reason. The timeout on ADFS was 12 hours so it should get them through the day.

After a lot of digging I found that an IE setting was causing it, in the Advanced Tab theirs an option called [Empty Temporary Internet Files folder when browser is closed].

So the scenario was, a user had outlook open, then opened CRM in IE, or a Report etc.. Then closed that window which deleted the CRM and ADFS Cookies. The CRM Outlook client then proceeded to ask for their login credentials again.

By un-ticking this option it wouldn't ask.

SQL To Find out who owns a saved view in CRM 2011

2013-08-13T13:17:00.003+01:00

Not so much fetch xml but still useful, in theory the same could be created in fetchxml too. This query shows how to find a view in CRM and who owns it. You can also retieve the actual fetchxml from the FetchXML field in the query below.

SELECT

FetchXml,

UserQueryId,

Name,

LayoutXml,

CreatedOn,

OwnerId,

(SELECT TOP (1) domainname

FROM FilteredSystemUser AS s

WHERE (systemuserid = u.OwnerId)) AS domainname

FROM UserQueryBase AS u

WHERE (Name LIKE '%Insert View Name%') -- change to the view your looking for

AND (OwnerId = 'xxxx') -- enter the quid of the user if your looking for views owned by a user.

Microsoft Dynamics CRM 2013

2013-07-02T16:26:00.003+01:00

Today Microsoft has announced that the next major release of CRM will be called Dynamics CRM 2013, available in fall 2013.

Very exciting news and I’m sure many people are going to be getting very excited over the next few months as more and more information becomes publically available.

More details can be found on the MS News Centre

http://www.microsoft.com/en-us/news/Press/2013/Jul13/07-02DynamicsCRM13PR.aspx

How to add a link to the CRM record from a report

2013-06-26T10:49:00.003+01:00

How to add a link to the CRM record from a report

1. Create the table with the data in
2. Select the box you wish to add the link to, right click and choose text box properties
3. Go to Action and select Go To URL
4. Click on the fx icon and type in the following

=Parameters!CRM_URL.Value & "?ID=" & Fields!accountid.Value.ToString & "&LogicalName=account"

5. The example above works for Accounts, just change the Field or Logicalname to your desired entity
6. Create a report parameter called CRM_URL, don't set any values.

The CRM_URL will be completed by CRM online when you run the report.

How To display percentage values as labels on a pie chart

2013-06-26T10:20:00.003+01:00

To display percentage values as labels on a pie chart

This may sound simple but its not obvious when you try to do it in SSRS, basically to add the percentage values you need to do this

1. Add a pie chart to your report.

2. Right-click on the pie and select Show Data Labels. The data labels should appear within each slice on the pie chart.

3. Right-click on the labels and select Series Label Properties.

4. Type #PERCENT for the Label data option.

5. (Optional) To specify how many decimal places the label shows, type "#PERCENT{Pn}" where n is the number of decimal places to display. For example, to display no decimal places, type "#PERCENT{P0}".

To display percentage values in the legend of a pie chart

1. Right-click on the pie chart and select Series Properties.

2. In Legend, type #PERCENT for the Custom legend text property.

FetchXML Outer Joins - CRM 2011, Opportunities and Customers.

2013-06-18T09:21:00.002+01:00

Joins in fetchxml or the lack of joins can cause big problems in data returned if you do the wrong thing.

The following line of code run from within the context of an opportunity would return all opportunities that also have a Customer selected.

If you want to show all Opportunities regardless of whether a customer has been added or not then you need to add in link-type=”outer” as per below.

Hope this helps

How to add a new SubArea to the Sitemap of CRM that links directly to an Activity, i.e (Task, Phone Calls, Appointments etc.)

2013-01-09T09:03:00.004+00:00

How to add a new SubArea to the Sitemap of CRM that links directly to an Activity, i.e (Task, Phone Calls, Appointments etc.)

I find the easiest way to edit a sitemap is to use this editor - http://sitemapeditor.codeplex.com/

It will allow you to connect your CRM, backup the sitemap first and then change/test it.

To add Tasks to the Navigation area of CRM
Expand Area (Workplace)
Expand Group (My Work)
Add a new SubArea and give it a Title and Description of Task
Get the GUID of the Task view
Add a URL of - /_root/homepage.aspx?etn=task&type=task&viewid=%7b2B5F5A5D-2D23-4FE7-AA58-E77995368AE7%7d&viewtype=1039
Save and Update the Sitemap and test

If you don’t have the Type=Task added then it will default to the main activities window

InitializationFailure whilst trying to connect to a CRM 2011 using the Office 365 Portal.

2012-09-06T13:31:00.000+01:00

I recently tried connecting to a new crm demo in BIDS, one which was setup using the office 365 portal. I connect this way for lots of customers so didnt expect an issues, although what actually happened was I received the following error message.

IntializationFailure
----------------------------
An authentication error occurred while connecting to server "https://disco.crm4.dynamics.com/XRMServices/2011/Discovery.svc". Make sure that the connection string and credentials are correct, and try again.

Through lots of investigation I found that in order to connect using the new Portal accounts and not the WLID's before you need to install the Microsoft Online Services Sign-In Assistant for IT Professionals RTW which you can find here:

http://www.microsoft.com/en-us/download/details.aspx?id=28177

Thank you Microsoft for making this so easy to find!

CRM 2011 Dashboard Chart Labels

2012-07-24T10:27:00.002+01:00

In order to hide the labels in crm charts you need to edit the chart xml. Here's how

Export the Chart
Create a copy of the chart xml and edit it.
Where it says IsValueShownAsLabel="True" change it to false
Save the xml
Import the chart back into CRM and Replace the existing one.

Then the chart will appear without the labels on.

How to retrieve all attributes using FetchXML in CRM 2011

2012-05-03T17:27:00.002+01:00

Sometimes you might be querying a hidden entity in CRM, or you simply dont want to have to go into CRM to check field names.

Therefore to return all attributes in a fetchxml query you can simply add <all-attribues/> where you would usually list them and it will return them all.

Example:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="listmember">
<all-attributes/>
</entity>
</fetch>

How to create a drill through Link from a SSRS Report in CRM 2011 Online

2012-03-07T08:54:00.003+00:00

How to create a drill through Link from a SSRS Report in CRM 2011 Online

Steps:

1. Create a Report Parameter called CRM_URL, Make it hidden and with a default value of https://YOURCRMORGANISATION.crm4.dynamics.com/main.aspx?

2. View the Text Box Properties of the cell you wish to add the link to, go to the Action tab, then select Go To Url, then expression and add in

=Parameters!CRM_URL.Value & "?ID={" &
Fields!customeridValue.Value.ToString() &"}&OTC=2"

Replace customerid with your value, keep the Value part as this is the GUID of the record.

OTC = 2 is contact, so change this to 1 for account, 3 for opportunity. To find the other OTC codes go to the entity in question, open a record and in the URL you will see etc=3, this number is the OTC (Object Type Code).

3. Save and Publish the report into CRM

Enjoy!

SSRS Report - UTC / UK Time Issue

2012-01-09T15:30:00.000+00:00

I came accross an issue recently, I created a SSRS Report from scratch and set the locationisation to en-GB which displays the dates etc in a dd/mm/yyyy format. This presented me with a problem, when connecting to a crm online server it likes the format to be in utc so when I passed 01/08/2011 (1st August 2011) it thought i was passing 08/01/2011 (8th January 2011).

The solution it seems was to edit the rdl file in notepad and change the query parameter, which once I found out where/how was a 10 second job.

The steps were:

Find the location of the Report in question
Right click on the report and select Open with
Choose Notepad
Do a find for <QUeryParamater>

Original Code:<QueryParameters>
<QueryParameter Name="@StartDate">
<Value>=Parameters!StartDate.Value</Value>
</QueryParameter>

New Code:<QueryParameters>
<QueryParameter Name="@StartDate">
<Value>=Format(Parameters!StartDate.Value,"MM-dd-yyyy")</Value>
</QueryParameter>

Notice the change in the Value, this simply formats the value into a mm-dd-yyyy format and then your away.

Enjoy

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

2011-09-30T09:55:00.002+01:00

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

2011-09-15T16:23:00.000+01:00

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..

Works a treat J

Reporting Services - Custom Sort Orders in Tables

2011-09-15T13:36:00.000+01:00

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

2011-09-15T12:53:00.001+01:00

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

2011-06-02T09:35:00.001+01:00

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

2011-05-31T14:15:00.000+01:00

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.

<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

2011-05-19T13:16:00.002+01:00

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

2011-05-11T16:24:00.001+01:00

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

2011-05-09T17:29:00.001+01:00

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

2011-02-24T09:50:00.000+00:00

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