10 - Create Charts with Multiple Datasets

Last updated 16/12/2025

This documentation will explain how to create charts with multiple datasets. These datasets can be plotted on the same chart area or used in different chart areas that are displayed on the one page. As you would remember from the Quick Start Guide, you create a chart from a data source, for this example we will be using queries as our data sources.

We are going to add two chart areas on the one chart view, one will display our investment by sector and the other our investment by stock.

The quickest way to add a chart is with Five's Chart Wizard, however, you cannot add multiple datasets using the wizard. We will add the first chart using the Chart Wizard and then edit the record to add another dataset.

Add the InvestmentBySector Query

1. Click Data in the menu followed by Queries in the sub-menu.

Figure 1 - Queries menu item

2. Click the Add Item button and type InvestmentBySector in the Data Source ID field.

3. Click in the Query field to open Five's Query Builder.

Figure 2 - Add InvestmentBySector query

4. Click the SQL tab.

Figure 3 - SQL tab

5. Click the Copy button for the syntax below.

InvestmentBySector

SELECT
    Sector,
    StockCode,
    SUM(Bought - Sold) AS Holding,
    SUM(OriginalCost * (Bought - Sold) / Bought) AS Investment
FROM (
    SELECT
        Sector.Name AS Sector,
        Stock.StockCode AS StockCode,
           Buy.PortfolioKey,
        Buy.Quantity AS Bought,
        IFNULL((SELECT SUM(Allocation.Quantity) FROM Allocation WHERE Allocation.BuyKey = Buy.BuyKey),0) AS Sold,
        Buy.Total AS OriginalCost
    FROM
        Buy
        INNER JOIN Stock ON Buy.StockKey = Stock.StockKey
        INNER JOIN Sector ON Stock.SectorKey = Sector.SectorKey
 HAVING
        (Buy.PortfolioKey = ?) AND
        (Bought > Sold)
    ) AS Summary
GROUP BY
    Sector

6. Paste the syntax in the SQL Editor.

7. Click the Save button in the SQL Editor app bar.

Figure 4 - Save button

8. Click the Fields tab.

Figure 5 - Fields tab

9. Select the Holding record.

Figure 6 - Holding record

10. Edit the following fields:

• Select Integer in the Data Type field
• Type 4 in the Size field
• Select _Integer in the Default Display Type field

Figure 7 - Edit Holding field

11. Click the Save button in the form app bar.

Figure 8 - Save button

12. Select the Investment record.

Figure 9 - Investment record

13. Edit the following fields:

• Select Float in the Data Type field
• Type 8 in the Size field
• Select _Float.2 in the Default Display Type field

Figure 10 - Edit Investment field

14. Click the Save button in the form app bar.

Figure 11 - Save button

15. Click the Parameters tab.

Figure 12 - Parameters tab

16. Click the Add Parameters button.

Figure 13 - Add Parameters button

17. Type PortfolioKey in the Parameter ID field.

18. Type

{{five.stack.Portfolio.PortfolioKey}}

in the Parameter field.

Figure 14 - Add PortfolioKey parameter

19. Click the Save button in the form app bar.

Figure 15 - Save button

20. Click the Save button in the form app bar above the list.

Figure 16 - Save button

Add the InvestmentByStock Query

The InvestmentByStock query has the same query fields and parameter as the InvestmentBySector query. The only difference between the two queries is one line of code in the SQL syntax. We will use the Copy button, as it will copy the InvestmentBySector record including the SQL syntax, the query fields, and the parameter.

1. Click the Copy button.

Figure 17 - Copy button

2. Delete Sector in the Data Source ID field and type Stock.

note

The value in your Data Source ID field should be: InvestmentByStock.

3. Click in the Query field to open Five's Query Builder.

Figure 18 - Add InvestmentByStock query

4. Edit the

GROUP BY

statement by adding

, StockCode

.

Figure 19 - Edit GROUP BY statement

5. Click the Save button in the SQL Editor app bar.

Figure 20 - Save button

6. Click the Save button in the form app bar.

Figure 21 - Save button

info

All the field and the parameter records have been copied from the InvestmentBySector query. You can check this by clicking the Fields and Parameters tabs.

Add the Investment Analysis Chart

Five's Chart Wizard will be used to add the Investment Analysis chart with its first chart area. This is only because it is quicker and a preview is available, you can make the chart and its chart areas all in the Charts view if you wanted to.

1. Click Visual in the menu followed by Chart Wizard in the sub-menu.

Figure 22 - Chart Wizard menu item

2. Type Investment Analysis in the Chart Title field and select InvestmentBySector in the Data Source field.

3. Select Sector in the X Value Column field and Investment in the Y Value Column field.

4. Type InvestmentBySector in the Chart Area ID field and Investment By Sector in the Chart Area Title field.

5. Click the Show Chart Area Title switch.

6. Select Pie in the Chart Type field.

Figure 23 - Add Investment Analysis chart

7. Type Investment in the Legend Label field.

8. Click in the Color List Picker field.

Figure 24 - Color List Picker field

9. Select a color block.

Figure 25 - Color picker

info

If you want multiple colors for your pie chart, you will need to continue to select more colors.

10. Click the Add New Color button and select a color block.

Figure 26 - Add New Color button

11. Repeat step 11 to add one more color and click the Save button.

Figure 27 - Save button

12. Click the Save button in the Chart Wizard app bar.

Figure 28 - Save button

Edit the Investment Analysis Chart

We will need to edit the Investment Analysis chart record so that we can add the Investment By Stock chart area.

1. Click Visual in the menu followed by Charts in the sub-menu.

Figure 29 - Charts menu item

2. Select the Investment Analysis record in the list and click the Edit button in the form app bar.

Figure 30 - Edit button

3. Click the Cancel button in the Columns field and type 2.

4. Type

true

in the Show Total If field.

Figure 31 - Edit Investment Analysis chart

Add the Investment By Stock Area

The Areas page enables you to add multiple charts to the one chart view.

5. Click the Areas tab.

Figure 32 - Areas tab

6. Click the Add Areas button.

Figure 33 - Add Areas button

7. Type Investment By Stock in the Title field and select Pie in the Chart Type field.

8. Click the Edit button in the Page Position field.

Figure 34 - Add Investment by Stock chart area

9. Select B1 and click the Save button.

Figure 35 - Grid picker

10. Click in the Color List field.

Figure 36 - Color List field

11. Select a color block.

12. Click the Add New Color button and select another color block.

Figure 37 - Color picker

13. Repeat step 14 to add three more colors.

note

Five colors in total should be selected.

14. Click the Save button on completion.

Figure 38 - Save button

15. Click the Save button in the form app bar.

Figure 39 - Save button

Add the InvestmentByStock Data Source

The Datasets page enables you to pick a data source to supply the data to your chart.

16. Click the Datasets tab.

Figure 40 - Datasets tab

17. Click the Add Datasets button.

Figure 41 - Add Datasets button

18. Select InvestmentByStock (Query) in the Data Source field.

Figure 42 - Data Source field

Add the Investment By Stock Chart Area Dataset

The Chart Area Datasets page enables you to select the fields required to return the results.

19. Click the Chart Area Datasets tab.

Figure 43 - Chart Area Datasets tab

20. Click the Add Chart Area Datasets button.

Figure 44 - Add Chart Area Datasets button

21. Select Investment By Stock in the Chart Area field.

22. Type Investment in the Legend Label field.

23. Select StockCode in the X Value Column field and Investment in the Y Value Column field.

Figure 45 - Add Investment By Stock chart area dataset

24. Click the Save button in the form app bar.

Figure 46 - Save button

25. Click both Save buttons in the stacked form app bars above the list.

Figure 47 - Save buttons

Add the Investment Analysis Menu

1. Click Visual in the menu followed by Menus in the sub-menu.

Figure 48 - Menus menu item

2. Click the Add Item button and type Investment Analysis in the Caption field.

3. Type 1 in the Menu Order field, select InvestmentAnalysis (Chart) in the Action field, and select Portfolios (Portfolios) in the Parent Menu field.

4. Optional: Click the Edit button in the Icon field and open an image file.

Figure 49 - Add Investment Analysis menu

5. Click the Save button in the form app bar.

Figure 50 - Save button

tip

This is a good time to deploy/run the Portfolio application to see the Investment Analysis charts!

Test the Investment Analysis Charts

1. Select the Growth Portfolio record in the list and click the Down button in the form app bar.

Figure 51 - Down button

Figure 52 - Investment Analysis charts

2. Close the browser tab and return to Five.