This article is part of the R365 Financial Report Writer training on Building Reports.  Click here for more information on Building Reports and click here for more information on R365 Financial Report Writer




Default Column Layouts


The Default Templates that are included with the R365 Financial Report Writer contain various Column Layouts that are an excellent starting point to mimic common Column Layouts in other R365 reports.  You cannot modify these Column Layouts, but you can select them, then Save them as new Layouts.  To select a Default Column Layout, click in the Column Layout selector and chose one of the existing layouts as shown below:



  • Note: Trial Balance Type Template does not allow the modification of the Column Layout



Creating New Column Layouts


After selecting a Default Column Layout, you can make any desired modifications to it, then click on the menu and select 'Save As New'. The 'Save' option is grayed out for Default Column Layouts, but will be available on any Column Layouts that you have manually created. Default Layouts cannot be deleted.



Once you click 'Save As New', the 'Save Column Layout' prompt will appear where you must enter the Name of the Custom Column Layout:



If you are working on a Default Template, once you save the Custom Column Layout, you'll also be prompted if you want to save the New Column Layout to a New Template.  Click 'Yes' to create a New Template and assign the Column Layout to the Template.



If you are working on non-default Template, and you select a Column Layout that is different than the layout that has previously been saved on your Template, you'll be prompted if you would like to assign the Column Layout to the Template




Custom Column Layout Actions



When a Custom Column Layout is selected, the Editand Deletebuttons will appear next to the Save menu. If the Column Layout is saved to any Templates (including the Current Template), the Deleteicon will be grayed out and unclickable.


Change Column Layout Name

If you need to change the Name of the Custom Column Layout, click the Editbutton, to open the 'Rename Column Layout' prompt and make the change and save.




Delete Custom Column Layout


To Delete a Column Layout, it must first be unassigned from all Templates where it is currently saved.  Hover over the Deleteicon to view a tooltip that lists all Templates that are currently using this Column Layout:



Next, open the Template that was listed in the tooltip (if it is not the Current Template you are working on) and click on the Column Layout Selector:



Choose a different Layout, then click on the Save menu and select 'Save'.  You will be prompted if you want to save the New Column Layout to the Current Report Template.  Click 'Yes' to save and assign the Column Layout to your Template, thereby removing the Custom Column Layout from the Template and making it available to be deleted.  Repeat this process for any other Templates that are utilizing the Custom Column Layout.  


Once all required Templates are unassigned from the Custom Column Layout, select the Custom Column Layout on any Template.  The Delete icon is now solid and clickable as shown below:



Click 'Delete' to delete the Custom Column layout



Change Column Layout & Create a Copy of a Column Layout


Use the 'Save' option to save your Custom Column Layout, or to select and assign a different Column Layout.  Select 'Save As New' to save a new version of the current Column Layout.  You'll be prompted to enter a name for the new Column Layout.




Closing the Column Editor without Saving


If you click out of the Column Editor after having made any changes, the Save Options prompt will automatically appear to confirm that you want to close the Column Editor.  The options include the following:



  • Return to Editor - Do not close the Column Editor and continue working
  • Discard Changes - Close the Column Editor and discard any changes that were made
  • Save - Save the Changes you made to the Column Layout (grayed out for Default Column Layouts) and close the Column Editor
  • Save As New- Save the Changes you made to the Column Layout as a New Column Layout and close the Column Editor.  When selected, the 'Save Column Layout' Prompt will load and the Column Layout Name must be entered to Save



Column Formatting


The R365 Financial Report Writer allows you to add custom formatting to each Column Header, the Data in the Column, and the optional '%' Column on your Custom Financial Reports.  Formatting Tools are located at the top of the Column Editor and appear when you click into any of the 'Header' areas on a Column.  Each type that can be formatted has a separate set of formatting tools.  By default, the 'Data' tools will be selected.  You can switch between each formatting option using the buttons above the tools, as shown below:



1) Header Formatting Tools - Select the tools for editing the format of the Column Headers.  These tools are described below

2) Data Formatting Tools - Select the tools for editing the format of the Data in the Columns.  These tools are described below

3) % Column Formatting Tools - Select the tools for editing the format of the optional '%' Column.  These tools are described below

4) Copy Format - Copy the format on the selected Column Header/Data/%.  Is used in conjunction with Paste Format (#5)

5) Paste Format - After Copying the Format (#4), this button is enabled and allows you to select another Column and Paste the copied format to the Header/Data/%



Header Formatting Tools



1) Bold

2) Italics

3) Font Size

  • 7 pt
  • 8 pt
  • 10 pt
  • 12 pt

4) Font Color

5) Fill Color

6) Border


Data Formatting Tools



1) Bold

2) Italics

3) Font Size

  • 7 pt
  • 8 pt
  • 10 pt
  • 12 pt

4) Font Color

5) Fill Color

6) Border

7) Number Format

  • $
  • %
  • Number Only

8) Rounding

  • 0
  • .0
  • .00



% Formatting Tools


1) Bold

2) Italics

3) Font Size

  • 7 pt
  • 8 pt
  • 10 pt
  • 12 pt

4) Font Color

5) Fill Color

6) Border

7) Rounding

  • 0
  • .0
  • .00


To modify the format of a Column, first check the box of that Column, then make the changes on the Formatting Tools.  



You can select several Columns at once to make formatting changes to all selected Columns.



Finally, you can check All Columns by checking the 'All Cols' checkbox.  Note: Columns that use the Repeat function require the use of the 'All Cols' checkbox as all Repeat Columns must have identical formatting



Each formatting change (as well as any other changes that are made to the Columns) will be displayed in the Preview Pane immediately below the Column Editor, as shown below:




Multi-select Actions


In addition to performing mass formatting edits as described above, when you select more than one Column, you can perform the following actions:

  • Copy - Create Copies of the selected Columns
  • Delete - Delete the Columns and remove it from the Template



Column Headers


The R365 Financial Report Writer supports up to three distinct levels of Column Headers.  When using either the 'Financial' or 'Budget' Column Types, Column Header 1 will be required.  Column Header 2 and Column Header 3 are optional.  Header 1 is the lowest header (closest to the data) when more than one Header is in use.  Headers can contain the following value types:

  • Text Description - Manually entered Text that is set by the User
  • Dynamic Header - Dynamically set Header value based on a selection (Time Frame, Date Range, or Organization Values).  Dynamic Headers are updated at the time the Report is ran to reflect the report parameters.  Note: Only available on 'Financial' or 'Budget' Column Types


Header 2 and Header 3 can also be used to span across several Columns at once, allowing you to group the Columns under a common Header.  This is accomplished by hovering over the edge of column, then clicking and dragging the Header to encompass the other Headers, as shown below:



Add and Remove Additional Headers


Click the plus icon to add an additional Header. Click the 'x'  icon to remove the additional Header.


Dynamic Headers


Dynamic Headers are only available on 'Financial' or 'Budget' Column Types and allow a dynamically set value to become the Description of the Column Header at the time when the Report is ran.  The options for Dynamic Headers will vary depending on the Report Type, if a Repeat is set on the Column, and the Location Reporting Categories that you have setup in your R365 Database (please refer to the Organization Structure article for more information)


To use a Dynamic Header, click into the Header Context menu and select 'Use Dynamic Header'



Once enabled, you can set the Dynamic Header Type by clicking into the 'Description' field.  The options that are available to you will then be displayed in a list.  Select the desired Dynamic Header to set it to that Column.  Options include the following:


  • Date Range - The Date Range the Report was ran for
  • Org Value - The Organization Structure Unit Value the Report was ran for
  • Org No. - The Organization Structure Unit Number the Report was ran for
  • Org No. + Org Value - A combination of the Organization Structure Unit Number and Value the Report was ran for
  • Time Frame Type- The value set in the 'TF Type' Field.  Only applicable when the Column is non-repeating.  Options include:
    • Week
    • Period
    • Quarter
    • Year to Date
  • TF Type + TF Value - The Time Frame Type that is set on the Repeat (same options listed above) combined with the value of the Time Frame Type, i.e., 'Week 42'
  • TF Type + TF Value + Year - The Time Frame Type that is set on the Repeat (same options listed above) combined with the value of the Time Frame Type, combined with the Year, i.e., 'Period 9 2019'



Column Fields

1) Header 2 - Click the plus icon to add Header 2.  After doing so, you'll also have the opportunity to create Header 3 as needed.  Both Header 2 and 3 have Repeat options, Formatting, Dynamic Header options, and Column Merging / Grouping capabilities 

2) All Cols Checkbox - All Columns Checkbox.  Check this box to check all Columns for mass formatting

3) Description - Enter the manual Text Description, or select to use a Dynamic Header to add a Description to the Column Header

4) Col Type - Column Type.  Select the Column Type for this Column.  Options include:

  • Financial - A Financial value will be displayed in the Column where applicable (based on the Row Layout of the Report).  Can be used in conjunction with Dynamic Headers
  • Budget - A Budget value will be displayed in the Column where applicable (based on the Row Layout of the Report).  Can be used in conjunction with Dynamic Headers
  • Calculation - The results of a Calculation Formula will be displayed in this Column.  Calculations will be described below.  No Header is required for this Column Type
  • Better / Worse Var. - A variance column that shows good increases as positive and bad increases as negative
    • Note: This Column Type will appear only on P&L Templates
  • % Column Var.- A Better / Worse Variance that is subtracting the %'s of 2 columns
    • Note: This Column Type will appear only on P&L Templates
  • Blank - A Blank Column will be displayed.  Can be used for visually breaking up your data.  No Header is required for this Column Type
  • Row Description - The Row Description (which is displayed at the far left of the Report) will be displayed again.  Can be used for lengthy reports that have numerous columns to assist in tracking back data values to Row Descriptions.  No Header is required for this Column Type
  • Vertical Line - A Vertical Line will be displayed. Can be used for visually breaking up your data. No Header is required for this Column Type

5) Calculation - Used in conjunction with the 'Calculation' Column Type.  Enter a Calculation formula to display the results of the Calculation in this Column

6) TF Type - Time Frame Type.  This field is only applicable when the Column is non-repeating and allows the User to set a specific Time Frame Type on the Column.  Options include:

  • Week
  • Period
  • Quarter
  • Year to Date

7) Time Frame - Used in conjunction with the 'TF Type' setting, allows the User to select a previous (or specific) Time Frame based on the 'TF Type' setting, i.e., TF Type = 'Period', Time Frame = 'Prior'.  When 'Specific' is selected, an additional field appears where the User must set the Specific Time Frame desired



8) Year - Only used in conjunction with 'TF Type' = 'Period' and 'Time Frame' = 'Specific'.  Allows the User to select a previous (or specific) Year.  This option is rarely used



9) Org Type - Used to set a specific Org Type value on the Column.  The Org Values are then set in the next field.  This option is rarely used.  Note: 'Concept', 'District' and 'Region' represent Location Reporting Categories that were created in the R365 Database used in creating these screenshots.  Your specific Location Reporting Categories will be listed here.



10) Org Values - Used in conjunction with the 'Org Type' field.  Allows the User to select which Org Values from the selected 'Org Type' to include in the Column.  Note: 'Legal Entity' is selected in the 'Org Type'; therefore, the Legal Entities for the R365 Database used to create these screenshots are displayed below. Your specific Legal Entities will be listed here.


11) Include % - Click the percent icon to disable the '%' column that appears to the right of each Column when running reports. When enabled, the percent icon will be bold: , when disabled it will become translucent:

12) Repeat - Allows you to set a Repeat function on the Column.  Refer to the 'Repeat Functions' section below for more information

13) Column Checkbox - Check this box to perform formatting or multi-selection functions on this Column

14) Column Context Menu - Hover over any Column to display the Context Menu icon.  Click this icon to open the Column Context Menu and perform any of the following actions: Insert Column (#1) with all corresponding Column Types, Copy (#2) the selected Column, Cut (#3) the selected Column, Paste (#4) the Copied/Cut Column, Delete (#5) the Column, or Clear the Header (#6) of the selected Column.  Note: If the Column can be assigned a Dynamic Header (and it has not yet been assigned), #6 'Clear Header' will be replaced with 'Use Dynamic Header'

15) Move Column - Click on the Moveicon to move any Column by dragging it to a new Column ordering.  When moving Columns, any Calculations on those Columns will be updated to reflect the new Column ordering of the moved Column



Repeat Functions


In place of manually duplicating Columns, the Repeat Function allows you to setup a single Column (with a Column Type of 'Financial' or 'Budget'), then Repeat that Column for each Entity / Location / Location Reporting Category / Time Frame that you select when you run the Report.  


Repeats can also be used on the Header 2 or Header 3 fields.  If you have combined Columns under a Header 2, you have the ability of using a Repeat Function on the combined Header 2 to repeat the group of Columns when the Report is ran. Repeats cannot be used if the Column is used in a Calculation.


To set a Repeat Function on a Column, click the Repeat icon to display the Repeat Options, then make the desired selection.  

1) Location (Sort by Name) - Repeat the Column for each Location selected at Report running.  Sort the Locations alphabetically by Name

2) Location (Sort by Number) - Repeat the Column for each Location selected at Report running.  Sort the Locations numerically by Number

3) Legal Entity (Sort by Name) - Repeat the Column for each Legal Entity selected at Report running.  Sort the Legal Entities alphabetically by Name

4) Legal Entity (Sort by Number) - Repeat the Column for each Legal Entity selected at Report running.  Sort the Legal Entities numerically by Number

5) Location Reporting Category - Repeat the Column for each Location Reporting Category selected at Report running

6) Week - Repeat the Column for each Week.  Only available on P&L Date Range Report Types

7) Period - Repeat the Column for each Period.  Only available on P&L Date Range Report Types

8) Quarter - Repeat the Column for each Quarter.  Only available on P&L Date Range Report Types

9) Year - Repeat the Column for each Year.  Only available on P&L Date Range Report Types



Column Calculations


Column Calculations are only available on Columns where the 'Col Type' has been set to 'Calculation'.  Create a Calculation by selecting a Column, then setting the 'Col Type' to 'Calculation', and finally entering the Calculation Formula in the 'Calculation' field.  Formulas use the Column Number of each Column to perform their calculations.  Only Columns with the 'Col Type' of 'Financial' or 'Budget' can be used in Calculations.

  • Note: Column numbers are updated dynamically in Calculations to ensure that the Calculation remains true.  If a Column that is part of a Calculation is moved, or another Column is inserted, the respective Column Number in any Calculations will be updated accordingly


To create a calculation, begin typing in any of the following Column identifiers: Column # or Column Description.  The Column lookup will display all possible results, where you must then click on one of the values to select it.  If a Column is not appearing in your search results, confirm that the Col Type is set to 'Financial' or 'Budget'.  You'll know you successfully selected a Column for your calculation once the value used for searching changes to 'C##' as shown below:



The following types of Calculations are supported:

  • Addition - Begin typing the Column Number that you will start with in your Formula. The Column lookup will appear where you can see all Columns that contain the Column Number you typed. In addition to the Column Number, you will also see the Column Description. Select the desired Column and the value will change to 'C##'. Next, type the plus sign ( + ). Continue by typing the next Column Number that will be added to the first. Continue this process until your Addition Formula is complete, then hit the 'Enter' key again, or click off the Calculation field, to complete the Calculation Formula. Hit the 'Esc' key to close without saving


  • Subtraction - Begin typing the Column Number that you will start with in your Formula. The Column lookup will appear where you can see all Column that contain the Column Number you typed. In addition to the Column Number, you will also see the Column Description. Select the desired Column and the value will change to 'C##'. Next type the minus sign ( - ). Continue by typing the next Column Number that will be subtracted from the first. Continue this process until your Subtraction Formula is complete, then hit the 'Enter' key again, or click off the Calculation firled, to complete the Calculation Formula. Hit the 'Esc' key to close without saving


  • Division - Begin typing the Column Number that you will start with in your Formula. The Column lookup will appear where you can see all Column that contain the Column Number you typed. In addition to the Column Number, you will also see the Column Description. Select the desired Column and the value will change to 'C##'. Next type the divide sign ( / ). Continue by typing the next Column Number that will be divided into the first. Continue this process until your Division Formula is complete, then hit the 'Enter' key again, or click off the Calculation field, to complete the Calculation Formula. Hit the 'Esc' key to close without saving


  • Complex & Order of Operations - Calculations are not limited to a single type, but can be used with all three Calculation Types. Additionally, you may utilize parenthesis to employ order of operations functions within your Calculations as needed