In order to bring you the best possible user experience, this site uses Javascript. If you are seeing this message, it is likely that the Javascript option in your browser is disabled. For optimal viewing of this site, please ensure that Javascript is enabled for your browser.
Login  |   On Demand  |   Home  |   qad.com




QAD BI Portal CFO Dashboard
This chapter provides information about configuring the QAD BI Portal CFO Dashboard application.
It discusses the following topics:
QAD Standard CFO Dashboard
Describes how to configure the CFO Dashboard.
QAD Standard CFO Dashboard
The QAD CFO Dashboard is one of the standard elements that ships with the QAD Business Intelligence Portal. The CFO Dashboard is designed to collect in one easy-to-view place the important financial metrics for your organization so that the financial health of the organization can be quickly understood.
Since GL account structures vary widely from company to company, the data elements of the CFO dashboard require initial configuration. The Dashboard’s visual elements are already set up in the Portal, and are ready to display graphs and charts once the data configuration is complete.
Configuring the CFO Dashboard
Metrics on the CFO dashboard are generated from report lines defined in the QAD BI database. Report lines are like the lines of a balance sheet or an income statement, representing a range, or ranges, of GL accounts. Once defined, report lines can be rolled up into groups and computations can be performed on the groups to generate the various metrics and data that appear on the CFO dashboard.
The CFO Dashboard report lines are defined and maintained with the help of an Excel workbook, which creates a set of text files that are used in the nightly QAD BI data load. Using Excel allows finance managers and analysts to maintain the financial metrics themselves and gives them the most freedom in creating and defining new metrics and financial reports.
The CFO Dashboard workbook, cfo_dashboard_template.xls, is located in the metadata installation package. This workbook consists of four tabs, all of which are used to build the configuration data for the CFO dashboard and its charts:
Reports. This tab contains the highest level of information to be displayed. This page lists the name of the report sets that the CFO dashboard describes. One set is given by default: CFO_METRICS. Multiple reports, or versions of reports, can be included in the same spreadsheet.
Report Lines. This tab defines the lines of each report. Report lines can be labels (which display text), data lines, which are rollups of GL accounts, or calculations, which are rollups of data lines. The CFO_METRICS report in the default configuration is broken down on the Report Lines page into 16 different lines, such as Inventory and Net Revenue.
Report Line Ranges. This tab maps the Report Lines to specific accounts in the General Ledger.
Important metrics for each report are not summaries of accounts but more complicated combinations of the report lines.
Report Calculations. Allows user to combine the lines of the report arbitrarily to create derived metrics.
These tabs are described in more detail in the next sections. Before configuring the CFO dashboard, however, start with a specific goal in mind to properly set up the configuration elements.
Metric Example
Consider the example of an organization that wants to see a metric like debtor days, which are the average number of days that a payment takes. The debtor days metric can be computed on any account by taking the total amount in accounts receivable, divided by net revenue received, and then multiplied by the number of days in the fiscal year. Furthermore, compute this metric by region so that you can see the measures for the North American and Pacific Rim regions.
To do this, first set up the accounts that you want to total. Specify the AR accounts in both North America and the Pacific Rim, and then set up the revenue received in those accounts.
After these account lines are set up, you can create the computation to display on the final dashboard.
Reports Tab
For this example, the reports tab does not change. This report can be collected in the set for the standard CFO_METRICS.
If the system calls for numerous reports that need to be divided at the highest level, add another element to the reports tab for that highest level grouping. That new element is then carried over to the Report Lines tab.
ReportLines Tab
This tab holds the high-level groupings on which the computations in the reports are performed. In this specific example, four lines must be defined: Accounts Receivable for both North America and the Pacific Rim, and revenue received for both regions.
These accounts are created by setting the report_name column to the name given on the first tab. In this example, this is CFO_METRICS.
The line_number column must be unique for each entry on this page, and it describes to the subsequent tabs how to reference this account line.
Line_type can have the value label, calc or data. A label type is the text placed into the visual item on the CFO dashboard for this line. A row can be marked either calc for a computed column as described in the upcoming Calculations tab, or data to indicate that this line’s data is used in a computation.
Line_text is a description of the account. Set Indent_level to 0 for a label, and 1 otherwise. To make items visible to the end user, enter Y in the visible column.
All computations on a report line are done on the value of the report. Because the report computations are additive and you must subtract values of one line from another, you can specify that a line is a negative value in the report lines tab by placing a Y in the change_sign column.
In this specific example, subtract the line of net revenues from the total accounts receivable for a region. When you specify the received revenue in the report lines tab, change its sign to a negative by putting a Y in the change_sign column for both the North American and Pacific Rim Net Received lines.
ReportLineRanges Tab
The report line ranges tab allows the user to group the accounts required for each report line.
Accounts in the GL are listed on the report line ranges tab sequentially to generate the higher level reporting accounts. Enter the report line name (CFO_METRICS) in the first column. The line_number column corresponds to the report line in the second tab. Range_type can take the value of include or exclude.
The remaining columns specify the characteristics range of accounts to include in the summarized report line. The following is a description of each column:
Source_system_code. Name of the source system in the QAD BI database. Most implementations have a single source system.
From_entity / to_entity. Entity codes that are processed, starting at from value and going until to value.
From_account / to_account. Account codes to process.
From_subaccount / to_subaccount. Sub accounts to process.
From_department / to_department. Departments to process.
From_project / to_project. Project indicators to process.
In this specific example, the accounts for the four elements of the computation are defined. Report Line 2, North American AR (East) is defined as 4000 to 4100, excluding accounts 4003 to 4005. Report Line 3, North American AR (West) is defined as 5010 to 5020. The source system for both is QAD_US. Line 4, Pacific Rim AR, is defined as accounts 1600 to 1700 for source system for both is QAD_PACRIM. The example tab continues to define the account ranges and source systems for all five data lines of the CFO_METRIC report.
ReportLineCalculations Tab
This tab defines the computations that are performed on the report lines as defined on the Report Lines tab. By combining the report lines, the higher-order metrics desired can be determined.
Each line in the ReportLineCalculations tab specifies part of a computation. The data into which the computation is placed is specified in the line_number of type calc specified on the ReportLines tab. The computation steps are processed one at a time, in an order specified by each line’s calc_seq. For each specific report_name and line_number, the calc_seq must be unique.
A step
Report_name - CFO_METRIC
Line_number - refers to the ReportLines tab, line_number. Must be a calc type.
Calc_seq - order in which computations should be performed
Operator - add or multiply
Source_line - refers to a data line in ReportLines
Providing the Workbook Data to the Data Warehouse
Once the data has been entered into the workbook as described, translated the data into a format that the data warehouse processes can operate on to build the required elements for the CFO dashboard.
Each sheet in the workbook contains a yellow section of cells that is built automatically as the data is filled out. Place this section of cells into a text file and that text file moved to a location specified in the data warehouse.
Find the location in which the text files must be placed by double-clicking the load tables in QAD BI DWD and noting the directory location in the File Path field. By default, the location is set to c:\work\QADReport, but it can be changed to match the locations required by any site.

File Path
The frniles to be used, by default, are
Reports tab - Reports.txt
Report Lines tab - ReportLines.txt
Report Line Ranges tab - ReportLineRanges.txt
Report Line Calculations tab - ReportLineCalculations.txt
Once the files are in place and the QAD Warehouse daily jobs run, the new computations are reflected into the data warehouse and the CFO dashboard contents are displayed. The daily jobs are usually run overnight.