CRMConsult.info

Be Smart About Charts in CRM 2011

noreply@blogger.com (RP) — Thu, 19 Apr 2012 14:31:00 +0000

Microsoft Dynamics CRM 2011 bought in some great capabilities. The Dashboard and Charting functionality really stepped up the ability to visualise data.

There are a couple of things that until recently I had forgotten about. Really I had got in to a rut of just creating standard Dashboards and Charts, so I thought I would just blog these reminders, which are by no means breakthrough developments, but more about just reminders of some nice functionality.

Firstly, There's the ability to display charts in records. For example you might want a series of charts in the header of a Contact record to show current Cases, activities etc:

The process for achieving this is:

Create your chart in the relevant entities
Create a Section in the record type you want to display the chart in
Click Insert --> Sub-Grid

You'll be faced with the following:

The Key thing to remember here is to choose the Data Source, so in this case it was Cases, Then tick the Box that says 'Show Chart Only'
Theres then a bit of tweaking around formatting, but essentially you'll get the results shown above.

The other thing worth a reminder is that you can 'Tweak' the chart by directly editing the XML. In the case above I've switched on the 3D formatting, which you can only do by editing the XML (No idea why!)

To do this....
Navigate to the Chart (In my case I could get to it through the solution)
Choose the Chart you want to edit and Click 'More Actions' --> Export Chart

This will then give you the option to save the XML.
My inclination is to edit in Notepad
For the option of switching on the 3D, just change the following to 'true':

There are some other teaks you can perform with the Charts. The following sites might help:

http://blogs.msdn.com/b/crm/archive/2011/01/04/crm-2011-charts-know-the-real-potential-part-i.aspx

Hope this reminder helps.

Rob

Direct Link for Copy Address to Clipboard Solution

noreply@blogger.com (RP) — Fri, 23 Mar 2012 10:26:00 +0000

I have a lot of people asking about the solution for copying an address in CRM to the clipboard, so I'm posting the perm link here, and also placing a link on the blog:

You can download the solution from my Skydrive:
https://skydrive.live.com/embed?cid=26342CD0EBB312D7&resid=26342CD0EBB312D7%21364&authkey=AA4SPAOuuBg9BHE
Please be aware the solution is offered without any warranties or support.

Rob

Hide Navigation Controls in Microsoft Dynamics CRM 2011

noreply@blogger.com (RP) — Thu, 22 Mar 2012 16:12:00 +0000

You may have a need to hide a navigation option on a CRM form dependent on a field in the record....
For example you might want to hide it based on Account type.

To accomplish this you need to first of all find out what the Nav item is called. You can do this by opening the form then running the Developer Tools (F12) and searching for the Nav item (In the example below I needed to find the link to a related entity that was only relevant to certain Account types)

The code is:
function Navcontrol()
{
//The following returns the value of the option set and the Nav item in to two variables

    var contactType = Xrm.Page.getControl("arcrp_accounttype").getAttribute().getValue();
    var objNavItem = Xrm.Page.ui.navigation.items.get("nav_arcrp_account_arcrp_localauthoritydetails_LocalAuthority");

if (contactType == 532000001)

//Display nav element if the contact type is ##
{

    objNavItem.setVisible(true);
}

else
    // Or hide it
{

objNavItem.setVisible(false);

}
}

Rotate Report in CRM 2011 Dashboard using HTML Web Resource

noreply@blogger.com (RP) — Mon, 12 Mar 2012 09:45:00 +0000

A couple of years ago I wrote a blog article that went through the process for using an HTML page that could display and rotate SSRS reports. We used them in my previous role to display case management dashboards projected on a wall.

The process was to host the HTML in the ISV folder calling in the reports with their URLS (and any parameters you might want to include)

Of course in Microsoft Dynamics CRM 2011, there is no ISV folder, however you can add the HTML file as a web resource. That then opens the possibility of calling the web resource in to a CRM dashboard, so the reports you pull in just keep refreshing and rotating.

The HTML to accomplish this is:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
　
　
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Dashboard</title>
</head>
　
　
<body style="background-color: #000000">
<br />
<iframe name="rotate" id="rotate" src="about:blank" scrolling="no" framespacing="0" frameborder="0" marginwidth="0" marginheight="0" border="0" style="width:100%; height:768px"></iframe>
　
　
<script language="JavaScript" type="text/javascript"></script>
　　
</body>
　　
</html>
　
　

The Variable 'Pages' needs to hold the URL's for the reports and the variable 'rint' holds the frequency of the rotation.

Re-import Dynamic Spreadsheet as Report

noreply@blogger.com (RP) — Tue, 28 Feb 2012 08:27:00 +0000

One of the very useful features of Microsoft Dynamics CRM is the ability to export data as Excel spreadsheets, both static and dynamic. A feature that is also useful, but probably under used is the ability to manipulate that spreadsheet, perhaps adding columns and formula's, then re-importing as a report.

Why is this useful?

Well I guess it's quite rare that only one person would want to see that information.By re-importing as a report, you can then just email the link (another feature) and anyone that needs it, can download it.

The process is simple enough. Just export your Excel spreadsheet as normal and save it.
Manipulate whatever way you want then go to the reports area of CRM and Choose Report Type 'Existing File' and navigate to the spreadsheet:

There you have it.

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.

CRMConsult.info

Be Smart About Charts in CRM 2011

Direct Link for Copy Address to Clipboard Solution

Hide Navigation Controls in Microsoft Dynamics CRM 2011

Rotate Report in CRM 2011 Dashboard using HTML Web Resource

Re-import Dynamic Spreadsheet as Report

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_