Friday, 5 April 2019

With Dynamics 365 Online, the greatest thing I miss compared to OnPremise is the relative limitation on what you can do with reports, as FetchXml is not so powerful as SQL. One option is to use the Data Export Service to get the data into a SQL database, and get the SQL queries out, but there is still a lot that can be done with FetchXml and Reporting Services. This post shows how to combine data from multiple datasets using the Lookup function.

My first rule of thumb when working out if a given SQL query can be implemented as a FetchXml query is, 'can the SQL query be written so that there is just one SELECT statement ?' If so, you've got a good chance of being to rewrite as FetchXml, but if not, you won't be able to do this in FetchXml. This test is useful, as it immediately eliminates Union queries, sub-queries and table expressions, which you can't do with FetchXml.

So, in many cases you can't do get the result that you want with one query, but Reporting Services allows you to define multiple Datasets, and hence multiple queries, in one report, and the Lookup function allows to connect the data across the Datasets.

For this post, I'll use an example I came across recently when the requirement was to get a count of records created per user, broken down by entity type. The simplified output I was looking for was:

User

Leads

Opportunities

David Jennaway

20

10

My Friend

15

8

This will need to get data from the systemuser, lead and the opportunity entities. It is possible to join these in one query, but not in a way that is useful, as you end up multiplying the opportunity and lead records.

Instead, we can create separate queries. Here I'm doing one for each entity, systemuser, lead and opportunity. The systemuser query will end up as the main source for the table, with lookups to the other queries to get the respective record counts. The 3 datasets and queries are:

And that's it to get the basic report. As a nicety, I can add a filter for the row visibility, so that it hides rows where there is no count across any of the datasets. The Lookup function returns Nothing if no record is found, so we can use the IsNothing function.

We can keep adding extra datasets to count other entities, using the same approach. I don't have the patience to work out if there's a practical limit to the number of datasets we can use in one report.

A couple of points to note:

You can't use the Lookup function as a calculated field, which is slightly annoying, as I think it would be neater if this were possible. I expect this is due to how Reporting Services first processes the datasets, and will then render the results

When testing in Visual Studio, you get prompted for credentials (or to use cached credentials)for each dataset in turn. I don't think you can do anything about this. Interestingly, it looks like Visual Studio caches the credentials per dataset, and they can be differ even if they use the same datasource. I once managed to have different datasets querying different CRM organisations, even though they were using the same datasource

I'm intending to post the full report up on GitHub in the next few days, once I've got that working properly

0
comments:

Feeds

Who I am

Professionally:I'm a founder member of Excitation Ltd, a Microsoft Gold Partner in the UK that specializes in Microsoft CRM, and I've been the technical lead in over 50 CRM implementations since the release of CRM 1.2.This is a personal blog, and any views expressed here do not necessarily reflect those of Excitation; sometimes they will, but that should be treated as a happy coincidence rather than a normal state of affairs.

Personally: We'll see if I get onto this in the blog; if so, I expect it will include some permutation of mountains, snow and gravity