Monday, September 22, 2008

Create re-usable Dynamic PivotTable Reports for Dynamics CRM

As you are probably aware, you can export any list in Dynamics CRM 4.0 as a dynamic Excel PivotTable. You have an option to select the columns you want to include in the PivotTable. Once you have exported the list as a dynamic PivotTable, you can then slice and dice the data for further analysis and save the PivotTable report for the future use.

You can open the saved Excel workbook at a later time and refresh it (Data > Refresh from CRM or Refresh All) to update the PivotTable to reflect the up-to-date information from Dynamics CRM 4.0.

If you want to make this PivotTable report available to the other CRM users, you can upload the Excel workbook containing your report to Dynamics CRM. You do this using the following steps:

1. Workplace > Reports > New
2. Report Type: Existing File
3. Browse to the folder where you saved your Excel workbook and select the workbook file
4. Save and Close

Your Excel report containing the dynamic PivotTable is now available to the other users (of course subject to their privileges set in CRM). For example, if you create a PivotTable report containing opportunities by salesperson and territory, the user viewing this report would see only the opportunities he/she can otherwise see in CRM.

Microsoft Dynamics CRM as a Development Platform

It is easy to customize Dynamics CRM using built-in customization tools. These customizations include adding new attributes, entities and views. However, sometimes customers are looking to automate their existing business processes or replace and enhance legacy applications using the latest technology without having to spend a fortune.

Microsoft Dynamics CRM is a sophisticated business application platform that provides the basic services required by developers and even business analysts to build upon. A wide range of applications can be created with this platform. These applications may be based on Microsoft Dynamics CRM but are not limited to CRM-type applications.

Microsoft partners and customers have taken advantage of this platform to build several industry specific solutions. These industries include education, government, financial services, manufacturing, distribution and many more.

Dynamics CRM is designed with the following goals in mind:
· Connect your company's "business model" and "software model": Dynamics CRM reflects more of the business model in the software model so that when changes occur within your organization or its business processes, the software can adapt to these changes more easily and affordably.
· Adaptive process: It enables you to customize the solution to adapt to the model of the business instead of forcing the business to adapt to a process built into the application.
· Focus on users: Users can access Dynamics CRM (including any customizations) from within Microsoft Outlook client or using the web browser. This provides the user experience that is tightly integrated with how they work today.

Customizations

Based on Microsoft CRM security roles, users, managers, system administrators, system customizers, and software developers can take advantage of the flexibility provided in Microsoft CRM to customize the application's appearance and behavior. Here are some examples of how users with different roles can customize Dynamics CRM:

End users can configure the way they view information and their personal options.

Company managers (executives, sales, marketing, customer service managers and other business managers) can customize user permissions, business unit settings, and workflow processes.

System customizers can customize the schema, including adding, modifying, renaming, or deleting entities and fields. System customizers can also modify the user interface by changing form and view content and defaults, adding or modifying reports, and changing the default filter for reports. System customizers can use the Workflow Manager tool to create workflow rules.

System administrators can do all the tasks system customizers can do, plus modify any record in Microsoft CRM.

Developers can customize and extend Microsoft CRM functionality by using the Workflow and Deployment Manager tools on the Microsoft CRM server, and by using the processes defined in the Microsoft CRM Software Development Kit.

Dynamics CRM System wide features
Dynamics CRM provides the following features and capabilities you can leverage in your application without having to build them from scratch.
· Access within Microsoft Outlook or from a web browser
· Import data using built-in data import tools in Dynamics CRM
· Integrate with other applications using web services based APIs (Application Programming Interfaces)
· Export data in various formats such as Excel (including dynamic PivotTables and worksheets), PDFs and Word documents
· Build cubes using SQL Server Analysis Services and analyze the data in Excel and other analysis tools

New Dynamics CRM 4.0 SDK features such as plug-ins and workflow assemblies make this platform even more appealing.

Create free form reports using cube functions in Excel 2007

Microsoft Analysis Services provides an OLAP (Online Analytical Processing) engine that provides fast access to aggregated business data such as sales, budgets, inventory and costs. Analysis Services publishes data in form of a cube which enables you to filter and group data by predefined categories or dimensions. A cube is often used with Excel PivotTable feature which enables you to browse data interactively in Excel with just a few mouse clicks.

PivotTable report is an excellent tool for structured browsing. On the other hand, it is too inflexible for many common reporting needs. I will describe some of these scenarios later.

The new “Convert to formulas” feature in Excel 2007 addresses this gap. Using this feature, you can convert the cells in a PivotTable to formulas. This feature converts the cells to formulas that use Cube functions. Once you have done that, you can then take full advantage of all Excel features (such as layouts, formats, coloring, annotations, etc.) to produce a report the way you desire. You are no longer limited by the PivotTable’s rigid predefined structure. In addition to converting PivotTable cells to formulas, you can also enter new formulas using Cube functions to create a custom report from scratch or add information to an existing report converted from a PivotTable.

When you convert cells to formulas, these formulas access the same data as the PivotTable report and can be refreshed to see up-to-date results. However, with the possible exception of report filters, you no longer have access to the interactive features of a PivotTable report, such as filtering, sorting, or expanding and collapsing levels.

Common scenarios for converting PivotTable reports to formulas

The following are a few examples of what you can do after you convert PivotTable cells to worksheet formulas to customize the layout of the converted cells.

Rearrange and delete cells
Example: There are two divisions in your company: Bicycles and Accessories. For the sales report for the Bicycles division managers, you want to include sales data for both divisions but the cost information only for the Bicycles division. You also want to rearrange and group certain products in the report and add subtotals for the group. You want to format some cells by changing the cell color and making them bold. In this case, you can create the PivotTable, convert it to worksheet formulas, rearrange cells to a design layout you desire, delete the cells that are not necessary (cost information for Accessories division) and format the cells (bold, color, conditional formatting, etc.) to suit your preference.

Insert rows and columns
Example: For the sales report, you want to insert a row to display a subtotal for two products. You also want to add three columns to show the Budget data, Budget Variance and Year Over Year Percentage change in sales for each product and product group.

Use multiple data sources
Example: You want to include the budget data stored in Excel spreadsheets or a relational database on your sales report for variance analysis that shows the difference between budgeted and actual sales.

Use cell references to vary user input
Example: When the user changes a cell value from “MTD” to “YTD”, you want to refresh the report with the Year to Date information and vice versa. You can reference the input cell in your worksheet formulas to achieve this result.

Create a nonuniform row or column layout (also called asymmetric reporting)
Example: You need to create a report that contains a 2008 column called Actual Sales, a 2009 column called Forecast, but you don’t want any other columns. You can create a report that contains just those columns unlike a PivotTable report, which requires symmetric reporting.

Create your own Cube formulas and MDX expressions
As mentioned earlier, you can create a completely new report from scratch by using the combination of cube functions and MDX expressions. For example, you may enter a cube formula to retrieve sales amount for the Western region for the sales person Smith for 2007 Q4 by product or product category. You may also enhance your converted PivotTable report by entering new formulas using Cube functions.

Steps to create a free form report
Next, let us see how all this works using an example. In this example, we will create the following custom report.













Follow these steps to create the above custom report.

1. Create a PivotTable Report as shown in the screen shot below. I used the Adventure Works Sales cube for the purpose of this example. I recommend making a copy of the workbook before converting to formulas, in case you need to refer to the original PivotTable or reuse it to create another free form report later. You would want to prepare the PivotTable report as close to the layout you want as possible. This will minimize the effort required in making changes to the report after you have converted it to formulas. You can use filtering, sorting and layout features of the PivotTable to achieve this.

2. Click PivotTable. On the Options tab, in the Tools group, click OLAP tools, and then click Convert to Formulas. If you want to convert the entire PivotTable to formulas, select Convert Report Filters checkbox. Clear this checkbox to convert only the PivotTable row labels, column labels, and values area, but keep the Report Filters. In this example, we do not have the Report Filters for our report. Therefore, clear the checkbox and click Convert.You will notice that Excel 2007 has converted the PivotTable cells to use the cube functions CUBEMEMBER and CUBEVALUE. I will discuss these and the other CUBE functions in the following section.
For cells that are converted, cell formatting is preserved, but PivotTable styles are removed because these styles are applicable to PivotTables only.

3. Delete unwanted columns and rows. In our case, we will delete the following columns: 2001, 2002, Grand Total.

4. Switch 2003 and 2004 columns as desired for our custom report.

5. Insert the following 3 columns: Budget, Variance %, YOY Change %. The Budget column was populated by copying and pasting values from a separate Budget spreadsheet. Variance % column is calculated as: (Actual – Budget) / Budget. YOY Change % column is calculated as: (Current Year Actual – Previous Year Actual) / Previous Year Actual.

6. Format the report and cells as desired.

That’s all! We were able to create a custom report with a few simple steps.

Explanation of CUBE functions

In this section, I will briefly explain the CUBE functions available in Excel 2007 using examples.

The CUBEMEMBER function returns a member or tuple from the cube. A tuple uniquely identifies a cell, based on a combination of attribute members that consist of an attribute from one or more attribute hierarchies in the cube. For example, a tuple may represent members from two dimensions (Geography and Product) or even one or more dimensions and measure. Here are a couple of examples showing the use of CUBEMEMBER function.
A1=CUBEMEMBER("Adventure Works","[Ship Date].[Calendar].[Calendar Year].&[2002]") retrieves the cube member representing calendar year 2002 of Ship Date dimension.
A2=CUBEMEMBER("Adventure Works","[Measures].[Reseller Sales Amount]") retrieves the cube member representing Reseller Sales Amount measure of the cube.
The above two cells (A1 and A2) can be used as references to retrieve the Reseller Sales Amount for the calendar year 2002 as shown below.

The CUBEVALUE function returns an aggregated value from the cube for a member or tuple from the cube. For example, the following function retrieves a tuple representing Reseller Sales Amount for calendar year 2002.
A3=CUBEVALUE("Adventure Works",A1,A2)
A member is a value of a dimension attribute (such as customer names, product names, month names, etc.).
A tuple uniquely identifies a cell, based on a combination of attribute members that consist of an attribute from every attribute hierarchy in the cube. When defining a tuple in an MDX query or expression, you do not need to explicitly include the attribute member from every attribute hierarchy. If a member from an attribute hierarchy is not explicitly included in a query or an expression, the default member for that attribute hierarchy is the attribute member implicitly included in the tuple. Unless otherwise explicitly defined in a cube, the default member for every attribute hierarchy is the (All) member, if an (All) member exists. If an (All) member does not exist within an attribute hierarchy, the default member is a member of the attribute hierarchy's top level. The default measure is the first measure specified in the cube, unless a default measure is explicitly defined. For more information, see Defining a Default Member and DefaultMember (MDX).

The following are a few more Cube functions available in Excel 2007.

CUBEKPIMEMBER
Returns a key performance indicator (KPI) property and displays the KPI name in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, that is used to monitor an organization's performance.
CUBEMEMBERPROPERTY
Returns the value of a member property from the cube. Use to validate that a member name exists within the cube and to return the specified property for this member.
CUBERANKEDMEMBER
Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or the top 10 students.
CUBESET
Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Office Excel.
CUBESETCOUNT
Returns the number of items in a set.

Conclusion

In this article, I have described how you can create free form reports in any desirable format using Excel 2007 and Cube functions.