Thursday, 31 March 2016

Intercept Column/Field Value in CRM View using RetrieveMultiple Plugin C#

In the previous post, I explained about how to modify the lookup field display using Plugin in CRM.

Now, I just want to show you how to modify also other column and I give a use case here is to display the column as Calculated Field.

Well, in CRM 2015 and above Microsoft introduced 2 new types of field: Calculated and Roll-up field, but what if your organization still using earlier version and you already created a field without using that type (do you care to re-create again?) or you want user not to always refresh it.

So, here I want to give you use case in simple scenario, that is to display “Age” field dynamically based on Birthdate of Contact versus current date.

Use Case

Now you have a custom field: Age, as Integer or Whole Number, but all blank.

You have choice: Always update this using batch job that will run everyday or you show as a report dynamically.

So, this is the combination of those choices, you do not need to create report, but you just need to intercept the Plugin to show the data that you want.

So here is before you apply the plugin:

*Age is blank

The Code

And here is the code:

 public void Execute(IServiceProvider serviceProvider)
            #region must to have

            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

            // Create service with context of current user
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

            //create tracing service
            ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));


            //this is to modify the Age column

            if (context.OutputParameters.Contains("BusinessEntityCollection"))
                var retrievedResult = (EntityCollection)context.OutputParameters["BusinessEntityCollection"];
                foreach (Entity entity in retrievedResult.Entities)
                    if (entity.Contains("birthdate"))
                        var birthday = entity.GetAttributeValue<DateTime>("birthdate");
                        int age = CalculateAgeYear(birthday);
                        //modify the Age field
                    entity.Attributes["pa_age"] = age;

You can modify any field so long you put the correct type of object as the value based on the each field type


And here is the result


Yes, you can display this in the view.

What if you want to display in the form?
You need to tweak the plugin and use the “Retrieve” message, instead.

Or you can use this as well

*Thanks Pedro, my friend.

Which Retrieve can also be used for form and also RetrieveMultiple also.

Well, this is not only for one field, is can be for many fields..Including lookup that I explained in my other previous post.

And the good thing also you can display in any view so long the field is exist.

But the disadvantage is you cannot use this as keyword to search or filter, because it is actually not stored in the database.


Active Contacts View


So by using this capability, you can extend this for example to display the combination of 1 to N records!

For example: Total Number of related cases, total dollar of Revenue (from Opportunity), total child Contacts, total number of days of open cases, any aging fields, any rollup fiel as well, and so on..

And you do not even need plugin or workflow to always update this field everytine got any changes in the source for calculated field or any new or removed Associated records changes as well, or creating batch job or recreating your field to be using CRM 2015 rollup and calculated fields..Amazing right..

Hope this helps!

Wednesday, 30 March 2016

Develop CRM SSRS Report in Local using “Windows Authentication” without really using the Joined Domain Windows Account

Hi all,

Just want to share how to develop SSRS Report that connect to the CRM Database, but using Windows Authentication.


Sometimes we need to develop custom report because report wizard cannot support the complex report and complex UI or filter or grouping, so that is the reason why we still need to rely on this SSRS.

As we know that using FilteredView is the recommended way query CRM Database that using Security Layer applied on the query, but this FilteredView we can only run if we use the valid CRM User, which is not other than using Windows AD Account registered in AD and CRM. Then, we found problem that mostly developers are using their own local Visual Studio, of course we cannot use the server to install VS and let developers to use that imagine we have more than 10 developers trying to access and remote desktop to server? So troublesome.



And here are the list that what developers usually do to develop report:

1. Access the Server (remote Desktop), installing VS and develop there, using Windows Authentication, so that you can do query using FilteredView as usual

It is a good idea to settle everything, but will make burden on the server and cannot imagine if many developers want to develop and access at the same time, who will kick and be kicked everytime.

2. Other way is we use SQL Authentication using “sa” account to access, but this can be troublesome because as mentioned before, FilteredView, you can only access using CRM AD User. What you need for this is just access to the SQL Server Database.

3. Then, try another way is you restore the database locally to your SQL Server Express probably, then run the query locally, but, again, you cannot use FilteredView in your Query. This also needs you to do regular backup if there is changes in the database schema


For point number 2 and 3, you can do that and can run the report so long you have access to the Database, but the bad thing is you cannot run FilteredView and you must using standard View then before you deploy to CRM you must manually adding the “filtered” keyword before your View name and you cannot run the report view the data act on behalf of a CRM User based on the security because you are not CRM user running that time.


As we know that solution number 1 is good for faster development without manually changing, but might result other weakness in other areas, so we need to combine those 3 items.

Now this is what you need to do:

1. Run the cmd (CTRL + R) then type “cmd”
2. And run this command:
runas /netonly /user:yourdomainname\yourusername "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe"
3. Example:


Then it will prompt you to fill in password:



I found this solution is quite useful because we can develop and run preview the report locally without remote desktop-ing to server and can use the recommend FilteredView query.
Then here is the result:


Keep loading database names


And in the end prompting this error message or when you click the “Test Connection” as well:



Now we can successfully login and can retrieve the database


Hope this helps!