User Queries

User queries are built and managed from the Corporate Settings page of the Projetex 2006 Server Administrator. Thus access to the queries and their functionality is for the present moment available only to the administrator. It is done first of all from the considerations of security. It will be improved in future, but even now you can view the data, stored in our database, from different sides. The general view of the user query module is shown below:

Here we can see the standard controls: add\edit\delete (fulfilling the corresponding functions) and four new ones. Load and Save will allow you to download new reports from the disc and save the existing reports to the disc (can enter/exit the system). This may initiate the creative exchange between users of Projetex 2006 from all over the world. Export Data and Print Data are the features, familiar to you owing to other parts of the program. The former will let you to export the result of the query into another format, and the latter – to print it. Note that you can use exterior instruments of analysis.

Building a query

Let us build the query to display ten most profitable clients.

1. Click the New button and the Query Builder window shown below appears.

2. Switch to the Model tab:

3. Find the table CINVOICES in the right panel and drag it into the field of the builder.

4. Select two fields, CLIENT_ID and CINV_TOTAL_B: client ID and sum of the invoice in basic currency, by clicking on the left from their names in the table. In the lower part of the screen we can see that the selected fields appear in the table.

5. Right-click on the cell where fields CLIENT_ID (the selected field) and GROUP (SQL section Group by) intersect and select the Group option. Similarly in the cell of intersecting CINV_TOTAL_B and Function fields we select the Sum option:

6. Click the Generate Query button in the toolbar and the built SQL-query will appear.

SELECT c4.client_id, SUM(c4.cinv_total_b)

FROM cinvoices c4

GROUP BY c4.client_id

Start executing the query (clicking Run Query button from the toolbar in the upper part of the window) and you will see the results. Above all is that you did not have to write anything by hands and the result is almost acquired. For reaching the final result we still need to extract the client name. For this purpose let’s add the following strings to the query.

SELECT FIRST 10 c4.client_id, SUM(c4.cinv_total_b),

(select client_name from clients where client_id = c4.client_id)

FROM cinvoices c4

GROUP BY c4.client_id

ORDER BY 2 DESC

In simple words it will mean the following: To select first ten clients (first 10 client_id) by counting the sum of invoices and display them in the order of sum reduction (ORDER BY 2 DESC).

Click Run Query button once again to see the new result.

Note: If you click Generate SQL after editing the query manually the query will be rebuilt and the results of your work will be lost.

7. To save the query model switch to the Query tab, specify the Query Name and Description (optionally) and click Save button.

See Also:

Query Builder

Example: creating a query within the report

Custom Reports