Microsoft Dynamics CRM Team Blog: Personalizing Reports for Microsoft Dynamics CRM Online

[Blog bot]

Источник: http://blogs.msdn.com/b/crm/archive/...rm-online.aspx
==============

Microsoft Dynamics CRM 2011 has excellent reporting capabilities. Customizers have the option to create SSRS reports using SQL or CRM’s querying language, FetchXML, in on premise deployments. Customizers must leverage FetchXML to create SSRS reports in online deployments for improved security and performance. ISVs should consider creating reports using FetchXML to reach the broadest audience. This blog will provide commons patterns that can be applied when writing reports using FetchXML or converting SQL based reports to FetchXML based reports.

RDLHelper


Many of the patterns rely upon members in the RDLHelper assembly. The RDLHelper assembly contains a set of VB types that can be accessed within SSRS reports in an online deployment. Only the RDLHelper assembly and a subset of the VB library can be accessed in an online deployment because the Report Server is running in sandbox mode for enhanced security and performance. Customizers of on premise deployments can also configure their Report Server to run in sandbox mode. More details can be found on MSDN. The whitelist of accessible types and blacklist of restricted members can be found in the SDK
documentation.

CRM Report Parameters


SSRS reports that are executed within CRM have access to report parameters that are populated by CRM at runtime. These parameters are populated with organization and user settings and data. These parameters are provided to facilitate creation of personalized reports based on the current user. The complete list of CRM Report Parameters can be found in the SDK documentation.

Personalization


Before authoring a report it is important to consider the audience who will view it. Author reports such that the target audience can easily understand them. A pattern to increase comprehension is to use the current user’s language, date, time, number, and currency settings to display information. The pattern can be implemented by combining CRM Report Parameters, members of the RDLHelper assembly, and other whitelisted VB types.

In on premise deployments customizers can gain access to the current user’s settings by calling the table valued user defined function called fn_GetFormatStrings. More information on this technique can be found in the SDK documentation. This technique is not possible in online deployments because SQL datasets are not allowed in SSRS reports. With clever use of CRM Report Parameters and VB scripting the same pattern can be implemented in online deployments.

Language


A report can specify the current user’s language by setting the value of the element. This technique calls the GetCultureName method and passes in the value of the CRM_UILanguageId report parameter.

=Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureName(Parameters!CRM_UILanguageId.Value)

The reason the CRM_UILanguageId parameter cannot be used directly is because it is in the format “1033”, but the element expects the format “en-us”. The GetCultureName method enables us to convert from the culture code to the culture name.

Date & Time


The text value of many report controls can be formatted using the current user’s settings. The example shown below passes a custom format string to the ToString method and a CultureInfo object. The CultureInfo
object is created by calling the CreateSpecificCulture method and passing in a culture name. The culture name can be obtained by calling the GetCultureName method and passing in the value of the CRM_UILanguageId report parameter.

CDate(Fields!FirstDayOfThisMonth.Value).ToString("MMM-yy", System.Globalization.CultureInfo.CreateSpecificCulture(Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureName(Parameters!CRM_UILanguageId.Value)))

This pattern is applicable anywhere format strings can be used including the VB Format method or the RDL element.

Format(Fields!duedatestring.Value, Parameters!CRM_ShortDatePattern.Value)

=Parameters!CRM_FormatDate.Value + " " + Parameters!CRM_FormatTime.Value

The calendar format can also be specified by setting the element’s value. The value is the name of the calendar. The CRM_CalendarTypeCode report parameter specifies the calendar type as an integer. The integer needs to be converted to the corresponding name. Use the IFF method rather than a Switch because the Switch method is in the blacklist table.



=IFF(

IsNothing(Parameters!CRM_CalendarTypeCode.Value) or Parameters!CRM_CalendarTypeCode.Value = -1 or Parameters!CRM_CalendarTypeCode.Value = 0, "Gregorian",


IFF(Parameters!CRM_CalendarTypeCode.Value = 1, "Japanese",

IFF(Parameters!CRM_CalendarTypeCode.Value = 2, "Korea",
IFF(Parameters!CRM_CalendarTypeCode.Value = 3, "Taiwan",
IFF(Parameters!CRM_CalendarTypeCode.Value = 4, "Gregorian US English",
IFF(Parameters!CRM_CalendarTypeCode.Value = 5, "Gregorian Arabic",
IFF(Parameters!CRM_CalendarTypeCode.Value = 6, "Gregorian Middle East French",

IFF(Parameters!CRM_CalendarTypeCode.Value = 7, "Gregorian Transliterated English",
IFF(Parameters!CRM_CalendarTypeCode.Value = 8, "Gregorian Transliterated French" "Gregorian")))))))))



Given a UTC time value the current user’s time can be retrieved by calling the ConvertUtcToLocalTime method and passing in the time value and the value of the CRM_UserTimeZoneName report parameter. Calling the method returns a DateTime object whose ToString method can be called by supplying a custom format string and the user’s CultureInfo object. The CultureInfo object can be obtained by calling the GetCultureInfo method and passing in the report parameters collection. It is important to note that the entire CRM report parameters collection needs to be referenced. The listing of all members of the collection can be found in the SDK documentation.

=CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(DateTime.UtcNow, Parameters!CRM_UserTimeZoneName.Value)).ToString("g", Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureInfo(Parameters))

Number & Currency


Customizers can apply the same pattern to format numbers and currency. An example of specifying the user’s format string for currency is shown below. The first parameter is the current user’s CultureInfo object, the second parameter is the decimal precision, and the last parameter specifies whether the number should be formatted as currency.

=Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetNumberFormatString(Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureInfo(Parameters), CDbl(2), True)

As an alternative the value itself can be formatted by calling the ToString method and passing in a custom format string and the current user’s CultureInfo object.

="(" & Fields!invcount.Value & ")" & " " &

CDbl(Sum(Fields!totalamount.Value)).ToString("C2", Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureInfo(Parameters))

Next Steps


The general pattern that has been applied is to use CRM report parameters as arguments to VB script methods to personalize reports. Only a small slice of CRM report parameters and VB script methods have been examined in this blog. Please contribute to and empower the CRM community by blogging about any other uses you have found. The next step is to brush up on FetchXML patterns and start writing your own custom FetchXML reports for Microsoft Dynamics CRM Online.



Bao Nguyen






Источник: http://blogs.msdn.com/b/crm/archive/...rm-online.aspx