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 Row Layouts
The Default Templates that are included with the R365 Financial Report Writer contain various Row Layouts that are an excellent starting point to include each range of GL Accounts that exist in your General Ledger. You cannot modify these Row Layouts, but you can select them, then Save them as new Layouts. To select a Default Row Layout, click in the Row Layouts selector and chose one of the existing layouts as shown below:
Creating New Row Layouts
After selecting a Default Row 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 Row Layouts, but will be available on any Row Layouts that you have manually created. Default Layouts cannot be deleted.
Once you have created your Custom Row Layout, you'll be prompted if you want to save the New Row Layout to the Current Report Template. Click 'Yes' to save and assign the Row Layout to your Template
Custom Row Layout Actions
1) Lock - Enables a Row 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 Row Layout to be edited
3) Delete - This will delete the selected Row Layout. If the Row 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 Row Layout
5) Column Sizing - The density of the Row size. The options include 'Large', 'Comfortable', and 'Compact' (default)
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
- Note: Only Channel Partners will have access to this feature
Change Row Layout Name
If you need to change the Name of the Custom Row Layout, click the Editbutton, to open the 'Rename Row Layout' prompt and make the change and save.
Delete Custom Row Layout
To Delete a Row 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 Row 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 Row 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 Row Layout to the Current Report Template. Click 'Yes' to save and assign the Row Layout to your Template, thereby removing the Custom Row Layout from the Template and making it available to be deleted. Repeat this process for any other Templates that are utilizing the Custom Row Layout.
Once all required Templates are unassigned from the Custom Row Layout, select the Custom Row Layout on any Template. The Delete icon is now solid and clickable as shown below:
Click 'Delete' to delete the Custom Row layout
Change Row Layout & Create a Copy of a Row Layout
Use the 'Save' option to save your Custom Row Layout, or to select and assign a different Row Layout. Select 'Save As New' to save a new version of the current Row Layout. You'll be prompted to enter a name for the new Row Layout.
Closing the Row Editor without Saving
If you click out of the Row Editor after having made any changes, the Save Options prompt will automatically appear to confirm that you want to close the Row Editor. The options include the following:
- Return to Editor - Do not close the Row Editor and continue working
- Discard Changes - Close the Row Editor and discard any changes that were made
- Save - Save the Changes you made to the Row Layout (grayed out for Default Row Layouts) and close the Row Editor
- Save As New- Save the Changes you made to the Row Layout as a New Row Layout and close the Row Editor. When selected the 'Save Row Layout' Prompt will load and the Row Layout Name must be entered to Save
The R365 Financial Report Writer allows you to add custom formatting to each Row on your Custom Financial Reports. Formatting Tools are located at the top of the Row editing area and include the following options:
4) Font Size
5) Font Color
6) Fill Color
10) Copy Format
11) Paste Format
12) Decrease Indentation
13) Increase Indentation
14) Show (Show/Hide Symbols ($, %) for the selected rows)
To modify the format of a Row, first check the box of the Row, then make the changes on the Formatting Tool.
You can select several Rows at once to make formatting changes to all selected Rows.
Each formatting change (as well as any other changes that are made to the Rows) will be displayed in the Preview Pane immediately to the right of the Rows Editor, as shown below:
In addition to performing mass formatting edits as described above, when you select more than one Row, you can perform the following actions:
- Cut - Creates copies of the selected Rows, which can be pasted elsewhere
- Copy - Create copies of the selected Rows
- Flip - Flip the sign on the Row when displaying on Reports (Positive vs. Negative). Typically Rows that contain GL Accounts that belong to the following GL Types will necessitate a Flip to the Negative ( - ) sign:
- Current Liability
- Long Term Liability
- Hide - Hide the Row when viewing the Report. This is particularly useful for Rows that contain Calculations that do not need to be seen on the actual Report
- Delete - Delete the Row and remove it from the Template
Row Fields & Options
1) Description - Enter the Description, or Display Name, for this Row as it will appear when running Reports. To create a section header in your Report, simply enter a Description without adding an Account or Calculation. Remember to use the Formatting Tools to set your Row indentations as desired
2) Account - Select the Account that will be Displayed in this Row. You can search for Accounts by Name or Number. You can select multiple Accounts, or a range of Accounts. Refer to the 'Row Accounts' section below for more information
- Note: For the P&L Templates only, Stat Accounts are included in this drop-down listing, and when selected, the value of this row will be determined by the selected Stat Account Type field on the Stat Account Record
3) Calculation - The Calculation Field allows you to perform Addition, Subtraction, and Division calculations using other Rows or GL Types on Rows that do not have an Account assigned to them. Refer to the 'Row Calculations' section below for more information
4) Flip Sign - Click the Flip Signicon to change the sign on this Row to Negative ( - ). Once flipped, the icon will appear with a slash through it:
5) Hide - Click the Hideicon to hide the Row when running Reports. Once hidden, the icon will appear with a slash through it:
6) Move Row - Click on the Moveicon to move any row by dragging it to a new row ordering. When moving Rows, any Calculations on those Rows will be updated to reflect the new row ordering of the moved Row
7) Row Context Menu - In addition to Moving Rows, you can also click the Arrowicon to open the Row Context Menu and (1) Insert a Row above the current Row, (2) Insert a Page Break above the current Row, (3) Copy the current Row, (4) Cut the current Row, (5) Paste a previously Copied or Cut Row above another Row, or (6) Delete the Row from the Row Layout
8) Add Row - Below the final Row in your Row Layout is the Plusicon. Click this button to add an additional Row to your Row Layout
9) % of Row - This field only appears on the 'P&L' and 'P&L Date Range' Report Types. It allows the User to set a '% of Row' reference, where the current Row will be displayed as a percent of the row selected in this field. The '%' Column can be disabled in Column Editor as needed
Accounts can be added to each Row using the following methods:
- Individual - Search for the Account Name or Number, then click (or hit your 'Enter' key) to select it. Hit the 'Enter' key again, or click off the Row, to complete the Account lookup. Hit the 'Esc' key to close without saving
- Multiple - Search for the Account Name or Number, then click (or hit your 'Enter' key) to select it. Next type a comma ( , ) to enable 'Multiple'. Proceed to search for the subsequent Account Name or Number, then click (or hit your 'Enter' key) to select it. To add another Account, continue the process of typing commas after each Account. When you have selected the final Account, hit the 'Enter' key again, or click off the Row, to complete the Account lookup. Hit the 'Esc' key to close without saving
- Range - Search for the Account Name or Number that will be first in the Range, then click (or hit your 'Enter' key) to select it. Next type a colon ( : ) to enable 'Range'. Proceed to search for the Account Name or Number that will be the final in the Range, then click (or hit your 'Enter' key) to select it. Hit the 'Enter' key again, or click off the Row, to complete the Account lookup. Hit the 'Esc' key to close without saving
Row Calculations are only available on Rows where an Account has not been assigned (by any method). Create a Calculation by selecting a Row that has a blank 'Account' field, then entering the Calculation Formula in the 'Calculation' field. Formulas use the Row Number of each row to perform their calculations.
- Note: Row numbers are updated dynamically in Calculations to ensure that the Calculation remains true. If a Row that is part of a Calculation is moved or another Row is inserted before it, the respective Row Number in any Calculations will be updated accordingly.
To create the calculations, begin typing in any of the following row identifiers: Row #, Row GL Account #, Row Description. The Row lookup will display all possible results, where you must then click on one of the values to select it. You'll know you successfully selected a Row for your calculation once the value used for searching changes to 'R##' as shown below:
The following types of Calculations are supported:
- Addition - Begin typing the Row Number that you will start with in your Formula. The Row lookup will appear where you can see all Rows that contain the Row Number you typed. In addition to the Row Number, you will also see the Row Description. Select the desired Row and the value will change to 'R##'. Next type the plus sign ( + ). Continue by typing the next Row 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 Row, to complete the Calculation Formula. Hit the 'Esc' key to close without saving
- Subtraction - Begin typing the Row Number that you will start with in your Formula. The Row lookup will appear where you can see all Rows that contain the Row Number you typed. In addition to the Row Number, you will also see the Row Description. Select the desired Row and the value will change to 'R##'. Next type the minus sign ( - ). Continue by typing the next Row 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 Row, to complete the Calculation Formula. Hit the 'Esc' key to close without saving
- Division - Begin typing the Row Number that you will start with in your Formula. The Row lookup will appear where you can see all Rows that contain the Row Number you typed. In addition to the Row Number, you will also see the Row Description. Select the desired Row and the value will change to 'R##'. Next type the divide sign ( / ). Continue by typing the next Row 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 Row, 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
- Range Calculations - Ranges can be used in the various Calculation types by using the colon ':' to represent a specific range of Rows.
Dynamic Row Groups
Dynamic Row Groups can be added from the Calculation Field. They allow groups of Account Rows based on GL Type to be added and maintained all at once. To view all of the options as they pertain to the different Row Groups, enter the entire Section Header name into the Calculation field and then select the desired option from its listing.
GL Type Row Groups
The Row Calculations field includes three GL Type options that allow Rows to be built in a dynamic fashion but not hard-coded to specific GL Accounts. This enables newly-created accounts to be automatically included in a Dynamic Custom Row Layout for all Users and Row Layouts to be shared across instances that have different Chart of Accounts for Partner Channel Users. These Options include:
- GL Type Total - A Row that represents the GL Type Total
- GL Type w/ Summary - A Row with the GL Type as a Description Row and then an indented list of all Parent GL Accounts with the totals of all their Children GL Accounts
- GL Type w/ Detail - A Row with the GL Type as a Description Row and then indented Parent GL Account Description Rows with their indented Children GL Accounts and their totals. A Subtotal Row at the end of that GL Type section will display the sum of all totals for the rows within that section
When a Dynamic Row Group has been selected, restrictions will be put into place for individual Rows:
- Rows that are tied together by a Parent GL Account can only be edited as a group rather as individual Rows
- Rows will not contain a Row Context Menu for additional actions
- Rows cannot be added or deleted within a Dynamic Row Group
Other Row Group
Additional options are included in the Calculation column under the 'Other' Row Group. The listed options are only available for P&L and Combination Report Types but not for Balance Sheet or Trial Balance Report Types.
1) Number of Full Periods - This value returns the number of Full Periods included in each Column's Time Frame. This value can be used in combination with other Calculation values such as dividing Rows by the Number of Full Periods to get a Monthly Average
- Example: A YTD column based on Calendar Months that is ran on July 1st will return a value of 6.
2) Number of Weeks - This value returns the number of Weeks that each Location was open. This value can be used in combination with other Calculation values to compare Sales across weeks
- Note: If the column represents multiple Locations, the total number of weeks for each Location will then be added together