Creating a query within the report

It may be useful to take a look at Example: creating a report first, because we will refer to the Top 10 Clients (by Invoice) report, described in that topic.

Let us build a similar to 10 Top Clients (by Invoice) report, but for freelancers – Top 10 Freelancers (by POs). To make the task easier we use the previously built report. For this purpose we can use Save (if the report was not saved yet, see Example: creating a report) and Load buttons. So we load the report under the Top 10 Freelancers (by POs) name.

After this we select the newly appeared Top 10 Freelancers (by POs) report and click Edit to open the report designer window.

Then click File > New Dialog Form and add the query component to the form that appeared. See the following screenshot.

Now we have to enter the SQL-query text. For this purpose open the SQL-editor from the object inspector. Select the query item and find the SQL feature in the inspector. This will take you to the editor, where we can create the query, very similar to the previous one.

SELECT

first 10 p.res_id,

SUM(p.rinv_total_b)       "Total POs",

(select res_name from resources

where res_id = p.res_id)    "Freelancer"

FROM rinvoices p

GROUP BY p.res_id

ORDER BY 2 DESC

We would like to attract your attention to the fact that you can write the name, visible to the user, beyond the selected field - "Total POs", "Freelancer". Now we shall go to the designer and double-click the Master Data caption to open the data-binding Select DataSet dialog to assign our new query.

As a result our new fields will appear in the navigation tree, which should be dragged to the band.

Do not forget to delete the old fields from there ().

We drag the fields, adjust their dimensions, and the report is ready! Then we build the report using Preview to be able to see the results. You can add example reports, which will be immediately visible on the right side of the list of reports in Projetex 2006 Workstation.

You can specify variables in the report, and Projetex 2006 will automatically add them to the “Variables”, as well as the custom variables, specified in “Custom Variables”. If you enter Report > Variables in any report designer, you will see the Variables window, which contains the above-mentioned variables.

The report facilities are much wider than those presented in this small excursus. In particular, such facilities, as script performance and dialogs to make the report more interactive, as well as hyperlinks and report navigation trees have not been described here. That is why we strongly recommend you to read the Report User Guide.