R365 Custom Financial Reports Writer: Report Columns

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.

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

Create 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 will 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 will be prompted if you would like to assign the Column Layout to the Template.

Custom Column Layout Actions

1. Lock - Enables a Column Layout to be locked and not editable by other Users. A layout can be unlocked by reclicking the lock icon

2. Edit - Enables the selected Column Layout to be edited

3. Delete - This will delete the selected Column Layout. If the Column Layout is saved to any Templates (including the Current Template), the Delete iconwill be grayed out and unclickable.

4. Save - This will save the selected Column Layout

5. Column Sizing -The density of the column size. The options include 'Large' (default) and 'Comfortable'

6. Shared with all instances - This marks this layout as shared across all instances. Any edits made to this layout will then update across instances

   Only Partner Channel Users will have access to this feature

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 will be prompted to enter a name for the new Column Layout.

Close 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 Layout Usage

Column Layouts list a number next to them to signify the number of templates that use the layout, which can help determine the layouts that have the highest and lowest impacts.

Hovering over the template icon will display the template names that use the layout, and the Shared iconwill be displayed when the Column Layout has been shared across instances.

Sharing Column Layouts is only available to Channel Partner Users.

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

8. Number Format

   • $

   • %

   • Number Only

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

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

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

  Dynamic headers are 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 plusicons to add an additional header. Click the 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).

Dynamic Headers can be entered on their own or combined with free text. To use a Dynamic Header, simply enter '@' and then select the desired option from the drop-down listing. The available options are dependent upon the selected Report Type. Select the desired Dynamic Header to include in that column. Options include the following:

• 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. When a CFR report is based on a Time Frame Type, Time Frame Fields in the Template columns are not customizable. All columns will use the date range selected.
  
  Time Frame Types are only applicable when the Column is non-repeating. Options include:

  • Day (available for Financial Column Types only)

  • Week

  • Period

  • Quarter

  • Year

  • Week to Date

  • Period to Date

  • Quarter to Date

  • Year to Date

    Time Frame Types that are 'to Date' reflect the Report Date selected in the Report Settings

  • YTD Cols by Quarter

  • YTD Cols by Quarter w/Ttl

  • YTD Cols by Period

  • YTD Cols by Period w/Ttl

  • YTD Cols by Week

  • YTD Cols by Week w/Ttl

  • QTD Cols by Period

  • QTD Cols by Period w/Ttl

  • QTD Cols by Week

  • QTD Cols by Week w/Ttl

  • PTD Cols by Week

  • PTD Cols by Week w/Ttl

  • PTD Cols by Day

  • PTD Cols by Day w/Ttl

  • WTD Cols by Day

  • WTD Cols by Day w/Ttl

    The YTD, QTD, PTD, and WTD Cols options listed will display the data for the time frame to date and not just the time frame, if selected. These columns are not valid in calculations, and they do not apply to P&L Custom Date or Trial Balance templates

• 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'

• Ending Date (date) - The end date of the selected Time Frame written as 'MM/DD/YYYY'; for example, 04/01/2020

• Ending Date (text) - The end date of the selected Time Frame written as 'Month DD, YYYY'; for example, April 1, 2020

• TF + Ending + EndDate (date) - The Time Frame Type + 'Ending' + the last date in the selected Time Frame Type written as 'MM/DD/YYYY'; for example 'Period Ending 03/31/2020'

• TF + Ending + EndDate (text) - The Time Frame Type + 'Ending' + the last date in the selected Time Frame Type written as 'Month DD, YYYY'; for example 'Period Ending March 31,2020'

• Start Date - End Date (date) - The date range listed as 'MM/DD/YYYY - MM/DD/YYYY'; for example, '04/01/2020-04/30/2020'

• Start Date - End Date (text) - The date range listed as 'Month DD, YYYY - Month DD, YYYY'; for example, 'April 1, 2020-April 30, 2020'

• No. of Full Periods - The number of months of data that are represented in this column

• Client Name- The name of the Client assigned to the Org Unit. If the Report or Column represents one or more Locations, one of the following variables will populate the Column Header:

  • Client Name - This will display if all Locations have the same Client assigned to them

  • Multiple Clients - This will display if the Locations have different Clients assigned to them

  • Client Missing- This will display if none or at least one Location have Clients assigned to them

    Only Partner Channel Users have access to Client records. Click here to learn more about Client Records

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, select to use a Dynamic Header to add a Description to the Column Header, or use a combination of Text and Dynamic Header (initiate using '@')

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

     Column Type will appear only on P&L Templates

   • % Column Var.- A Better / Worse Variance that is subtracting the %'s of 2 columns

     This Column Type will appear only on P&L Templates

   • Blank - A blank column will be displayed. This 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. This 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. This 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. When a CFR report is based on a Time Frame Type, Time Frame Fields in the Template columns are not customizable. All columns will use the date range selected. 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:

   • Day (available for Financial Column Types only)

   • Week

   • Period

   • Quarter

   • Year

   • Week to Date

   • Period to Date

   • Quarter to Date

   • Year to Date

     Time Frame Types that are 'to Date' reflect the Report Date selected in the Report Settings

   • YTD Cols by Quarter

   • YTD Cols by Quarter w/Ttl

   • YTD Cols by Period

   • YTD Cols by Period w/Ttl

   • YTD Cols by Week

   • YTD Cols by Week w/Ttl

   • QTD Cols by Period

   • QTD Cols by Period w/Ttl

   • QTD Cols by Week

   • QTD Cols by Week w/Ttl

   • PTD Cols by Week

   • PTD Cols by Week w/Ttl

   • PTD Cols by Day

   • PTD Cols by Day w/Ttl

   • WTD Cols by Day

   • WTD Cols by Day w/Ttl

     The YTD, QTD, PTD, and WTD Cols options listed will display the data for the time frame to date and not just the time frame, if selected. These columns are not valid in calculations, and they do not apply to P&L Custom Date or Trial Balance templates

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

   '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

    '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 percenticon 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

    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

16. Hide Button - This button allows the User to hide the column in the Viewer, on the Sample Report of the Builder, or when a report is printed or exported to PDF. If exported to Excel, this column will display as hidden in the Excel file. To show this column again, simply click the hidden icon

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.

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