R365 Custom Financial Report Writer: Report Rows

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.

Create 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 clicking 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

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

Close 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
   

Row Layout Usage

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

Row Formatting

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:

1. Bold

2. Italics

3. Underline

4. Font Size

5. Font Color

6. Fill Color

7. Border

8. Autofit

9. Height

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 and 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:

Multi-Select Actions

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 - Creates copies of the selected rows

• Flip - Flips the sign on the selected rows 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

  • Equity

  • Long Term Liability

  • Sales

• Hide - Hides the selected rows 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 - Deletes the selected rows and removes them from the Template

• % Of Row- Updates the '% Of' entries for the selected rows

  This will only be listed for P&L and Combination Reports

• Row Type- Updates the 'Row Type' entries for the selected rows

  This will only be listed for Combination Reports

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

   Note: The option to 'Hide %' can also be selected from this field

5. Options - This column lists the following row options:

   • - Click the Flip Sign icon to change the sign on this row to negative ( - ). Once flipped, the icon will appear with a slash through it 

   • - Click the Hide icon to hide the row when running Reports. Once hidden, the icon will appear with a slash through it 

   •  - Click the Org Unit icon to filter and return the row's data for specific Org Unit(s). These can be chosen in the 'Org Type' and 'Org Values' drop-down selectors that appear when this icon is clicked on a selected row, as shown below

     This is not available on rows that belong to a Dynamic Row Group

     

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

Row Accounts

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

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.

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 will 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:

1.  Rows that are tied together by a Parent GL Account can only be edited as a group rather as individual rows

2.  Rows will not contain a Row Context Menu for additional actions

3.  Rows cannot be added or deleted within a Dynamic Row Group

'Other' Row Groups

Additional options are included in the calculation column under the 'Other' Row Group. The listed options are only available for certain report types.

1. Net Income - This is only available on Balance Sheet and Combination Report Types. This value returns the Net Income for the Column's Time Frame

2. Number of Full Periods - This is only available on P&L and Combination Report Types. 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

3. Number of Weeks - This is only available on P&L and Combination Report Types. 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

   If the column represents multiple Locations, the total number of weeks for each Location will then be added together 

4. YTD Income - This is only available on Combination Report Types. This value returns the Year to Date Income