Dynamics / SSRS – Matrix Control for adding Dynamic Columns

Through the blog, just wanted to share a learning regarding a “Matrix” control available in SSRS.

Using this control, we can mimic the functionality of “Pivot Column” which is provided in Power Query feature of Excel.

What is control basically does, it that it allows us to dynamically add columns instead of being based upon the columns available in results of a dataset.

For example, consider a scenario, wherein we need to make a Report on Survey’s in Voice of Customer such that we capture the questions present in the survey as different columns, and in rows we show the  responses given by different people on the rows, we can use this control. Please note that questions are dynamic and will be different in each survey and are not present as fields on the survey.

In the mentioned below screenshot, i have just added the matrix control, and in the column groups i have specified the Question, while in the data i have added the response for that question present in each survey response.

SSRS Report Configuration.png

Mentioned below screenshot shows the output of the SSRS report with the Matrix control showing the questions in columns

Survey Response.png

 

Advertisements

SSIS / Dynamics – Tips For Data Migration

In regards to Data migration via SSIS to Dynamics or for instance any other system, just wanted to share some of the design aspects / practices which i believe should be considered

1.Package Structure – We must design our SSIS data migration packages in a very precise manner. In long run, it not only helps ensuring the data integrity between the two systems but helps us in maintenance and performance aspect of the system.  We should consider factors like

  • Designing intermediate databases – While moving data to the Dynamics or for instance any other system, there are several things we need to consider like lookups, option-sets etc. If we have the guid’s in database layer itself, we wont have to make calls to Dynamics to fetch there ID’s. Such transformations along with many others can be handled in that intermediate database.
  •  Maintaining hierarchy of packages – To maintain the data integrity, while migrating data, we need to maintain a particular order. There the packages must be designed that they execute in a sequential order.

2. Error handling – We should consider proper exception handling in our packages. Packages should be designed in a way to log errors related to data integrity, network connectivity etc. Proper mechanism to raise notifications to relevant parties should also be provisioned.

We should also consider the need to have stages or milestone for each of our package runs. These milestones could be different stages like Reading data from source, doing transformations in intermediate database etc. This ensures that if we need to rerun the package for the failed records, we do not need to rerun the entire package.

3. SSIS Package / Dynamics Settings – Mentioned below SSIS package settings can be changed to optimize the data flow

  • a) DefaultBufferSize
  • b) DefaultBufferMaxRows
  • c) EngineThreads

Please refer to the msdn link for more information on these settings

http://msdn.microsoft.com/en-us/library/ms141031.aspx

If the target is Dynamics,

  • We should use ExecuteMultipleRequest for the operations in Dynamics.
  • For different entities, we should use combination of Batch Size and Multi-thread  count and check the performance.

4. Further Readings – For SSIS , mentioned below msdn blog link is quite rich in content

https://blogs.msdn.microsoft.com/sqlperf/

 

Dynamics – Potential Issue For Closed Opportunities

While doing some work in Dynamics 365 V 9.1, I observed one odd behavior in  regards to Opportunities and Quotes which i thought to share.

I believe it was not working this way in the previous releases, however in the current release i have observed that we are able to update a Closed Quote/Opportunity in Dynamics without changing its Status to Active.

In the mentioned below example, i have just written an on-demand workflow which is setting some dummy values on an Opportunity record. I will then be running this workflow on a closed opportunity.

a) In the below snapshot, i am just setting some dummy values on the opportunity

Update Opportuntiy

b) In the below snapshot, i am just running the on demand workflow for the opportunity

Running Process on Opportunity

c) After running the values, refresh the form, to see the updated values

Updated Oppor

I tried the same behavior with Quotes and Cases. With Cases, the record was in read -only state and changes were not saved. However with Quote it was the same behavior as Opportunity.

Currently really not sure if its supposed to work this way, however i believe earlier we used to activate the opportunity before we can make changes on a closed opportunity.

 

 

 

Dynamics – Using Auto Complete Control in Unified Interface

Auto Complete Control, in Dynamics provides us a capability to render possible options in a “Single Line of Text” field based upon values present in an existing Entity which can act as a master list.

This can be very useful in scenarios such as Out of Box address attributes in Dynamics. As fields like Country are text fields, using Auto Complete control, they can be mapped to a master entity of Countries in Dynamics.

This avoids us a need to create lookup attributes on the contact to master country entity and mapping the values with the out of box country field via workflow. Or in other cases when we write a custom script to populate the possible options.

In the below example, we are configuring the auto complete on out of box country field on a contact based upon a master list of country entity

a) The first step would be to add the “Single Line of Text” field on the form, then clicking on it to navigate to “Field Properties”.

Auto Complete 1

b) In the Controls section, now add a control “Auto-Complete” and select “Web” so that it renders on the UCI interface.

c) Now select the “Auto-Complete” control, select the source as “View”. Once the “View” source is selected, user will get an option to configure “Entity”, “Respective View” and the Field in the view which will act as the source of possible options for the field.

Auto Complete 2

c) Now once, the field is viewed in the UCI, it renders the following way

Auto Complete 3

d) Auto Filtering, based upon the value entered by the user is also available.

Auto Complete 4

Importantly, if a user tries to enter a value out of the available entities in the master entity, it will be not accepted.

 

 

Dynamics – Plugin Assembly Habits to Avoid during Development and Maintenance

Just wanted to share some of the habits while development / maintenance of Dynamics plugin projects which i feel are not correct and i feel they should be avoided

a) Registering different versions of the same assembly – Although having different versions of the plugin / workflow assembly is acceptable till dev / test, however one must make sure that before the solutions are deployed to UAT / Production, they must be combined.

If not, we just end up having multiple assemblies in production which can be a nightmare for maintenance.

b) Disabling / Enabling steps from the solution – This is another point one must keep into consideration while maintaining the different environments.

It just adds unnecessary complexity / effort to analyse what could be the root of the issue.

c) Maintaining Unmanaged Assembly in Production – Just in the case with all the other components present in a solution, its not a best practice to have unmanaged assembly instead of a managed one in production.

d) Running Workflows / Plugin Impersonation  – In the scenario’s when a plugin is being impersonated to run under the context of a particular user or when an out of box workflow is running under the ownership of a particular user, we must ensure that the user set should be the service account user.

This is because in many cases, the license for the developer user is revoked. In such cases, the functionality will start breaking and could be difficult to trace.

e) Update plugins filter attribute  – If we are having any update plugin, we must give some thought on the attributes on which the plugin step is triggering. This is because if we randomly choose any attribute, it could impact the performance of the system.

For further reading please refer to the Microsoft official site for best practices on Plugin development

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/best-practices-sdk

 

 

 

 

Azure – Managing Resources Using Resource Groups

In this blog, I just wanted to share some tips which people can use to manage resources in Azure via Resource Groups. Resource Groups in Azure users can help users in organizing the resources in a much better way. It can help them not only in development but also helps them in better monitoring of the resources, there maintenance and the associated cost.
Mentioned below are some of the points which people can use to organize their resources better

a) Always create resource groups in Azure to manage the different environments of dev, test, uat and production. Also do not create unnecessary resource groups.

This ensures that there is a good level of segregation between the components in azure and people can easily develop and deploy resources in dev without impacting the other components in test, uat and production.

With this approach, if developers needs to flush out the entire resources in a particular environment, they can just do it by deleting the respective resource group

b) Provide access to only relevant people in each resource groups. This can be managed by either providing them access as a standalone user or as a team.

This segregation can help avoid unauthorized access and update of components by mistake.

c) While deploying the resources, it’s always beneficial to tag resources. This can enable better monitoring of the environment. For example using tags we can monitor the usage and costing of respective resources.

Please read the mentioned below Microsoft blog for better understanding on resource groups
https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-overview

Microsoft Dynamics Portals – Setting Value in a Lookup Attribute

In Dynamics Portal’s we can use the mentioned below syntax to set value in a lookup attribute

In the code example below, attribute needs to be replaced with the schema name of the attribute.
txtName , txtID and txtEntityName needs to be replaced with the name, guid and entityname of the record which you are setting in the lookup attribute.

$(“#attribute_name”).attr(“value”, txtName);
$(“#attribute”).attr(“value”, txtID);
$(“#attribute_entityname”).attr(“value”, txtEntityName);