Custom Fetchxml based Report in CRM 2011 using SSRS

As you all know there are mainly two types of Custom reports could be developed by using SSRS ( SQL Server Reporting Services ) 

•  SQL- based ( Microsoft MSDN says "For security reasons, you cannot deploy custom SQL-based reports to Microsoft Dynamics CRM Online") 

•  Fetch - based. ( Works in both On-Premise and Online versions)

Fetchxml basics could be found here --http://crmdm.blogspot.com/2011/02/fetch-xml-in-ms-dynamics-crm-2011.html

This post is to demonstrate how to do a very simple fetch based ( Fetchxml based ) report using SSRS.

Scenario : There are different kinds of system Jobs in CRM 2011 ( For instance Workflow, Bulk delete Bulk email etc ). The user would like to see an overview of these system jobs type and the count of each of these system jobs in a chart. So how many workflow system jobs, how many bulk delete jobs etc in CRM.

In simple words just imagine a graph for which one axis represents System job type ( Horizontal ) and other axis (Vertical ) represents the Count.

So We have SQL Server Business Intelligence Development Studio ( version 2008 ) installed and also Microsoft Dynamics CRM 2011 Report Authoring Extension installed on top of that.

Its good understand the importance of Microsoft Dynamics CRM 2011 Report Authoring Extension. This plug-in provides us the option to use Fetchxml queries from  SSRS. If we don't have this plug-in then the "Microsoft Dynamics CRM Fetch' option will not be available in the SSRS Data source.

Here we are using a CRM On-Premise version to demonstrate this report.

Now its time for kick off.

1. Select the SQL Server Business Intelligence Development Studio ( BIDS ) as shown below.

2. File --> New --> Project.

3. Business Intelligence Projects--> Report Server Project. Lets call it as "DEMO Project". Say okay to them.

4. View solution explorer and Reports right click Add--> New item.

5. Select Report and lets call the report SystemJobsCountBasedonType.dll and say 'Add'.

6. So we could see the Design area by default as shown below.

7. The next step is to define a Data Source. Right click on the Data Sources and Add Data Source..

8. Lets call the Data source as DSourceDEV. Please note the option Microsoft Dynamics CRM Fetch is available because of the plug-in Microsoft Dynamics CRM 2011 Report Authoring Extension as mentioned earlier. Connection String format is http://ServerName/Orgname and for Online version, the corresponding connection string to be used.

9. In the Credentials tab its possible to choose the credentials as shown below. For online version, we could provide username and password. For now, we choose Windows Authentication

9.  The next step is to choose a Data Set. Right click on the Datasets and Add Dataset.

10. Lets call the Dataset as DSetSystemJobsCountBasedonType. And choose the option Use a dataset embedded in my report. Click on the Query Designer button. We are going to design the Fetchxml query in the Query Designer

11. Regarding System Jobs .  "A system job, also known as an asynchronous operation, is used to define and track the execution of an asynchronous operation, for example an asynchronous registered plug-in, workflow, or other background system operation. asyncoperation record is created in the database by the platform whenever an asynchronous plug-in or a workflow is to execute, or any one of other asynchronous system operations is to be performed" - ref: MSDN

Fetchxml basics could be found here --http://crmdm.blogspot.com/2011/02/fetch-xml-in-ms-dynamics-crm-2011.html

So we are going to write to the fetchxml for the enity System Job (name for this entity is AsyncOperation)

Here is a simple Fetchxml to retrieve all the Attributes of AsyncOperation entity. This is not our final query but just to give you a helicopter view.

12. Now lets modify the fetchxml for our scenario.

Count of each System Job Types.

So need the count of System jobs and grouped by System Job type.

This means Aggregate of System Jobs based on count and  grouped by its type.

Please note a few things in this fetchxml. 

aggregate = 'true' -- This indicates the we are going to use an aggregate function in the fetchxml.

aggregate ='count' -- Here we need the aggregate function count. Because we need count of system jobs.

groupby='true' -- Group by System job type. And its based on count.

13. So our dataset looks like this. Say Okay and press Okay.

14. Please note that the selected fields are available as show below.

15. From the tool box, select the Chart and drag it to the Design Area.

16. Select the chart type. Its possible to change the chart type later. For now lets just select the column type.

17.  You could see a chart inserted. Double click on the chart.

18 Now drag the CountValue to the Aggregate section and System job type to the Category groups. Please note that its possible to resize the report. Also possible to change the title if you double click on it. Axis titles also could be changed. So please feel to play around it.

19. You could see that The Countvalue added in Aggregation section and Type in Category group. Also change the axis title accordingly. We could see the preview easily which is the tab next to Design.

14.  Here is the preview. Please note this is a very simple report.  The principle is "First things first".

15. In order to deploy this report, please refer the following link. 

How to Deploy a custom Fetchxml Report in CRM 2011

http://crmdm.blogspot.com/2013/09/how-to-deploy-custom-fetchxml-report-in.html

Deployment of Custom Workflow activity in CRM 2011- Tips

As we all know, we could create custom workflow activities in on-premise deployments of CRM 2011.  This post is regarding the deployment of custom workflow activity.

Lets consider a scenario.

So imagine that we have a workflow say 'CalculateInterest' which has got a custom workflow activity namely 'InterestZonewise' in it. After the deployment to the production it was noticed that the workflow 'CalculateInterest' was turned into draft state and custom workflow activity in error state although it was in active state in DEVELOPMENT. 

Later it was found that we could resolve this problem by running IISRESET command in the PRODUCTION CRM Server. After the IISRESET, the custom workflow activity was identified by the CRM system and which also turned the workflow into active state. 

 

Facebook with CRM 2011 OR Facebook Image on entity Form in CRM 2011

Ref: MSDN Blogs- ("http://blogs.msdn.com/b/crm/archive/2011/09/28/displaying-a-contact-s-facebook-picture-in-microsoft-dynamics-crm-2011.aspx")


Can you imagine that your contact form is displayed with contact's Facebook image? Well, lets bring this imagination into reality.


Samples:

The interesting thing is that it could be done using simple javascript code. So lets kick off.


Consider the Contact entity. Now the idea is that on the contact form we are going to place a default image. And if the contact could provide the Facebook ID, then it would be replaced by the real profile picture from Facebook.


The default image could be something like this ( Eg: Dimension of 109x108 pixels). And this is saved as a webresource.

Lets have a look at the form design.
As you all know, we need a place holder for the image. And this could be a web resource. Thanks to CRM 2011 !
Lets place a new web resource on the form.

We could choose the default image which we already uploaded to web resources.

Also lets have a quick look at the Formatting tab of Webresource properties. Certainly, you could play around further for the best display results.

Also there would be one custom attribute on the form namely ' Facebook ID' to supply the Facebook ID from the User. Here is the form Design

In our case,
Facebook ID could be supplied in 2 formats
1. As a string ID. For instance, datong, d.manjaly etc
2. As a numeric ID. For instance, 100003583160143
The javascript code will pull the profile picture using the Facebook ID.
As you all know, the javascript code should be saved as webresource and should be triggered at the Form Load event as shown below.

Javascript Code:

function profilePicture_onFormLoad() {
 var profilePictureElement = Xrm.Page.getControl("WebResource_myphoto");
    var facebookAttribute = Xrm.Page.getAttribute("ap_facebookid");
   if (facebookAttribute && facebookAttribute.getValue() != null ) {
        var profileUrl = "http://www.facebook.com/"+facebookAttribute.getValue();
        if (profileUrl) {
            var profilePictureUrl = getProfilePictureUrl(profileUrl);
            if (profilePictureUrl) {
                // set src attribute of default profile picture web resource.
                profilePictureElement.setSrc(profilePictureUrl);
                return;
            }
        }
  }
}


function getProfilePictureUrl(profileUrl) {
    // trim trailing forward slash in url
    profileUrl = profileUrl.replace(/\/*$/, "");


    var patterns = [];
    // Format is http://www.facebook.com/userid
    patterns[0] = /^http:\/\/www\.facebook\.com\/([a-zA-Z0-9\.]+?)$/;
    // Format is  http://www.facebook.com/profile.php?id=987654321
    patterns[1] = /^http:\/\/www\.facebook\.com\/profile\.php\?id=(\d+?)$/;


    for (i in patterns) {
        var matches = patterns[i].exec(profileUrl);
        if (matches) {
            return "http://graph.facebook.com/" + matches[1] + "/picture?type=normal";
        }
    }
    return null;
}



Here we go....
A new contact form would have the default image on it

The new image would be displayed as soon as the user key in the Facebook ID and save the form.  (Refer the samples given at the beginning)

Duplicate Detection in CRM 2011 --- Pitfalls

As you all know Duplicate Detection is a very good feature in Dynamics CRM 2011. But there are some useful tips to be remembered.


Lets consider a scenario.


Imagine that a user Mr.Derek would like to import some data into CRM 2011


Data Import feature in CRM 2011 is explained here http://crmdm.blogspot.com/2011/04/how-to-import-data-into-crm-2011.html


So Derek sets the 'Allow Duplicates' to No as shown below.

But please remember that the above option works based on the duplicate detection settings and duplicate detection rules defined.

Sadly, after the import Derek found duplicates. What could be the reason for this?

  There is a pitfall related to duplicate detection rules. As you all know, the duplicate detection rule must be in a published state if that has to be valid.
So in our case Derek was for sure that he published all the duplicate detection rules after defining them. But from his investigation, he finds that the state of the duplicate detection rule was turned automatically into unpublished later. And this is the pitfall.

Now the very important thing how does it happens ? How and Why the state of a duplicate detection rule turns from published to unpublished ?


We found the very useful information from a blog.
"By design, whenever any entity metadata is changed all duplicate rules associated with that entity are unpublished"
Here is reference for the above information.
Ref: http://www.madronasg.com/blog/duplicate-detection-rules-automatically-unpublished


Note: In simple words, whenever we do a release (importing solution to Test OR QA OR Production) if there is any change for an entity, say  for contact entity then the duplicate detection rule for contact would turn into unpublished state. So after importing a solution we need to make sure that the duplicate detection rules are in published state. This check could be done either manually or through scripts.


Hope this helps you !

Latest SDK for CRM 2011

             Microsoft released the latest SDK for CRM 2011 ( version 5.0.9  Dated 16.01.2012) . It could be downloaded from the following link.


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

eXtremeCRM 2012 Berlin

One of our Colleagues ( Mr. Sebastian ) would be a speaker at this great event.

Hats off you Seb ! Wish you good luck !

Lets listen to his words,

"eXtremeCRM in 2012 is going to be held in Berlin and again I have a privilege to be invited as speaker. What I like about that  conference, well  eXtremeCRM is great event , deep technical readiness on Microsoft Dynamics CRM 2011 a lot of content delivered by Microsoft product team, and technical experts within the channel, not enough new track is coming Practice Leaders Summit. Now a bit about myself, I’ll  present definitely more about Azure + XRM  working together a lot of new content in comparison to event in Prague and also some new session about SharePoint 2010 + XRM, details will be soon published on eXremeCRM website. There is still a time to register so don’t miss it and see you in Berlin" -- Mr. Sebastian

Further details could be found from his blog

http://www.mindthecloud.net/?p=605

OR From eXtremeCRM website

http://extremecrm.com/default.aspx

How to override System Ribbon Button in CRM 2011 ( Display / Enable Rule to System Ribbon Button in CRM 2011)

This post would shed light on the following.
How to override System Ribbon Button in CRM 2011 ( Display / Enable Rule to System Ribbon Button in CRM 2011)?
How to show / hide a system button based on a value on the form?


In CRM 2011, it is possible to override the System ribbon button functionality. Also we could apply Enable / Disable Rule Display / Hide Rule to the existing System Ribbon button.
In order to grasp this quickly, lets consider a scenario.


As we all know, there is a Deactivate system ribbon button on the Account form.
Suppose we would like to Enable / Disable this button based on a value on the Account form. To make it simple, lets consider the City field. 
For instance, if the city is London, then Deactivate system button should be enabled. Also we should be able to fire our custom javascript when the Deactivate System button gets clicked. This sample could take you through very useful scenarios.


There is a key to unlock the door here. what is that key ? Lets see.


If you refer the SDK, you could find the following folder and file


sdk\resources\exportedribbonxml\accountribbon.xml


Lets find out our System Deactivate button.


Please Note: We don't need to write the following piece of code. This is just an easy way to refer and then to find the key from SDK.
<Button Id="Mscrm.Form.account.Deactivate" ToolTipTitle="$Resources:Ribbon.HomepageGrid.account.Record.Status.Deactivate" ToolTipDescription="$Resources(EntityPluralDisplayName):Ribbon.Tooltip.Deactivate" Command="Mscrm.Form.Deactivate" Sequence="60" Alt="$Resources:Ribbon.HomepageGrid.account.Record.Status.Deactivate" LabelText="$Resources:Ribbon.HomepageGrid.account.Record.Status.Deactivate" Image16by16="/_imgs/ribbon/deactivate16.png" Image32by32="/_imgs/ribbon/Deactivate_32.png" TemplateAlias="o2" />


Now can you guess what's the key in this? Its nothing but the Command.


The only key thing we need to override the System Ribbon button is to know the command value. Rest is as easy as we open a door with a valid key. We need to write the CommandDefinition as shown below.

Please make sure that the code is written under right parent tags.



          <CommandDefinition Id="Mscrm.Form.Deactivate">
            <EnableRules>
              <EnableRule Id="Mscrm.Form.account.Deactivate.EnableRule"></EnableRule>
            </EnableRules>
            <DisplayRules></DisplayRules>
            <Actions>
              <JavaScriptFunction Library="$webresource:ap_DeactivateOverride.js" FunctionName="DeactivateOverride" />
            </Actions>
          </CommandDefinition>




Also the enable rule under rule definitions as show in the above picture.

         <EnableRule Id="Mscrm.Form.account.Deactivate.EnableRule">
              <ValueRule Field="address1_city" InvertResult="false" Value="London"/>
            </EnableRule>


Here we go.


Scenario1: City value is not London -- Deactivate Button got disabled.

Scenario2: City value is London -- Deactivate Button got enabled.

Scenario3: City value is London -- Deactivate Button was clicked by the user. (alert from custom js function)

As this is my first post in 2012,

"тнєяє ѕнαℓℓ вє ѕнσωєяѕ σƒ вℓєѕѕιηg тσ уσυ αℓℓ ιη тнє вℓσωιηg ƒℓσωєя -2012"