Creating dynamic charts in Excel

You can make data in a chart map to a single cell in Excel, then when you change the data in a cell, the corresponding change will also be displayed in the chart. This kind of chart is called dynamic chart. To create a dynamic chart in Excel, the chart should use the same data as a banded object or table, which is exported to Excel together with the chart. That is to say, each data value in the chart must correspond to the one in the banded object or table. Moreover, the option Report Format should be selected when exporting the report to Excel.

To have the same data with a banded object or table, you must meet the following conditions for the chart before exporting the report to Excel:

The following example shows how to use the Dynamic Chart feature in Excel. Here the chart inherits data from its parent banded object.

  1. Open the catalog file JinfonetGourmetJava.cat in <install_root>\Demo\Reports\TutorialReports, and create a query Products in Data Source 1 of the catalog based on the table Products. Check all the columns in the table.
  2. Create a standard banded report based on the query Products. The banded report displays the following fields: Products_Product ID, Product Name and Price, is grouped by Category and Product Type Name (Category is the higher level), and takes the Neutral report style.
  3. In the Resource View panel, click <Add Summary...> from the Summaries node and create a summary named Sum_Price which uses the function Sum, sums on Price in the Products table, and applies to the Product Type Name group level.
  4. Drag the summary to the footer panel of the Product Type Name group level in the banded object.
  5. Click Insert > Chart to insert a Clustered Bar 2-D chart into the banded header panel. The chart inherits the dataset from the banded object (make sure Current is checked in the Data screen of the Create Chart wizard), displays Product Type Name on the category axis, Category on the series axis, and Sum_Price on the value axis.
  6. Right-click the chart and select Chart Wizard from the shortcut menu.
  7. In the Chart Wizard, switch to the Layout screen, select Export in the Options box and then select BandedObject from the Mapping Component drop-down list, then click Finish.
  8. Click File > Options. In the Options dialog, check the option Check the availability of dynamic chart for Excel in the General category, then click OK.

    This option is provided to check if the chart can be correctly mapped to the banded object when you save the report or export it to Excel.

  9. Click File > Export To > Excel to export the report to Excel (make sure Report Format is checked in the Export to Excel dialog).
  10. Open the exported result file in <install_root>\Demo\Reports\TutorialReports.

  11. Change the price of Gold Coast Blend and Colombia El Tam to 30.00, and you will find that the value of the chart is changed accordingly.

Notes: