Progress® Telerik® Reporting R3 2017

How to: Create a Map with BarChart series using CSV data source

In this how-to article we will show you how to create a Map which will present the countries with the most medals won in Olympic Games divided by their continental Association of National Olympic Committees. We will also add some interactivity to the report, giving the user the option to choose which countries to be displayed on the map, highlighting the currently selected country and displaying an additional chart with detailed information about it. We will be using the Standalone Report Designer to demonstrate how such a complex report can be done without writing any code.

Important note Important

This article assumes that you have obtained a valid Location Provider key to authenticate your geocoding requests. If you don't want to use a location provider, you need to provide the geographical coordinates of your points yourself and setup the MapSeries accordingly. For more information please take a look at the Location Providers article.

  1. Start the Map Wizard:

    • If you create a new report, select the Map Wizard icon from the Available Templates page.

      Item Template Map Wizard
    • If you want to add the Map item to an existing report, you have to select the Map item from the Insert menu. This will bring up the Map Wizard which will guide you through the creation process.

      Insert Menu Select Map
  2. Add new CSV Data Source and name it medalsData. This data source will hold the information about the countries, the ID of their respective olympic association, which place and how many times they have occupied it.

    1. Download the MedalsData.csv file , save it locally and insert its path into the Select a file to import textbox. The information in this file is an example data set that shows how many times a national olympic team had occupied a first, a second or a third place in the Olympic Games. It also has a relation with the Association of National Olympic Committees table that we will build later.

    2. Press Next until you get to the CSV Headers page. Note that the CSV file has headers, so you should check the The CSV has headers checkbox.

    3. On the Map columns to type page set the type of AssociationId, Place and Count columns to Integer.

    4. When you click Parse CSV... on the next page, you should see the result of the parsed file, which should look like this:

      CSV Preview Results

      Click Finish when you are ready.

  3. In the Available data sources list you should see the datasource you've already created. Select it and click Next.

  4. In the next page you have to select the fields which will be used to build the map charts.

    Note Note

    Since the map will use a Location Provider, there is no need to provide the Latitude and Longitude coordinates by yourself, so you can left these boxes empty and just define a location group, which will set the the geocoding string.

    • Select the Column Chart radiobutton from the Datapoints type box.

    • Select the Place field and drag it to the Series (color) box.

    • Select the Team field and drag it to Categories (location) box.

    • Select the Count field and drag it to Size box, where it will be transformed to Sum(Count).

    Your Arrange map fields page should look like the one shown below:

    Column Chart Arrange Map Fields

    Once the mandatory fields are set up, the Next button will get enabled and you can go to the next page.

  5. On the Choose a location provider page you have to select the location provider that will be used to geocode the State field that was dragged in the Categories (location) box on the previous page. Currently the supported providers are MapQuestOpenAPILocationProvider , MapQuestLocationProvider and BingLocationProvider. They both require a valid client token (key) to authenticate the geocoding requests that will be sent from the Map item. Once you have obtained the key, you should paste it in the Client token box, as shown below:

    ChooseALocation Provider
  6. When you click Finish, the Wizard will create the definition of the Map item, show it in the designer and will start to request the geocode and tiles information from the providers asynchronously. Initially it will take a few seconds to fetch all the data from the geocoding service, but the following requests should be executed faster. The tiles, needed to prepare the map background, will be displayed as they arrive, but the map will stay responsive and you can examine and change its definition. When finished, your map should look like this:

    Map Column Chart Done
  7. Now we will add a PointMapSeries that will display a circle around each column chart. The circle size will be relevant to the whole number of medals each team has won.

    The easiest way to add a series to the already created Map or Graph is to select it and then start the respective Wizard. In this case you should start the MapWizard again by clicking the Map toolbar icon and on the Arrange map fields page perform the following steps:

    • Select the Point radiobutton from the Datapoints type box.

    • Select the Team field and drag it to Categories (location) box.

    • Select the Count field and drag it to Size box, where it will be transformed to Sum(Count).

    Your Arrange map fields page should look like the one shown below:

    Point Arrange Map Fields

    When you click the Finish button, the map will display the two series together, rendered in the order they have been created. That's why you will see the column charts being overlapped by the circles.

  8. The map needs some more setup and styling in order to make it look good. Select the Map item, click on the Series item in the Property grid and perform the following steps in the opened MapSeries Collection Editor:

    • In the left pane (Members:) select the pointMapSeries1 item and move it up using the arrow buttons. This will cause the circles to be displayed as a background of the column charts.

    • Since the PointMapSeries will use the same grouping as the ColumnMapSeries, you should set its GeoLocationGroup to teamGroup and its SeriesGroup to seriesGroup, which were created along with the ColumnMapSeries.

    • As you have the pointMapSeries1 selected, set its DataPointStyle . BackgroundColor to White and the LineColor to a color of your choice. In the example report is used the DarkSlateBlue color from the Web palette.

    • Set the DataPointLabel expression to =Fields.Team to display the name of the national team above the point.

    • Set the DataPointLabelStyle. Visible property to True.

    • Select the columnMapSeries1 item from the Members: pane and set its MarkerMinSize to 6mm and MarkerMaxSize to 10mm.

    • In order to match the column series colors with the real world medal colors, you need to define a custom ColorPalette. The process is explained here and the colors used for this example are shown on the following screenshot:

      Medals Color Codes
    • If you wish, you can change the map size, adjust the fonts properties and try various styling, but finally your map should look like this:

      Map Point And Column Chart Done
  9. As you may have noticed, the information about the occupied places is present in the CSV file as integer numbers, that's why the legend displays it this way. But the report will look better if the information about the places is displayed with medal names - Gold, Silver and Bronze. This can be achieved using an expression for the LegendItem. Value, but we will use a Calculated Field , added to the CSV Data Source. The benefit of this approach is that we can use the calculated field as any other field, without having to use an expression every time we need to address it.

    Select the medalsData data source, add a calculated field named Medal of type String to it and set its expression to:

    =IIF(Fields.Place = 1, 'Gold', IIF(Fields.Place = 2, 'Silver', IIF(Fields.Place = 3, 'Bronze', 'none')))

    Now you can set the columnMapSeries' LegendItem.Value expression to =Fields.Medal and style the legend by your choice.

  10. In case we want to reduce the amount of displayed information on the map, one of the best options is to use Report Parameters and apply Filtering. In this case the filtering will be based on the AssociationId field.

    In order to display the meaningful names of the olympic associations, we will add another CSV Data Source, which has a relation with the current data source's AssociationId field and will be used only for displaying the report parameters.

    • Add a new CSV Data Source, select the tab Enter CSV as text and paste the following text in the box:

      Id,Name
      1,Pan-American Sports Organization
      2,European Olympic Committees
      3,Olympic Council of Asia
      4,Oceania National Olympic Committees
      5,Association of National Olympic Committees of Africa
    • Check the The CSV has headers box on the CSV Headers page.

    • On the Map columns to type page set the type of Id column to Integer.

    • When you click Parse CSV... on the next page, you should see the result of the parsed file. Click Finish when you are ready and name the dataSource associationsData when the Wizard closes.

    • Add new report parameter named associationParam and set its properties as shown at the following screenshot:

      association Param

      By default the parameter will be set to the association with Id = 1, which means that the report initially will show all the teams that are members of Pan-American Sports Organization. However, because it is a multivalue parameter, you can add all the Association IDs in its Value property.

    • Select the Map item and add a Filter Rule, that will filter out the teams with an AssociationId different than the ones, selected in the associationParam. Set the Expression to =Fields.AssociationId, the Operator to In and the Value to =Parameters.associationParam.Value.

      When you close the Filter Rules dialog, the designer gets updated and the map should show only the teams from the Pan-American Sports Organization. If you preview the report, you will be able to change the map extent only by selecting different combinations of parameter values.

  11. Since the column charts on the map are not suitable to display any labels on them, we will add an additional CrossTab, which will show the number of medals and calculate their amount for each national team.

    • Start the Crosstab Wizard and choose the medalsData data source on its Choose a Data Source page.

    • Select the Medal field and drag it to the Column Groups box.

    • Select the Team field and drag it to Row Groups box.

    • Select the Count field and drag it to Detail Values box, where it will be transformed to Sum(Count).

      Your Arrange fields page should look like the one shown below:

      Arrange Crosstab Fields
    • On the Choose Layout page select Blocked layout, subtotals below option and select Finish if you do not need to choose from any predefined stylings.

    • Once the Crosstab Wizard is closed, your crosstab is done, but now it needs to be filtered the same way as the Map item. Apply the same filter rule as you did in the previous step (=Fields.AssociationId In =Parameters.associationParam.Value).

    • If you want to apply a different color for any of the medals columns, you have to set up three ConditionalFormatting Rules to the textbox with the value =Fields.Medal , as shown below:

      Medals Conditional Formatting Rules Dialog
    • In order to have the information in your crosstab sorted properly, you can set =Sum(Fields.Count) Desc as a sorting for the row group and =Fields.Place Asc as a sorting for the column group.

    • You can change the table styling by your choice, but finally when you preview the report, the crosstab should look like the one below:

      Crosstab Done
  12. In this step we will add some interactivity to our report, allowing the user to select a national team from the crosstab and changing the map extent according to its selection. The drill-through action will use an invisible report parameter which will store the selected team name and utilize it in conditional formattings and filterings.

    • Add new report parameter named teamParam. Set its AllowNull property to True and leave the other properties as set by its default constructor.

    • If you haven't saved your report so far, save it now. Select the crosstab's textbox that displays the team name and add a Navigate to Report action, as explained here.

      Click on Select a Report Source button, select URL or File option and from the Select a file or enter a valid URL: drop-down list click the < Select a file > option.

    • From the Open file dialog select the report you're currently working on. This will cause the report to call itself every time this action is performed.

    • Click on Edit Parameters... button, which will bring up the Edit Parameters dialog. When you click on New toolbar button, the two report parameters - associationParam and teamParam - should be loaded in the Parameter Name drop-down list.

    • Select associationParam from the Parameter Name drop-down list and choose =Parameters.associationParam.Value for Parameter Value.

      Select teamParam from the Parameter Name drop-down list and add the following expression as Parameter Value: =ReportItem.Text

      This setup means that on every report call the value of the associationParam will be preserved and the teamParam value will be set from the textbox that initiated the action.

    • To make the currently selected crosstab row more distinctive, we can apply a conditional formatting to it. Select the whole row, clicking on its header. This will select its three cells. Add new conditional formatting rule and set its expression to =Fields.Team and its value to =Parameters.teamParam.Value. Set an appropriate color as a style background to denote the selected state of the row.

    • We can apply conditional formatting to the map series as well, outlining the currently selected national team so it would be easier to find it among the data points on the map. Select the PointMapSeries and set a new DataPointConditionalFormatting rule, as shown here:

      Point Map Data Point Conditional Formatting
  13. Finally we will add a small Graph showing a Column Chart that will be used to display the amount of medals won by each team.

    • Start the Graph Wizard by selecting Column, Clustered Column from the Insert toolbar of the Standalone Report Designer.

    • Select medalsData as a Graph's data source.

    • On the next page select the Medal field and drag it to the Series box.

      Select the Count field and drag it to Values box, where it will be transformed to Sum(Count).

      Click Finish when you are ready.

    • Since the graph's purpose is to show the currently selected team's medals, you have to apply filtering by Team to it: =Fields.Team = =Parameters.teamParam.Value

    • In order to make it look the same way as the map's column charts, you have to set the Sorting of the first member of the graph's series groups to =Fields.Place ASC and apply the same ColorPalette you have defined in step 8 to the Graph item.

      Additionally you can make the graph axes invisible, set the Y axis Minumum to 0 and change the styling properties of its data points and data point labels.

  14. When you are finished, you can preview your report and it should look similar to this one:

    Map Olympic Medals Done

In this example we demonstrated some of the newest features of Telerik Reporting along with a couple of advanced techniques that can be used to make your reports more dynamic and more interactive, and all that without writing a single line of code.

You can download this example report as a .trdx report definition from the following link: How to: Create а map with ColumnChart series using CSV data source.

See Also