Because so many information workers today use Microsoft Excel extensively for worksheet applications and data manipulation, it follows that enterprise report data should also be accessible in Microsoft Excel. The Crystal Reports plug-in for Excel creates the facility in Excel to connect to Crystal Enterprise and retrieve data in an interactive fashion from within the worksheet itself.
Architecture and Deployment Scenarios
The Excel plug-in requires installation on the local machine, and so requires administrative rights on the local machine.
The connection between the Excel plug-in and Crystal Enterprise uses TCP/IP over the local area network. Because of the direct connectivity between Excel and Crystal Enterprise, an Internet connection does not easily support the plug-in; usually a LAN connection to the Crystal Enterprise server is required.
Administration and Setup
The installation commences by executing the setup executable on the client machine. Because the plug-in opens Crystal Reports, the Crystal Enterprise server should be configured to grant View, Edit, Refresh, and View instances permissions to the end users who would use this application. Also, from the Start menu the plug-in must be activated by navigating to Programs, Crystal Enterprise, Add-In, and then enable or disable.
Connecting to a Report
The following steps connect to a report:
Figure 21.16. The Select Fields dialog of the Excel add-in for Crystal Reports.
This is particularly interesting for creating time-series analysis, as many different Crystal Report views can be created within one Excel worksheet, not to mention across many tabs. Of course, any Excel calculation can be applied to these values as well, facilitating the creation of summarized and formatted dashboard views that might refer to many worksheet tabs in a workbook.
Figure 21.17. The Filter the Data dialog of the Excel Add-in for Crystal Reports.
Modifying the Report View
The data then displays in Excel. Should you then want to modify the report display, both right-click menus and the Crystal menu enable you to manipulate the data view in real time. To activate the right-click menu, you have to right-click a cell that contains data populated by the Crystal Reports view (typically cells with a light yellow background).
The right-click menu has several choices. The Insert choice enables you to insert a blank row or column in the display. The Filter submenu enables you to only see data where the value includes the value you clicked on (the Focus On choice), everything other than the value you have right-clicked on (the Exclude This option), and the Add/Modify option, which opens the Filters dialog. This enables you to simply right-click a value like USA for country, choose Filters and then Focus On, and see only values for the USA. Choosing the right-click option for Remove All Filters does exactly as advertised and restores an unfiltered view of the data.
The Field choice on the right-click menu enables you to remove the selected field or open the original Filters dialog box.
The View choice exposes the options available on that report view. The Refresh choice connects to the database and refreshed the data from the database at that moment. The Properties opens a dialog with details on the report view like the Report Title, the Crystal Enterprise system supplying the view, the connection ID (useful for troubleshooting), the type of data source connected to (such as Data source versus Report instance), whether the particular instance is the latest and when it was run, if this data comes from an instance at all. The Remove option removes the entire report view from the worksheet. The Set Origin choice takes you back to the Set Origin dialog as previously mentioned. The Cell Properties choice gives you information on the value, field title, and data type of the cell. Finally, the Add/Modify Parameters opens a dialog to change the parameter values in the report.
Lastly, the Crystal menu in Excel offers one option not available from the right-click menu: the Options dialog. This dialog exposes many of the default behaviors of the plug-in. The View tab enables you to specify default data values through the Data Defaults button, and also specify whether you want to conceal the data retrieved when saving the workbook. Should you want to change the right-click behavior or how cell widths or cell protection occurs, you accomplish this from the General tab. The Connection tab governs how and when you connect to the report data source, and the Enterprise tab enables you to set a default login for convenience.
The Excel plug-in, although simple in terms of usage, results in powerful interactivity. Users value this particular method of accessing report data very highly, as they are accustomed to doing data manipulation in Excel, and the value of having enterprise data from a variety of sources available in this format increases knowledge worker efficiency greatly.