Repeat Element Example: Dynamic Analysis Grid Columns

Repeat Elements can also be used with super-elements, such as the Analysis Grid. This allows you to present dynamic Analysis Grids and let the user select the datasource at run time.

1. We'll start by adding an Analysis Grid element, and its child datalayer, to the definition, as shown above. The datalayer query will retrieve all of the data from the table: SELECT * FROM Orders

2. Next, we add Repeat Elements element as a child of the Analysis Grid, with its own child datalayer, as shown above. This datalayer will retrieve the column information from the datasource. Once again, the syntax for querying the schema will vary by datasource and this example is for Microsoft SQL Server 2012. The SQL query is:
   

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 need to specify the column data types for the Analysis Grid columns and, to do that, we need to know the data type for each column. The CASE statement translates the data type name in the system schema table to one of the values used in the Analysis Grid Column element's Data Type attribute. Once again, the actual data types available to you for use in the CASE statement will depend on your datasource.

3. Finally, we add an Analysis Grid Column element, as shown above, and set is attributes using @Repeat tokens.

When the report is run, an Analysis Grid will be displayed that dynamically includes the available data columns. Data can be formatted and aligned using techniques from the previous example.