Repeat Elements Example: Dynamic Data Table Columns

Suppose you have a requirement to display a Data Table that may have a varying number of columns. You could use Auto Columns to display the table columns but you want more control over formatting and appearance. This is a perfect opportunity to use Repeat Elements, but assumes that your datasource can accept queries about the data schema. Here's how:

1. Start by adding a Data Table element and a datalayer to your definition. Then, as a child of the Data Table, add a Repeat Elements element, as shown above.
   
   
2. Next, add a datalayer beneath the Repeat Elements element. This datalayer is going to query the datasource, in this example a SQL server, for information about the table schema. You will need to know the specific syntax required to do this, which can vary widely from one datasource to another. This query is for Microsoft SQL Server 2012:

SELECT column_name,
CASE WHEN data_type= 'nchar' THEN 'Text'
WHEN data_type = 'int' THEN 'Number'
WHEN data_type = 'money' THEN 'Number'
WHEN data_type = 'datetime' THEN 'Date'
END As column_type
FROM information_schema.columns
WHERE table_name = 'Orders'
AND ordinal_position < 9 -- limits number of columns returned
ORDER BY ordinal_position

It's pretty straightforward, except for the CASE statement. We're going to add Sort elements to our Data Table columns later and, to do that, we need to know the data type for each column. The CASE statement translates the data type name in the database's system schema table to one of the values used in the Sort element's Data Type attribute. The actual data types available to you for use in the CASE statement will depend on your datasource.

3. Now we can add the Data Table Column element, as shown above. Its Column Header attribute makes use of the @Repeat token and the column name we retrieved in the query.
   
   
4. To display data in the column, we add a Label element. Its Caption uses something you may not have seen before: "nested" tokens. Basically, this is a token within a token; the inner token gets evaluated first, then the outer token. If this was a mathematical expression, it might look like: @Data.(@Repeat.column_name~)~ and the token in the parenthesis gets evaluated first.
   
   
5. And, finally, we add the Sort element and set its attributes, using @Repeat tokens, as shown above.
   
   

When the report is run, we get something like the table shown above. Obviously, additional formatting can be done as well. If the schema of the table changes, the report will handle it without any additional development.

Special Crosstab Table Considerations

If you want to create dynamic Crosstab Tables using Repeat Elements and the techniques shown above, there are some special considerations:

When working with Crosstab Tables, you may decide to use Extra Crosstab Value Column elements, summaries, or individual column IDs, all of which use special column names consisting of an "rd" reserved word, a dash, and a column ID, such as rdCrosstabValue-OrderTotal.

However, these special column names won't work in nested tokens. For example, this nested token won't work:

@Data.rdCrosstabValue-@Repeat.column_name~~

In order to use these special column names correctly in nested tokens, you need to concatenate the "rdCrosstab" reserved word and the dash with the column ID value in the column name data in the Repeat Element element's datalayer. You can do this in your SQL query, or with a Calculated Column element. The @Data.@Repeat.column_name~~ nested token will then work correctly.