CHMlib Logo Leading Translation Management System for Translation Agencies, Top Translation Management Software for Freelance Translators, Time Tracking Software, Word Count, Character Count and Line Count Software, Computer Assisted Translation Tool for Easy Word Count, Clipboard Character Count Software, User-Friendly Translation Memory Software, Terminology Management Software, Terminology Management Server, Microsoft Glossary Software, Dictionary of Acronyms, Social Network for Translators and Translation Agencies, Acronyms and Abbreviations Search Engine, Russian Translation Lab, Ukrainian Translation Lab.
You are reading help file online using chmlib.com
CrisisHelper - First Aid in Tough Times of World Economic Crisis


_bm0 User Queries

Top  Previous  Next

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:

picture1

 

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.

 

_bm2

 

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.

query_builder

 

2. Switch to the Model tab:

model

 

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

builderk

 

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.

buildern

 

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:

picture4

 

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 lets 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.

_bm3 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.

 

_bm2

 

See Also:

Query Builder

Example: creating a query within the report

Custom Reports



You are reading help file online using chmlib.com

If you want your help file to be removed or added please send e-mail to chmlibcom@gmail.com
Partner sites: Logo Design, Simple Anti-Crisis Accounting Software, Voice Search for Web