How to use map charts with Sun Report Builder

Suppose you have an OpenOffice.org Base database of European countries with internet usage data, and would like to use the Sun Report Builder extension to create an automatic report.

If you have the EuroOffice Map Chart extension installed, you can also insert map charts into Report Builder reports. This is how to do it:

1. Open your database file and click Reports in the left column, then select Create report in design view.

2. In the Report Builder window, first set the Content type of the report by selecting Table and the Content by selecting the data table in your database (e.g. Internetusage) on the Property sheet.

3. Design your report with labels, fields, and then use the Insert / EuroOffice Map Chart menu command.

4. In the dialog that appears you should first enter the data source. This is not too intuitive, but can be learned quickly. The Map Chart expects a list of country names and data in the "country1:data1, county2, data2, ..." format. For example, if you would like to show the countries with color codes according to internet usage percents, enter [Location]&":"&[Internetusage] where Location is the field containing country names and Internetusage is the field containing the usage percents.

5. This formula creates a chart with data of the "current" record, that is if you place the diagram in the Detail section, it will create a map for each record in the database, each map with coloring a single country. If you put it in the Footer section, it will only show the first record, again a single country.

6. Creating a map chart with all countries in a single map is a little more tricky: you should have a function that creates a list in the expected format.

7. Functions in Report Builder can be defined by opening the Report Navigator from the View menu. In the navigator window, right-click on Functions, and select New Function...

8. Select the new function, give it a name, like MapData, using the property sheet on the right side. Then copy the following instructions into the Formula field:

IF( FIND( [Location]; [MapData] & [Location] ) = LEN( [MapData] ) + 1
;
        [MapData] & [Location] & ": " & TEXT( [Internetusage] ) & ", "
;
        SUBSTITUTE(
                [MapData];
                [Location] & ": " & LEFT(
                        RIGHT(
                                [MapData];
                                LEN( [MapData] ) - (FIND( [Location]; [MapData] ) + LEN( [Location] ) + 2) + 1
                        );
                        FIND( ", "; [MapData]; FIND( [Location]; [MapData] ) + LEN( [Location] ) + 2 )
                            - (FIND( [Location]; [MapData] ) + LEN( [Location] ) + 2)
                ) & ", ";
                [Location] & ": " & TEXT( [Internetusage] + INT( LEFT(
                        RIGHT(
                                [MapData];
                                LEN( [MapData] ) - (FIND( [Location]; [MapData] ) + LEN( [Location] ) + 2) + 1
                        );
                        FIND( ", "; [MapData]; FIND( [Location]; [MapData] ) + LEN( [Location] ) + 2 )
                            - (FIND( [Location]; [MapData] ) + LEN( [Location] ) + 2)
                ) ) ) & ", "
        )
)

This is a little complicated as it has to work around some Pentaho Reporting bugs :-(

9. Now that you have the function, insert the Map Chart as before. In the Data Source field, enter the following formula (it strips the trailing comma in the MapData output):

LEFT( [MapData]; LEN( [MapData] ) - 2 )

10. Click Next, and set the properties of the Map Chart diagram on the second dialog.

11. Click Create. The map chart is inserted in your report.

12. You can view the map by executing the report. Select the Edit / Execute report menu command or click on the Execute report button.

Download sample database