SSRS Fetch XML based CRM Online Report performance in Dynamics 365

Hi,

Recently we were analyzing the performance of a fetch xml based online report.

The report was running against opportunity, and had 4 data set in it. Custom entity A and custom entity B were n – 1 related to opportunity.

Data Set Opportunity 13000 records
Data Set for Custom Entity A used for Lookup 100 records
Data Set for Custom Entity B used for Lookup 8000
Data Set for Tasks Activity used for Lookup 130000 records

More on Lookup Function in SSRS

Running without filter 107 seconds
Filter Last 6 months 94 seconds

We then updated the report’s design, to include only one Dataset by using link entities in the main dataset’s fetch xml. We got the below result

Running without filter 280 seconds
Filter Last 6 months 10 seconds

Another variation we tried was having two Dataset, one for opportunity and another one for tasks to be used for lookup.

Running without filter 50 seconds
Filter Last 6 months 50 seconds

The last variation we tried was using only one Dataset for Opportunity in the main report and the drill down report for Tasks

Running without filter 34 seconds
Filter Last 6 months 7 seconds

It clearly shows that the most efficient way of implementing is through the usage of drill down report, only glitch here is user needs to click on the main report to get the details.

Using single dataset is efficient for subset of records, however it can take long time if it runs on huge number of records in which case lookup performs better.

Basically,

g

Hope it helps..

Advertisements

Using Lookup, LookupSet and Multilookup function in SSRS (Dynamics 365)

Using the Lookup, LookupSet and Multilookup functions we can basically combine values from 2 different data set.

The definition of them are

Lookup/LookupSet/Multilookup(source_expression, destination_expression, result_expression, dataset)

source expression – the field from the source data set (for e.g. primary key)

destination expression – the field to be matched from the destination dataset (sort of foreign key)

result expression – the field whose value has to be returned from the destination dataset

dataset – destination dataset name.

For e.g. we have custom entity Team and Team Members (1 à n)

We have following two DataSet one for Team and one for Team Members.

  1. DSTeam

  1. DSPlayer

The sample report has the Tablix which is bound to DSTeam and has two columns one which is bound new_name field of DSTeam dataset and the second column which gets the player name from DSPlayer dataset.

Output:

Lookup function picks the “first matching value” from the second dataset DSPlayer.

To get all the matching we need to use the LookupSet function (along with Join)

Output:

To understand Multilookup, let us create one more entity Tournament that will have a text attribute which stores comma separated team name.

Let us create one more dataset DSTournament and bound it to a new table. Which have one column which lists the Tournament name and the other column that does the multilookup to DSPlayer to get the player name.

It brings the first matching value, by searching (doing lookup) on multiple value i.e. India and South Africa

Hope it helps..

ID3242: The security token could not be authenticated or authorized error in Report Designer (Dynamics 365, CRM 2016 Online)

While working on Report we suddenly started getting the below error

The strange thing was that the other reports that were using the same data source in the same Report Project were working properly.

Find few posts

http://www.dynamicscrmpros.com/solve-error-id3242-security-token-not-authenticated-authorized-connecting-scribe-microsoft-dynamics-crm/

https://social.msdn.microsoft.com/Forums/en-US/ff35a32e-06bc-4c8c-a77f-793fd3cfefb6/id3242-the-security-token-could-not-be-authenticated-or-authorized?forum=crmdevelopment

but none applied here.

Tried creating a new data source, entered credentials repeatedly but nothing worked, eventually restarted the SQL Server Data Tools (IDE) and it fixed the issue.

Hope it helps..

Error while loading code module ‘Microsoft.Crm.Reporting.RdlHelper’. Could not load file or assembly error in report in Dynamics 365

While trying to preview an existing report in SQL Server Data Tools for Visual Studio 2012 got the below error

To fix it,

Go to Report à Report Properties à References Tab and browse to the correct version of the assembly in the BidExtensions folder

Hope it helps..

Error installing Report Authoring Extension – “Setup cannot continue because there is a pending restart required. Restart the computer and then try running Setup again” in Dynamics 365

While installing Report Authoring Extension got the above error.

Deleting the following Registry Key fixed the issue

  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\RunOnce.
  • HKEY_CURRENT_USER\Software\Microsoft\Windwos\CurrentVersion\RunOnce


Hope it helps..


Recommended Field showing up as Required Field Issue in Dynamics 365

Hi,

Yesterday while doing the analysis of how the existing solution which uses legacy form rendering works for new form rendering, we found a weird issue.

The field which we were setting as recommended through JavaScript was appearing as required. However, its behavior was like recommended field only i.e. we can save the form without specifying value for it.

In left the fields in legacy form rendering and in right Turbo forms or new form rendering

If we inspect through debugger, it seems like the issue is in the image that is being set

We are still analyzing it.

Hope it helps..

Unsupported filtering of subgrids in Dynamics 365 (CRM 2016)

Hi,

In one of the projects which was recently upgraded to CRM 2016 Online Update 1, legacy form rendering was being used. So, we were analyzing how the new form rendering will affect the existing JavaScript.

One of the unsupported JavaScript that was being used was for sub-grid filtering.

The interesting thing was that it works in CRM 2016 Online Update 1 for legacy form rendering but fails in case of new form rendering (turbo form).

For turbo forms, we get the following objects as null

I couldn’t find any workaround of implementing the same in case of turbo forms. The only possible solution that I could think of is either using Quick View Form with Subgrid control inside it or through a html web resource.

Hope it helps..

List of solution components that can be deactivated in Dynamics 365 (or CRM 2016)

Recently while working on one of the requirements, I had to prepare list of solution components that can be deactivated.

Below is the table that I had prepared for quick reference

Solution Component Can be deactivated?
Form Yes
View Yes
Business Rule Yes
Process Yes
Connection Role Yes
Routing Rule Set Yes
Record Creation and Update Rule Yes
SLA Yes
Entity No
Chart No
Field No
Key No
Relationship No
Option Set No
Application Ribbon No
Site Map No
Web Resource No
Dashboard No
Report No
Templates No
Security Roles No
Field Security Profile No

Hope it helps..

Parallel.For vs ExecuteMultiple in Dynamics 365

Hi,

Thought of getting a rough idea on how much performance improvement we can gain if we are using Parallel.For and ExecuteMultiple.

Took a very simple scenario i.e. creating 10000 account records.

Normal For Loop 3242061 milliseconds à 54 minutes à 185 records per minute
Parallel.For 2160337 milliseconds à 36 minutes à 277 records per minute
ExecuteMultiple (single request with batch size of 1000) 494663 milliseconds à 8 minutes à 1250 records per minute

This clearly tells ExecuteMultiple far better.

The sample code that I used for Parallel.For

Hope it helps..

New Sort Date DateTime attribute added in Activity Entity in Dynamics 365.

A new SortDate attribute of type DateTime is added for Activity Entity (so basically to all child Task, Phone Call, Email activity etc.)

The value is null for it for the existing records and for the new record as well until we populate it through Business Rule, Plugin or any other method. The basic use could be to use it for sorting apart from OOB Modified on and Created on in View, Report, Chart as well as in Social Pane.

Check this awesome tip

http://crmtipoftheday.com/2016/11/15/change-the-default-order-of-activities-on-the-social-pane/

I remember while working in Oracle CRM On Demand project, where in we can add fields to the base Activity Entity and it would be available in all the child Activity entities. However, in Dynamics CRM the Activity Entity has always been locked i.e. we can’t add new fields in it.

Now what if we create a new custom activity entity?

Yes the field will be available in new custom activity entity as it would derive from the baes Activity Entity to which this new field has been added.

Hope it helps..