Export (0) Print
Expand All

What’s New in Reporting for Microsoft Dynamics CRM 4.0

Inna Agranov
Microsoft Corporation

August 2008

Summary

This article provides a description of new reporting features introduced in Microsoft Dynamics CRM 4.0 Web Services.

Applies To

Microsoft Dynamics CRM 4.0

Microsoft Visual Studio 2005

Introduction

Microsoft Dynamics CRM includes a number of ready to use business reports and provides the capabilities for creating custom reports. In Microsoft Dynamics CRM 3.0, you could manage the reports only by using the Microsoft Dynamics CRM Web application. Now, you can manage the reports programmatically by using the Microsoft Dynamics CRM Web services. In Microsoft Dynamics CRM 4.0, the reports are represented by a set of entities described in Report Entity Model. You can use Microsoft Dynamics CRM messages to create, delete, update and retrieve reports. You have complete control over the ownership of reports by making them organization-owned or user-owned. User-owned reports can be shared with other users and teams and assigned to other users. The reports can be standalone or have child reports that contain more detailed information. The parental relationship between the parent and child reports lets you use cascading rules in the operations performed on the parent report.

Some of the new features available in Microsoft Dynamics CRM 4.0 Web services include linking reports, publishing reports using Web services, categorizing and displaying reports in different languages, and formatting numbers, dates and time.

Comparing Reporting Features in Microsoft Dynamics CRM 3.0 and 4.0

Many reporting features that were only available in the Microsoft Dynamics CRM 3.0 Web application are now available in Microsoft Dynamics CRM 4.0 Web services. A rich report entity model and a set of new APIs let you effectively manage the reports programmatically.

The following table contains a summary of new reporting features that are available in Microsoft Dynamics CRM 4.0 Web Services and compares them to the features in Microsoft Dynamics CRM 3.0.

 

Feature Microsoft Dynamics CRM 3.0 Microsoft Dynamics CRM 4.0

Report Entity Model

Not available.

The report entity model contains the report, report entity, report category, and report visibility entities.

Categorization

Available in Web application only.

You can specify report category, such as Marketing, Sales, or Service and report related entities by using the report entity and report category entities.

Report visibility

Available in Web application only.

You can specify report visibility, such as grid or form, by using the report visibility entity.

Create, delete, retrieve, update report

Available in Web application only.

To perform these operations, you can use the following Microsoft Dynamics CRM messages: Create, Delete, Retrieve, RetrieveMultiple, and Update.

Download the report definition

Available in Web application only.

Use the DownloadReportDefinition message.

Manage reports in the offline mode

Not available.

Reports can be retrieved by using the Retrieve message or the RetrieveMultiple message, but reports cannot be created, deleted, or updated.

Ownership and sharing

Available in Web application only. All reports are organization-owned, and cannot be shared or assigned to other users and teams.

Reports can be user-owned or organization-owned. User-owned reports can be shared and assigned to other users and teams. For example, to share a report with another user, use the GrantAccess message.

Linking reports

Handled internally by Microsoft SQL Server Reporting Services.

Create a parent report entity first and pass the ID of the parent report to a child report.

Categorize the reports by language

Not available.

Use the report.languagecode property to categorize the reports by language. This enables you to display the reports in different languages.

All new Web services features listed in the table are also available in the Microsoft Dynamics CRM 4.0 Web application. For more information, see Microsoft Dynamics CRM online Help.

Report Entity Model and Messages

The report entity model contains a set of entities that are used for managing reports in Microsoft Dynamics CRM. The report entity represents a report definition. Other report entities are used to specify the entities and categories that are related to a report, areas in Microsoft Dynamics CRM where the report is shown, and links and dependencies between related reports.

For more information, see Report Entity Model.

The following table lists the report entities.

 

Entity name Description

report

Data summary in an easy-to-read layout.

reportcategory

Categories related to a report. A report can be related to multiple categories.

reportentity

Entities related to a report. A report can be related to multiple entities.

reportlink

Links and dependencies between reporting services reports. A report may drill through to another report, or it may have another report as a subreport.

reportvisibility

Area in which to show a report. A report can be shown in multiple areas.

The report entity can be used with the new messages listed in the following table.

 

Message name Description

DownloadReportDefinition

Use this message to return the report definition associated with the report.

Pass the ID of a report in the ReportId property of this request.

The report definition is returned in the BodyText property of the message response.

GetReportHistoryLimit

Use this message to retrieve an integer value that represents the number of report history snapshots that a given report is permitted to have.

The default number of the report history snapshots is 8. If -1 is returned, the report is permitted to have an unlimited number of report history snapshots.

MakeAvailableToOrganizationReport

Use this message to change the ownership of the report to organization-owned.

Pass the ID of a report in the ReportId property of this request.

MakeUnAvailableToOrganizationReport

Use this message to change the ownership of the report to user-owned.

Pass the ID of a report in the ReportId property of this request.

SetReportRelated

Use this message to specify in which areas of the Microsoft Dynamics CRM Web application the report can be run and viewed.

Linking Reports

The report can be self-contained or have child reports that provide more detailed information. The parent and the child reports have a parental relationship. This means that all operations on the parent entity instance are propagated to the child entity instances (cascade all). However, in a delete operation, the child report is not deleted, but the link between the parent report and the child report is removed. The child report must contain the information about the parent report ID. To specify the parent report ID, set the report.parentreportid property in the child report. The linking between the parent report and child report is performed when the child report is created or updated. The parent can have multiple child reports. It is best to publish a parent report first and then publish a child report. If you create a child report before you create a parent report, you have to update the child report with the parent report ID after the parent report is created.

Publishing a Report Using the Microsoft Dynamics CRM Web Services

To publish a report by using Web services, you have to use the classes and methods that are contained in the CrmService Web service.

A report is represented by the report entity instance. You have to use the Create message to create a new report, or use the Update message to update an existing report.

By default, the report is visible in the All Reports, Including Sub-Reports view in the Reports grid. To show a report in additional views in the grid, or different areas, such as entity form or entity grid, use the reportvisibility.visibilitycode property to specify the view or the area. You can specify multiple views and areas.

To show and run a report in the different report categories, such as Marketing, Sales, or Service, use the reportcategory.categorycode property to select a category. You can specify multiple categories.

To specify a related entity for the report, use the reportentity.objecttypecode property. You can specify multiple related entities.

When you create a report entity instance, you have to specify the report name and the report type. If the report type is Reporting Services Report, you have to set the report.bodytext property. If the report type is Other Report, you have to set the report.bodybinary property. If the report type is Linked Report, you have to set the report.bodyurl property.

If the report type is Reporting Services Report or Other Report, you also have to specify the name of the file that contains the report definition by setting the report.filename property. Other properties are optional.

Example

This sample code shows how to create the entity instances needed to publish a report. Notice that this sample code uses two helper files: businessentitypartialtypes.cs and enums.cs. For more information, see CrmHelpers Classes.

// Set up the CRM Service.
CrmAuthenticationToken token = new CrmAuthenticationToken();
// Notice that this enumeration is found in the helper code file enums.cs.
token.AuthenticationType = AuthenticationType.AD; 
token.OrganizationName = "AdventureWorksCycle";
CrmService service = new CrmService();
service.Url = "http://<servername>:<port>/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Read the report file.
string body = System.IO.File.ReadAllText(@"C:\MyReports\Account Summary.rdl", Encoding.UTF8);

// Create the report object.
report r = new report();
r.bodytext = body;
r.name = "Account Summary";
r.filename = "Account Summary.rdl";

// Notice that this constructor for CrmNumber is found in the helper code file BusinessEntityPartialTypes.cs.
r.languagecode = new CrmNumber(1033); // US English

// Notice that this enumeration is found in the helper code file enums.cs.
r.reporttypecode = new Picklist(ReportTypeCode.ReportingServices);
Guid reportId = service.Create(r);
Lookup reportLookup = new Lookup(EntityName.report.ToString(), reportId);

// Create the report category.
reportcategory rc = new reportcategory();
rc.reportid = reportLookup;
rc.categorycode = new Picklist(4); // Administrative
service.Create(rc);

// Define which entity this report uses.
reportentity re = new reportentity();
re.reportid = reportLookup;
re.objecttypecode = new EntityNameReference(EntityName.account.ToString());
service.Create(re);

// Set the visibility for the report.
reportvisibility rv = new reportvisibility();
rv.reportid = reportLookup;
rv.visibilitycode = new Picklist(ReportVisibilityCode.Form);
service.Create(rv);
rv = new reportvisibility();
rv.reportid = reportLookup;
rv.visibilitycode = new Picklist(ReportVisibilityCode.Grid);
service.Create(rv);
rv = new reportvisibility();
rv.reportid = reportLookup;
rv.visibilitycode = new Picklist(ReportVisibilityCode.ReportsGrid);
service.Create(rv);

Categorizing and Displaying Reports in Different Languages

You can enable additional languages in Microsoft Dynamics CRM by installing Language Packs. This lets you display text in the user interface, online Help, and the reports in different languages. For more information about how to install Language Packs, you can download the Microsoft Dynamics CRM 4.0 Implementation Guide

To categorize the reports by language, use the report.languagecode property. You can set the property to a specific locale ID (for example, 1033 for US English) to make the report visible to the users of that language. For example, the English out-of-the-box Account Summary report appears in the Reports grid in the English user interface, but not in the Spanish or German user interfaces in the same organization.

You can also set the report.languagecode property to -1 (minus one) to make the report visible to all users in the base language user interface (this user interface is installed during the original Microsoft Dynamics CRM server installation) and in the user interfaces in other languages. For more information about locale IDs, see List of Locale ID (LCID) Values as Assigned by Microsoft.

You can use the report language information in combination with information that is contained in the report entity, report category, and report visibility entities to determine the areas and categories in the Microsoft Dynamics CRM Web application where the report is shown in different user interfaces languages.

noteNote
The Language element inside the report definition language (RDL) file does not determine where the report is shown inside the Microsoft Dynamics CRM Web application. It contains an expression that evaluates to a language code as defined in the Internet Engineering Task Force (IETF) RFC1766 specification. The language code is used mainly for formatting numbers, dates, and times for a specified language. For more information about the Language element, see Language Element (Report) (RDL)

Formatting numbers, dates, and time

Formatting values for time, date, number, and currency can be obtained by using the fn_GetFormatStrings SQL function that is provided in the Microsoft Dynamics CRM database. This function returns a single row data table that contains formatting values. To view the contents of the returned table, execute the following SQL code in Query Analyzer on the Microsoft Dynamics CRM database server:


USE <organization>_MSCRM
SELECT * FROM dbo.fn_GetFormatStrings()

To use the formatting values in reports, follow the steps.

  1. Create a dataset to contain the formatting data. For more information about how to create a dataset, see Microsoft SQL Server Books Online

  2. Name the dataset DSNumandCurrency or use another name. DSNumandCurrency is the dataset name that is used in reports that are included with Microsoft Dynamics CRM.

  3. Use SQL code with the SELECT statement to fill the dataset.

  4. Reference the format field from the dataset in the Format property of the report item you want to format.

Date and Time Values

 

Formatting string Report item Format property value

Date

=First(Fields!DateFormat.Value, "DSNumandCurrency")

Time

=First(Fields!TimeFormat.Value, "DSNumandCurrency")

For date and time, you should also set the Calendar property of the report item to "=First(Fields!CalendarType.Value, "DSNumandCurrency")".For date and time, you should also set the Calendar property of the report item to "=First(Fields!CalendarType.Value, "DSNumandCurrency")".

Number Values

 

Formatting string Report item Format property value

Integer

=First(Fields!NumberFormat_0_Precision.Value, "DSNumandCurrency")

Decimal with 2-decimal points precision

=First(Fields!NumberFormat_2_Precision.Value, "DSNumandCurrency")

The fn_GetFormatStrings function returns the number of format strings with precision values between 0 and 5. You can specify a precision by putting a decimal value between the underscore characters in the field name.

Base Currency Value

 

Formatting string Report item Format property value

Base currency with 2-decimal points precision

=First(Fields!NumberFormat_0_Precision.Value, "DSNumandCurrency")

The fn_GetFormatStrings function returns base currency format strings with precision values between 0 and 5. You can specify a precision by putting a decimal value between the underscore characters in the field name.

Transaction currency

When you create a report on an entity with the transaction currency information, you can retrieve the transaction currency format string from the crm_moneyformatstring column of the filtered view for an entity. After a column has been added to the dataset, you can reference the column on the Format property of the report. For more information about how to add columns to a dataset, see How to: Add, Edit, or Delete a Field in the Datasets Window (Report Designer). For example, to retrieve a price unit and the transaction currency formatting information from the quote detail filtered view, use the following SELECT statement:

SELECT priceperunit, crm_moneyformatstring FROM FilteredQuoteDetail

You can reference a new column on the Format property of the report item as follows:

=Fields.crm_moneyformatstring.Value

noteNote
For date, time, and currency formatting, set the Language property of the report item or the report to "=First(Fields! NumberLanguageCode.Value, "DSNumandCurrency")".

Managing a Report in the Offline Mode

The following report managing activities cannot be performed when the local Microsoft Dynamics CRM system remains in the offline mode:

  • Create new reports or update existing reports by using the Create message and the Update message.

  • Delete the reports by using the Delete message.

  • Change the ownership type of the reports by using the MakeAvailableToOrganizationReport message or the MakeUnavailableToOrganizationReport message.

  • Run the custom reports that use the multi-valued parameters. The multi-valued parameters are defined in the MultiValue element of the Report Definition Language (RDL) file.

However, you can retrieve the published reports in the offline mode by using the Retrieve message and the RetrieveMultiple message. This enables you to view the reports in Microsoft Dynamics CRM Report Viewer and the Reports grid.

noteNote
We do not recommend that you use the ReportFolder, ReportName and ReportServerUrl global variables in reports that run in the offline mode. These variables are members of the Globals collection that contains the global variables for the report, such as execution time, number of pages, and report name. For more information about Global collections, see Using Global Collections in Expressions (Reporting Services).

Additional Information

Download the Microsoft Dynamics CRM 4.0 Software Development Kit (SDK) from the MSDN Developer Center.

For more information, see the Report Writers Guide.

Send Us Your Feedback about this Article

We appreciate hearing from you. To send your feedback, click the following link and type your comments in the message body.

noteNote
The subject-line information is used to route your feedback. If you remove or modify the subject line we may be unable to process your feedback.

Send Feedback

 

 

Community Additions

ADD
Show:
© 2014 Microsoft