Crystal Analysis Excel Plug-in
Similar to the Crystal Enterprise Add-in for Excel covered in the previous section, the Crystal Analysis Add-in for Excel exposes the power of Crystal Analysis (CA) from within Excel. However, the CA Add-in differs in that it does not report directly from existing CA workbooks stored in Crystal Enterpriseinstead it connects to a cube and works from there. The Crystal Enterprise ActiveX viewer for Crystal Analytic reports and the thick client CA designer also enable exported CA views to the Excel Add-in and enable continued analysis from within Excel.
End users find that the CA plug-in offers very much the same interface that they are accustomed to within the CA worksheet, and offers the capability to massage a view, or several views, at a time, and then tie them together using standard Excel formulas and functionality. This flexibility facilitates much more self-service. The user interface, although powerful, uses simple concepts that enable data manipulation that most users prefer to Excel's own pivot tables, which require quite a bit of training to properly use.
Architecture and Deployment Scenarios
Note from the previous paragraph that the add-in connects directly to the data source. The direct connection from Excel to the data cube uses either the appropriate drivers or Microsoft's Pivot Table Services when connecting to a Microsoft SQL Server Analysis Services cube. Similar to using the Crystal Analysis Rich Client, these drivers sit on the client machine and connect directly to the data source, compared to using the HTML viewer that directly connects to Crystal Enterprise, which in turn connects to the data source. So direct connectivity to the data sourceusually in the form of a LAN connectionmust be available for the Add-in to function properly.
Setting up and Administering the Crystal Analysis Excel Plug-in
The installation requires that an actual set of files install on the local machine, which again requires local administrative rights during the install process. Should a distributed installation be required, a command-line interface for the installer should be used to specify that only the CA Excel add-in should be installed. Please see the Install.pdf document on the CA installation disc for further details.
Lastly, most Excel users must actually open the Excel Tools menu, and then select Add-ins, and check the Crystal Analysis Add-in to enable it. This results in a new menu appearing to the left of the Window menu, called Crystal_Analysis. A toolbar might also appear, and initially the CA splash screen loads, and continues to load every time Excel starts.
Because the add-in directly connects to the data cube, you should not have to set up special permissions within Crystal Enterprise to enable the add-in.
Connecting to a Workbook
This section pre-supposes that you have already read Chapter 19, "Creating Crystal Analysis Reports," and Chapter 20, "Advanced Crystal Analysis Report Design," which cover CA in depth. You should also be familiar with the concepts in OLAP and in CA. Thus the following discussion of the Excel Add-in focuses on how these are implemented, not on the actual functionality.
To begin with, the user either exports a view from Crystal Analysis viewer, rich client, or designer, or starts a new connection from within Excel either through the Crystal Analysis menu or the Crystal Analysis toolbar (on the left in Figure 21.18). Then you open a new Cube View, connect to a cube using a dialog identical to the one in CA, and then orient the view in a dialog screen identical to the Worksheet tool in CA.
Figure 21.18. The Crystal Analysis toolbar and menu in Excel.
After orienting this view, you choose OK to return to the worksheet with your new view. Here you also have the option to save or open a viewpoint file (an XML file ending in *.cvp), which describes a particular orientation. This creates a cube view in Excel where your cursor was last positioned (see Figure 21.19).
Figure 21.19. The Crystal Analysis cube view within Excel.
Modifying the Workbook View
After the view has been established within Excel, you can manipulate it just as you did in CA. Because this is Excel, however, the right-click command menus are a bit different. To access the CA right-click menu, you must right-click on a cell (see Figure 21.20), and then hover over the Crystal Analysis item in the menu to expose the right-click menu.
Figure 21.20. Right-clicking on a cell will display the Crystal Analysis option, which you must hover over or click on.
Once again we discuss the menu with respect to how it differs from CA. The Reorient Cube selection navigates back to the worksheet element discussed previously to enable you to swap, pivot, and use an interface like the one in CA's worksheet. The Member Selector exposes the same Member Selector as in CA. The Copy to OLAP Formula copies a formula which refers back to the location of that data in the cube. This formula can be pasted elsewhere and resolves to the data in the cube. The Cube View menu enables you to remove the OLAP view, clone the current view to a new worksheet, select the entire OLAP view (to be able to easily copy it), select the data grid which this time selects only the data area of the OLAP view, and access the properties that are visible via a dialog enabling you to specify properties like which formats you want to have and how the cube view behaves (see Figure 21.21).
Figure 21.21. The Properties Data tab.
The remainder of the right-click menus are largely the same except that you can right-click on a dimension member in the slice selector area, navigate to the CA menu, and within the drill submenu you find the options to go left and right, which move the selected member to the next value in the cube either left or right of the current value. For instance, choosing Right when the 2001 value is selected would change the value to 2002.
Also unique to this OLAP view in Excel is the capability to drill up and down by double-clicking on the cells with a [+] or [-] cell. Double-clicking on a dimension label (which is blue by default) opens the Member Selector as well. Clicking and dragging an item, however, does not have the effect of swapping or pivoting dimensions within Excelinstead you will find an area of the worksheet selected. Use the right-click menus to choose Re-orient Cube, which opens the worksheet dialog that supports the click-and-drag operations you are accustomed to within CA.