Most of the time, we say that CrystalReports is to show a huge amount of detail records in fixed and complex formats while Xcelsius is for visualizing aggregated information with attractive display.
Sometimes we may want to combine them for the following purposes:
1. To jump to a Crystal Report from Xcelsius dashboard
The manager playing with the Xcelsius dashboard with highly-aggregated information drills down to more detailed level. Sometimes the most detailed data is placed in a Crystal Report. To do this, you can create a URL button in your dashboard which will redirect the user to the Crystal Report using OpenDocument.
2. To embed an Xcelsius dashboard in a Crystal Report
The user plays with detailed data inside a Crystal Report, but want to see some aggregated information displayed in a dashboard. This is what we’re going to talk about in this blog.
The Crystal Report Data Consumer connectivity is used for you to insert Xcelsius dashboards into Crystal Reports 2008, and feed live data into Xcelsius. In this way you leverage and improve existing Crystal Reports by adding the power of Xcelsius to it. The integration of Xcelsius and Crystal Reports is one of the interactivity enhancements in Crystal Reports 2008. This integration enables report designers to embed Xcelsius visualization into Crystal Reports and link report data to the visualization, with the help of this connectivity defined inXcelsius side.
If you are new to this usage, Figure 1 below showsa simple report in Crystal Report 2008 designer environment, with an Xcelsius dashboard embedded.
Figure 1. A Crystal Report with an Xcelsius dashboard inserted
When to use
Generally speaking, you may choose such a connectivity if you want to combine the advantages of Crystal Reports and Xcelsius, providing intuitive analysis as well as formatted enterprise-level reporting into one single Crystal Report.
With the development of the business intelligence infrastructure, the demand of the end users also grows. The consumers of formatted reports may ask for more intuitive charts, easier data access, or even what-if scenario analysis models inside the reports. Their primary concerns are still the detailed information while combining the interactivity of Xcelsius with aggregated data will improve their efficiency. This is the situation under which you should consider using the Crystal Reports Data Consumer connection to feed data toXcelsius visual models embedded into Crystal Reports. Otherwise, if the user’s focus is primarily on the visualization itself, you don’t bother embedding itinto Crystal Reports. You should use other kind of connections to pull live datafrom data sources. And if you have Crystal Reports, Web Intelligence orUniverse data in place, Live Office connection may be your best choice.
There are some other technical limitations which could prevent you from using the Crystal Reports Data Consumer connections. Keep in mind that exporting and printing of Flash objects with connections to Crystal Reports data is not supported and embedded Flash objects that do not have connections to Crystal Reports data can be exported to PDF only, so if you have strong requirement on exporting and printing, you may not be able to use the Crystal Reports Data Consumer connection.
How to use
To embed Xcelsius dashboards into Crystal Reports, you need to work with both Xcelsiusand Crystal Reports designer. The common steps are:
1. Create a Crystal report to prepare data forXcelsius first.
2. Design the visualization in Xcelsius and configure the Crystal Reports Data Consumer connection which is required for linking with Crystal Reports data later.
3. Now that you have both the Crystal Report andXcelsius dashboard ready, insert the exported SWF file into the crystal report
4. Finally, set your Xcelsius model to receive realtime Crystal Reports data.
In the rest ofthis blog we’ll focus on configuring the Crystal Reports data consumer connection and linking Crystal Reports data.
Define CR Data Consumer connection in Xcelsius
You can add only one connection of this type in a single dashboard, which is the same as Flash Variables and Portal Data. Its property panel is displayed in Figure 2 below:
Figure 2. Definition of a Crystal Report Data Consumer connection
As shown inthis figure, the Crystal Report Data Consumer connection pulls three types of data from the Crystal Report and populates the embedded Excel spreadsheet with that data. These three types of data, RowHeaders, Column Headers, and Data Range, are essentially form across tabulation, or crosstab in abbreviation. Crosstab is a very efficient wayto display measures with two dimensions. It can provide greater insight thansimple vertical table or horizontal table within a smaller space. If your work involves analyzing data in Excel, you must be familiar with the PivotTable functionality in Excel, which, in essence, creates a crosstab. Figure 3 below shows what the 3 types are in a cross-tab:
Figure 3. Data organized in a cross-tab
You can bind each of the 3 types to a cell range in the embedded spreadsheet. Generally speaking, you bind Row Headers and Column Headers to a row or column respectively, and Data Range to a table. You may wonder what if your data do have only one series. If that is the case, you can omit the binding for Row Headers or Column Headers to linking to a vertical table or a horizontal table of data. That being said, you should be aware that you don’t need to bind all these three types of data. You can even bind only Row Header, only Column Header, or only Data although that would be hardly useful.
Integration withCrystal Reports
After you’ve built the dashboard with a Crystal Reports Data Consumer connection properly set up, you export the visual model to a SWF.
Now you open a Crystal report in Crystal Reports 2008 Designer and insert the SWF by clicking the “Insert Flash” command. A dialog box prompts up, as displayed in Figure 4 below, in which you can choose a SWF file and specify whether to embed it into the Crystal report orcreate a link to the original SWF document only. To save you troubles when you migrating the Crystal report to other environment, the embed option is preferred, though it may increase the size of the crystal report a little.
Now you’ve added the SWF into Crystal report, you need to link Crystal Reports data. To do this, you launch Flash Data Expert in the context menu of the Flash Object.
The Flash Data Expert interface is divides into two parts. On the left, available fields in the report are listed and you can drag them into the boxes below to bind a field to Row Headers, Column Headers, or Data. Each of these three boxes is only available if the corresponding part in the Crystal Reports Data Consumer is bound to cells in the Excel worksheet. For example, the Xcelsius designe rneeds a horizontal table of data, so he only binds the Row Headers and Data inthe Crystal Reports Data Consumer connection in Xcelsius. Then there will be only an Insert Row Label box and an Insert Data Values box in the Flash Data Expert for you to bind Crystal Reports Data. On the right part, there is a preview window showing what the Xcelsius visual model looks like under current binding configuration. The description you entered in the Crystal Reports Data Consumer connection is displayed under the Preview window. You can find useful description the Xcelsius designer leaves to you here.
As displayedin Figure 5 below, we insert the Year field to column headers, the Store Namefield to the row headers, and the Sales Revenue filed to the data values. Wecan see the preview of the data and the Xcelsius visual model.
To help you better understand this, you can create a native Cross-Tab in Crystal Reports, without Xcelsius. If you already have a native Crystal Reports crosstab in the report, you can directly feed it to the Xcelsius visual model in the Flash DataExpert.
There’s another Flash Vars tab in the Flash Data Binding Expert dialog, with which you can also pass data from Crystal Reports to the embedded Xcelsius dashboard using Flash Variables if the Xcelsius dashboard has a Flash Variable connection.In Figure 6 below, you can see that in the FlashData Expert for this Flash object, there is no Xcelsius Data tab, but onlya tab “Flash Vars” tab available where you can bind a field or a formula to a Flash Variable.
In fact,using Flash Variables to connect to Crystal Reports data has nothing to do with the Crystal Reports Data Consumer connection. As long as there is a Flash Variable connection defined in the Xcelsius visualization, you can pass data into Xcelsius through Flash Data Expert, using Flash Vars. This is primarily used with Xcelsius 4.5, the previous version of Xcelsius 2008, since which version the Crystal Reports Data Consumer connection is provided.
Generally speaking, you can use these two methods interchangeably, but theCrystal Reports Data Consumer connection is easier to use. For Flash Variables connection, you usually have to create Excel formulas to transform the data to the format Flash Variable connection requires, either CSV or XML.