CRMConsult.info

Tuesday, 24 January 2012

Entity Not Displayed After Solution Import

This one is not rocket science, but if you forget to do it, you'll be scratching your head.

Issue:
You import a solution from a development or other environment and after import the Entities aren't displayed in the areas you had set.

Solution:
Make sure you include the Site Map under Client Extensions in the solution:

Sunday, 8 January 2012

Programmatically Set Field Requirement Levels

One of the features of Microsoft Dynamics CRM 2011 is the ability to set requirement levels of a field. There are three options:

None
Recommended
Required

I always smile at the recommended option, as I actually think it’s a waste of time – Just my opinion.

What would be useful sometimes though is that a field requirement might change based on a choice somewhere else on the CRM form

While Microsoft Dynamics CRM doesn’t allow for this out of the box, there is a nice way to accomplish this by way of scripting. The code below will assign the requirement level of Required on the field ‘prioritycode’:

function setrequire()
{
Xrm.Page.getAttribute("prioritycode").setRequiredLevel("required");
}

This in itself isn’t particularly useful. If all we’re going to do is change a requirement level onload, then you might as well set it at the field level anyway. What we want to do is change it based on a choice on another field.

You could place the above code within an ‘If’ statement so if a certain choice is made, then the requirement level of a field is changed:

function setrequire()
{
var type = Xrm.Page.getAttribute("new_natureofenquiry").getSelectedOption().text;

if (type == "Option1")
{

Xrm.Page.getAttribute("prioritycode").setRequiredLevel("required");
}
else
{

Xrm.Page.getAttribute("prioritycode").setRequiredLevel("none");
}
}

The code above initially gets the value of a field (Text Value in this case) and if it meets a criteria (= “Option1”) then changes the requirement level.

Enjoy.

Rob

Saturday, 17 December 2011

SQL Report from iTunes

Mini weekend Project.

Had this idea it might be interesting to pull out some visualisation from my iTunes library.

1st step was to get the information out of iTunes. Fortunately Apple have made it nice and easy by providing a nice XML file that stores all your information called iTunes Music Library.xml

The next step is to take this file and push the data in to SQL. I found a few blogs that had some SQL scripts, but none of them seemed to work properly. I then found a great article at ‘The Code Project’ web site - http://www.codeproject.com/KB/database/sqltunes.aspx

This will take your XML file and create a DB in SQL for you:

Once this is run you should then find your data in your SQL database:

So what can we do with this information?

Why not create a dashboard?:

No Comments about music choice please!

FetchXML Reporting in Microsoft Dynamics CRM 2011–Part 2

I wrote a blog a couple of months ago about the ability to create reports in CRM 2011 using FetchXML instead of standard SQL.

In part two I’m going to cover a couple of techniques that will also be useful:

Contextual FetchXML Reports
Parameters

Contextual FetchXML Reports

In the ‘Normal’ world of SQL there is a process for creating reports for CRM to run contextually, that is return results contextual to the record you are running it from, for example running a report to pull back all Cases for a Contact.

The Prefix CRMAF_ was used in the query which CRM recognised as a reference to contextualise the results.

The same is accomplished in CRM with FetchXML using the following syntax (Highlighted):

Parameters

So as in SQL reports, adding parameters in FetchXML reports allows the user to decide what data will be returned, based on criteria. For the following example we’ll add a date parameter for when the returned cases where created:

Worth noting here that there are several options for the ‘Operator’ part of the filter. They include:

eq – Equal
on-or-after
on-or-before
gt – Greater Than
lt – Less Than
ne – Not Equal To

Also note that in the example above, you could also hard code a date parameter in or use a date picker.

Hope that helps, but for more information it’s worth checking the section on Technet - http://technet.microsoft.com/en-us/library/gg328117.aspx

Thursday, 8 December 2011

Customise The CRM 2011 Masthead

Before I start, I have to say, like the fix in CRM 4.0, the following (While simple) is still unsupported.

One of the nice tweaks you could do in CRM 4.0 was change elements of the masthead.

Essentially the process is the same in CRM 2011, but some things have changed.

There are a couple of elements to the Masthead area:

Microsoft CRM Logo (Center) – C:\Program Files\Microsoft Dynamics CRMWeb\_imgs\CRMMastheadLogo.png

Blue Masthead (Right) - C:\Program Files\Microsoft Dynamics CRMWeb\_imgs\theme\Outlook14Silver\Masthead.png

Both can be customised.

ALWAYS back up the originals and maintain the dimensions.

Wednesday, 9 November 2011

Moving On

It’s my last day today working with Time4Advice.

I’ve only been with the guys at T4A for about 7 months, but have seen some amazing things in that time. They are bringing to market, what I feel is an incredible product, a truly amazing xRM solution for financial services.

I’ve seen the boundaries pushed in every direction of what the core Microsoft Dynamics CRM product is capable of.

I wish them all the best for the future.

For me…..I’m now moving on to a new project building a CRM solution for a national housing association.

Really looking forward to starting with the team (tomorrow!)

Rob

Friday, 4 November 2011

N:1 or Lookup Field…..What's The Difference in CRM 2011?

Something I stumbled on by chance this week, had me scratching my head for a while……

I was working on some reports for CRM 2011 which worked fine in my VM image, but failed to run on live environment..

The logs were of no use, as was the error message.

Eventually I realised the only difference between my VM image and Live was a lookup field to Contacts for a Secondary contact on the main Account form. We had placed one there on the live environment, and rather than export the solution, I just re-created it on my VM.

However it was causing an issue.

On closer inspection I noticed that they were named differently:

Why should this be the case? Effectively they were the same field, same lookup and same relationship.

The difference was the way it was created.

The two ways to create this sort of relationship in CRM 2011 are:

Create a field on a form of type lookup

Create a new relationship (N:1) THEN add the resulting field to the form:

Both produce the same effect, but name them differently.

I had created the lookup in one way, and the Live system had been created in the other way.

Probably not something you’ll encounter, but if you do, you now know why.

Thursday, 27 October 2011

CRMConsult.info Reaches 50,000 Views

I have to say it’s a bit of a milestone. CRMConsult.info has reached 50,000 views in under 3 years.

I’m quite pleased with this, as my reason for starting to blog was mostly to record things for myself. It’s nice to see that some find it useful. It was never intended to be a very technical blog, but just a few snippets of information on Microsoft Dynamics CRM and SQL.

Just looking at a few of the stats, it’s interesting to see where most of the visitors come from, but it always amazes me when I see a visit from some remote place.

Anyway….Here's to the next 50,000, and I hope people continue to find it interesting.

Rob

Wednesday, 26 October 2011

Microsoft Dynamics CRM Glossary

I saw an interesting Tweet from Neil Benson today discussing initial meetings with clients and debates over naming conventions – What is a Contact? – What is a Lead? Could it be called a ‘Prospect’.

I have had that same conversation over and over again, which got me thinking……

It’s easy to understand CRM terminology when you work with it every day, but most clients are new to CRM systems and have got used to calling their data what they want to call it. I guess our role as CRM consultants is to define what the concept is, then agree a naming convention.

The following is just my take on this, not an official Microsoft Glossary. It’s mostly about what I have experienced, and how I have explained it.

Name	Definition	Options
Lead	A Lead is an unqualified record. It can be thought of as no more than a name and address, similar to a list of names and addresses in a business directory. You have no relationship with them. At some point you develop a relationship with this contact, whereby you as a business decide they are more than just a name and address, and qualify them in to an Account and Contact, and possibly create an Opportunity to do business with them. This may be the qualifier…..If the name and address express an interest in a product or service of yours, then they are qualified.	Many companies like to use the name Prospect as opposed to Lead. It is a matter of personal choice, and the name Lead can be changed, however as you’ll see in the section for Contacts, in CRM terms, just because an individual is a Contact or a business is an Account in CRM, doesn’t necessarily mean you are actively doing business with them….they could even at Contact level still be a prospective customer, having expressed an interest, but not ‘bought’ anything.
Contact	An individual person. This is someone you have a relationship with. they may be an individual customer, or perhaps a business partner or supplier. They may also be a prospective customer or even a customer who no longer does business with you. They are more than just a Lead.	As with Leads the name can be changed. Some companies like to use the title ‘Individual’ or ‘People’.
Account	This is a business, a collection of Contacts or individual people. The word ‘Account’ shouldn’t be exclusively for businesses your are actively working with. An Account may be a supplier or business partner.	Accounts can also be changed to suit business needs – Businesses, Organisations are some options.
Opportunity	This is, as the name suggests, and opportunity to sell. You cannot create an opportunity against a Lead, as a Lead is just a name and address.	Can also be changed.

It’s worth noting that each of these records can be sub-divided. For example an Account record could also be shown as one of the following:

Customer
Prospect
Partner
Reseller
Supplier
Vendor

In this way, you can segment your CRM data using views, perhaps creating views of Customers or resellers etc.

Microsoft dynamics CRM 2011 is an extremely flexible product. unlike many systems which make the end user bend toward their business process, Dynamics CRM will fit the clients needs. It’s important though as a first step that the client understands the language and concepts of CRM before rushing in with name changes.

Some clients I have worked with view their leads as opportunities. In CRM, no amount of bending will allow this. Understanding that a Contact could be a prospect, and therefore allow you to create an opportunity, is step one for a client like that, before looking at name changes.

Saturday, 22 October 2011

FetchXML Reporting in Microsoft Dynamics CRM 2011–Part 1

Traditionally I have created custom reports In CRM (4.0) using SQL. I got quite comfortable doing it that way, and created some nice reports in either SQL Report Builder or BIDS.

With the advent of CRM 2011, Fetch based reporting really took off. Mainly this was due tot the fact that if you went with CRM online (Microsoft's Cloud offering) you could only create custom reports (outside of the wizard) using FetchXML, as you don’t have direct access to the SQL like you would with an on premise solution.

It’s also interesting to note that any wizard created reports that are then downloaded for editing outside of CRM, are created using FetchXML, so to a certain extent, even if you are working on premise, it’s good to get to grips with FetchXML reporting.

There are a couple of things you will need to do in preparation to building a Fetch based report. You will need to download and install the Microsoft Dynamics CRM 2011 Report Authoring Extension. This is required if you are going to author custom Fetch based reports in Business Intelligence Development Studio (BIDS).

Once this is done, you are ready to start.

A good starting point for becoming familiar with FetchXML for reporting is Advanced Find. I have always viewed advanced find as a form of reporting, so it fits nicely. Essentially the logic you use to build an Advanced Find query can easily be transposed to use in a Report.

For example, if you created an advanced find query to find all Accounts beginning with ‘A’ the logic would look like this:

Giving these results:

So what does the FetchXML look like?

In the first image above you will see an option to ‘Download FetchXML’ – click that and save the results.

Open them up in Notepad and this is what you’ll see:

You’ll notice some elements to this. After the header information, you’ll see the entity that we are querying, in this case ‘Account’.

The next part of the Fetch outlines the attributes or fields we are returning….because this is an advanced find fetch query based on a view (in this case ‘My Accounts’), we have several attributes/fields/columns shown.

Next you’ll see that the Fetch allows you to set an ‘Order’ to the results, in this case by ‘Name’.

The next line is our filter criteria. We set the filter to be Accounts that begin with ‘A’. You’ll notice the Operator is set to ‘Like’. I will outline the other options for this later, including ‘eq’ for Equal and ‘ne’ for ‘Not Equal’.

Next we see that another table or record type is pulled in, in this case the ‘Contact’ table. This is pulled in as in the View the query is based on the primary contact of the Account is displayed. The table is included by linking to the Account table where Contact ContactID = Account PrimaryContactID. The Primary contacts Email address is also included.

So how could this type of query be used in a report? Well copy the above Fetch and start BIDS. Create a new Report Server Project and a new report.

First thing you then need to do is (as in a ‘normal’ SQL report, add the connection string. For fetch based reports the connection string needs to be in this format:

ServerURL;CRM Organization

So for example it might be:

http://CRMVM:5555;contoso

You’ll also notice the ‘Type’ is set to Microsoft Dynamics CRM Fetch. You have this option after installing the Report Authoring Extensions mentioned above.

Once this is done you can create your dataset. For the purpose of this example, paste in the query above:

Then as normal use the returned data in your report, in this example a simple table:

Now that really is a whistle stop tour of FetchXML reporting from Microsoft Dynamics CRM 2011.

In Part 2 I will go through some of the details including:

Contextual FetchXML Reports
Parameters
Filters

For some more reading check out the following references and blogs:

http://msdn.microsoft.com/en-us/library/gg328332.aspx

http://gtcrm.wordpress.com/2011/03/24/fetch-xml-reports-for-crm-2011-online/

http://blogs.msdn.com/b/crm/archive/2011/02/23/fetch-xml-based-reports-bits-amp-pieces.aspx

Tuesday, 18 October 2011

SQL Snippets

I’ve been doing quite a lot of report authoring recently, which has meant I needed to get to grips with some FetchXML for creating the reports in CRM 2011, but also dust off some old SQL and SSRS expressions.

Thought I would put a few of these expressions together in one place, mostly for my own reference, but if anyone finds them useful you’re more than welcome to use them.

None of them are radical new ways of doing things, but when you don’t use a function regularly it’s easy to forget the syntax etc. They are:

FormatNumber
Alternating Row Colours in a Table
Colour Change
FetchXML Report Parameters
Display Chosen Parameters
Contextual FetchXML Reports

FormatNumber

One of my reports needed to have a calculated field, which was then rounded and a currency symbol inserted. I wasn’t able to strictly format the field as a number, with a coma for the thousands, so just added FormatNumber before to give me the commas.

Example:

= Fields!currencysymbol.Value & FormatNumber(round( Fields!CalculatedSum.Value,Fields!currencyprecisionValue.Value))

Alternating Row Colours in a Table

I needed to show each row in alternating White and Grey for easier reading.

Example:

=IIf(RowNumber(Nothing) Mod 2 = 0, "#f2f2f2", "White")

Colour change

In this case, the expression is checking a value for the field 'enteredon' and if the difference between that date and the current date (Now) is greater than (>) 48 hours (The choice of checking against hours is set by the 'DateInterval.Hour' part of the expression), then the colour is Red, otherwise if it's less than 12 hours it's green, else DarkOrange.

= iif (DateDiff(DateInterval.Hour,Fields!enteredon.Value,Now()) >48 , "RED",iif (DateDiff(DateInterval.Hour,Fields!enteredon.Value,Now()) <12 , "Green","DarkOrange"))

FetchXML Report Parameters

Adding a parameter in a FetchXML report is quite easy:

Example:

Then call it as you normally would in a SQL report

Display chosen parameters

Had a report that allowed the user to stipulate several parameters……

I then wanted to show those chosen as a comma separated list as part of the sub title.

Example:

=join(Parameters!ParaName.Label,", ")

Contextual FetchXML Reports

I am really starting to get in to FetchXML for reporting, mostly if I’m honest adapting wizard reports in BIDS to tweak.

In the past I’ve blogged about making SQL reports contextual in CRM using the CRMAF_ prefix.

In FetchXML the following will give you a contextual report against , for example a Contact, pulling in Sales Order details:

Example:

Then your FetchXML would continue pulling in all the required Sales Order fields.

Wednesday, 5 October 2011

Originating Lead Notes in Accounts and Contacts

One of the nice features of Microsoft Dynamics CRM is the ability to qualify Leads to Accounts and Contacts, carrying through all that useful historical data including emails, activities etc. One of the things it doesn’t do is carry through the Notes from the Lead. Now you can easily get to it by navigating through the Originating Lead field, but it would be nice to see them there.

There are a few ways to achieve this including a report, but I think the best solution is to display the notes in an iFrame right in the notes section, perhaps being differentiated by calling them Original Lead Notes.

There were a couple of solutions for Microsoft Dynamics CRM 4.0, so I thought I would take some good points from these and tweak it for 2011.

Firstly you need to place an iFrame on the Contact or Account form (Or Both!) to display the Lead Notes. set it up with the following parameters, making sure that you note the Name, Set the URL to about:blank and un-tick the ‘Restrict cross-frame scripting’ box:

You then need to setup the web resource that will hold the JavaScript.

The script is the following:

   1:  function OriginLead()

   2:   {

   3:   var Primary = Xrm.Page.data.entity.attributes.get("originatingleadid");

   4:       var IFrame = Xrm.Page.ui.controls.get("IFRAME_LeadNotes");

5:

   6:      if (Primary.getValue() != null) {

7:

   8:          var GUIDvalue = Primary.getValue()[0].id;

9:

  10:          IFrame.setSrc("http://crmvm:5555/Contoso/_controls/notes/notesdata.aspx?id="+GUIDvalue+"&ParentEntity=3&EnableInlineEdit=false&EnableInsert=false");

  11:          }

  12:       else

  13:  {

  14:             IFrame.setSrc("about:blank");

  15:           }

16:

17:

  18:  }

You’ll notice a couple of things here..

Firstly the JavaScript is grabbing the GUID of the ‘OriginatingLead’ field. This is whats used in the URL of the iFrame set out in Line 10.

Also if there is no Originating Lead then the iFrame remains blank with no errors.

Once this is all done the results should be as follows:

Enjoy

Rob

Thursday, 29 September 2011

When ‘This Week’ is not really This Week

It’s funny how some things just slip from your mind. I had to put together some views in Microsoft Dynamics CRM 2011 and, as usual, started with the advanced find criteria. It was going to be a simple view of all records where a certain date field was ‘This Week’. So I set the advanced find up like this:

Now I knew there ‘should’ be one record that had a follow up date this week, but instead got two records returned:

When I looked at the two records, one had a follow up of Monday, and the other of Sunday. The advanced find filter for ‘This Week’ was including Sunday, and for this instance, and actually for this business, the week started on a Monday (The Sunday would actually be part of the previous week for them).

I had totally forgotten that a setting in the ‘System Settings’ allows you to choose the first day of the week, and obviously by default it’s set to Sunday.

To change it, navigate to Settings –> System Settings –> Formats, and click the ‘Customize’ button in the Current format section. This will open the following window, where you need to go to the Date tab:

You’ll notice in the image above, ‘First Day of The week’ was set to Sunday. Just change to Monday and save, and your Advanced Find ‘This Week’ will start from the Monday.

Totally forgot this setting until I needed it!

Monday, 19 September 2011

CRM Report for Entity and Attribute Details

Quite often I have encountered a need to see a list of entities, along with their attributes, and in particular the details of the attributes, so for example the Account entity along with all Attributes including custom and their details – Display Name, Database Name, Field Type etc…..

In CRM 4.0 there were a few applications that could take the Customisations XML and feed it to Word to deliver as a document set out with all the details. I haven’t found one in CRM 2011, and want to build one slightly differently using a SQL report.

I decided to include a parameter that would let you choose which entities to report on.

In brief:

Create 2 datasets.

1st dataset will be used as the parameter:

   1:  SELECT

   2:  distinct EntityView.Name AS EntityName

3:

4:

   5:  FROM LocalizedLabelView AS LocalizedLabelView_2

   6:  INNER JOIN AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId

   7:  RIGHT OUTER JOIN EntityView

   8:  INNER JOIN LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON AttributeView.EntityId = EntityView.EntityId

   9:  WHERE LocalizedLabelView_1.ObjectColumnName = 'LocalizedName'

  10:  AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName'

  11:  AND LocalizedLabelView_1.LanguageId = '1033'

  12:  AND LocalizedLabelView_2.LanguageId = '1033'

13:

  14:  order by EntityView.Name asc

Then create a main dataset that will pull in the details from the entities and attributes:

   1:  SELECT EntityView.Name AS EntityName,

   2:  LocalizedLabelView_1.Label AS EntityDisplayName,

   3:  AttributeView.Name AS AttributeName,

   4:  LocalizedLabelView_2.Label AS AttributeDisplayName,

5:

   6:  column_name as columnname,

   7:  data_type as datatype,

   8:  table_name as tablename

9:

  10:  FROM LocalizedLabelView AS LocalizedLabelView_2

  11:  INNER JOIN AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId

  12:  RIGHT OUTER JOIN EntityView

  13:  INNER JOIN LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON AttributeView.EntityId = EntityView.EntityId

  14:  left outer join INFORMATION_SCHEMA.COLUMNS as infoschema on attributeview.Name = COLUMN_NAME and EntityView.Name = table_name

15:

  16:  WHERE LocalizedLabelView_1.ObjectColumnName = 'LocalizedName'

  17:  AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName'

  18:  AND LocalizedLabelView_1.LanguageId = '1033'

  19:  AND LocalizedLabelView_2.LanguageId = '1033'

  20:  AND table_Name IN (@Entity)

  21:  --AND EntityView.Name IN ('Account')

  22:  ORDER BY EntityName, AttributeName

Once this is done, design your report in some sort of table layout, and the results will be as follows:

Rob.

Friday, 2 September 2011

Add a Calculator to a CRM Record Using Web Resources

There have been a few occasions where I thought it would be useful to have a calculator in a CRM form. Maybe on a quote form to work out discounts on the fly etc..

Microsoft Dynamics CRM 2011 has a great process for including resources in Forms so I used this functionality to create a calculator – Just built in HTML and JavaScript – and called as a Web Resource in a form:

Needs a little work on the look and feel, but essentially it’s all there. Ver 1.2 will enable you to post the results to certain fields.

If anyone is interested in the process, let me know.

Thursday, 11 August 2011

Pre-Filtering Reports – Fun with CRMAF_

I have blogged before about the technique for pre-filtering reports using the CRMAF_ prefix, enabling your CRM reports to run contextually etc., however I had a need recently to develop a series of reports that might call for all sorts of outer joins and unions, and pulling in a lot of fields.

I had all sorts of issues trying to get it to work properly – It ran fine pulling in the single SalesOrder table, but we have another custom table that sits under the SalesOrder table and it just spat that out!.

I decided to look at it slightly differently.

Firstly create a dataset that pre-filters the record you want to run it from – In this case from a contact:

select
CRMAF_filteredcontact.contactid as contactid
from
filteredcontact as CRMAF_filteredContact

If you were to run this, you would just get a long list of your contacts GUIDS.

Secondly you need to use the results of this as a parameter, so create a parameter called something like ‘Contact’ and choose the dataset as it’s source (In Report Builder it looks something like this):

Once this has been setup you create a second dataset which is you main query.

Because you are already pre-filtering with this first dataset, you don’t need to prefix with CRMAF_ in the second dataset. This not only makes life easier, but also opens up the ability to use other techniques for retrieving your data that you couldn’t before with pre-filtering your whole query. For example, I needed to do some calculations and aggregates prior to pulling in the data, as it was more efficient to do it that way. With that in mind I created a temporary table using Declare @tablename, and grabbed my data – doing some calculations on the way.

The key thing is that once you have created your query, part of it must pull in the parameter, so in my case it was:

where
customerid =@contact

The result was a much easier to construct main query. A much quicker report, and a more robust way of calculating fields.

Tuesday, 5 July 2011

Copy Account Address to Clipboard for Dynamics CRM 2011

Some time ago I wrote a blog article outlining the steps needed to create a button in CRM to copy the address from an Account in CRM 4.0 to the clipboard.

I have just converted it to Dynamics CRM 2011 and have taken advantage of a few of the cool new features of 2011 like Web Resources and Solutions.

Essentially it’s a similar process, but with a few tweaks to update the JavaScript – which is now held as a web resource along with the images needed for the Ribbon button.

I’m going to blog the ‘how to’ at some point, but if you just want the solution, Just Email me peledie@hotmail.com

The results when applied give you the following button in your Ribbon:

This will grab the Name, Line1, Line2, City, and Postal Code from your Account form in to the Clipboard. Tweaking the JavaScript will enable you to copy more or less.

Interested in any feedback on this solution.

Monday, 30 May 2011

Spatial Reporting for Microsoft Dynamics CRM

Bit of a weekend project but was quite excited at the possibilities with this one.

I wanted to have a play with SQL 2008's ability to report using geospacial data. With that in mind I created a basic report that would pick up Longitude and Latitude from a contact record in CRM (I’ll come back to the whole latitude and longitude in a bit). The new SQL Server 2008 allows you to report geographically and display on a map which is nice.

The initial result if you have a longitude and latitude field in the contact record is something like this:

Thinking about how users could use this sort of information is interesting. Contacts could be grouped in views or after an advanced fined then run the report contextually. The pushpins could be hyperlinked back to the relevant CRM record etc.

The trick with this was the Longitude Latitude. Having to add it manually would be a non-starter for users, especially if there are hundreds or even thousands of records, so I wrote a little script that would grab the Postcode, which should be in every record, and get the longitude and latitude from the BingMaps API, posting it into 2 new fields I created to store the Longitude and Latitude.

This is still a work in progress, but a nice use of CRM, SQL and Bingmaps.

Friday, 27 May 2011

Cascading Parameters in SSRS Report

Working on a report for a client today, and the requirement for cascading parameters came up...
So a choice in parameter 1 would filter out the options for parameter 2 and so on. In the clients case they actually wanted 3 tiers.

Creating parameters in SSRS is nice and easy, and for ease of use I created a dataset that is used exclusively to poulate the 1st parameter. the trick now was to take that choice and poulate the 2nd and so on.

To do this just follow these steps:

To create the main dataset with a query that includes multiple related parameters
1.In the Report Data pane, right-click a data source, and then click Add Dataset.
2.In Name, type the name of the dataset.
3.In Data source, choose the name of the data source or click New to create one.
4.In Query type, choose the type of query for the selected data source. In this topic, query type Text is assumed.
5.In Query, type the query to use to retrieve data for this report. The query must include the following parts:
a.A list of data source fields. For example, in a Transact-SQL statement, the SELECT statement specifies a list of database column names from a given table or view.
b.One query parameter for each cascading parameter. A query parameter limits the data retrieved from the data source by specifying certain values to include or exclude from the query. Typically, query parameters occur in a restriction clause in the query. For example, in a Transact-SQL SELECT statement, query parameters occur in the WHERE clause. For more information, see Filtering Rows by Using WHERE and HAVING.
6.Click Run (!). After you include query parameters and then run the query, report parameters that correspond to the query parameters are automatically created.
Note
The order of query parameters the first time you run a query determines the order that they are created in the report. To change the order, see How to: Change the Order of a Report Parameter (Reporting Services)

7.Click OK.
Next, you will create a dataset that provides the values for the independent parameter.
To create a dataset to provide values for an independent parameter
1.In the Report Data pane, right-click a data source, and then click Add Dataset.
2.In Name, type the name of the dataset.
3.In Data source, verify the name is the name of the data source you chose in step 1.
4.In Query type, choose the type of query for the selected data source. In this topic, query type Text is assumed.
5.In Query, type the query to use to retrieve values for this parameter. Queries for independent parameters typically do not contain query parameters. For example, to create a query for a parameter that provides all category values, you might use a Transact-SQL statement similar to the following:
CopySELECT DISTINCT <column name> FROM <table>
The SELECT DISTINCT command removes duplicate values from the result set so that you get each unique value from the specified column in the specified table.
Click Run (!). The result set shows the values that are available for this first parameter.
6.Click OK.
Next, you will set the properties of the first parameter to use this dataset to populate its available values at run-time.
To set available values for a report parameter
1.In the Report Data pane, in the Parameters folder, right-click the first parameter, and then click Parameter Properties.
2.In Name, verify that the name of the parameter is correct.
3.Click Available Values.
4.Click Get values from a query. Three fields appear.
5.In Dataset, from the drop-down list, click the name of the dataset you created in the previous procedure.
6.In Value field, click the name of the field that provides the parameter value.
7.In Label field, click the name of the field that provides the parameter label.
8.Click OK.
Next, you will create a dataset that provides the values for a dependent parameter.
To create a dataset to provide values for a dependent parameter
1.In the Report Data pane, right-click a data source, and then click Add Dataset.
2.In Name, type the name of the dataset.
3.In Data source, verify the name is the name of the data source you chose in step 1.
4.In Query type, choose the type of query for the selected data source. In this topic, query type Text is assumed.
5.In Query, type the query to use to retrieve values for this parameter. Queries for dependent parameters typically include query parameters for each parameter that this parameter is dependent on. For example, to create a query for a parameter that provides all subcategory (dependent parameter) values for a category (independent parameter), you might use a Transact-SQL statement similar to the following:
CopySELECT DISTINCT Subcategory FROM <table>
WHERE (Category = @Category)
In the WHERE clause, Category is the name of a field from <table> and @Category is a query parameter. This statement produces a list of subcategories for the category specified in @Category. At run time, this value will be filled in with the value that the user chooses for the report parameter that has the same name.
6.Click OK.
Next, you will set the properties of the second parameter to use this dataset to populate its available values at run time.
To set available values for a report parameter
1.In the Report Data pane, in the Parameters folder, right-click the first parameter, and then click Parameter Properties.
2.In Name, verify that the name of the parameter is correct.
3.Click Available Values.
4.Click Get values from a query.
5.In Dataset, from the drop-down list, click the name of the dataset you created in the previous procedure.
6.In Value field, click the name of the field that provides the parameter value.
7.In Label field, click the name of the field that provides the parameter label.
8.Click OK.

To test the cascading parameters
1.Click Preview.
2.From the drop-down list for the first, independent parameter, choose a value.
The report processor runs the dataset query for the next parameter and passes it the value you chose for the first parameter. The drop-down list for the second parameter is populated with the available values based on the first parameter value.
3.From the drop-down list for the second, dependent parameter, choose a value.
The report does not run automatically after you choose the last parameter so that you can change your choice.
4.Click View Report. The report updates the display based on the parameters you have chosen.

Sunday, 24 April 2011

Connections in Microsoft Dynamics CRM 2011

A great new feature of CRM 2011 is Connections. Rather than just explain what it does, I thought I would give a scenario that you may encounter, then show how Connections in CRM 2011 can be leveraged to solve the problem.

Scenario: You are a company that manages Assets for your client, perhaps Hardware used by their personnel. You need a way of tracking who has use of the asset, but also need a way of tracking service cases related to the asset.

Firstly, create a new entity for holding the Asset information.

This might include a lookup to the associated contact, and may even include a lookup to the Product if you originally supplied it ( As a further step you might include a relationship to the Sales Order, thus giving you that complete transparency from Purchase to support)

When you create a new entity in CRM 2011, you now get the ability to enable connections for the records:

This connection can then be used in conjunction with traditional relationships, so in this example, the asset has a direct relationship to a contact record, as the Asset Owner, but you could also create a connection to this asset from, for example, a Case:

The possibilities with this feature are endless. As mentioned at the outset, you could easily include connections to the original Sales Order.

What will this give you?

Well, imagine you supplied a hardware item to a client.Being able to follow it’s life through Sales to Cases would give you a complete trail of the item. Being able to look from within the asset record and see all the connections, gives you a complete lifecycle of the asset.

This is just another great feature of Microsoft Dynamics CRM 2011.

Friday, 15 April 2011

Save and Close Macro for Word

Saw a Tweet recently asking for the functionality to 'Save and Close' from Word, very much like the Microsoft CRM ability.

Nice quick solution - Add a Macro and a button:

First of all, create a macro called SaveandClose with the following logic:

Sub Saveandclose()
'
' Saveandclose Macro
'
'
Application.Quit SaveChanges:=wdPromptToSaveChanges
End Sub

Once the Macro is saved, right click on the Ribbon and choose 'Customize Ribbon':

In the 'Choose Commands From' drop down, choose macros, and find your 'SaveandClose' Macro.
[You may need to create a New Group]

You should then have a Save and Close button on your ribbon.

Thursday, 14 April 2011

CRM 2011 & SharePoint 2010 Issue

Had a real strange issue with the integration between CRM 2011 and SharePoint 2010 (Specifically Foundation).

Everything went well following the implementation guide and list component until I tried to create a library for an account. It would sit there for an age and eventually time-out. Interestingly though all other entities I had associated SharePoint to worked just fine, giving me exactly the functionality I expected.

I tried to run the configuration again, but to no avail. Eventually I decided to get my hands dirty and get in to SharePoint and have a look around. All the respective folders seemed to be setup OK, so I decided to manually delete the 'Account' folder:

........and recreate it through the CRM/SharePoint configuration wizard:

.........and everything now works fine!

Wednesday, 2 March 2011

Preview Images in CRM Notes

Ever wanted to have a preview of images you store in the Notes tab of CRM?

I had a thought recently that this might be quite useful, so I have begun a little project to develop it. It’s not finished but I thought I would just preview it.

Basically any images held within an entity Notes can be previewed:

There’s plenty of scope for developing this further and a CRM 2011 version will be previewed soon.

If anyone is interested in details, let me know.

Rob

Saturday, 12 February 2011

Competitor Monitoring in CRM 2011

There’s an old proverb that says:

“Keep your friends close, and your enemies closer”

That’s never more true than in business. The whole ethos of CRM is that of making, maintaining and keeping relationships with your customers – ‘friends’. What about your ‘enemies’ though? – Your competitors. Do you aim to keep them close?

In a perfect business world you would win every sale, contract or project. We are not in a perfect business world, so at times we may lose business, and although we may lose it because the potential customer decides not to buy at all, for the most part we may lose it to a competitor. Being able to tract that information and learn from it is vital.

Microsoft Dynamics CRM gives you the ability to not only record information on your competitors, but then track lost Opportunities against them:

I have to say it’s a part of CRM that in demos, I have tended to mention but gloss over.

So why is recording our losses and our competitors just as important than recording our wins? Simply put, it will give us the ability to track lost opportunities, and more importantly, why we lost it.

For example if we had a product range that we constantly lost sales to, and specifically lost it to a competitor solely on price, that tells us a lot. Should we reduce our price? How are they able to sell cheaper?

What if we lost it for another reason? Is it our service, our after sales care, is it based on location?

Keeping tabs on our competitors, and in particular what we lose to them, is as important as recording our wins, so take this part of CRM seriously and take the time to use it.

Friday, 21 January 2011

Planning Tools for Microsoft Dynamics CRM 2011

Many of us have our own process for planning a CRM deployment.

The CRM 2011 implementation guides contain a wealth of information for planning, but specifically there is a download link for some planning tools. It’s hidden away a little, but well worth downloading.

The planning tools you get contain 4 folders:

Business Management Planning Tools
Configuration and Customisation Planning
Project Management Tools
Service Scheduling Tools

The collection includes Word Document templates, Excel Spread sheets and Project Files.

If you haven’t had a chance to look yet, and are planning a CRM implementation, it’s well worth a look.

Wednesday, 5 January 2011

New Article on The Microsoft Small Business Site

I have a new article published on the Microsoft Small Business Web site:

You can read the article below:

http://www.microsoft.com/uk/smallbusiness/ask-the-experts/articles/crm-keeping-tabs-on-customers.mspx

Tuesday, 4 January 2011

Top 5 CRMConsult.info Articles

Ok I’ve gone top 5 and top 10 crazy, but I thought I would list the top 5 CRMConsult.info articles.

I wasn’t sure whether to base it on what I would consider the top 5 or which articles have been read the most, but in the end decided to go with the latter.

1. http://www.crmconsult.info/2010/10/quick-start-dialogs-on-microsoft.html

2. http://www.crmconsult.info/2010/08/displaying-visio-diagrams-in-microsoft.html

3. http://www.crmconsult.info/2010/05/10000-record-limit-on-excel-exports.html

4. http://www.crmconsult.info/2010/12/top-10-microsoft-dynamics-crm-2011.html

5. http://www.crmconsult.info/2010/09/bing-maps-and-microsoft-dynamics-crm.html

Wednesday, 22 December 2010

Microsoft CRM 2011 and SharePoint 2010 Integration

Very quick post today.

Just been playing with the CRM 2011 and SharePoint integration.

I have to say it’s very nice. I installed SharePoint 2010 Foundation as I thought most SMB’s would probably be looking at this rather than full blown MOSS.

The help files that come with both the Foundation install and the associated CRM List Components are really quite good, and there wasn’t a great deal of configuration needed (Just had to add .htc extensions to the allowed file list)

The results are great:

What's nice is that it doesn’t ‘feel’ bolted on, but rather part of the system.

The advantages of using SharePoint (And I’m sure there are lots more) Include:

Versioning

Permissions

Alerts when Documents are Edited etc

There are many more advantages to using SharePoint for document management in CRM 2011, and the implementation of it is painless.

Just another reason why Microsoft Dynamics CRM 2011 is a giant leap forward.

Monday, 20 December 2010

Review of 2010

Just thought I would spend a little time reviewing what we have achieved this year at Chorus IT when it comes to Microsoft Dynamics CRM.

I think most businesses would agree it’s not been the easiest of years economically speaking. But just looking back at some of the projects that have completed this year for us, we have been involved with some cool applications and uses of Microsoft Dynamics CRM 4.0.

We finished an implementation of CRM for a charity, which has given them a much clearer picture of their business. It’s enabling them to not only look after their core ‘business’, that of animal welfare, but also the vital financial side which keeps them going.

Another Implementation this year that Joel (www.xrmconsultant.com) took the lead on was for a major Barcode Scanner supplier. They needed a full CRM system that would not only provide their sales team with the history and data needed to sell to new and existing customers, but also needed a comprehensive warehousing system that would enable them to have complete visibility of their stock.

We also provided a CRM system for an international biochemical company that needed to replace their legacy database system with a CRM system that could cope with their Product list numbering over 50,000 lines. The solution truly built on the Microsoft CRM foundation giving them a true xRM solution where a complex pricing structure and matrix could easily be supported.

Finally our Microsoft CRM add-on – PixRM continued to provide CRM users with the ability to attach multiple images to CRM records. This add-on is now being used in the UK, USA and Asia and continues to be popular.

2011 is promising to be an exciting year. Not only is the next generation of Microsoft Dynamics CRM being released on January 20th, but at Chorus IT we have several projects ready to start. We really can’t wait to get going on them!

Sunday, 19 December 2010

Top 10 Microsoft Dynamics CRM 2011 Features

I thought to coincide with the release CRM 2011 RC and the fact that the Global launch date has been set for January 20th, I would take a good look at 2011 and define what I consider the top 10 features. It’s quite a hard thing to do, because according to Microsoft at Convergence 2010 there are over 500 enhancements, so pulling out my top 10 is quite hard.

There are however several areas that I feel are ‘stand out’ enhancements and new features, so the following is my list. I haven’t done it in any order it’s just my favourite 10.

Dialogs

Dialogs are a new feature that goes hand-in-hand with Workflows. In essence, Dialogs are interactive forms which can be used to collect information, or perhaps script a process out.

They could be used in a support environment for example for 1st line technicians to gather initial information before case escalation. They may be of benefit to your sales team for first contact to a prospect.

Once a dialog is finished, the record is saved within the record type it was created from, so if you start it from a case, the record can be viewed from the relevant case:

Option Sets

Option Sets are an absolute triumph. They give you the ability to create pick lists that can be used multiple times, and by multiple entities.

For example you may want to create a pick list of countries or sales regions to keep data uniform. In the past, you may have to create it for all the entities that need it.

Global option sets enable you to create it once and re-use wherever needed. Very useful, and time saving option.

Then just call it from the form:

Built In BI and Dashboards

BI and Dashboards are a vital part of any CRM system, so having them built in to the system as they now are in CRM 2011 is a great step forward.

The nice thing about the way it’s been integrated in 2011, is the ease of use. Building a new chart is really as simple as clicking Charts in the entity, and deciding where you want it and what it should look like:

You also have the ability to create your own dashboards, pulling in from a number entities and records:

You can add views or iFrames to a dashboard, and the ability to drill-down to individual records gives you a fuller BI experience.

Improved Security and Audit

The ability to Audit changes to records is a nice, and sometimes vital feature of Line of business applications.

Microsoft Dynamics CRM 2011 has some great new security features including Audit.

CRM 2011 allows you to switch the audit capability on or off and choose what you want to audit:

Once this is enabled, any changes can be tracked:

You now also have the option to apply field level security to attributes, enabling you to decide if a user has permissions to Create, Update or just Read. This might especially be useful if you don’t want certain personnel to see, for example financial information.

Tighter Outlook Integration

One of the biggest selling points I have always felt with Microsoft CRM, is the tight integration with Office and in particular Outlook. The integration between CRM 2011 and Outlook 2010 is tighter than ever. You don’t feel in any way that you’re ‘dipping’ out of outlook in to another application. CRM records are viewed right there in outlook within reading panes for example.

Sending KB articles, Sales Literature or using Email Templates can now be done right in Outlook.

I’ll pick just one nice feature, that I think will be used over and over again. Conditional formatting.

Conditional formatting enables you to take a view of records, Opportunities for example, and format that view right in the grid in Outlook based on a criteria. So in the example below said that if the Opportunity Expected Close Date is within a week, the details in the view will be Red:

The logic you use s very similar to Advanced Find, so getting something like the results above is very easy, but can be very useful as a quick memory aid.

I think Conditional Formatting will become a greatly used feature.

SharePoint Integration

In the old CRM 4.0 days Smile there were a few ways to integrate SharePoint with CRM. Most of them home grown, and nothing ‘official’. CRM 2011 gives you the ability to integrate SharePoint (Full blown SharePoint or WSS)- by way of contextual libraries – right in the application:

Building Document libraries for your Accounts gives you some great flexibility, and all the nice features of document control SharePoint gives you.

Web Resources

For those of us who are heavy users of additional development of CRM, including custom aspx pages and JavaScript events, the addition of Web Resources in CRM 2011 is an absolute bonus.

In part, the SDK defines Web resources in this way:

Web Resources represent files that might normally be placed on a web server to extend Microsoft Dynamics CRM. Web Resources can be used in Form customizations, Sitemap or the application ribbon because they can be referenced using a URL syntax.

Because Web Resources are stored in the Microsoft Dynamics CRM database and are solution components, they can be easily exported and installed to any Microsoft Dynamics CRM deployment - both for On-Premise and Online. Web Resources are also available to users of the Microsoft Dynamics CRM for Outlook with Offline Access while offline because they are synchronized with the user's data.

You can use the Form Editor to add and configure form enabled Web Resources into your entity forms.

Text based Web Resources (JScript, CSS, XML, XSL and HTML) can be edited and saved within the application. HTML Web Resources can also be edited using a basic HTML Editor in the application.

For most of us that means that all the JavaScript OnLoad, OnSave and OnChange events we put together for 4.0 that had to be exposed for every entity and field, can now be called from a central location – Change for one - Change for all!

Role Tailored Forms

Role specific forms really does make CRM 2011 the Line of Business application for all parts of your Organisation. Imagine the different roles in your organisation: Do your sales team need to see all the same information as you service team? Going even deeper, do all your service guys need to see the same information? For example does the 1st line support need to see as much information as the 3rd line?

Role Tailored forms give you the opportunity to reduce the clutter for people that don’t need to see it.

Data Enhancement/Re-Import

I’m going to be very careful what I say here, as the ability to re-import data that has been exported from CRM was in the beta for 4.0 then removed on production (although you could still get to it if you knew where to look Winking smile )

Enhancing data can be very useful sometimes. Being able to edit it in something like Excel, then push it back in is a great feature.

Just export to Excel as normal and choose the option to make the data available for re-import:

Personal Views

Finally personal views give you the ability to adapt system views and add/take-away columns as you want.

As an added bonus, you can then set default views, so whereas 4.0 the default view was set Organisation-wide, you can now set your own view as the default view.

AndFinally……

The purpose of this post wasn’t to provide an exhaustive view of these 10 features. It was just to wet the appetite of anyone using or planning to use Microsoft Dynamics CRM 2011.

I’m sure another CRM consultant may choose other new or improved features, but my aim was just to start to highlight what a great tool Microsoft Dynamics CRM 2011 promises to be.

Can’t wait for the official launch in January 2011

Pages

Tuesday, 24 January 2012

Sunday, 8 January 2012

Saturday, 17 December 2011

Contextual FetchXML Reports

Parameters

Thursday, 8 December 2011

Wednesday, 9 November 2011

Friday, 4 November 2011

Thursday, 27 October 2011

Wednesday, 26 October 2011

Name

Definition

Options

Lead

Contact

Account

Opportunity

Saturday, 22 October 2011

Tuesday, 18 October 2011

FormatNumber

Alternating Row Colours in a Table

Colour change

FetchXML Report Parameters

Display chosen parameters

Contextual FetchXML Reports

Wednesday, 5 October 2011

Thursday, 29 September 2011

Monday, 19 September 2011

Friday, 2 September 2011

Thursday, 11 August 2011

Tuesday, 5 July 2011

Monday, 30 May 2011

Friday, 27 May 2011

Sunday, 24 April 2011

Friday, 15 April 2011

Thursday, 14 April 2011

Wednesday, 2 March 2011

Saturday, 12 February 2011

Friday, 21 January 2011

Wednesday, 5 January 2011

Tuesday, 4 January 2011

Wednesday, 22 December 2010

Monday, 20 December 2010

Sunday, 19 December 2010

Dialogs

Option Sets

Built In BI and Dashboards

Improved Security and Audit

Tighter Outlook Integration

SharePoint Integration

Web Resources

Role Tailored Forms

Data Enhancement/Re-Import

Personal Views

AndFinally……