Reports

Running negative queries

Sometimes you need to get a list of records (say contacts) that don't have any related records of a particular type (e.g. appointments).
Unfortunately, this is not possible using Advanced Find as it can only return lists of records that have related records (e.g. contacts with appointments). Yes, you can narrow the related records (e.g. contacts with appointments after a certain date) but not to the extent of querying for contacts with no appointments.

Activities Report and Activities Detail Report - Circle Colours

The out of the box Activites Report only shows activites that are regarding Opportunities and Cases.

The report includes an option to show all. This uses the Activities Detail report which includes colour coding against the activities.

By analysing the rdl file for the report the following sets the colour circle to show:

IIf(Fields!scheduledend.Value>DateAdd("d",7,Now),"ballgr",IIF(IsNothing(Fields!scheduledend.Value),"ballgr",IIf(Fields!scheduledend.Value>Now,"ballyl","ballrd")))

Reporting on cases

Reporting on cases and resolutions can be frustrating until you discover that the information recorded in the Resolve Case Dialog window is found in more than one view.

When a case is resolved, a special type of activity called a case resolution is added to the case. To write reports on Cases, the following filtered views might be needed:

Report Errors - Execution 'random string' cannot be found

When CRM and SQL/SQL Reporting Services are on different computers, problems with trust for delegation may occur.

The SRS Data Connector should sort out these errors. However, I did have a situation where reports via CRM did not work but they did in Report Server.

In conjunction with Microsoft Product Support, I discovered I had to change a delegation setting on the computer account for the SQL Server.

In Active Directory, open the computer account for the SQL Server and

Republish CRM 4.0 Reports

If the CRM reports get removed from the SQL Reporting Services Server they can be republished at the CRM server by using a command-line program. Navigate to the CRM installation folder, usually C:\Program Files\Microsoft Dynamics CRM and then to Tools. Within this folder run the following command:

C:\Program Files\Microsoft Dynamics CRM\Tools> PublishReports "organizationname"

where organization name is the internal name of the CRM organization (as found in Deployment Manager).

Pages