PC SOFT

WINDEVWEBDEV AND WINDEV MOBILE
ONLINE HELP

Home | Sign in | English UK
New WINDEV 25 feature!
  • This lesson will teach you the following concepts
  • Overview
  • Displaying data in a Chart control
  • Selecting the data that will be displayed in the Chart control
  • Creating the Chart control
  • Creating summary tables with the Pivot Table control
  • Creating the Pivot Table control
  • Test of Pivot Table control
Lesson 4.8. Statistics: Chart and Pivot Table controls
This lesson will teach you the following concepts
  • Displaying data in a chart.
  • Creating summary tables via the Pivot Table control.
Lesson duration

Estimated time: 30 mn
Previous LessonTable of contentsNext Lesson
Overview
The presentation of statistics or summary tables is often required in a management application. This type of presentation can be used for example to follow:
  • the evolution of orders in time,
  • the evolution of turnover,
  • the evolution of stocks,
  • ...
Any executive manager wants to get this information.
WINDEV proposes several controls allowing you to easily include this information in your applications. Two specific controls will be used in this lesson:
  • the Chart control.
  • the Pivot Table control.

Answer

If you did not create the windows in the previous lessons, you can follow this lesson by opening a corrected project: in WINDEV's home page (Ctrl + <), click "Tutorial" and select "Full application (with windows)".
A full corrected application project is also available: in WINDEV's home page (Ctrl + <), click "Tutorial" and select "Full application (Answer)".
Displaying data in a Chart control
To handle the Chart control in real conditions, we are going to create a Chart control displaying the status of sales in the "WD Full Application" application.
First of all, we are going to create a query used to select the data that will be displayed in the Chart control.

Selecting the data that will be displayed in the Chart control

To create our chart, we want to get the sum of orders by date.
We are now going to create a query used to perform a sum. Indeed, we are going to calculate the total amount of orders (TotalIOT item in the Orders data file).
We are going to calculate the total amount of all orders per date (the Turnover per date).
  • Create a new query:
    1. Click Create an element among the quick access buttons.
    2. The element creation 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 items Orders.Date and Orders.TotalIOT to the query:
      • On the left, expand the "Orders" 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 "Orders.TotalIOT":
    1. Select the "Orders.TotalIOT" item in the middle.
    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 into the list of query result.

Remark

You will notice that the query editor of WINDEV creates the queries in everyday's language (and also in SQL language).
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 "Orders.Date" item in the middle.
    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 popup 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 to 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 proposed information.
    4. The graphic query representation is as follows:
      Graphic representation of the query
    5. Click Test an element to run the query test.

Creating the Chart control

We are going to include the Chart control in a new tab pane of WIN_Menu.
  • To create a new tab pane in "WIN_Menu":
    1. Display "WIN_Menu" in the window editor (if necessary).
    2. Double-click the Tab control: the control description window appears.
    3. In the "General" tab, select the "Finding orders" tab pane and click the "New" button. A new tab pane appears.
    4. Select the new tab pane (named "Pane 4").
    5. In the right section of the screen, type the caption of tab pane: "Chart".
    6. In the right section of the screen, select an image in the image catalog:
      • Click the Menu for images button on the right of "Image" control. Select "Catalog" from the popup menu that is displayed.
      • 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" pane, 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
      Go to the next step of the wizard.
    5. Type the chart parameters:
      • the title: "Sales evolution".
      • the legend: the chart has no legend.
      • the labels must be displayed.
    6. Go to the next step.
    7. Type 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 chart, the years in our example):
        • the source corresponds to: "Browsing a file or a 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.
        • Double click the "Caption" column on the first row of the "Sources of series" table. The "Caption" column becomes editable.
        • Type the caption: "Series 1".
        • The source corresponds to: "Browsing a file or a 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: "CHART_SalesEvolutions" and validate the wizard.
    11. The Chart control is automatically created in the editor.
    12. Click Run the window test to run the window test.
    13. Click the "Chart" tab pane to see the chart.
      Viewing the graph in test mode

      Remark

      Automatic features of Chart control (AAF)
      Like the Table control, the Chart control proposes several automatic features accessible via its popup menu.
      You can for example:
      • change the type of chart,
      • save the chart,
      • print the chart, ...
    14. End the test and go back to the editor.

Example

To master the use of a Chart control, don't forget to take a look at the examples supplied with WINDEV:
  • Unit example: The Chart control
  • Training example: WD Chart
These examples are available from the WINDEV home page (Ctrl + <).
Creating summary tables with the Pivot Table control
To handle the Pivot Table control in real conditions, we are going to create a Pivot Table control used to see the sales of products per country and per year in quantity and in 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. Display "WIN_Menu" in the window editor (if necessary).
    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 "Pane 5").
    5. In the right section of the screen, type the caption of tab pane: "Pivot Table".
    6. In the right section of the screen, select an image in the image catalog:
      • Click the Image menu button on the right of "Image" control. Select "Catalog" from the popup menu that is displayed.
      • 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 pane if necessary.
    2. On the "Creation" pane, 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 information, select:
      • Display: "The sum of".
      • Source: "TotalIOT".
      Click the "Add an additional value" button.
      For the second information, select:
      • Display: "The sum of".
      • Source: "Quantity".
        Pivot Table control creation wizard
      Go to the next step.
    6. The years must be displayed in the column headers. On the left, expand the Orders data file and double-click the Date item.
      Pivot Table control creation wizard
      Go to the next step.
    7. The wizard proposes a link to reach the Orders data file and it proposes to display three levels of information in header: the years, the quarters and the months.
      Pivot Table control creation wizard
    8. Go to the next step.
    9. The products grouped by country must be displayed in the row headers. 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 proposes a link for each row header (Customer.Country and Product.Caption). Validate each link and go to the next step.
    11. Give a name to the Pivot Table control: PVT_Sales.
    12. Validate 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 ( Save the element among the quick access buttons).

Test of Pivot Table control

  • Run the window test ( Run the window test among the quick access buttons).
    1. Click the "Pivot Table" tab pane, then the "Calculate" control.

      Remark

      CAUTION: The calculation time depends on the size of the database and on the number of row and column headers. The result of the pivot table can be saved to avoid re-calculating at each interrogation.
    2. Click the "+" signs to expand the different columns and rows.
      Testing the window containing the Pivot Table control
  • Stop the test. We will make a small improvement in this Pivot Table control. Indeed, 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 use 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 values displayed in the cells appears.
    3. In the "Mask" area, add the prefix "Qt: ".
      Describing the Pivot Table control
    4. Validate the description window.
    5. Run the window test again.
Previous LessonTable of contentsNext Lesson
Minimum version required
  • Version 25
Comments
Click [Add] to post a comment