Crystal Reports Excel Plug-in

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:

1. From Excel, highlight a cell where you would like to display the report values, and then choose the Crystal menu, and the New Report View item.
    
2. Then provide credentials to log on to Crystal Enterprise, and see the Crystal Enterprise folders, from which you can choose a report as a starting point for your query.
    
3. This in turn opens the Report View Expert, which starts with the Select Fields item. Like the Ad-Hoc application and the Crystal Reports Wizard, you simply move items to the right to include them in the view. Note the origin button at the bottom left: This enables you to choose to report off of the underlying data source of that Crystal Report, saved data in a historical instance previously scheduled in Crystal Enterprise, or saved data in the latest instance of the Report (see Figure 21.16). This dialog also enables you to specify or change report parameters if required for the 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.

1. By clicking the Next button you navigate to the Filters dialog. If you had no need to filter the content, you could simply click Finish to populate the data into Excel starting from where the cursor was placed before you chose the menu item. The Filters dialog asks you to choose a field on the left, and then working from the top on the right, choose an operator (for example, is one of, equals) and appropriate values beneath to filter the returned data (see Figure 21.17).
    

   Figure 21.17. The Filter the Data dialog of the Excel Add-in for Crystal Reports.

   

    
2. Once finished adding appropriate filters, you choose Finish and populate the data into Excel.
    

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.