In this lesson, you start with the report from the previous
lesson. You a second table to the report. You nest one table inside
the other table and control the information in the inner table based
on the information in the outer table.
The application development team wants a report that features
table information by schema. You decide to create a small report that
features only the tables from the GOSALES schema to test your report
design before you present it to the application development team.
You
can combine information from two data sets if the data sets have columns
in common. In this lesson, you use information from the Column and
Table data sets. The column data set has the column Source Table URI,
which is the same data as the URI column in the Table data set.
To create a report with information from two data sets:
- Open the Report Editor view.
- If the report design is closed, open it.
- Click the Layout tab at the bottom
of the Report Editor to open the view.
- Delete the GroupByTable group from
the Columns table:
- Open the Outline view.
- Expand the node.
- Right-click the Table Group - GroupByTable element
and select Delete.
The group is removed from the table and the Report Editor
view is updated. The Columns table contains one group element.
- Add a new table to the report:
- From the Palette view, drag a table
into the Report Editor below the existing table. The Insert
Table window opens.
- In the Number of columns field,
type 2.
- Click OK.
A new table is added to the report in the Report Editor.
- Change the properties for the table. Use the Property
Editor to set properties for the table:
- In the Outline view, select . You must select the second table in the view. The
table properties display in the General tab
of the Property Editor.
- In the Name field, type TableInfo.
- Change the text format to blue and left-aligned.
- Bind the table to a data set. Open the Binding tab
of the Property Editor, and select the Data
set option. Then, select the Table option. The settings in the Binding tab
should look like the following image:
- Create sorting criteria for the TableInfo table to sort
by schema, then by table name. Select the Sorting tab,
then create the following two sort criteria:
- Add a sort key to sort by Source Schema in ascending order.
- Add a sort key to sort by Name in ascending order.
The settings in the Sorting tab
should look like the following image:
- Add the source schema data element and table name data
element to the TableInfo table. In the Data
Explorer, under the Data Set folder,
drag the following two data elements from the Table data
set into the report:
- Drag the Name data element into the first
column of the detail row of the TableInfo table.
- Drag the Source Schema data element into
the second column of the detail row of the TableInfo table.
The two data elements in the second table
are in blue text, as shown in the following image:
- Move the Columns table below the TableInfo table. In
the Report Editor, hover over the Columns table to display the table
handle at the bottom of the table. With the table handle, drag the
Columns table below the TableInfo table.
- Preview the report. Select the Preview tab
at the bottom of the Report Editor. The Report
Editor generates a preview of the report. The report displays the
TableInfo table in blue text and the Columns table in black text.
- In the TableInfo table, create detail row below the table
detail row:
- Open the Layout tab of the Report
Editor.
- Display the table guide cells.
- Right-click the detail row guide cell and select.
- Merge the cells of the new row:
- Select the left cell.
- Press the Shift key and click the right cell. Both
cells are selected.
- Right-click the left cell and click Merge
Cells
The two empty cells are merged into a single detail row
cell, as shown in the following image:
- Insert the Columns table into the empty detail cell of
the TableInfo table. With the Columns table handle, drag
the Columns table into the detail row that you created in the TableInfo
table.
- Set the format of the Columns table to black text:
- Select the Columns table in the outline view. In
the Outline view, click . The Columns table is in the second detail row of the
TableInfo table.
- In the Property Editor, open the General tab
and change the font color to Black.
The outer table text is blue and the inner table text
is black, as shown in the following image:
- Set a filter condition on the Columns table. The
filter controls the column information that is in the nested Columns
table based on the source table name in the outer TableInfo table.
- In the Property Editor, select
the Filters tab and click Add. The New Filter Condition window opens.
- Complete the New Filter Condition window
as shown in the following table:
Field |
Setting |
1: Data field to filter |
Source Table URI |
2: Logical function |
Equal to |
3: Value by which to filter |
<Build Expression> |
The New Filter Condition window should
look like the following image: The Expression Builder opens.
- Complete the fields of the Expression Builder window
as shown in the following table:
Field |
Setting |
Category |
Available Column Bindings |
Sub-Category |
Table - TableInfo |
Double-click to insert |
URI |
Double-click the URI option to add
the row._outer["URI"] expression to the Expression
Builder edit pane. The Expression Builder window
should look like the following image:
Click OK. You created the following filter for the inner table based
on the value of the outer table row:The TableInfo table is the
outer table, and the Columns table is the inner table. For each row
in the TableInfo table, the Columns table lists only the column information
where the Table Source URI matches the URI of the Table data set.
- Preview the report. Select the Preview tab
at the bottom of the Report Editor. You see a report
with table and schema names in blue. For each table, you see the columns
for the table sorted by type of column.
When you nest one table in another by adding a table to the
detail row of another table, the table that is nested in the detail
row is generated for each data row of the outer table. The Column
table is generated each time a row in the TableInfo table is generated.
When
you create the filter in the Columns table, you limit the data that
is listed in the nested table. Only the column data that is associated
with the table name for the row in the outer table is listed.
If
you did not add the filter to the inner table, the column information
for all the tables is listed for each table name that is listed in
the outer table.
You have further refined your report to include a nested
table. Next you will improve the appearance of the report by adding
dynamic text and formatting. The dynamic text allows the report to
display the words "Data type" in front of each data type. This formatting
helps application developers to better understand the report.