CRMConsult.info

Flags and Alerts Web Resource in Microsoft Dynamics CRM 2011

noreply@blogger.com (RP) — Thu, 16 Feb 2012 16:21:00 +0000

One of the requirements for the project I'm working on is to easily show any alerts or flags against a record. The CRM record itself might record it somewhere on the form, but having a very quick reference or reminder can be quite important.

I thought about going down the Silverlight road, but in this case it didn't need to be too complicated.

The solution is an HTML web resource which looks for the flags and dependent on them will display an alert:

Bad Alert

Good Alert!

The alert in the example above is toggled by the 'Credit Hold' choice, but could easily be used in different ways.

This is the basic web resource, which I haven't cleaned up from my early attempts, but could easily be hacked around to suit:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

function ContextInformation() {

document.getElementById('td2').innerHTML = window.parent.Xrm.Page.getAttribute('creditonhold').getValue();
var result = window.parent.Xrm.Page.getAttribute('creditonhold').getValue();

}

</script>

</head>

                document.getElementById('td2').innerHTML = window.parent.Xrm.Page.getAttribute('creditonhold').getValue();
                var result = window.parent.Xrm.Page.getAttribute('creditonhold').getValue();
                //alert(result);

if (result == true) {
document.write('<img alt= "alert2" style="height: 38px; width: 51px" src="http://crm-dev-01:5555/CRMDEV/WebResources/arcrp_alert" /><font color ="Red"><marquee>There is a Flag on this record</marquee></font>');

}

                else {
                    document.write('<img alt= "alert2" style="height: 51px; width: 51px" src="http://crm-dev-01:5555/CRMDEV/WebResources/arcrp_Smile" />');
                }
</script>

</body>
</html>

Calculate Age from Date of Birth in Microsoft Dynamics CRM 2011

noreply@blogger.com (RP) — Wed, 15 Feb 2012 10:30:00 +0000

There are a few posts regarding this, but thought I would throw mine in as well.
Most Organisations have a Date of Birth field, but it's also useful to have an Age field, and even more useful if it's calculated based on the DOB value.

You might think it's a simple equation of Current Year - Birth Year e.g.
2012 - 1969 = 43, but actually for me (born in June) this is wrong at the moment, so the code needs to look at the DOB and work out whether the birthday has passed in the current year.

So the code is:

function CalcAge()
{
    var now = new Date(); //Todays Date
    var birthday = Xrm.Page.getAttribute("arcrp_dob").getValue(); //Get the Date of Birth value

    var diff = now.getMonth() - birthday.getMonth(); //Check to see if Birthday has already passed
if (diff > -1) //If Birthday has already occurred
    {
        var bd1 = now.getFullYear() - birthday.getFullYear();
        //set the age attribute
        Xrm.Page.getAttribute("arcrp_age").setValue(bd1.toString());
    }
    else //If Birthday has not already occurred
    {
        var bd2 = now.getFullYear() - birthday.getFullYear() - 1;
        Xrm.Page.getAttribute("arcrp_age").setValue(bd2.toString());
    }
}

CRM 2011 Roadmap - R8 Release Preview Guide

noreply@blogger.com (RP) — Mon, 06 Feb 2012 15:14:00 +0000

Some nice things coming up in the world of Microsoft Dynamics CRM 2011

Mobile solutions accross platforms - Microsoft Dynamics CRM Mobile
Cross Browser functionality - CRM Anywhere

Read about them here:

http://crmpublish.blob.core.windows.net/docs/ReleasePreviewGuide.pdf

Useful Microsoft CRM 2011 Documentation

noreply@blogger.com (RP) — Fri, 03 Feb 2012 12:12:00 +0000

Pulled together a list of useful CRM 2011 Documents and their download links.

Feel free to add comments with ones I have missed out.

Microsoft Dynamics CRM 2011 Quick Start Guide for Business Users

http://www.microsoft.com/download/en/confirmation.aspx?id=27575

Microsoft Dynamics CRM 2011 User’s Guide

http://www.microsoft.com/download/en/details.aspx?id=27134

Microsoft Dynamics CRM 2011 for Outlook Quick Start Guide

http://www.microsoft.com/download/en/details.aspx?id=27574

Microsoft Dynamics CRM 2011 Administrators Guide

http://www.microsoft.com/download/en/details.aspx?id=27135

Microsoft Dynamics CRM 2011 for Outlook Installing Guide for use with Microsoft Dynamics CRM Online

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=15822

Microsoft Dynamics CRM 2011 Implementation Guide – Includes link to Planning Guides

http://www.microsoft.com/download/en/details.aspx?id=3621

Optimizing and Maintaining the Performance of a Microsoft Dynamics CRM 2011 Server Infrastructure

http://www.microsoft.com/download/en/details.aspx?id=27139

Optimizing and Maintaining the Performance of a Microsoft Dynamics CRM 2011 and CRM Online

http://www.microsoft.com/download/en/details.aspx?id=23261

Microsoft Dynamics CRM 2011 Performance Counters

http://www.microsoft.com/download/en/details.aspx?id=27119

Microsoft Dynamics CRM 2011 for E-mail Router Installing Guide for use with Microsoft Dynamics CRM Online

http://www.microsoft.com/download/en/details.aspx?id=18991

How to configure the Microsoft Dynamics CRM on-premises and Microsoft Dynamics CRM Online E-mail Router in different deployment scenarios

http://www.microsoft.com/download/en/details.aspx?id=21445

Microsoft Dynamics CRM 2011 Readme

http://rc.crm.dynamics.com/rc/2011/en-us/on-prem/5.0/readme.htm

Microsoft Dynamics CRM 2011 Readme

http://rc.crm.dynamics.com/rc/2011/en-us/online/5.0/readme.htm

Microsoft Dynamics CRM 2011 Entity Relationship Diagrams

http://www.microsoft.com/download/en/details.aspx?id=2640

Entity Not Displayed After Solution Import

noreply@blogger.com (RP) — Tue, 24 Jan 2012 09:48:00 +0000

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:

Programmatically Set Field Requirement Levels

noreply@blogger.com (RP) — Sun, 08 Jan 2012 20:38:00 +0000

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

SQL Report from iTunes

noreply@blogger.com (RP) — Sat, 17 Dec 2011 21:35:00 +0000

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

noreply@blogger.com (RP) — Sat, 17 Dec 2011 18:27:00 +0000

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

Customise The CRM 2011 Masthead

noreply@blogger.com (RP) — Thu, 08 Dec 2011 11:07:00 +0000

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.

Moving On

noreply@blogger.com (RP) — Wed, 09 Nov 2011 09:06:00 +0000

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

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

noreply@blogger.com (RP) — Fri, 04 Nov 2011 16:26:00 +0000

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.

CRMConsult.info Reaches 50,000 Views

noreply@blogger.com (RP) — Thu, 27 Oct 2011 09:33:00 +0000

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

Microsoft Dynamics CRM Glossary

noreply@blogger.com (RP) — Wed, 26 Oct 2011 09:56:00 +0000

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.

FetchXML Reporting in Microsoft Dynamics CRM 2011–Part 1

noreply@blogger.com (RP) — Sat, 22 Oct 2011 17:42:00 +0000

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

SQL Snippets

noreply@blogger.com (RP) — Tue, 18 Oct 2011 20:49:00 +0000

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.

Originating Lead Notes in Accounts and Contacts

noreply@blogger.com (RP) — Wed, 05 Oct 2011 11:51:00 +0000

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

When ‘This Week’ is not really This Week

noreply@blogger.com (RP) — Thu, 29 Sep 2011 09:14:00 +0000

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!

CRM Report for Entity and Attribute Details

noreply@blogger.com (RP) — Mon, 19 Sep 2011 11:18:00 +0000

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.

Add a Calculator to a CRM Record Using Web Resources

noreply@blogger.com (RP) — Fri, 02 Sep 2011 09:55:00 +0000

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.

Pre-Filtering Reports – Fun with CRMAF_

noreply@blogger.com (RP) — Thu, 11 Aug 2011 07:25:00 +0000

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.

Copy Account Address to Clipboard for Dynamics CRM 2011

noreply@blogger.com (RP) — Tue, 05 Jul 2011 14:53:00 +0000

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.

Spatial Reporting for Microsoft Dynamics CRM

noreply@blogger.com (RP) — Mon, 30 May 2011 15:23:00 +0000

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.

Cascading Parameters in SSRS Report

noreply@blogger.com (RP) — Fri, 27 May 2011 15:27:00 +0000

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.

Connections in Microsoft Dynamics CRM 2011

noreply@blogger.com (RP) — Sun, 24 Apr 2011 21:23:00 +0000

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.

Save and Close Macro for Word

noreply@blogger.com (RP) — Fri, 15 Apr 2011 10:25:00 +0000

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.

CRMConsult.info

Flags and Alerts Web Resource in Microsoft Dynamics CRM 2011

Calculate Age from Date of Birth in Microsoft Dynamics CRM 2011

CRM 2011 Roadmap - R8 Release Preview Guide

Useful Microsoft CRM 2011 Documentation

Entity Not Displayed After Solution Import

Programmatically Set Field Requirement Levels

SQL Report from iTunes

FetchXML Reporting in Microsoft Dynamics CRM 2011–Part 2

Contextual FetchXML Reports

Parameters

Customise The CRM 2011 Masthead

Moving On

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

CRMConsult.info Reaches 50,000 Views

Microsoft Dynamics CRM Glossary

Name

Definition

Options

Lead

Contact

Account

Opportunity

FetchXML Reporting in Microsoft Dynamics CRM 2011–Part 1

SQL Snippets

FormatNumber

Alternating Row Colours in a Table

Colour change

FetchXML Report Parameters

Display chosen parameters

Contextual FetchXML Reports

Originating Lead Notes in Accounts and Contacts

When ‘This Week’ is not really This Week

CRM Report for Entity and Attribute Details

Add a Calculator to a CRM Record Using Web Resources

Pre-Filtering Reports – Fun with CRMAF_

Copy Account Address to Clipboard for Dynamics CRM 2011

Spatial Reporting for Microsoft Dynamics CRM

Cascading Parameters in SSRS Report

Connections in Microsoft Dynamics CRM 2011

Save and Close Macro for Word