Custom Fetchxml based Grid Report ( With Matrix control ) in CRM 2011 using SSRS OR Custom Grid Report in CRM 2011 using SSRS

Please refer the following links for basic steps.

Custom Fetchxml based Report in 

CRM 2011 using SSRS - http://crmdm.blogspot.com/2013/09/custom-fetchxml-based-report-in-crm.html

How to Deploy a custom Fetchxml Report in CRM 2011

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

This post is regarding a custom fetchxml report which uses the report control ' matrix' OR a grid report in simple words.

"Matrix is a report tool component which displays aggregated data in a grid that has a variable number of columns and variable number of rows"

Lets understand grid report with a sample scenario.

Scenario 1: 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.  So System jobs  should be displayed in a grid. The grid should have the Column group as Year and System Job type as row group. The count of System jobs should be displayed in the grid for the corresponding system job type and year.

Scenario 2: 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.  So System jobs  should be displayed in a grid. The grid should have the Column group as Year and System Job type as row group. The grid should have a further column grouping based on the status of the system job. The count of System jobs should be displayed in the grid for the corresponding system job type,year and status of system job.

So lets get the ball rolling.

Scenario 1:

1. Here is the fetchxml Query. The System Job entity is  called 'asyncoperation'

It has an aggregate function to find the count of system jobs. It has multiple grouping. One grouping is based on System Job type and other one based on Year.

<fetch distinct='false' mapping='logical' aggregate='true'> 

   <entity name='asyncoperation'> 

       <attribute name='asyncoperationid' alias='COUNT' aggregate='count'/> 

       <attribute name='operationtype' alias='SystemJobType' groupby='true' /> 

       <attribute name='createdon' groupby='true' dategrouping='year' alias='year' />

   </entity> 

</fetch>

2. Dataset is shown here.

3. We could see the selected fields in the Data set as shown below.

4. Select the matrix control from the tool box and drag it to the Design area as shown below.

5. Its possible to re-size the matrix. And then double click on it.

6. Lets put the row group first. So in our case its System job type. This can be done in 2 ways.

Either we could drag System job type to the Rows OR select the Row cell of the matrix and then a tiny box will appear in the top right corner of the Rows as shown below. So click on it. Lets select System Job Type.

7. Secondly we could put the column group. In our case its Year. Lets drag year value to the Columns as shown below.

8. And the next item in the Matrix is Data. Lets put System Jobs count value into that.

9. Lets change the first cell description as System Job Type Vs Year. (Optional)

10. Here comes the end of fist scenario.

To deploy this report in CRM 2011 please refer the following.

How to Deploy a custom Fetchxml Report in CRM 2011

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

Scenario 2 :

1 Lets alter the fetchxml query as per the requirement in scenario 2.  It should have an additional column group for status of the system job. So we need one more grouping based on the status.

2.  So now we have status field added to our dataset as shown below.

3. We need to select the column group and then right click and then Add Group --> Child group.

And choose Status. In this case first level column grouping is based on Year and the next one is based on status.

4. So now our matrix includes the status. Please note that the status is added to column groups.

So first group it by year and then status on the column level.

5.  We could put a description for the status as shown below.

6.  And here is our apple pie.

7. To deploy this report in CRM 2011 please refer the following.

How to Deploy a custom Fetchxml Report in CRM 2011

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

How to Deploy a custom Fetchxml Report in CRM 2011

Please refer the following link for - Custom Fetchxml based Report in CRM 2011 using SSRS

http://crmdm.blogspot.com/2013/09/custom-fetchxml-based-report-in-crm.html

Lets see how to deploy a simple custom fetch xml report in CRM 2011.

1. Navigate to the report section in CRM 2011. Click on New.

2. Choose the report type as Existing file.

Navigate to the file location where the report project was saved.

Choose the .rdl file, in this case its SystemJobCountBasedonType.rdl

Description: optional

Related Record Types : Choose this if the report is to be run on a particular entity type record. In this case its fine because its just an overview sample report. Click on Save and Close

3. Newly created report could be found in the reports sections. Choose the report and double click on it. We could see the report in the report viewer as shown below.

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)