Skip to content

Dataset Extensions

Document Creator Dataset Extensions allow you to add your own columns, dataitems 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 and Word layouts 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 ExtensionsLink

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.

report-dataset=extensions

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.
  • Custom Report Layouts - View or edit the Document Creator custom report layouts for the selected report.
  • Document Creator Translations - View or edit Document Creator Translations.

report-dataset=extensions-actions

Custom ColumnsLink

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.

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.

custom-columns-page

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 ItemLink

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.

custom-columns-lookup-parent-data-item

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.

FieldLink

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.

FiltersLink

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.

filter-set-create

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

filter-set-card

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.

filter-set-edit-action

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.

filter-set-reuse

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.

ExampleLink

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 72102 "Sales - Invoice" included in the Document Creator W1 Report Templates. See Templates.

  1. Open the Document Creator Report Dataset Extensions page.

    Add a new record for the report that you want to add a custom column for.

    step-1

  2. With the record for your report selected, invoke the Custom Columns action.

    step-2

    This will open the Custom Report Columns page for the report.

  3. First, to select a parent data item to add the column use the lookup of the Parent Data Item Name field.

    step-3

  4. Select the data item to which you want to add a custom column.

    step-4

  5. Next, select a table and the field you want to use for the custom column's value.

    step-5

    A column name will be suggested automatically based on the values you selected.

  6. Then, create a new filter set. For this, open the dropdown of the Filter Set field and choose New.

    step-6

  7. 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.

    step-7

  8. 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.

    step-8

    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.

  9. 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 Custom Report Layouts action.

    step-9

  10. This will open the Custom 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.

    step-10

  11. 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.

    step-11

  12. 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).

    step-12

  13. And when we preview the layout, we will see it is filled with a value based on our filters.

    step-13

Custom LabelsLink

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.

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.

custom-labels-page

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.

custom-labels-tag-column

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.

custom-labels-language-code

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.

custom-labels-example

Note

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

Custom Data ItemsLink

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.

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.

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.

custom-data-items-page

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.

custom-data-items-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.

custom-data-items-columns-page

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.

custom-data-items-designer

Info

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

Export / ImportLink

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.

export-import-actions

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 ItemsLink

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.

view-columns-data-items-actions

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.

view-columns-page

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".

view-columns-custom

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.

view-data-items-page