Personalizing Reports for Microsoft Dynamics CRM Online - Story From: Microsoft Dynamics CRM Team Blog
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 <Language> element. This technique calls the GetCultureName method and passes in the value of the CRM_UILanguageId report parameter.
<Language>=Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureName(Parameters!CRM_UILanguageId.Value)</Language>
The reason the CRM_UILanguageId parameter cannot be used directly is because it is in the format “1033”, but the <Language> 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 <Format> element.
Format(Fields!duedatestring.Value, Parameters!CRM_ShortDatePattern.Value)
<Format>=Parameters!CRM_FormatDate.Value + " " + Parameters!CRM_FormatTime.Value</Format>
The calendar format can also be specified by setting the <Calendar> 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.
<Calendar>
=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")))))))))
</Calendar>
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.
<Value>=CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(DateTime.UtcNow, Parameters!CRM_UserTimeZoneName.Value)).ToString("g", Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureInfo(Parameters))</Value>
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.
<Format>=Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetNumberFormatString(Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureInfo(Parameters), CDbl(2), True)</Format>
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.
<Value>="(" & Fields!invcount.Value & ")" & " " &
CDbl(Sum(Fields!totalamount.Value)).ToString("C2", Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureInfo(Parameters))</Value>
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.
Categories: CRM, Microsoft Dynamics, Reporting Tags: CRM, CRM Software, dynamics, microsoft dynamics crm
Video: Create and run - Story From: Microsoft Dynamics CRM Team Blog
The reporting feature in Microsoft Dynamics CRM provides default
(out-of-the-box) reports for the most common business needs. It also provides a
Report Wizard that lets you create custom reports for your specific
requirements.
We’ve recently published a new video that shows how you can
create new reports or run reports to extract and view your data in a structured
way.
You can watch the video here:
- Video: Create and run reports (CRM On-premises users)
- Video: Create and run reports (CRM Online users )
As always, your comments and suggestions are welcome.
Cheers,
Categories: Blogging, CRM Basics, Microsoft Dynamics, Reporting Tags: microsoft dynamics crm
Tracking Contact Activity from the Organization Perspective - Story From: The Suite Advantage
You interact with people, you phone them, make appointments with them, keep notes to track details – we are all in the people-business.
But in fact, in sales we need to track how and what we are doing with the individual person’s Organization. We need to make certain we are keeping our sales and delivery efforts balanced [...]
Keep your finger on the Pulse with Dashboard Reports - Story From: The Suite Advantage
The EBSuite application always opens at the Main Page. From the Main Page all users will see the submenu items of the Main Page including the Dashboard and Reports.
The link to the Dashboard is right under the Main Tab (upper left hand quadrant of the page)
The Dashboard is designed as a series of pages (you [...]
Driving better data habits in Microsoft Dynamics CRM through scoring - Story From: Microsoft Dynamics CRM Team Blog
Good data in a CRM system is critical to its ongoing success and usefulness to a business. Months or even years after you have successfully deployed the system, the real measure of its success is how much the system is being used and the value being returned...(read more)
Categories: Data Management, JScript, Plug-in, Reporting, plugin Tags:
