Repeat Elements Example: Dynamic Tables with Nested Repeat Elements

Let's expand on the previous example - this time we don't even have to know the table names. We can use nested Repeat Elements elements to gather all the information to we need to display the data for whatever tables are available. Let's also do a little more sophisticated formatting and aligning this time, and some summarizing.

1. This time we'll start by adding a Repeat Elements element to our definition, with a child datalayer, as shown above. Once again, the syntax for querying the schema will vary by datasource and this example is for Microsoft SQL Server 2012. The SQL query, which limits us to three tables for this example, is:
   
   

SELECT table_name FROM information_schema.tables
WHERE table_name IN ('Customers', 'Employees', 'Orders')
ORDER BY table_name

Think of this as the "outer loop" of the code, which allows us to iterate through each of the tables.

2. Now we'll add a Data Table element, and use @Repeat tokens from the datalayer to give each table its required unique ID and a caption, as shown above.

3. Add a datalayer beneath the Data Table, as shown above, and set its attributes as shown above, right. This query will retrieve that data for each table.

4. We want to summarize freight costs for the Orders table, so add an Aggregate Column element beneath the datalayer and set its attributes as shown above.

5. Now things begin to get interesting: add anotherRepeat Elements element beneath the Data Table, as shown above, with its own datalayer. This is the "inner loop" of the code, which iterates the table columns. The SQL query for this datalayer is:
   

SELECT column_name, data_type,
CASE WHEN data_type = 'nchar' THEN ''
WHEN data_type = 'int' THEN '######'
WHEN data_type = 'datetime' THEN 'Short Date'
WHEN data_type = 'money' THEN 'Currency'
END As column_format,
CASE WHEN data_type = 'nvarchar' THEN 'ThemeAlignLeft'
WHEN data_type = 'nchar' THEN 'ThemeAlignCenter'
WHEN data_type = 'int' THEN 'ThemeAlignRight'
WHEN data_type = 'datetime' THEN 'ThemeAlignCenter'
WHEN data_type = 'money' THEN 'ThemeAlignRight'
END As column_align
FROM information_schema.columns
WHERE table_name = '@Repeat.repeatTableNames.table_name~'
AND ordinal_position < 9 -- limits number of columns returned
ORDER BY ordinal_position

As shown above, the query uses CASE statements to set formatting and alignment values based on the data type the column and, in the WHERE clause, uses an @Repeat token from the first Repeat Elements element (the "outer loop") to limit the result set to a specific table.

You can identify columns from the first Repeat Elements element more specifically using a token that includes its element ID: @Repeat.repeatTableNames.table_name~.



When using nested Repeat Elements elements in any type of table, you cannot have more than one table hierarchy (Rows, Row, Column Cells) level between them. As shown above, the arrangement of rows and columns in the left and middle examples is correct. The right example is invalid and the second Repeat Elements element will not be processed.

6. At last we can add a Data Table Column element under the second Repeat Elements element, as shown above, and set its attributes using @Repeat tokens.
   

7. And then we can add a Label element to display the data, using nested tokens as before, in its attributes as shown. We'll also use an @Repeat token to set the format.

8. Finally, we can finish up by adding Summary Row (and its child elements) and Interactive Paging child elements beneath the Data Table and a New Line element beneath the first Repeat Elements element, as shown above. To ensure that the summary row only appears for the Orders table, set the Condition attribute of its three Column child elements to "@Repeat.table_name~" = "Orders".

When the report is run, three tables should be displayed, as shown above.