Skip to main content

Open Templates

Templates for various regulatory authorities can be open or closed. Open templates can be open on the Y axis (down) or the Z axis (deep).

If you load all data into a template, you might create a template with an unmanageable amount of data. Alternatively, you can bind a query to an open template to specify only the data that you require, and then generate the data in an instance. When you bind a query to a template and then generate the instance, you might produce one or more forms.

To generate your instance from an open template, you must complete three main steps:

  1. Use the query building feature to build the query. See Build a Query for an Open Axis Template.
  2. Bind the query to the template. See Bind a Query to an Open Axis Template.
  3. Generate the instance.

Tip: If you would find it useful to see all data in an Excel workbook before you generate your instance, you can do so.

Open X Axis Templates

To prepare your data for filing, you can use CDM to add data to an open X axis template. Typical data for open X axis template is currencies.

A template with an open X axis contains a single blank column. If you filled the template with data, you would fill this column and many other columns beside it. For example, you might have data for many currencies. If you inserted all data into your template with an open X axis, the template might contain hundreds of columns, which would be very difficult to manage.

Instead, you can use a query to define the location of the data. Then CDM retrieves the data when you generate your instance.

Open Y Axis Templates

To prepare your data for filing, you can use CDM to add data to an open Y axis template. Typical data for an open Y axis template is investment data.

A template with an open Y axis contains a single blank row at the top. If you filled the template with data, you would fill this row and many other rows below it. For example, you might have data for thousands of different investments. If you inserted all data into your template with an open Y axis, the template might contain thousands of rows, which would be difficult to manage.

Instead, you can use a query to define the location of the data. Then, CDM retrieves the data when you generate your instance.

Open Z Axis Templates

To prepare your data for filing, you can use CDM to add data to an open Z axis template. Typical data for an open Z axis template is countries or currencies.

Open Z axis templates are sometimes called breakdown forms. You might want to use a breakdown form for multiple countries. When you do so, instead of selecting the country manually from a predefined list, the query picks up the information automatically.

A template with an open Z axis typically contains a range of cells that can be filled with data. For example, you might have the same set of data for each of the countries in which your company does business.

  • If the dimension members that define the Z axis are selected from a fixed list, such as a list of all the countries in the world, you can access the complete list during the process of defining your filing report.
  • If the dimension members that define the Z axis are selected from a non-fixed list, the list must be defined with a typed dimension.

If you inserted all data into your template with an open Z axis, the template might contain hundreds of pages, which would be difficult to manage. Instead, you can use a query to define the location of the data. Then, CDM retrieves the data when you generate your instance.

Build a Query for an Open Axis Template

To prepare data for an open axis template, you must use the query building feature in CDM to build the query. You can use query variables, just as you would for any other query. CDM supports all relational data sources for binding queries to a template.

If you add data directly into a template that uses a query, that data is not used in the validation and generation of the template or the filing report.

Important: In the following procedure, column names are case sensitive.

To build a query:

  1. Create a column with the name ORD_X in which the X ordinates will be listed.
  2. Create a column with the name ORD_Y in which the Y ordinates will be listed. If you are working with an open Y template, all Y ordinates will state 999.
  3. Create a column with the name ORD_Z in which the Z ordinates will be listed.
  4. Create key columns with the name Dimension_<DIMENSION CODE> in which the template keys will be listed. A column is needed for each set of keys.
  5. Create a column with the name Value in which the value of the data points will be listed. To give a certain data point a value, you must match the X and Y ordinates along with the relevant set of keys.
  6. A template supports multiple queries for binding, so you can build your query with multiple Value columns, one for each data type (numeric, string, and date). In this situation, you must bring up three data queries: one for the numeric values, one for the date values, and one for the string values (columns for ordinates, keys, dates, and only one for values). Construct the queries in a way that only one non-NULL value can ever be returned for any one-dimensional intersection. In addition, this value must be of the correct data type.
  7. If necessary, you can change default settings in the CDM.config file to specify preferences for your query.
    • By default, the ExcelRefresh queryResultMaxRows parameter specifies a maximum of 1000 rows in a query. To ensure that your query is not truncated at 1000 rows, you can increase the limit in the parameter.
    • By default, the truncateOnQueryResultMaxRowsExceeded parameter is set to true, which means that you will see the completed query even if it is truncated. If you do not want to see the completed query if it is truncated, change the parameter to false.

Bind a Query to an Open Axis Template

After you build your query, you can use CDM to bind the query to any type of open axis template. To create and bind a query to an open axis template, follow these steps:

  1. Create a relational data source that connects to the created table.
  2. Build a relational data query against the defined data source to bring in data. See Build a Query for an Open Axis Template.
  3. Create a filing report (see Add a Filing Report), and add the query to it.
  4. Assign report permissions. See Permissions for Filing Reports.
  5. Assign the data query to the filing report. (If you made your relational data query global, you do not need to complete this step.)
  6. Open the filing report that you want to work with.
  7. Check out the open axis template report object that you want to bind the query to.
  8. In the Data tab, click Insert Data, select the query that you built, and click Next.
  9. In the Data Query tab of the wizard, complete these steps:
    1. Click Location > Bind to the report object.
    2. Click Finish.
  10. In the Report Object tab, click Save and then click Yes. In the Validation area, you can now see a message that confirms that the template has an attached query.
  11. When you are ready to generate the instance document, click Generate, select the Microsoft Excel format, and click OK. To see the result of the generation, click Filing Generation. You can now see the appropriate data in a separate Microsoft™ Excel spreadsheet. You can see details about successful actions, and also about errors and warnings.
  12. When you are finished, check in the report object.

Was this article helpful?

We're sorry to hear that.