Winter 24 version

This section provides additional details about the standard financial reports and how to use Financial Report Builder (FRB).

Appendix A – Configuration Menu

This appendix provides a “crash course” in how to configure an FRB report, Using the Income Statement Details as basis.

Appendix B – Drill Configuration

Any FRB report can be configured to support Drill to underlying details/transactions. All reports in the Report Packs are predefined with Drill.

1. To configure Drill, click Drill Configuration
   
   
   
   
2. If Drill is not already available, select the dataset you want to drill to from the prompt.
   • Usually this is the same dataset as the report is based on, but it doesn’t have to be. The key component in Drill Configuration is mapping the data from the Source Table to the Target Table.
3. Define at least one Lens for the drill.

Apart from Drill Configuration, all features are the same as FRB’s Configuration Menu (Appendix A). 

Appendix C – "Do It Yourself" Approach

1. If the Income Statement or Balance Sheet has more or less details than the one Certinia uses as a basis for the Report Pack, use these two basic reports as a starting point to create reports from scratch:
   • Income Statement - Detailed (Dual)
   • Balance Sheet - Detailed - 1 Column - UK (Dual)
     
     
     
     
2. Review the report rows one-by-one, rename and/or add subtotals and/or additional lenses to specify the rollup of the financial reports for Income Statement and Balance Sheet.
3. Reconcile the data by General Ledger Account.
4. Now begin cloning the two basic reports mentioned above.
5. Create the following:
   1. Summary versions
   2. Comparative reports
   3. Dimensional reports and/or multi-company reports

Refer to Appendix A to get an understanding of how FRB’s configuration menu is used to customize reports. There are also instruction videos on Certinia Community, see Bite Size Analytics: Getting Started with Report Builder. 

Appendix D – Real Time Reporting

Salesforce CRM Analytics does not natively support real time reporting. Certina recognizes that there is a need for real time reporting, especially when Finance is doing month-end activities and creating adjustment journals, invoicing, etc. With the Spring ’23 version of FRB, it is possible to set up real time reporting, however, there are some limitations with this functionality. The data that is brought into FRB using the real time configuration comes from Certinia Accounting, usually Transaction Line Items. Only the fields in this object can be used for real time queries and therefore it is not currently possible to support full real time updates of all reports. 

The Report Packs have 5 reports that are preconfigured with real time updates: 

• Balance Sheet - Detailed Real Time, YTD vs Last YTD - UK (Dual) 
• Balance Sheet - Detailed Real Time, YTD vs Last YTD - US (Dual)
• Income Statement - Detailed Real Time - CP and YTD vs Last Year (Dual) 
• Trial Balance - Real Time - Periodic (Dual) 
• Trial Balance - Real Time - Year to Date (Dual)

Considerations Before Getting Started

The report packs consist of standard reports in each “Home” and “Dual” reporting currencies. There is also a report pack for “Reporting” currency, however, not all reports are available in the Reporting currency. The reports are preconfigured to work with Certinia’s recommended General Ledger Account (GLA) structure for Trial Balance 1-3. However, even if the GLA structure is adhering to our recommended structure, users may not want the layout provided in the report packs. 

Users may have a Functional Income Statement requirement and therefore need to combine a dimension (i.e. department) with GLA (such as defining Marketing Expenses, Selling Expenses, or Administration Expenses). Another use case could be because Income Statement or Balance Sheet have more or less details than the one Certinia uses as basis for the Report Pack. 

So, before getting started review the report layouts in this document. If the user's requirements are different from the reports in the report packs, Certinia recommends using a "do it yourself" approach as described in xRB Report Appendices using a couple of the reports from the Report Packs as starting points. 

Report Design Principles

Report Definitions

As a principle, the Financial Report Pack reports have Merged columns and are based on a structure enabling data-driven subtotals and totals. For example, see a breakdown the screenshot below: 

• The “Balance Sheet, Detailed – YTD vs LYTD” is based on Trial Balance 1, Trial Balance 2, Trial Balance 3 and GLAs
• The Analytics Lenses define the ‘query’ for each section of the report (i.e. Assets or Liabilities)
• The Dimension Columns drive the Groupings and Subtotals of the sections
• The Soft Dates define the amount to display
• The Measure Columns show the calculations for variance and variance percent. The below screenshot illustrates the design of the report.
• The Dimension Columns define the groupings within each section as well as subtotals, and in this report the Dimension Columns are: 

Each level of the report is formatted with a different shaded background and bolded text using the formatting options available in Lens Configuration, Subtotals, and Measure columns. Finally, the report columns are then merged into the final report layout.

Drill

Reports are all configured to drill to other reports and detailed transactions in Certinia. Drill reports can be configured to add or delete additional fields. See instructions in xRB Report Appendices for more information. 

Filters

All reports have relevant filters defined. Filter examples: 

xRB (Report Builder) Tool Navigation

Soft Period Selector is not relevant for all reports. It is used in reports like ‘Multi Company’ and ‘Dimensional’ where columns are not based on time/actual/budget, but reflect a subset of the data in a given dimension. For a report showing a pivot table by dimension or by company, a checkbox on the Edit button on the report header enables Show Soft Date Filter Configuration.  This field allows users to dynamically select the value shown within the pivot table. Using this selector, the report can toggle between Soft Periods like Current Year to Date, Period, Cumulative, Last Year, etc. For more details about Configuration Menu, refer to in xRB Report Appendices.

Report Details

Trial Balance Reports

The Trial Balance reports have a Retained Earnings calculated row at the bottom of the report. This row may be redundant if Year End processes have been run.

Year to Date 

Trial Balance report Year to Date, Start Balance column as opening balance at year start for Balance Sheet GLAs, and 0 (zero) for Profit and Loss GLAs.

Periodic

Trial Balance report for a given period, Start Balance column as opening balance at period start (end of previous month) for Balance Sheet GLAs, and YTD prior period Profit and Loss GLAs.

Income Statements / Profit and Loss Reports

Income Statement Report Versions

All income statement reports have two versions:

• Summary grouped and subtotaled by Trial Balance 2 and Trial Balance 3
• Summary detailed by General Ledger Account, grouped by Trial Balance 2 and Trial Balance 3

These report templates are the basis of the following reports that display different columns for reporting purposes. Only the detailed report will be shown, but both report formats are available for each income statement report. 

 Income Statement - CP and YTD vs Last Year

Income Statement - Actual vs Budget - CP and YTD

This report has two versions:

• One uses Budget as imported into Reporting Balances (displayed below is the Budget version)
• The other uses Planning Budget which uses budget data from Certinia’s Planning module

Income Statement - Multi Company

This report uses the Soft Period Selector to toggle between Year-to-date, Period, Last Year etc. The report below displays Certinia’s demo company names, but the columns are data driven so they display the companies in the specific Certinia Accounting instance. 

Income Statement - Current Year by Period 

Displays a column for each period, as defined by the Period Filter on the report.

The start and end periods do not need to be in the same year, meaning it is possible to have any number of columns representing 6, 12, 18, 24 (or other) periods. The report has a total column, summarizing all periods displayed. 

Income Statement - Current Year by Quarter

Income Statement - By Dimension 1

To create reports for other dimensions (2, 3 or 4), clone the report and change the dimension column accordingly. 

Income Statement - 1 Column

This report only has one column which is controlled by Period Date Override and a Soft Date filter to enable a different view of the financials, as Soft Date becomes a filter in itself. It is recommended to use this report as the basis for the "do it yourself" approach. 

Balance Sheet Reports 

Balance Sheet Report Versions

All balance sheet reports have two versions: 

• Summary grouped and subtotaled by Trial Balance 1, Trial Balance 2 and Trial Balance 3
• Summary detailed by General Ledger Account, grouped by Trial Balance 1, Trial Balance 2 and Trial Balance 3

Balance Sheet - YTD vs Last YTD

Balance Sheet - Multi Company

The report below displays Certinia’s demo company names, but columns are data driven, so it displays the companies in a specific Certinia Accounting instance. 

Balance Sheet - Rolling 12 

This report is using Soft Date Rolling periods and displays the first column as Current Period (P), second column previous period (P -1), third column 2 periods ago (P-3) and so on. Columns can be reordered and headers can be changed to month names. 

Balance Sheet - 1 Column

This report only has one column which is controlled by Period Date Override and a Soft Date filter to enable different view of the financials, as Soft Date becomes a filter in itself. It is recommended to use this report as the basis for a "do it yourself" approach. 

Other Reports

Cash Flow – Indirect Method

A version using Cash Flow Line Summary is available as well but this requires Cash Flow Line Summary to be configured in the General Ledger Accounts object. 

Aging as of Now Outstanding Dual Value

This report displays the outstanding Accounts Payable or Accounts Receivables as of now (meaning the time of the latest data refresh in Analytics). The report is subset to Company “Merlin Technology Inc” and General Ledger Account “1200 - Accounts Receivable”. These filters need to be changed to mirror the Company and GLA for the user's own setup. 

The underlying lens defines the Aging Buckets. These can easily be changed and/or add additional buckets.

Gross Margin Analysis

To create reports for other dimensions (2, 3 or 4) clone the report and change the dimension column accordingly. 

Report Pack Files

There are three Financial Report Pack files:

• Financial Reports Dual Currency
• Financial Reports Home Currency
• Financial Reports Reporting Currency

1. Download the file(s)
2. Navigate to Report Builder’s Share Report Definitions
   
    
   
   
3. Upload/drop the Report Pack file – and the list of reports will appear in the Available pane
4. Select the desired files and move them to the Selected pane
   • Note: users may not need all reports in a given file, so it is better to just pick the relevant ones
5. Click Import

The reports are now available in Report Builder.

 

This article provides details about the standard financial reports made available using Financial Report Builder (xRB) and the Report Pack published on Certinia Community. Reports are based on the Winter ’24 release (2024.10x) of Certinia Foundations.