Design a configuration for generating documents in Excel format

You can design an Electronic reporting (ER) format configuration that has an ER format component that you can configure to generate an outbound document in a Microsoft Excel workbook format. You must use specific ER format components for this purpose.

To learn more about this feature, see Design a configuration for generating reports in OPENXML format.

Add a new ER format

When you add a new ER format configuration to generate an outbound document in an Excel workbook format, select the Excel value for the Format type attribute of the format or leave the Format type attribute blank.

• If you select Excel, you can configure the format to generate an outbound document only in Excel format.
• If you leave the attribute blank, you can configure the format to generate an outbound document in any format that the ER framework supports.

To configure the ER format component of the configuration, select Designer on the Action Pane, and open the ER format component for editing in the ER Operation designer.

In Finance version 10.0.34, you can view and change the Format type value for the selected format configuration version in the designer.

Excel file component

Manual entry

To generate an outbound document in Excel format, add an Excel\File component to the configured ER format.

To specify the layout of the outbound document, attach an Excel workbook that has the .xlsx extension to the Excel\File component as the template for outbound documents.

To specify how the attached template fills in when you run the configured ER format, add nested Sheet, Range, and Cell components to the Excel\File component. Associate each nested component with an Excel named item.

Template import

To import a new template into a blank ER format, select Import from Excel on the Import tab of the Action Pane. In this example, you automatically create an Excel\File component and attach the imported template to it. The process also automatically creates all required ER components, based on the list of Excel named items that it discovers.

Sheet component

The Sheet component represents a worksheet in the attached Excel workbook that you need to fill in. You set the name of the worksheet in an Excel template through the Sheet property of this component.

On the Mapping tab of the ER Operation designer, you can configure the Enabled property for a Sheet component to specify whether to include the component in a generated document:

• If you configure an expression for the Enabled property that returns True at runtime, or if you don't configure an expression, the appropriate worksheet is included in the generated document.
• If you configure an expression for the Enabled property that returns False at runtime, the generated document doesn't contain the worksheet.

Range component

Nested components

Data typing

The Range component can include other nested ER components that you use to enter values in the appropriate ranges.

• If you use any component from the Text group to enter values, you enter the value in an Excel range as a text value.

  Note

  Use this pattern to format entered values based on the locale that the application defines.

• If you use the Cell component from the Excel group to enter values, you enter the value in an Excel range as a value of the data type that the binding of that Cell component defines. For example, the data type might be String, Real, or Integer.

  Note

  Use this pattern to enable the Excel application to format entered values based on the locale of the local computer that opens the outbound document.

Row handling

You can configure the Range component as vertically replicated, so that it generates multiple rows in an Excel worksheet. The parent Range component or its nested Range components can generate the rows.

In version 10.0.26 and later, you can force a generated worksheet to keep the generated rows on the same page. In the ER format designer, set the Keep rows together option to Yes for the parent Range component in the editable ER format. ER then tries to keep all the content that it generates by that range on the same page. If the height of the content exceeds the remaining space on the current page, ER adds a page break, and the content starts at the top of the next new page.

To learn how to use this option, follow the example steps in Design an ER format to keep rows together on the same Excel page.

Replication

The Replication direction property specifies whether and how a range is repeated in a generated document:

• No replication – The appropriate Excel range isn't repeated in the generated document.

• Vertical – The appropriate Excel range is repeated vertically in the generated document. Each replicated range is placed below the original range in an Excel template. The number of repetitions is defined by the number of records in a data source of the Record list type that is bound to this ER component.

• Horizontal – The appropriate Excel range is repeated horizontally in the generated document. Each replicated range is placed to the right of the original range in an Excel template. The number of repetitions is defined by the number of records in a data source of the Record list type that is bound to this ER component.

  To learn more about horizontal replication, see Use horizontally expandable ranges to dynamically add columns in Excel reports.

Enabling

On the Mapping tab of the ER Operation designer, you can configure the Enabled property for a Range component to specify whether the component is included in a generated document:

• If you configure an expression for the Enabled property to return True at runtime, or if you don't configure an expression, the appropriate range is filled in the generated document.
• If you configure an expression for the Enabled property to return False at runtime, and if this range doesn't represent the entire rows or columns, the appropriate range isn't filled in the generated document.
• If you configure an expression for the Enabled property to return False at runtime, and this range represents the entire rows or columns, the generated document contains those rows and columns as hidden rows and columns.

Resizing

You can configure your Excel template to use cells to present textual data. To ensure that the whole text in a cell is visible in a generated document, you can configure that cell to automatically wrap the text inside it. You can also configure the row that contains that cell to automatically adjust its height if the wrapped text isn't fully visible. For more information, see the "Wrap text in a cell" section in Fix data that is cut off in cells.

As of Dynamics 365 Finance version 10.0.23, when you're working in a generated document, you can force ER to calculate the height of every row that was configured to automatically fit its height to the content of nested cells whenever that row contains at least one merged cell that was configured to wrap the text inside it. The calculated height is then used to resize the row to ensure that all cells in the row are visible in the generated document.

To start to use this functionality when you run any ER formats that were configured to use Excel templates to generate outbound documents, follow these steps:

1. Go to Organization administration > Workspaces > Electronic reporting.
2. On the Localization configurations page, in the Related links section, select Electronic reporting parameters.
3. On the Electronic reporting parameters page, on the Runtime tab, set the Autofit row height option to Yes.

When you want to change this rule for a single ER format, update the draft version of that format by following these steps.

1. Go to Organization administration > Workspaces > Electronic reporting.

2. On the Localization configurations page, in the Configurations section, select Reporting configurations.

3. On the Configurations page, in the configurations tree in the left pane, select an ER configuration that is designed to use an Excel template to generate outbound documents.

4. On the Versions FastTab, select the configuration version that has a status of Draft.

5. On the Action Pane, select Designer.

6. On the Format designer page, in the format tree in the left pane, select the Excel component that is linked to an Excel template.

7. On the Format tab, in the Adjust row height field, select a value to specify whether ER should be forced, at runtime, to change the height of rows in an outbound document that is generated by the edited ER format:

   • Default – Use the general setting that is configured in the Autofit row height field on the Electronic reporting parameters page.
   • Yes – Override the general setting, and change the row height at runtime.
   • No – Override the general setting, and don't change the row height at runtime.

Cell component

Use the Cell component to fill in Excel named cells, shapes, and pictures. To indicate an Excel named object that the Cell ER component must fill in, specify the name of that object in the Excel range property of the Cell component.

On the Mapping tab of the ER Operation designer, you can configure the Enabled property for a Cell component to specify whether the object is filled in in a generated document:

• If you configure an expression for the Enabled property that returns True at runtime, or if you don't configure an expression, the appropriate object is filled in in the generated document. The binding of this Cell component specifies a value that goes in the appropriate object.
• If you configure an expression for the Enabled property that returns False at runtime, the appropriate object isn't filled in in the generated document.

When you configure a Cell component to enter a value in a cell, you can bind it to a data source that returns the value of a primitive data type (for example, String, Real, or Integer). In this case, the value is entered in the cell as a value of the same data type.

When you configure a Cell component to enter a value in an Excel shape, you can bind it to a data source that returns a value of a primitive data type (for example, String, Real, or Integer). In this case, the value is entered in the Excel shape as the text of that shape. For values of data types that aren't String, the conversion to text is done automatically.

When you configure a Cell component to enter a value in an Excel picture, you can bind it to a data source that returns a value of the Container data type that represents an image in binary format. In this case, the value is entered in the Excel picture as an image.

To learn more about how to embed images and shapes, see Embed images and shapes in documents that you generate by using ER.

Page break component

The PageBreak component forces Excel to start a new page. This component isn't required when you want to use Excel's default paging, but use it when you want Excel to follow your ER format to structure paging.

Page component

Overview

Use the Page component when you want Excel to follow your ER format and structure pagination in a generated outbound document. When an ER format runs components that are under the Page component, it automatically adds the required page breaks. During this process, it considers the size of the generated content, the page setup of the Excel template, and the paper size that you select in the Excel template.

If you need to split a generated document into different sections, each with a different pagination, configure several Page components in every Sheet component.

Structure

If the first component under the Page component is a Range component where you set the Replication direction property to No replication, the system considers this range the page header for pagination that is based on the settings of the current Page component. The Excel range that is associated with this format component repeats at the top of every page that the system generates by using the settings of the current Page component.

If the last component under the Page component is a Range component where you set the Replication direction property to No replication, the system considers this range the page footer for pagination that is based on the settings of the current Page component. The Excel range that is associated with this format component repeats at the bottom of every page that the system generates by using the settings of the current Page component.

You can add multiple other Range components between the optional Range components to specify how a generated document is filled in.

If the set of nested Range components under the Page component doesn't comply with the previously described structure, a validation error occurs at design time in the ER format designer. The error message informs you that the issue can cause problems at runtime.

If you want pagination-related summing and counting to compute running totals and totals per page, configure the required Data collection data sources. To learn how to use the Page component to paginate a generated Excel document, complete the procedures in Design an ER format to paginate a generated document in Excel format.

Limitations

When you use the Page component for Excel pagination, you don't know the final number of pages in a generated document until the pagination is complete. Therefore, you can't calculate the total number of pages by using ER formulas, and you can't print the correct number of pages of a generated document on any page before the last page.

Dynamics 365 Finance version 10.0.22 doesn't consider configured Page components when you update an Excel template in the editable format. This functionality is considered for future releases of Finance.

If you configure your Excel template to use conditional formatting, it might not work as expected in some cases.

Applicability

The Page component works for the Excel file format component only when you configure that component to use a template in Excel. If you replace the Excel template with a Word template and then run the editable ER format, the Page component is ignored.

The Page component works only when the Enable usage of EPPlus library in Electronic reporting framework feature is enabled. An exception is thrown at runtime if ER tries to process the Page component while this feature is disabled.

Footer component

Use the Footer component to add footers at the bottom of a generated worksheet in an Excel workbook.

When you configure an individual Footer component, use the Header/footer appearance property to specify the pages that the component applies to. The following values are available:

• Any – Run the configured Footer component for any page of the parent Excel worksheet.
• First – Run the configured Footer component for only the first page of the parent Excel worksheet.
• Even – Run the configured Footer component for only the even pages of the parent Excel worksheet.
• Odd – Run the configured Footer component for only the odd pages of the parent Excel worksheet.

For a single Sheet component, you can add several Footer components, each of which has a different value for the Header/footer appearance property. By using this approach, you can generate different footers for different types of pages in an Excel worksheet.

Under the added Footer component, add the required nested components of the Text\String, Text\DateTime, or other type. Configure the bindings for those components to specify how your page footer is filled in.

You can also use special formatting codes to correctly format the content of a generated footer. To learn how to use this approach, follow the steps in Example 1, later in this article.

Header component

Use the Header component to add headers at the top of a generated worksheet in an Excel workbook. Use it like the Footer component.

Edit an added ER format

Update a template

Select Update from Excel on the Import tab of the Action Pane to import an updated template into an editable ER format. During this process, the template for the selected Excel\File component is replaced by a new template. The content of the editable ER format syncs with the content of the updated ER template.

• If the ER format component isn't found in the editable format, the process automatically creates a new ER format component for every Excel name.
• If the appropriate Excel name isn't found for an ER format component, the process deletes that component from the editable ER format.

In version 10.0.28 and later, you can use the Update Excel Header and Excel Footer format elements option.

• When you set this option to No, the Excel Header and Excel Footer format elements remain unchanged, even if the corresponding headers or footers are updated in the worksheets of the imported template in the Excel workbook format.

• When you set this option to Yes, Excel Header and Excel Footer format elements change when the corresponding headers or footers are updated in worksheets of the imported template in the Excel workbook format.

  • If the structure of a worksheet header or footer isn't changed, or if it's only appended, the structure of the corresponding Excel Header or Excel Footer format element is updated. Bindings of format elements that are nested under this Excel Header or Excel Footer format element are preserved.
  • If the structure of a worksheet header or footer is changed, the corresponding Excel Header or Excel Footer format element is re-created. Bindings of format elements that are nested under this Excel Header or Excel Footer format element are removed.

To learn more about this feature, see Modify Electronic reporting formats by reapplying Excel templates.

Validate an ER format

When you validate an ER format that you can edit, the system checks for consistency to make sure that the Excel name exists in the Excel template that you're currently using. The system notifies you about any inconsistencies. For some inconsistencies, the option to automatically fix problems is available.

Control the calculation of Excel formulas

When you generate an outbound document in a Microsoft Excel workbook format, some cells in this document might contain Excel formulas. When you enable the Enable usage of EPPlus library in Electronic reporting framework feature, you can control when the formulas are calculated by changing the value of the Calculation Options parameter in the Excel template that you're using:

• Select Automatic to recalculate all dependent formulas every time new ranges, cells, and other elements are added to a generated document.

  Note

  This choice might cause a performance problem for Excel templates that contain multiple related formulas.

• Select Manual to avoid formula recalculation when a document is generated.

  Note

  Formula recalculation is manually forced when a generated document is opened for preview by using Excel. Don't use this option if you configure an ER destination that assumes the usage of a generated document without its preview in Excel (PDF conversion, emailing, and so on) because the generated document might not contain values in cells that contain formulas.

Example 1: Format footer content

1. Use the provided ER configurations to generate a printable free text invoice (FTI) document.

2. Review the footer of the generated document. Notice that it contains information about the current page number and the total number of pages in the document.

   

3. In the ER format designer, open the sample ER format for review.

   The footer of the Invoice worksheet is generated based on the settings of two String components that reside under the Footer component:

   • The first String component fills in the following special formatting codes to force Excel to apply specific formatting:

     • &C – Align the footer text in the center.
     • &"Segoe UI,Regular"&8 – Present the footer text in the "Segoe UI Regular" font at a size of 8 points.

   • The second String component fills in the text that contains the current page number and the total number of pages in the current document.

   

4. Customize the sample ER format to modify the current page footer:

   1. Create a derived Free text invoice (Excel) custom ER format that is based on the sample ER format.

   2. Add the first new pair of String components for the Footer component of the Invoice worksheet:

      1. Add a String component that aligns the company name on the left and presents it in 8-point "Segoe UI Regular" font ("&L&"Segoe UI,Regular"&8").
      2. Add a String component that fills in the company name (model.InvoiceBase.CompanyInfo.Name).

   3. Add the second new pair of String components for the Footer component of the Invoice worksheet:

      1. Add a String component that aligns the processing date on the right and presents it in 8-point "Segoe UI Regular" font ("&R&"Segoe UI,Regular"&8").
      2. Add a String component that fills in the processing date in a custom format (" "&DATEFORMAT(SESSIONTODAY(), "yyyy-MM-dd")).

      

   4. Complete the draft version of the derived Free text invoice (Excel) custom ER format.

5. Configure Print management to use the derived Free text invoice (Excel) custom ER format instead of the sample ER format.

6. Generate a printable FTI document, and review the footer of the generated document.

   

Example 2: Fixing the merged cells EPPlus issue

You can run an ER format to generate an outbound document in an Excel workbook format. When you enable the Enable usage of EPPlus library in Electronic reporting framework feature in the Feature management workspace, the EPPlus library is used to make Excel output. However, because of known Excel behavior and a limitation of the EPPlus library, you might encounter the following exception: "Can't delete/overwrite merged cells. A range is partly merged with the another merged range." To learn what kind of Excel templates might cause this exception and how you can fix the issue, complete the following example.

1. In the Excel desktop application, create a new Excel workbook.

2. On worksheet Sheet1, add the ReportTitle name for cell A2.

3. Merge cells A1 and A2.

   

4. On the Configurations page, add a new ER format to generate an outbound document in an Excel workbook format.

5. On the Format designer page, import the designed Excel workbook into the added ER format as a new template for outbound documents.

6. On the Mapping tab, configure the binding for the ReportTitle component of the Cell type.

7. Run the configured ER format. Notice that the following exception is thrown: "Can't delete/overwrite merged cells. A range is partly merged with the another merged range."

   

You can fix the issue in either of the following ways:

• Easier but not recommended: In the Feature management workspace, turn off the Enable usage of EPPlus library in Electronic reporting framework feature. Although this approach is easier, you might experience other issues if you use it, because some ER functionality is supported only when the Enable usage of EPPlus library in Electronic reporting framework feature is enabled.

• Recommended: Follow these steps:

  1. In the Excel desktop application, modify the Excel workbook in one of the following ways:

     • On worksheet Sheet1, unmerge cells A1 and A2.
     • Change the reference for the ReportTitle name from =Sheet1!$A$2 to =Sheet1!$A$1.

     

  2. On the Format designer page, import the modified Excel workbook into the editable ER format to update the existing template.

  3. Run the modified ER format.

     

Limitations

Known EPPlus library limitations

External data sources

If one of your templates contains a PivotTable that is based on a PowerPivot model that refers to an external data source, and you enable the Enable usage of EPPlus library in Electronic reporting framework feature, you receive the following error message when you run an ER format that uses that template to generate an outbound document in Excel format: "The cachesource is not a worksheet." To fix this issue, use the following options:

• Recommended: Redesign the Excel solution that you're using:

  1. Isolate the part that contains pivots in a separate Excel workbook (workbook A).
  2. Use ER to generate a second Excel workbook (workbook B) from Finance that has the required details.
  3. Refer to workbook B in workbook A as soon as workbook B is generated.

• Turn off the feature, Enable usage of EPPlus library in Electronic reporting framework to use an option other than EPPlus.

Additional resources

Electronic Reporting overview

Design a configuration for generating reports in OPENXML format

Modify Electronic reporting formats by reapplying Excel templates

Use horizontally expandable ranges to dynamically add columns in Excel reports

Embed images and shapes in documents that you generate by using ER

Configure Electronic reporting (ER) to pull data into Power BI