Crystal Report and Crystal Xcelsius Integration

This blog is intended to display formatted report (Crystal Report) where the Xcelsius file will be embedded within the Crystal report. Only Crystal 2008 will support embedding Flash file. Further the Xcelsius file (SWF) present within the crystal report can be operated within the same page. Here an excel sheet will be acting as the database for the crystal report. Database for the crystal Report can be anything like SAP R/3 or BW data, etc

Create an Excel database with below data and save it in local disk.

Now the Xcelsius file has to be designed and SWF file will be generated. Now open the Xcelsius and place a List Box, Column chart and a Gauge in the Canvas. The List box will be displaying all the States, Column Chart will be used to display the Population on 2009 and 2010 of the selected state; and Gauge will be pointing the total population of the selected state.

Select the List Box and apply the below properties.

• Map the Label ranges by selecting the “A” column.
• Select Insertion type as “Filtered Rows”
• Map the Source Data ranges by selecting “A”, “B”, “C” and “D” columns.
• Map the Destination ranges by selecting “E”, “F”, “G” and “H” columns.

Select the Column Chart and apply the below properties.

• Create 2 Series and Name it as “2009” and “2010”
• Select 2009 series and Map cell “F1” to values(Y)
• Select 2010 series and Map cell “G1” to values(Y)
• Map the Category Labels (X) with cell “E1”

Select the Gauge and follow the below properties.

• Map the Data with cell “H1”
• Change Maximum Limit to “500”

In the Alerts tab, Check “Enable Alerts” and change “As Percent of Target” to 500

Open Data Manger and add a “Crystal Report Data Consumer” connection and map the cells as below.

• Map the Row Header ranges by selecting the “A” column.
• Map the Data ranges by selecting the “B” “C” and “D” columns.

Save the Xcelsius file (.XLF) and export the Xcelsius file as SWF.

Open Crystal Reports 2008 and select Report Wizard.

Now a new connection has to be opened by keeping the Excel file which we mentioned above will be acting as the database.

We will select “Access/Excel” as the new connection and mention the path of the excel file by changing the Database Type as “Excel 8.0.

By clicking on “Finish” several properties can be assigned in the following steps by clicking on “Next”, like

• What are the fields to be displayed in the report
• Template to be used
• Selecting fields if summary is needed and etc

Follow the below screen and assign the properties.

 While Clicking in “Finish” following screen displayed on Preview mode.

Now the Xcelsius (SWF) file has to be integrated and based on the users need, design has to be done in the formatted report. Go to design mode and click on INSERT -> FLASH and select the SWF file and place it in the crystal report

Now the values has to be mapped to which will be from the database which we are using for the crystal report. Right click on the SWF file and select “Flash Data Expert”.

Now the values have to be mapped for the SWF file.

• Drag and Drop Sheet1_.State field to Insert row Label
• Drag and Drop Sheet1_.Population-2009, Sheet1_.Population-2010 and total fields to Insert Data value fields.

Now by clicking on “Ok” save the crystal report. The crystal report can be exported to “PDF” or “HTML” so that the report can be visualized interactively.

From the above screen mention the path and export the report in HTML 4.0. Open the HTML file from the exported path and the report will generate as below.

When the database (Excel Sheet) is updated with more records, by clicking on “Refresh Data” icon in crystal report; the data will be updated and affected in the crystal report. The Xcelsius file will also be affected and displayed in the same manner as of crystal report.

By refreshing Crystal report

Save the Crystal report and export the same in HTML 4.0 format.