Custom Reports in Projetex > Creating a Master-Detail Report

It is often needed to gather information from different database tables. For example, to obtain list of quotes and list of marketing records for each of clients. In this case, list of clients represents "master" data, and lists of quotes and events are called "detail" data (they show details on each of the client).

Objective: Create a report showing lists of quotes and marketing events per each of clients.

1. Run Projetex Server Administrator, turn the database offline by clicking Go Offline button and switch to Corporate Settings tab.

2. Switch to Custom Reports section on Corporate Settings tab of Projetex Server Administrator.

3. Click New button; enter report name and description in 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.

4. Click Data tab to begin adding data to your query,

data_tab_of_fastreport_window

5. Click FIB Query button and place an FIB Query object anywhere on the white field.

data_tab_fastreport_adding_FIBquery

6. 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 base currency (CQUO_TOTAL_B). Line WHERE CLIENT_ID = ?MAS_CLIENT_ID arranges the data by master client reference.

7. Repeat step 5 (click FIB Query button and place an FIB Query object anywhere on the white field) and add the following code to 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 Client window), and will also organize the data by master client reference field.

8. Repeat step 5 (click FIB Query button and place a FIB Query object anywhere on the white field) and add the following code to third query:

Example:

SELECT

CLIENT_NAME

FROM CLIENTS

This query will extract client names, which are also needed to build this report.

9. Click Page 1 tab to begin designing the report page.

page1_tab

10. Click Insert Band button and add a Master Data band. Select FIBQuery3 as dataset for this band.

select_dataset

11. Click Insert Band button and add a Detail Data band. Select FIBQuery2 as dataset for this band.

12. Click Insert Band button and add a Detail Data band. Select FIBQuery1 as dataset for this band.

13. Drag necessary fields to the appropriate data bands.

Example:

Dragged the following fields: CLIENT_NAME - to the MasterData1 band, CTRACK_DATE,CTRACK_INFO - to the DetailData1, CQUO_SENT,CQUO_NAME - to the DetailData2.

master-data_bands

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

master-data-bands-final

15. Now the report can be obtained:

Example:

Fragment of resulting report, showing clients (master data), lists of quotes and marketing records per each of them (detail data).

master-data_bands-result