Using the Lookup function to create reports on multiple DataSets

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:

dsUser:
<fetch > <entity name='systemuser' > <attribute name='systemuserid' /> <attribute name='fullname' /> </entity> </fetch>

dsLead:
<fetch aggregate='true'>
    <entity name='lead' >
       <attribute name='createdby' groupby='true' alias='createdby' />
       <attribute name='leadid' alias='lead_count' aggregate='countcolumn' />
    </entity>
</fetch>

dsOpportunity:
<fetch aggregate='true'>
    <entity name='opportunity' >
       <attribute name='createdby' groupby='true' alias='createdby' />
       <attribute name='opportunityid' alias='opportunity_count' aggregate='countcolumn' />
    </entity>
</fetch>

dsLead and dsOpportunity are both simple aggregate queries to get the respective record counts for each entity by user.

Then, to create the report, I add a table based on the dsUser dataset, with the Fullname in the first column. Then for the count of leads, I can use the following Lookup expression:

=Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!lead_count.Value, "dsLead")

Taking each of the parameters in turn:

• Fields!systemuserid.Value - this is the Guid for the systemuserid in the dsUser dataset. This value will be compared against...
• Fields!createdbyValue.Value - this is the Guid of the createdby in the dsLead dataset. Note that I use createdbyValue to get the Guid, as for lookup attributes the createdby will be the name
• Fields!lead_count.Value - this is the field in the dsLead dataset that I want to display
• "dsLead" - this is the name of the dataset that the Lookup works on

We can then do the same for the expression for the opportunity count:

=Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!opportunity_count.Value, "dsOpportunity")

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.

=IsNothing(Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!opportunity_count.Value, "dsOpportunity")) AndAlso IsNothing(Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!lead_count.Value, "dsLead"))

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

Reporting Services log files

This should be a very brief post. I find myself replying to many posts in the CRM forums about reporting problems, and most commonly direct people to the Reporting Services log file(s), which provides a lot of potentially useful information. The main information you can get is:

• More detail than CRM will provide on errors running a given report
• Whether or not a report is being run

In general, I find the information reported in the log files to be self-explanatory, but if I find examples of messages than could benefit from further explanation, then I'll post them here.

The log files are in the Reporting Services\LogFiles folder within the Reporting Services directory. On a default instance on SQL 2008 R2, this would be:

%Program Files%\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

The CRM 4.0 Reporting Services Connector - how it works

The Dynamic CRM Connector for Reporting Services is a very useful component that avoids the need to configure Active Directory delegation (aka double-hop authentication) when multiple servers are used for the CRM, Reporting Services and SQL Server roles in a CRM implementation. In general, it is easy to install and use, but I'm always interested in how these things work.

How the Connector is installed and invoked
The connector is installed as a Data Processing Extension with SSRS. These extensions are registered within the rsreportserver.config file on the Reporting Server, as per the following snippet:

<Data>
<Extension Name="SQL" Type="Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Microsoft.ReportingServices.DataExtensions" /> <Extension Name="OLEDB" Type="Microsoft.ReportingServices.DataExtensions.OleDbConnectionWrapper,Microsoft.ReportingServices.DataExtensions" />
<Extension Name="MSCRM" Type="Microsoft.Crm.Reporting.DataConnector.SrsExtConnection,Microsoft.Crm.Reporting.DataConnector" /> </Data>

All CRM reports with SSRS are configured to use a specific SSRS Data Source. When the Connector is installed, the Data Source is changed to use the MSCRM Data Processing Extension, instead of the default SQL Server extension. See images below:

Report properties showing the MSCRM Data Source


MSCRM Data Source using the SQL Server extension before the connector is installed


MSCRM Data Source using the CRM extension after the connector is installed.

There are 3 differences between these configurations:

1. The Connection Type, which specifies the extension
2. The Connection String is absent with the CRM connector. This is because the connector reads some of the database information from registry values that were created during its installation, and some from data passed to it when the report is run (see below)
3. The Credentials. With the SQL Server connector, standard Windows Integrated security is used - i.e. the user's AD credentials are used to connect to SQL Server. With the CRM connector, separate 'credentials' are passed to SSRS (again, see below)

What happens when a report is run
If you try to run a CRM report with the CRM connector installed, the connector will require some 'credentials', as per point no.3 above. This image shows what happens if you try to run a report from Report Manager:


Running a CRM report from Report Manager when the CRM connector is installed

These 'credentials' are not what they seem; rather they are a cunning way for the CRM platform to pass information about the current user to the CRM connector. The CRM connector expects the current user's systemuserid (a Guid) to be passed into the Log In Name box, and the organizationid (another Guid) to be passed into the Password box. These are not your login name and password.

As the report uses a data source that uses the CRM connector, the RS Report Server code calls the CRM connector code (the SrsExtConnection class in the Microsoft.Crm.Reporting.DataConnector assembly, as per the rsreportserver.config data above). The code will then:

1. Check that it is permitted to impersonate a CRM user. This checks that the identity the code is running under (which the identity of the ReportServer application pool, or the Reporing Services service, depending on the version of Reporting Services) belongs to the AD group PrivReportingGroup
2. Connect to the MSCRM_Config database to determine the correct MSCRM organization database, based on the organizationid that was passed in the 'credentials'
3. Connect to the relevant MSCRM organization database. Note that this is done (as was the previous step) using integrated security under the AD identity as per step 1 above
4. Use the SQL statement SET Context_Info to pass the calling CRM user's systemuserid into the Context_Info
5. Execute the SQL statement(s) within the report definition. The definition of all filtered views use the fn_FindUserGuid function to read the systemuserid from the Context_Info

What can you do with this information
One use is for troubleshooting. Checking the rsreportserver.config is a quick way to see if the connector is installed, and checking the configuration of the MSCRM Data Source will tell you if the connector is in use. Changing the MSCRM Data Source is a quick way to turn the connector on or off for test purposes.

You can also run the reports directly, rather than from CRM. Again, when troubleshooting I find it useful to run a report directly from Report Manager web interface. To do this with the connector, you need to enter the systemuserid and organizationid when prompted (see image above). These values can be read from the filteredsystemuser and filterorganization views respectively in the MSCRM database.

A further option is to run the reports via other means, such as Url Access, as described here (that article was written for CRM 3, see here for an update for CRM 4). To do this with the connector installed, you will also have to pass the systemuserid and organizationid on the query string. This is done using the following syntax:

&dsu:CRM=<systemuserid>&dsp:CRM=<organizationid>

Data Integration using Reports

I've another post on the CRM Team Blog site at http://blogs.msdn.com/crm/archive/2009/09/22/data-integration-a-reporting-approach.aspx

This is another post on design patterns (like this one), looking initially at potential data integration approaches and key issues, then covering some specifics on the reporting approach.

Reports on CRM Privileges - Update to view by User

I've added 2 reports to the resource on the MSDN Code Gallery. These show cumulative privileges by user across their roles

Hidden CRM Privileges

Not all CRM privileges are visible within the CRM User Interface. I recently spent some time investigating what privileges exist in CRM, and how the privilege information is stored in the MSCRM database.

The results of these investigations have been posted on the Microsoft Dynamics CRM Team Blog. I also created a couple of reporting services reports to display the privilege data by role. These are available on the MSDN Code Gallery

Reading RDL Definitions directly from a ReportServer database

Just a quick post: I recently had to extract the report definitions (RDL) directly from a ReportServer database, as the ReprtServer installation was broken. This should be straightforward, but requires some work to convert the data to a readable format. This can be done with the following SQL query:

select convert(varchar(max), convert(varbinary(max), content))
from catalog
where content is not null

Report Server location and the error: The specified path is not a metabase path

One of the ‘known’ issues with using CRM 4.0 and ESRV0008 is that CRM does not realise that SQL 2008 Reporting Services hosts the ReportServer application outside of IIS. Hence, when you try and edit the ReportServer URL in CRM Deployment Manager, or install CRM, you may get the error "The specified path is not a metabase path". As I said, this is a known issue, and a workaround is described at http://support.microsoft.com/kb/957053 (issue no. 6 in the article).

The problem though is that this workaround doesn’t always, well, work. I tried this, with no success whatsoever. Eventually, I went back to some other issues CRM had had with identifying IIS metadata in the past with CRM 3.0 (e.g. http://support.microsoft.com/kb/916164 ). What I had to do was temporarily change the IIS bindings so that the default web-site (i.e. web-site with ID=1) was on port 80. As long as this is the case when you edit the Report Server URL, then everything works. Fortunately, all I was doing was editing an organisation in an existing deployment, and could arrange downtime to temporarily change web-site bindings; if you’re installing a new CRM deployment it may be harder to play with web-site bindings. For completeness, the steps to make this work were:

1. In IIS Manager, create a new web-site with bindings that match those of the ReportServer URL, as per issue 6 in KB 957053 (link above)
2. In IIS Manager, change the current web-site bindings so that the web-site with ID=1 is on port 80 with no host-headers (if necessary turn off your phone and don’t check email while people try and complain they can’t access their web-sites)
3. In CRM Deployment Manager, disable the organisation, edit the organisation an put in the ReportServer URL you want and continue till the organisation has been updated successfully. Enable the organisation
4. Back in IIS Manager, change the web-site bindings back to how they were, turn your phone back on, and find someone/something else to blame for the temporary outage

Why Microsoft code things assuming the default web-site is on port 80 is beyond me. Oh well.

During my investigations I found out a few more things:

• McAfee anti-virus software hooks into some popular alternate ports (in this case 8081), and it’s not easy to diagnose conflicts when you put ReportServer and McAfee on the same port
• Configuring multiple bindings for the ReportServer URL works fine
• 'All' the above does is make sure that CRM populates the ReportServer with the correct folders, reports, data sources and permissions. It is possible to set all this up manually and get it to work, but it’s very tedious and you may need to change the Guids that identify the reports within either CRM or Report Server

Using the IIS Logs to get CRM Usage information

I've been posting on the Microsoft CRM Team blog again.

The article describes how to make use of the IIS logs to get useful information about who is accessing CRM and when.

This technique uses some SQL objects (tables, view and functions), and a reporting services report. The source code for these is available on the MSDN Code Gallery

Viewing CRM 4.0 Reports in Report Manager

Some of my previous posts (e.g. http://mscrmuk.blogspot.com/2008/01/scheduling-and-emailing-reports-with.html) were written based on CRM 3.0. Most of the reporting principles are similar for CRM 4.0, but the reports are configured slightly differently within Report Manager. The key differences are:

1. The CRM items are hidden by default in Report Manager. If you browse to the OrganisationName_MSCRM folder you won't see the hidden items. To see them, click on the 'Show Details' button on the right of the toolbar
2. The CRM 4.0 reports are stored in a sub-folder called 4.0. Click on this folder to see the reports
3. CRM 4.0 specifies the ReportID Guid as the report name, and the friendly name is now in the Description fields

Using the Reporting Services Execution Log with CRM 4.0

I've contributed another article to the Microsoft CRM Team Blog. This is about making good use of the Reporting Services Execution Log with CRM 4.0 reports.

In the past I've found the execution log to be very useful for getting statistics on when reports were run, and the overall performance. However, CRM 4.0 introduced some complexities around using the log effectively. One issue is that the report name is not stored in Reporting Services, and another issue is that, if you use the Reporting Services connector, then the user name is not recorded in the execution log. The article describes how to resolve these issues.

I've added some support files for this to the MSDN Code gallery

Report Builder 2.0 for SQL 2008 - No need for a report model

I've always considered the Report Builder tool that ships with SQL Reporting Services to be a good tool for generating reports without SQL knowledge, and in the context of CRM it's much more powerful than the CRM 4.0 Report Wizard.

One drawback of the Report Builder for SQL 2005 was that it needed a Report Model to abstract the underlying data, and Report Models are not dynamic (see below for why this was a pain). One major advantage of SQL 2008 is that it ships with a new version of Report Builder, Report Builder 2.0, that allows direct connections to the SQL database. This will make it a lot more useful in CRM deployments, especially when your schema changes. A good overview of the use of Report Builder 2.0 can be found here.

Why Report Models are a pain with CRM
Earlier, I said 'Report Models are not dynamic' Put another way, if you built your Report Model on your CRM database, then added a new attribute to an entity, you would have to regenerate your Report Model. In itself this wouldn't be too bad, but when you generate a Report Model from a filtered view you end up with a lot of unnecessary attributes, and a fair bit of work to do to provide a sensible set of attributes in the Report Model. To get round this we wrote a set of tools to manipulate the Report Model files directly and automate a lot of the rebuilding. This saved a lot of time, but still required a deployment process to allow reports against new attributes or entities.

Report Wizard: Query execution failed for data set 'DSMain'

There is a problem with the CRM 4.0 Report Wizard that can result in an error like the following:
An error has occurred during report processing.Query execution failed for data set 'DSMain'.The column 'accountid' was specified multiple times for 'account0'. The column 'accountid' was specified multiple times for 'opportunity1'.

Explanation of the problem
The ultimate cause is how the Report Wizard stores the Filtering Criteria for reports based on the account entity. The Report Wizard stores the query for any criteria as a combination of all fields in the account entity, and all fields in the related primary contact. When the report is run, the SQL query attempts to use the results of the following (or similar) as a table alias:

select DISTINCT account0.*, accountprimarycontactidcontactcontactid.* from FilteredAccount as account0 left outer join FilteredContact as accountprimarycontactidcontactcontactid on (account0.primarycontactid = accountprimarycontactidcontactcontactid.contactid) where (account0.statecode = 0)

This returns two fields called accountid (one from the account entity, and one from the contact), which breaks the main SQL query for the report, and gives the error above.

Resolution
The way to resolve this is to ensure that, when you create the report with the Report Wizard, you do not specify any criteria for the account entity. This will cause the Report Wizard to store the query as solely against the account entity. Once you’ve created the report, you can happily edit the default filter to whatever you want, and the report will work fine – the key factor is not having any criteria when you first create the report.

Unfortunately there’s not an easy way to fix existing reports with this problem – it should be possible to edit the data in the DefaultFilter column in the reportbase table, but this is unsupported. I’d suggest in this scenario that you’re best off recreating the report from scratch

Reports in IFrames - Updated for CRM 4.0

One of my earlier posts here and on the CRM Team Blog was about writing reports to display in IFrames and via ISV.Config. This was written for CRM 3.0, and although the principle remains the same for CRM 4.0, a couple of changes are required to the report parameters.

Extra Parameters passed by CRM
To support multi-tenancy and multi-language deployments, CRM 4.0 passes additional parameters on the query string when the 'pass record object-type...' option is set for the IFrame. These extra parameters are orgname, UserLCID and OrgLCID.

For the report to work in reporting services you will have to add parameters with these names to your report. The orgname parameter has to be a string, the UserLCID and OrgLCID can be either strings or integers. As with the type and typename parameters, these new parameters don't have to be used in the report, they just need to exist for reporting services to accept values for them on the query string.

Changes to the query string for ISV.Config buttons
In CRM 4.0, Microsoft have standardised the parameters such that they are the same for IFrames and ISV.Config buttons. This means that your report should have parameters id, type, typename, orgname, UserLCID and OrgLCID.

MSDN Code Gallery

Microsoft have just made the new MSDN Code Gallery live. This is a site for sharing sample code, and I've posted some of my example code from previous blog articles. So far I've posted the following 4 resources:

MSCRM: Automate Local Data Group creation

Microsoft CRM report of all files related to an account

MSCRM - web page hosting to extend client script

Writing reports to use in IFrames and from ISV.Config in Microsoft Dynamics CRM

For reference, if you want to search for CRM examples, there's an emerging tagging protocol to tag all CRM examples as both CRM and Dynamics, so you can search on either tag. I'm also tagging samples with reports as 'Reporting Services'

Scheduling and emailing reports with CRM

A common customer request is to be able to schedule CRM reports and automatically send the report to one or more users by email. This can be done without the need for any code, but needs a combination of configuration within both CRM and Reporting Services.

CRM Configuration
CRM reports are designed to be run under the context of the user running the report so as to return only the data that user has permission to see. Due to this configuration, Reporting Services will not permit you to schedule CRM Reports out of the box (if you try, you'll get the error 'Subscriptions cannot be created because the credentials used to run the report are not stored') . The easiest way to resolve this issue is to download the CRM Report Scheduling Wizard from here and install it on the CRM server.

Then, navigate to Workplace, Reports in CRM, select the report you want to schedule, and go to More Actions, Schedule Report. As you will do the scheduling later within Report Manager, I'd suggest you select the following options:

• Generate snapshots 'On demand'
• 'Make snapshots available only to me'
• Specify any parameter values
• You then need to specify the credentials under which the report will run. This needs to be a valid CRM user
• 'Yes, generate the snapshot now'

Reporting Services configuration
The rest of the configuration is done via Reporting Services. First of all, you may have to configure the email properties of Reporting Services. To do this open RSReportServer.config, which by default will be in the Reporting Services\ReportServer directory under the SQL installation directory in Program Files. Within the 'RSEmailDPConfiguration' element you will need to set values in, as a minimum, the 'SMTPServer' and 'From' elements. You may need to apply other settings. For more information see the documentation for SQL 2005 or SQL 2000

Scheduling the Report in Report Manager
You can now schedule the report. Browse to Report Manager (by default it will be in the Reports virtual directory), go to the OrganisationName_MSCRM folder and the report you have scheduled (it's name will have the suffix On demand Snapshot). On the toolbar you should have a button 'New Subscription'. Click this, select 'Report Server E-mail' in the Delivered by drop down, and set all other options as required. (If 'Report Server E-mail' is not an option, go back to the instructions in the above paragraph

Viewing all files in CRM related to an account

I've recently published a second CRM related reporting services project on codeplex. This one illustrates how to get a list of all files related to a CRM account. This includes files attached directly to the account or via related objects (e.g. contacts, opportunities, cases), and also includes both file and email attachments.

Most of the work is done in the SQL, which I've written as a set of SQL views for ease of reading, rather than embedding all the SQL in the report.

The project also illustrates how to link to CRM objects, within a report, as the report contains reporting services actions to display the CRM entity forms, and also actions on each file to link to the CRM file download dialog.

This report is written so as to take parameters from a ISV.Config button, as described here

Retrieving Billable time and activity time for cases

When a case is resolved in CRM, the user can specify the amount of Billable Time spent on the case, and they can also see the total activity time spent on the case:


Both sets of information can be useful, and are necessarily stored in the CRM database. However, how you retrieve these figures is not well documented.

Where the data is stored
Data about the case is stored in 3 main entities (note that I'm using the schema name for the entities, and in the schema a case is called an incident):

incident: This is the main case entity, which stores the information you see on the main form, but does not include and of the duration information listed above. The statecode field identifies whether a case is open, resolved or cancelled.

incidentresolution: This is strictly a special type of activity, and stores the user entered data in the Case Resolution dialog. The 'Resolution Type' is stored in the statuscode field, and the user-entered 'Billable Time' is stored in the timespent field.

activitypointer: All activities related to a case have an activitypointer record. The duration of each closed activity is stored in the actualdurationminutes field.

There are a couple of additional complexities to the data storage:

1. It is possible for a case to have been resolved, then reactivated, and resolved again. This results in 2 incidentresolution records, so you need to identify the correct one. I always go by the most recent
2. The incidentresolution entity is a type of activity, hence has an associated activitypointer record. Fortunately, this record has no value for the actualdurationminutes, so doesn't affect any calculations of total activity time. However, you do have to be aware of this if you want a count of the number of activities

Retrieving the data
Case data is normally retrieved via reports, and hence SQL queries. The following query shows how to extract the correct Billable Time and Total Activity Time:

select i.incidentid, i.title, ir.actualend
, max(timespent) as BillableTime
, isnull(sum(a.actualdurationminutes), 0) as TotalActivityTime
from filteredactivitypointer a
join filteredincident i
on a.regardingobjectid = i.incidentid
left outer join filteredincidentresolution ir
on i.incidentid = ir.incidentidand ir.statecode = 1 -- Resolved incident resolutions
and ir.actualend = (select max(actualend) from filteredincidentresolution ir2 where ir2.incidentid = ir.incidentid and ir2.statecode = 1) -- Most recent ir activity
where i.statecode = 1 -- Resolved cases
group by i.incidentid, i.title, ir.actualend

A few comments on the query:

1. I'm only returning resolved cases in this example, but I've used an outer join to incident resolution to make it easy to change the i.statecode filter to include unresolved cases
2. I'm using isnull on the actualdurationminutes field to return 0 if there is no activity time
3. If you add any other fields from the case, or related entities (e.g. account), you'll need to add them to the group by clause as well as the select clause, or join to them in a separate query

Displaying the data in CRM
As the billable time and activity time are not stored in the incident entity, they cannot be displayed directly on the case form or in any views. To display this information in the case form, my preferred approach is to create a report using the above SQL query, and display this in an IFrame on the case form, using the techniques described in Writing Reports to display in IFrames

In CRM 3.0 there is no useful way to display this data in a view. In CRM 4.0 this should change, though it may not be as easy as you'd like; CRM 4.0 allows display of related data in a view, but it looks like this is only data from parent entities (e.g. the account to which a case belongs), not the summarised data from child entities that we're discussing here. However, the new PlugIn model allows intercepting of the view data, and should permit such display of child data with a bit of code. This is something I'll be posting on in the future, but it will have to wait till the CRM 4.0 SDK documentation is finalised.

Using reports in IFrames and from ISV.Config

This blog is not the first one I've posted on; I've previously been invited to post on the Microsoft CRM Team blog, and I posted this article Writing Reports to display in IFrames and from ISV.Config

It seems unnecessary to repeat the post here, so I won't. However I will soon be posting some additional reports that build on this technique.