< Previous | Next >

Creating a report with information from two data sets

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:

  1. Open the Report Editor view.
    1. If the report design is closed, open it.
    2. Click the Layout tab at the bottom of the Report Editor to open the view.
  2. Delete the GroupByTable group from the Columns table:
    1. Open the Outline view.
    2. Expand the Body > Table - Columns > Groups node.
    3. 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.

    Report with single group element

  3. Add a new table to the report:
    1. From the Palette view, drag a table into the Report Editor below the existing table. The Insert Table window opens.
    2. In the Number of columns field, type 2.
    3. Click OK.
    A new table is added to the report in the Report Editor.
  4. Change the properties for the table. Use the Property Editor to set properties for the table:
    1. In the Outline view, select Body > Table. You must select the second table in the view. The table properties display in the General tab of the Property Editor.
    2. In the Name field, type TableInfo.
    3. Change the text format to blue and left-aligned.
    4. 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:
      Property Editor binding
    5. 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:
      Property Editor sort criteria
  5. 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:
    Report with two table elements
  6. 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.
  7. 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.
    Preview of report with two tables
  8. In the TableInfo table, create detail row below the table detail row:
    1. Open the Layout tab of the Report Editor.
    2. Display the table guide cells.
    3. Right-click the detail row guide cell and select Insert > Row > Below.
  9. Merge the cells of the new row:
    1. Select the left cell.
    2. Press the Shift key and click the right cell. Both cells are selected.
    3. 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:
    Table with merged cells in detail row.
  10. 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.
  11. Set the format of the Columns table to black text:
    1. Select the Columns table in the outline view. In the Outline view, click Body > Table - TableInfo > Detail > Row > Table - Columns. The Columns table is in the second detail row of the TableInfo table.
      Selected columns table in the Outline view.
    2. 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:
    Selected columns table in the Outline view.
  12. 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.
    1. In the Property Editor, select the Filters tab and click Add. The New Filter Condition window opens.
    2. 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:
      Selected columns table in the Outline view.
      The Expression Builder opens.
    3. 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:

      Selected columns table in the Outline view.
      Click OK. You created the following filter for the inner table based on the value of the outer table row:
      Filter in the Property Editor

      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.

  13. 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.
    Preview of the report grouping columns by table.
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.
< Previous | Next >

Feedback