A report is usually called Master-Detail if it is used to display data from a hierarchical data source. Premier Report Engine uses DevExpress Reporting at its base which provides two approaches for creating a master-detail report. The first approach is based upon using the Detail Report band and the second approach is to create two different report classes and incorporate the detail report into the master report as a subreport. (Premier Report Engine does not support the second approach)

To better understand the process of creating master-detail reports using the Detail Report band, we are going to create a simple ‘Clerk Time in Attendance’ report for Premier Retail.

01. Open Premier Report Engine – formerly known as Encumpus Report Engine.

02. Create a connection to your Premier Retail/Interprise database and save. Name it ‘ISSIDEMOUS – For DEMO’.

03. The connection should now appear in the Connections list. Click on it and the Connection tab will be enabled in the application ribbon.

04. Click on the Query button to open the Query Designer and enter the query below:

Note: @ClerkCodes denotes a Parameter value. When the user designs/prints the report, they are prompted for the value to use.

05. Click the Save & Close button. Enter ‘ClerksQuery’ as the name of the query when prompted.

06. Click on the Query button again to open the Query Designer a second time and save the following query as ‘TimeInAttendanceQuery’;

Note: When both these queries are assigned to a report, on creation of datasource Premier Report Engine identifies @ClerkCodes as a common parameter and will prompt for the value only once. This allows end-users to easily filter multiple queries using common/related fields.

07. Now that we have both the required queries, we need to create a relationship between the two. To do that click on the ‘New Query Relation’ button in the Ribbon menu located in the General tab.

08. We will select ClerksQuery as the Primary query and TimeInAttendanceQuery as the Foreign Query. Click on Load Fields to identify all fields returned via the Query. Select ClerkCode from both tables and hit Save.

Note: A query can have any number of relationships defined with other queries.

We now have two queries created with a relationship defined against them. This means that whenever we add both these queries to a report, the resulting data tables will include a relationship defined on the selected columns. This is essential in creating our Master-Detail report.

Now we design our report.

09. Click on the ‘New Report’ button located in the General tab on the Ribbon. Enter ‘TimeInAttendanceDEMO’ as the name of the report and select the category to add the new report to.

10. Hi Save to create a blank report and add it to Premier Report Engine. Now we define the datasource of the report.

11. Select the Report that was just added from the list on the right. This will open the Report Tab on the Ribbon.

12. Click on the ‘Add Query’ button – this button allows us to select an existing query and add it as a data-source for the report.

Note: Each Query added to the report denotes a table added to its data-source. When a report is designed/printed, the queries associated with the report are executed and resulting records are stored in tables merged into a single dataset. This dataset is then assigned as the data source.

13. Select ‘ClerksQuery’ from the listed queries and click on OK. Repeat for the ‘TimeInAttendanceQuery’ query.

14. Click on the Design button to start up the report designer.

Note: Before the designer is displayed, Premier Report Engine will build the datasource for the project. This process can take a few seconds.

15. Once the report is opened in design mode, observe the Field List window.

Both the ClerksQuery and TimeInAttendanceQuery queries resulted in two tables, with the same name as the queries. Furthermore, a link between both tables is created in the ClerksQuery table based on the relationship that we defined earlier – defined as ClerksQuery_TimeInAttendance.

16. First we need to specify the Data Member property of the report. The Data Member property defines the table that the report is being built against. Set it to ‘ClerksQuery’.

17. Next, let us start off by creating the main report. For this report, we will simply display the list of clerks – their name and clerk code assigned.

18. The report should contain 3 bands by default, the Page Header, the Detail band, and the Page Footer. Add two controls to the Page Header; a label control and a Page Info control. The Page Info label can used to print page #, current date, and a few other details. Set the title of the report to ‘Clerk –Time In Attendance’. Set the Page Info label to show the Current Date & Time – this will print the date and time at which the report is printed.

19. Next we add two label controls in the Detail Band for Clerk Code and Clerk Name.

20. From the Field list drag the ClerkCode and ClerkName columns onto the report. This will create two new labels and bind them to the respective columns.

21. You should now have something like the report shown below:

22. Click on the Preview button at the bottom of the designer to switch to design mode and review the report.

23. Switch back to design mode.

Now we add a detail report band to the report and create our sub-report. Although you can add a detail-report and assign to its datasource any table from the main datasource, since we created a relationship between our two queries/tables, things are made much simpler. We can simply select the defined relationship as the datasource of the detail report. This will auto-filter records for us, so that when it prints the detail report, it will only list records for the clerk that was printed in the master report.

24. To add the Detail Report, right click on the report and select ‘Insert Detail Report’ > “ClerksQuery_TimeInAttendanceQuery”

25. You will now have a Detail Report band under your Detail band as shown below;

26. First we add a Report Header to the Detail Report - since a detail report is a complete report incorporated within the main report, it can contain anything including all the report bands.

27. Add header labels for Clocked In/Out and Workstation clocked in/out at to the Report Header.

28. Next we add fields from our table to the Detail tab. Important: When creating a detail report that uses a relationship generated table, it is important to bind to the field from that relationship table instead of the original table. For example, in this case we will drag & drop fields from the “clerksQuery_TimeInAttendanceQuery” table located inside of ClerksQuery instead of using fields from the TimeInAttendanceQuery.

29. Drag & Drop the Clocked In, Clocked Out, Workstation Clocked Out At, and Workstation Clocked In At fields onto the Detail Report’s Detail band. You should end up with something similar to this;

30.  Preview the Report and Save.

For more information on creating Master-Detail reports and to understand the power of scripting, please refer to the other Premier Report Engine Knowledge Base Articles.