Query text

Let us create the query text.

  1. Click Query Builder (fig. 7.6).

    Fig. 7.6. Opening the Query builder

    The Query builder is intended for generating queries using visual tools. Even users who are unfamiliar with the query language can use the wizard to generate valid queries.

    The Database list displays the tables used to generate a query. The report is generated based on the data stored in these tables.

    If you expand the AccumulationRegisters branch, you will see that, in addition to the BalanceOfMaterials register table, this branch contains several virtual tables that are generated automatically (fig. 7.7).

    Fig. 7.7. Tables that you can use in a query

    The register virtual tables are generated automatically, they are mainly used in report generation. Since we want to see both the balance of materials and information about material receipts and expenses, let us opt for the BalanceOfMaterials.BalanceAndTurnovers virtual table.
  2. Drag the BalanceOfMaterials.BalanceAndTurnovers table to the Tables list and expand its structure (fig. 7.8).

    Fig. 7.8. The BalanceOfMaterials.BalanceAndTurnovers table

    As you can see, this table contains the dimensions of the BalanceOfMaterials register (Material and Warehouse), the opening and closing balance, as well as receipt, expense, and turnover values for all the resources of the BalanceOfMaterials register.

    Let us select the table fields in the required order.
  3. Double-click the following items in the specified order:
    • Warehouse
    • Material
    • QuantityOpeningBalance
    • QuantityReceipt
    • QuantityExpense
    • QuantityClosingBalance
    Note. Instead of double-clicking, you can move the selected items between the lists by dragging them of by clicking the , , , and  buttons.

    At this point the Fields list looks as follows (fig. 7.9).

    Fig. 7.9. Selected fields
  4. Click OK to return to the data composition schema wizard (fig. 7.10).

    Fig. 7.10. Data composition schema wizard

The platform populates the Query field with the query text generated by the Query builder. This field is basically a text editor where you can edit the resulting query. Alternatively, you can reopen the Query builder and use it to edit the query.

To simplify things, we will not discuss the query text here. This aspect will be covered in Lesson 13. Reports (in the part dedicated to the query language). In this scenario (just like with many other reports) you can avoid analyzing and editing the query text.

Note the automatically populated list of data composition system fields in the upper part of the wizard window. It lists the fields available in the current data set. In this case the platform populated the list automatically based on the query text, so you do not need to adjust it manually.

So we have discussed how to extract data that is used in a report. But to have this data displayed in a report, you have to create the default report settings. Let us create simple report settings suitable for viewing typical detailed infobase records. These include the records of the BalanceOfMaterials accumulation register virtual table selected in the order that you used to add them into the table.

Next page: Report settings

Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.