Dataset Extensions

Document Creator Dataset Extensions allow you to add your own columns, data items and translatable labels to your reports, completely from within your Business Central client, i.e., no need for any report(extension) development!

Important

This feature is only enabled when reports are run with a Document Creator layout. For RDLC, Word and other layout types this feature is disabled.

Watch the Extending Datasets (Adding new columns) section of our demo video to learn more about how to extend your report datasets!

Report Dataset Extensions¶

Report Dataset Extensions can be set up in the Document Creator Report Dataset Extensions page. In this page you can specify reports to which you want to add new columns, dataitems and/or translatable labels to use them in your Document Creator report layouts. With the Enabled field you also have the option to either enable or disable the customizations that you defined for the report.

The page offers the following actions:

Custom Columns - View or edit the custom columns for the selected report.
Custom Labels - View or edit the custom labels for the selected report.
Custom Data Items - View or edit the custom data items for the selected report.
Export - Export the dataset extensions for the selected report(s) to an XML file.
Import - Import dataset extensions from an XML file.
View Columns - View the columns of the selected report in a tree view.
View Data Items - View the data items of the selected report in a tree view.
Filter Sets - View or edit the filter sets configured for the selected report.
Report Layouts - View or edit the Document Creator report layouts for the selected report.
Document Creator Translations - View or edit Document Creator Translations.

Custom Columns¶

To start adding custom columns to a report, open the Document Creator Report Dataset Extensions page, select the report for which you want to add columns and then invoke the Custom Columns action.

This will open a page in which you can view and edit the custom columns for the selected report. A custom column contains the value from a specified table field and can be added to any of the data items of the report object.

Some examples of custom columns you could add:

Add the Bill-to Customer's "City" to header data item of the Sales Invoice report.
Add the Item's "Vendor No." to the line data item of the Sales Invoice report.

Parent Data Item¶

In the Parent Data Item Name field you can enter the data item to which you want to add the new column. You can use the lookup available from the field to look up and select a parent from the data items available in the report.

Note

Custom columns can be added to existing data items or custom data items. If the parent data item is a custom data item then the Custom Parent field will have a checkmark to signify this. In this case the custom column will automatically use the table and the filters of the parent data item.

Field¶

In the Table ID field you can specify the table containing the field for the custom column. Accordingly, in the Field No. field you can specify the field where the value for the custom column is taken from.

In the Column Name field you can enter the name for the custom column. A name will be suggested automatically after you entered a table and field for the column. The suggested name will be <FieldName>_<TableName>, but you can freely change this to your liking.

Warning

You cannot use names that are already used by other columns or data items in the report.

Filters¶

In the Filter Set field you can enter the filter set that should be used for the column. A "filter set" is a set of filters that define the link between the parent data item and the table that the field for the custom column is taken from.

To create a filter set for a custom column, open the dropdown of the Filter Set field and choose New to create a filter set for the parent data item and table combination of the column.

This will open the Filter Set card page with parent data item and table already filled in and a code automatically suggested.

By adding filters you can set up the link between the parent data item and the table that the field value is taken from.

You can set up filters on another column in the report by entering the column name in the Value Column Name field. The Assist-Edit of this field will open a page where you can select a column from the parent data item or any of the other ascendant data items.

You can also set up filters on a constant/fixed value, which you can enter in the Constant Value field (e.g., enter 1/Order to filter on "Sales Document Type").

Finally, you can also set up filters by entering a Filter String on the filter field's value. In this field you can enter filters like <>0, 42|365|9000 or %1... This filter string can also include a placeholder %1 in which the Value Column's value or Constant Value will be substituted if either one is specified.

Info

Each filter can only have either a Value Column or Constant Value specified. If you try to enter both at the same time, the other field's value will be cleared.

For convenience, you can use the Edit Filter Set... action to open the Filter Set card page and edit the filter set for the selected custom column.

Reusing filter sets

You can reuse filter sets in other custom columns that add a field for the same parent data item and source table combination. If you add a new column with a field from the same table, simply open the dropdown and you will see the filter sets created earlier for other custom columns.

If you do not want to set any filters and use the first record from the table, you should explicitly set the Unfiltered field. In that case the custom column will use the first record in the table to retrieve the field value. This could be used if the source table only contains a single record in the first place (e.g., the "Company Information" table).

Attention

If there are multiple records that match the filters, then custom column(s) will use the field value(s) of the first record in the record set.

Restrictions¶

In the Filter Set page, you can also set up Restrictions. These can be used for scenarios in which you want to skip certain records that do not meet the condition specified in one of these restrictions. For example, you could specify that you only want to retrieve fields from the "Item" table, when the Sales Line that is being processed is in fact a line of type Item, i.e., TypeNo = 2. If the restrictions are not met, then no values will be retrieved and the values of the column(s) that use this filter set will be empty.

For a more detailed example usage of Restrictions, please see: Add Item Column for Item Sales Line.

Advanced Settings¶

Unfiltered - If you set this setting to true ('marked the checkbox'), then no filters/links will be used: the field value of the first record from the specified table will be retrieved.
Cast as Data Type - This setting allows you to convert ('cast') a custom column to a different data type, e.g.:
- Add Enum/Option fields as Integer (their integer ('number') value) or as Text (Caption value, translated based on the Language Code Column setting).
- Add Boolean fields as Boolean (true/false) or as Text (Yes/No, translated based on the Language Code Column setting)
- Add Date fields as Date (N.B., column gets DateTime datatype in the designer, which allows for calculations and custom formatting) or as Text (the formatting of the date is determined based on the Regional setting in Business Central)
- Add Decimal fields as Decimal (N.B., column gets Decimal datatype in the designer, which allows for calculations and custom formatting) or as Text (the formatting of the decimal is determined based on the Regional setting in Business Central)
Language Code Column - This setting allows you to select a column that contains the "Language Code" that should be used for translating the column value (e.g., an Enum/Option value as Text is translated based on this language)
Decimal Formatter - This setting allows you to specify the (default) formatting that should be used for a custom column with data type Decimal. You can use the format syntax as described on the Custom Numeric Format Strings and Standard Numeric Format Strings pages.

Examples¶

In this section you will find examples of how to add and/or configure custom columns to your report objects and Document Creator layouts.

Getting Started¶

Please check the example below for a complete walkthrough of adding a custom column to a report object and adding it to a Document Creator layout for that report accordingly.

Example: Adding a custom column to a report

A step-by-step walkthrough of how to add a custom column to a report.

The example as seen in the screenshots uses report 11249662 "Sales - Invoice" which is one of the Document Creator reports.

Open the Document Creator Report Dataset Extensions page.

Add a new record for the report that you want to add a custom column for.
With the record for your report selected, invoke the Custom Columns action.

This will open the Custom Report Columns page for the report.
First, to select a parent data item to add the column use the lookup of the Parent Data Item Name field.
Select the data item to which you want to add a custom column.
Next, select a table and the field you want to use for the custom column's value.

A column name will be suggested automatically based on the values you selected.
Then, create a new filter set. For this, open the dropdown of the Filter Set field and choose New.
This will create a filter set for you.

To add a filter, first enter the field from the table to filter on using the lookup of the Filter Field No. field.

Then, set a filter value on another column of the report by clicking on the Value Column Name field to start selecting a column.
Select a column to filter on from the Report Columns page.

For our example, we use the BilltoCustNo column so that the Bill-to Customer of the invoice is retrieved for our custom column.

Select a column and choose OK.

Add any more filters if applicable for your column and when done choose OK to close the Filter Set card page.
The custom column is now ready to use in your report.

Now go back to the Document Creator Report Dataset Extensions page.

With the record for your report selected invoke the Report Layouts action.
This will open the Report Layouts page (filtered on Document Creator layouts for the report).

Select one of the Document Creator layouts and then invoke the Export Design action to start editing the layout in the designer. See Editing Layouts.
In the Data window, you will now see the custom column that was just added.

Custom columns are added at the end of the data item, after all the other columns of the data item.
You can now add the custom column to your layout. Actually, you can do anything with custom columns that you could do with any other column (e.g., using it in the VisibleExpression property).
And when we preview the layout, we will see it is filled with a value based on our filters.

Add Item Column for Item Sales Line¶

If you would like to add a custom column for a field from the Item table (e.g., the "Shelf No." field) to the SalesLine data item in report object 11249661 "Sales - Order Confirmation", then please follow these steps:

Open the Document Creator Report Dataset Extensions page.
If there is no entry present for the report with ID 11249661 ("Sales - Order Confirmation"), then add it.
Invoke the Custom Columns action.
On the Custom Report Columns page, add a new entry with the following configuration:
- Use the drill-down of the Parent Data Item field, and select the SalesLine data item.
- In the Table ID field, select table 27 Item.
- In the Field No. field, select the field from this table you would like the custom column to use, e.g., the Shelf No. field.
- A Column Name will be automatically suggested after entering the previous data. You can choose to change this to a different name, if you would like. For example, you might want to add a suffix, e.g., "_DSX", to prevent possible future collisions).
Then, create and configure a new filter set for this entry. For this, open the dropdown of the Filter Set field and choose New.
Configure the filter set as follows:
- The Item."No." field should be equal to the SalesLine data item's column value of column No_2 (this is the column containing the value of the Sales Line"."No." field for each record of the Sales Line data item).
  
  To set this up, in the Filters tab, first select the Item."No." field using the drill-down of the Filter Field No. field on the Filter Set page.
  
  Then, select the No_2 column using the drill-down of the Value Column Name field.
- The column/field value should only be retrieved for Sales Line records of type Item.
  
  To set this up, in the Restrictions tab, first select the TypeNo column using the drill-down of the Column Name field.
  
  Then, enter 2 in the Constant Value field (where '2' is the ordinal value of the "Item" type for the "Sales Line Type" enum field).
  
  EXPLANATION: You should use the TypeNo column and check against the ordinal/number value instead of just checking that "Type" = "Item", because the field/column values are translated based on the document's language code. This means that the Type field/column value is translated into the current document's language, which is why you should check against the integer/ordinal value of the line type.
Finally, choose OK.
You have now added a new custom column for an Item table field to the SalesLine data item, with the appropriate filters, to the Sales Order Confirmation report object. You can now add this custom column to your Document Creator layouts for this report using the designer.

Add Sales Line Column¶

If you would like to add a custom column for a field from the Sales Line table (e.g., the "Job No." field) to the SalesLine data item in report object 11249661 "Sales - Order Confirmation", then please follow these steps:

Open the Document Creator Report Dataset Extensions page.
If there is no entry present for the report with ID 11249661 ("Sales - Order Confirmation"), then add it.
Invoke the Custom Columns action.
On the Custom Report Columns page, add a new entry with the following configuration:
- Use the drill-down of the Parent Data Item field, and select the SalesLine data item.
- In the Table ID field, select table 37 Sales Line.
- In the Field No. field, select the field from this table you would like the custom column to use, e.g., the Job No. field.
- A Column Name will be automatically suggested after entering the previous data. You can choose to change this to a different name, if you would like. For example, you might want to add a suffix, e.g., "_DSX", to prevent possible future collisions).
Then, create and configure a new filter set for this entry. For this, open the dropdown of the Filter Set field and choose New.
Configure the filter set as follows:
- The "Sales Line"."Document Type" field should be equal to the SalesHdr data item's column value of column DocumentType (this is the column containing the value of the Sales Header"."Document Type" field for each record of the SalesHdr data item).
  
  To set this up, in the Filters tab, first select the "Sales Line"."Document Type" field using the drill-down of the Filter Field No. field on the Filter Set page.
  
  Then, select the DocumentType column (of the SalesHdr data item(!)) using the drill-down of the Value Column Name field.
- The "Sales Line"."Document No." field should be equal to the SalesHdr data item's column value of column DocumentNo (this is the column containing the value of the Sales Header"."No." field for each record of the SalesHdr data item).
  
  To set this up, in the Filters tab, first select the "Sales Line"."Document No." field using the drill-down of the Filter Field No. field on the Filter Set page.
  
  Then, select the DocumentNo column (of the SalesHdr data item(!)) using the drill-down of the Value Column Name field.
- The "Sales Line"."Line No." field should be equal to the SalesLine data item's column value of column LineNo (this is the column containing the value of the Sales Line"."Line No." field for each record of the SalesLine data item).
  
  To set this up, in the Filters tab, first select the "Sales Line"."Line No." field using the drill-down of the Filter Field No. field on the Filter Set page.
  
  Then, select the LineNo column using the drill-down of the Value Column Name field.
Finally, choose OK.
You have now added a new custom column for a Sales Line table field to the SalesLine data item, with the appropriate filters, to the Sales Order Confirmation report object. You can now add this custom column to your Document Creator layouts for this report using the designer.

Custom Labels¶

You can add custom labels which can be translated with the Document Creator Report Translations feature.

To start adding custom columns to a report, open the Document Creator Report Dataset Extensions page, select the report for which you want to add translatable labels and then invoke the Custom Labels action.

This will open a page in which you can view and edit the custom labels for the selected report. A custom label contains the translation for a tag and can be added to any of the data items of the report object.

Note

Typically labels will be added to a top-level data item that contains the language code.

Similarly to custom columns, the Parent Data Item Name can be used to select the parent data item to add the label column to.

In the Tag field you can enter the tag/identifer for the translations. The Column Name will be suggested automatically after entering the tag.

In the Language Code Column field you can enter the column in the report object that contains the language code to use for retrieving translations. The Assist-Edit of this field will open a page where you can select a column from the parent data item or any of the other ascendant data items.

This way you can create labels that you can provide translations for in the Document Creator Translations page (See Report Translations). Similarly to custom columns, the columns for custom labels are added at the end of the data item, after all the other columns of the data item.

Note

The Language Code Column field is automatically populated for new records.

Custom Data Items¶

It is also possible to add new data items to your reports. That way you can retrieve multiple records from a table and use it as a datasource for your layouts.

What is the difference between custom columns and custom data items? When should we use custom data items vs. custom columns?

Custom Columns should be used when you are dealing with a 1-to-1 relation. For example, each Sales Line record (of Type = Item) only has a single related Item record. And, another example, each Sales Header only has a single bill-to Customer record.

Custom Data Items should be used when you are dealing with a 1-to-n relation. For example, each Sales Line may have multiple Sales Comment Line records linked to it. Each Item record may have multiple Item Attribute records, and similarly, it may have multiple Item Substitute records. And, another example, each Sales Line may have multiple Assembly Line records linked to it (via an Assemble-to Order Link record).

To start adding custom data items to a report, open the Document Creator Report Dataset Extensions page, select the report for which you want to add data items and then invoke the Custom Data Items action.

This will open a page in which you can view and edit the custom data items for the selected report. A custom data item has a source table (either filtered or unfiltered) and custom columns linked to it.

Similarly to custom columns, the Parent Data Item Name can be used to select the parent data item to add the new data item to. It is however also possible to leave the field blank so that the data item is added to the root.

The Table ID field is used to specify the source table of the data item. After specifying the table, a name for the data item will be suggested automatically in the Data Item Name field, but you can freely change this afterwards.

In the Filter Set field you can enter a filter set to use to filter the records that should be included in the data item. It is also possible to reuse filter sets that are also used for custom columns.

If you do not want to set any filters and use all records from the table, you should explicitly set the Unfiltered field.

To add columns to the custom data item, you can invoke the Columns action.

This will open a page in which you can view and edit the columns of the custom data items. The names of the columns are suggested automatically, but you can freely change them afterwards.

Similarly to custom columns, the custom data items are added at the end of the parent data item, after all the other child data items of the parent.

Info

With Document Creator (version "1.30.0.0") it is also possible to link custom data items/columns to other custom columns.

Export / Import¶

Dataset Extensions can be exported to and imported from an XML file. On the Document Creator Report Dataset Extensions page you can find an Export and Import action for this purpose.

On the page you can select records for one or more reports and export them to a single XML file with the Export action.

When you use the Import action to import dataset extensions, then please note that this could overwrite any dataset extensions that may already exist for the included reports.

View Columns and Data Items¶

On the Document Creator Report Dataset Extensions page you can find a View Columns and View Data Items action, which can be used to view all the columns or all data items of a report respectively.

The View Columns action opens a page that shows all the columns of a report, the parent data item they each belong to and the data type of the value they contain.

Custom columns are found at the end of their parent data item. For custom columns the Custom field is checked and shown in "Bold + Green".

Custom data items and their columns are included in the overview as well.

The View Data Items action opens a page that shows all the data items of a report, including all the custom data items.

Last update: January 30, 2023