It is often needed to gather information from different database tables. For example, to obtain a list of quotes and a list of marketing records for each client. In this case, the list of clients represents the "master" data, and the lists of quotes and events are called "detail" data (they show details for each client).
Objective: Create a report showing lists of quotes and marketing events for each of client.
1. Run the Projetex Server Administrator, turn the database offline by clicking the Go Offline button and switch to the Corporate Settings > Reports tab.
2. Click the New button; enter the report's name and description into New Custom Report window and click OK.
In this case none of the pre-designed queries suits this report, thus new queries must be created.
3. Click the Data tab to begin adding data to your query,
4. Click the FIB Query button and place a FIB Query object anywhere on the white field.
5. Double-click the new object, and enter the code text of the first query:
Example: SELECT CQUO_NAME, CQUO_SENT, CQUO_TOTAL_B FROM CMULTIQUOTES WHERE CLIENT_ID = ?MAS_CLIENT_ID |
This query will extract quote names (CQUO_NAME), dates (CQUO_SENT), and quote totals in the base currency (CQUO_TOTAL_B). The line WHERE CLIENT_ID = ?MAS_CLIENT_ID arranges the data by master client reference.
6. Repeat step 5 (click the FIB Query button and place a FIB Query object anywhere on the white field) and add the following code to the second query:
Example: SELECT CTRACK_DATE, CTRACK_INFO FROM CTRACK WHERE CLIENT_ID = ?MAS_CLIENT_ID |
This query will extract dates (CTRACK_DATE) and notes (CTRACK_INFO) of marketing events (shown on the Marketing tab of the Client window), and will also organize the data by the master client reference field.
7. Repeat step 5 (click the FIB Query button and place a FIB Query object anywhere on the white field) and add the following code to the third query:
Example: SELECT CLIENT_NAME FROM CLIENTS |
This query will extract client names, which are also needed to build this report.
8. Click the Page 1 tab to begin designing the report page.
9. Click the Insert Band button and add a Master Data band. Select FIBQuery3 as the dataset for this band.
10. Click the Insert Band button and add a Detail Data band. Select FIBQuery2 as the dataset for this band.
11. Click the Insert Band button and add a Detail Data band. Select FIBQuery1 as the dataset for this band.
12. Drag the necessary fields to the appropriate data bands.
Example: Dragged the following fields: CLIENT_NAME - to the MasterData1 band, CTRACK_DATE,CTRACK_INFO - to DetailData1, CQUO_SENT,CQUO_NAME - to DetailData2.
|
13. Add Header and Footer bands to separate various fields visually.
Example: The following bands were placed in this example: ReportTitle, 2 Header-type bands (Header1 and Header2), Footer band and PageFooter band. Also labels for header bands have been added.
|
14. Now the report can be obtained:
Example: A fragment of the resulting report, showing clients (master data) and the lists of quotes and marketing records for each (detail data).
|
See also:
