ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

New WINDEV 2024 feature!
Help / WINDEV Tutorial / Tutorial - WINDEV application: Managing data
  • Lesson 6 - Chart and Pivot Table
  • Overview
  • Displaying data in a Chart control
  • Selecting the data that will be displayed in the Chart control
  • Creating the Chart control
  • Testing the Chart control
  • Creating summary tables with the Pivot Table control
  • Creating the Pivot Table control
  • Testing the Pivot Table control
  • To sum up

Tutorial - WINDEV application: Managing data

Lesson 6 - Chart and Pivot Table
We will cover the following topics:
  • Displaying data in a chart.
  • Creating summary tables via the Pivot Table control.
Durée de la leçon 30 min
Overview
The presentation of statistics or summary tables is often required in a management application. This type of presentation can be used to monitor:
  • the evolution of the orders over time,
  • the evolution of turnover,
  • the evolution of stocks,
  • ...
Executive managers usually need to know this information.
WINDEV offers different controls to easily include this information in your applications. Two specific controls will be used in this lesson:
  • the Chart control.
  • the Pivot Table control.
Displaying data in a Chart control
To handle the Chart control in a real use case scenario, we will create a Chart control to display the sales status in "WD Full Application".
First, we'll create a query to select the data, then we'll create a Chart control to display that data.

Selecting the data that will be displayed in the Chart control

In the chart, we will get the sum of orders by date.
We will create a query to perform a sum. We will calculate the total amount of orders (TotalIOT item in the Order data file).
We will calculate the total amount of all the orders per date (i.e., the Turnover per date).

To create a new query:
  1. Click Create an element in the quick access buttons.
  2. The new element window appears: click "Query".
  3. We are going to create a select query. Select the "Select" option. Go to the next step.
  4. The query description window appears.
  5. Add the Order.Date and Order.TotalIOT items to the query:
    • On the left, expand the "Order" data file.
    • Double-click the Date item then the TotalIOT item.
    • The two items appear in the middle of the screen (in the "List of elements in your query" area).
To calculate the sum of values of "Order.TotalIOT":
  1. Select the "Order.TotalIOT" item in the middle of the screen.
  2. In the "Actions", on the right, select "Sum". The sum description window appears.
    Query - Sum description window
  3. Validate the sum description. The sum of "TotalIOT" was added to the list of the query result.
You will notice that the WINDEV query editor creates the queries in common language (and also in SQL).
Query description in common language
This allows you to check the purpose of your query.
We are now going to group the data by year and to sort it:
  1. Select the "Order.Date" item in the middle of the screen.
  2. In the "Actions", on the right, click "Year, month" and select "Year". A window appears allowing you to create the Year item:
    Sort by year
  3. Validate this window ("OK").
  4. The "Year" item is displayed in the middle of the query description.
  5. Select the "Year" item and define the sort:
    • Open the context menu of the "Year" item.
    • Select "Sort item .. Sort in ascending order".
    • An arrow appears in the query description, indicating the sort type.
The query is created. We are going to give it a name and save it.
  1. At the top of the query description window, enter:
    • the name "QRY_SalesEvolutions".
    • the caption "Sum of orders by date".
  2. Validate the query description window.
  3. The save window is displayed. Validate the default information.
  4. The graphic query representation is as follows:
    Graphic representation of the query
  5. Click Test an element to test the query.

Creating the Chart control

We will create the Chart control in a new tab pane of WIN_Menu.
To create a new tab pane in "WIN_Menu":
  1. Open "WIN_Menu" in the window editor.
  2. Double-click the Tab control: the control description window appears.
  3. In the "General" tab, select the "Find orders" tab pane and click "New". A new tab pane appears.
  4. Select the new tab pane (named "Tab 4").
  5. On the right section of the screen, enter the name of the tab pane: "Chart".
  6. On the right section of the screen, select an image from the image catalog:
    • Click Show image options on the right of "Image". Select "Catalog" in the context menu that appears.
    • The image catalog window appears.
    • Type "Chart" in the search control.
    • Start the search by clicking the button with the magnifier.
    • Select Chart for example, and validate the different screens that appear.
  7. Validate the Tab control description window.
  8. A new tab pane appears in the window.
To create the Chart control:
  1. In the "WIN_Menu" window, select the "Chart" tab pane if necessary.
  2. On the "Creation" tab, in the "Graphic controls" group, click "Chart". The control appears under the mouse cursor.
  3. Click the "Chart" tab pane. The Chart control creation wizard starts.
  4. In the wizard, select a "Column" chart.
    Chart control creation wizard
    Proceed to the next step of the wizard.
  5. Specify the chart parameters:
    • the title: "Sales evolution".
    • the legend: "Without legend".
    • select "Show labels".
  6. Go to the next step.
  7. Specify the parameters of the axes:
    • X-axis title: Year
    • Y-axis title: TO
    Keep the default options and go to the next step.
  8. We are now going to define the data source.
    • For the labels (values displayed at the bottom of the chart, years in this case):
      • the source corresponds to: "Loop through a file or query",
      • the data corresponds to the Year item in the QRY_SalesEvolutions query.
        Chart control data source
    • For the series (values displayed in the Chart control): the Chart control will display a single series corresponding to the total IOT calculated by the QRY_SalesEvolutions query.
      • Click the "+" button.
      • Double click the "Caption" column on the first row of the "Series sources" table. The "Caption" column becomes editable.
      • Type the caption: "Series 1".
      • The source corresponds to: "Loop through a file or query",
      • The data corresponds to the "sum_TotalIOT" item in the QRY_SalesEvolutions query.
        Chart control data source
    Go to the next step.
  9. No background image will be associated with the Chart control. Go to the next step.
  10. Give a name to the Chart control: enter "CHART_SalesEvolutions" and finish the wizard.
  11. The Chart control is automatically created in the editor.

Testing the Chart control

Test the window (click Test window in the quick access buttons).
  1. Click the "Chart" tab pane to see the chart.
    Graph view in test mode

    Automatic features of the Chart control (AAF)
    Like the Table control, the Chart control offers multiple automatic features that can be accessed via its context menu. For more details, see Manipulating the Chart controls at runtime (AAF).
  2. End the test and go back to the editor.
Creating summary tables with the Pivot Table control
To handle the Pivot Table control in real conditions, we will create a Pivot Table control to see the sales of products by country and year in quantity and turnover.
Window containing a Pivot Table control
Like for the Chart control, we are going to create the Pivot Table control in a new tab pane of WIN_Menu.

To create a new tab pane in "WIN_Menu":
  1. Open "WIN_Menu" in the window editor.
  2. Double-click the Tab control: the control description window appears.
  3. In the "General" tab, select the "Chart" tab pane and click the "New" button. A new tab pane appears.
  4. Select the new tab pane (named "Tab 5").
  5. On the right section of the screen, enter the name of the tab pane: "Pivot table".
  6. On the right section of the screen, select an image from the image catalog:
    • Click Show image options on the right of "Image". Select "Catalog" in the context menu that appears.
    • The image catalog window appears.
    • In the search control, enter "Array".
    • Start the search by clicking the button with the magnifier.
    • Select Array for example, and validate the different screens that appear.
  7. Validate the Tab control description window.
  8. The new tab pane appears in the window.

Creating the Pivot Table control

To create a Pivot table control:
  1. In "WIN_Menu", select the "Pivot table" tab if necessary.
  2. On the "Creation" tab, in the "Data" group, expand "Table and List Box" and select "Pivot Table (PVT)". The control appears under the mouse cursor.
  3. Click the "Pivot table" tab pane. The Pivot Table control creation wizard starts.
  4. Go to the next step.
  5. Various information must be displayed in the cells:
    • the total sales amount.
    • the quantity sold.
    We are going to select the source data file in the wizard: OrderLine.
    For the first value select:
    • Display: "The sum of".
    • Source: "TotalIOT".
    Click the "Add an additional value" button.
    For the second value, select:
    • Display: "The sum of".
    • Source: "Quantity".
      Pivot Table control creation wizard
    Go to the next step.
  6. The years will be displayed in the column headings. On the left, expand the Order data file and double-click the Date item.
    Pivot Table control creation wizard
    Go to the next step.
  7. The wizard proposes to establish a link to the Order data file and to display three levels of data in the headings: years, quarters and months.
    Pivot Table control creation wizard
  8. Go to the next step.
  9. The products grouped by country will be displayed in the row headings. In the left section:
    • expand the Customer data file and double-click the Country item.
    • expand the Product data file and double-click Caption.
      Pivot Table control creation wizard
    Go to the next step.
  10. In the following steps, the wizard shows a link for each row heading (Customer.Country and Product.Caption). Go to the next step to validate all links.
  11. Give a name to the Pivot Table control: PVT_Sales.
  12. Finish the wizard.
  13. The Pivot Table control is automatically created in the window as well as a "Calculate" Button control. This Button control will allow the user to calculate the data to display in the Pivot Table control. This Button control can be positioned anywhere in the window.
  14. Save the window (click Save element in the quick access buttons).

Testing the Pivot Table control

Test the window (click Test window in the quick access buttons).
  1. Click the "Pivot table" tab, then the "Calculate" control.
    CAUTION: The calculation time depends on the size of the database and the number of row and column headings. The result of the pivot table can be saved to avoid recalculating values.
  2. Click ">" to expand the different columns and rows.
    Testing the window containing the Pivot Table control
Stop the test. We will make some changes to improve the Pivot Table control. So far, nothing indicates that one of the numbers in the cells corresponds to a quantity. We are going to use a specific display mask for this value.
To set a display mask in a cell:
  1. Open the description of the Pivot Table control (double-click the control).
  2. In the "Content" tab, click "VAL_NoName2". The description of the values displayed in the cells appears.
  3. In the "Mask" area, add the prefix "Qt: ".
    Pivot Table control description
  4. Validate the description window.
  5. Test the window again.
To sum up
Completed project
Do you want to check the end result of the steps described here?
A completed project is available. This project contains the different windows created in this lesson. To open the completed project, go to the home page and click "Tutorial", then in "Tutorial - WINDEV application: Manage data", double-click "Full application - Answers".
In this lesson, we saw how to show statistics using the Chart and Pivot Table controls.
In the next lesson, we will discover how to send an email from a WINDEV application.
Previous LessonTable of contentsNext Lesson
Minimum version required
  • Version 2024
Comments
Click [Add] to post a comment

Last update: 12/04/2023

Send a report | Local help