4Oracle Fusion General Ledger
This chapter contains the following:
Oracle Transactional Business Intelligence Reports for GL
This topic includes details about the Oracle Fusion General Ledger reports predefined using Oracle Transactional Business Intelligence (BI).
Overview
These reports assist in reviewing journals, accounts, and balances. You can use these reports as part of your reconciliation process.
The Oracle Business Intelligence reports for the General Ledger include:
| Journal Reports | Description |
|---|---|
|
Account Analysis Report |
Provides the beginning, periodic activity, and ending balances for the selected accounts and periods. Drill down on the periodic activity balance provides journal details and transactions details including journal source, category, transaction number, and event class. |
|
General Journals Report |
Provides the journals activity for the selected period. The content can be filtered by ledger set, ledger, entered currency, approval status, date range, account, source, category, journal batch, and batch status. |
|
Trial Balance Report |
Provides the beginning, periodic activity, and ending balances summarized by balancing segment, natural account, and cost center for the selected ledger, accounting period, and currency. |
Key Insights
Before running the journal reports, verify that all subledger transactions have been imported and all journal entries for the period have been posted.
Columns
Oracle Transactional Business Intelligence reporting tool provides embedded analytics and supports online inquiry for most transactions. The selections you make are dependent on the columns that were chosen during the report definition.
The output of data in a predefined format provides little or no interaction and simplifies your reporting.
Frequently Asked Questions
The following table lists frequently asked questions about these reports.
| FAQ | Answer |
|---|---|
|
How do find these reports? |
Edit, run, and view these reports from Navigator > Tools > Reports and Analytics > Financials > General Ledger. |
|
Who uses these reports? |
|
|
When do I use these reports? |
|
|
What type of reports are these? |
Oracle Transactional Business Intelligence |
Oracle General Ledger Predefined Reports
Oracle General Ledger provides predefined reports that cover the following areas:
-
Account Analysis
-
Journals
-
Trial Balance Reports
-
Reconciliation Reports
-
Chart of Accounts
You can schedule and run reports from the Scheduled Processes work area. In some cases, you can access and open reports in the Reports and Analytics work area. Use the Browse Catalog icon on the Reports and Analytics work area to open the business intelligence (BI) Catalog. You can run and edit reports in the BI Catalog.
The following table describes the predefined account analysis reports.
| Name | Description |
|---|---|
|
Account Analysis |
|
|
General Ledger Account Details Report |
|
|
Account Analysis for Contra Account Report |
|
|
Average Balance Audit Account Analysis Report |
Note: Use to research how average balances are calculated
for an account.
|
The following table describes the predefined journal reports.
| Name | Description |
|---|---|
|
Journals Report |
|
|
General Journals Report |
Provides journal activity for a given period or range of periods, balancing segment value, currency, and range of account segment values. |
|
Journals Batch Summary Report |
|
|
Journals Details Report |
Provides information about manually entered journals prior to posting, including field by field, all data entered into the applications or data imported from external sources. |
|
Journals Day Book Report |
|
|
Reconciled Transactions Report |
Provides details of clearing account journal lines that have been successfully reconciled by both automatic and manual reconciliation processes. |
|
Unreconciled Transactions Report |
Lists all unreconciled clearing account journal lines, specifically the unreconciled lines that weren't subjected to reconciliation yet, those lines rejected by previous automatic or manual reconciliation processes, and those lines that were marked as reviewed. |
The following table describes the predefined trial balance reports.
| Name | Description |
|---|---|
|
Trial Balance Report |
Provides summarized actual account balances and activity by ledger, balancing segment, and account segment value. |
|
Trial Balance - Average Balances |
Note: Request additional information by specifying balancing
segments and account ranges.
|
The following table describes the predefined reconciliation reports.
| Name | Description |
|---|---|
|
Cash to General Ledger Reconciliation Report |
Extracts cash management and general ledger accounting and transactional data for reconciling cash management to the general ledger. |
|
Payables to Ledger Reconciliation Report |
|
|
Receivables to Ledger Reconciliation Report |
|
The following table describes the predefined chart of account reports.
| Name | Description |
|---|---|
|
Balancing Segment Value Assignments Report |
Note: The application doesn't check for overlapping balancing
segment values online.
|
|
Chart of Accounts Mapping Rules Report |
|
To run the predefined reports, navigate to the Scheduled Processes work area and follow these steps:
-
Click Schedule New Process.
-
Search for the process name.
-
Specify the parameters.
-
Enter the process options and schedule.
-
Click Submit.
General Ledger Account Analysis Reports
This topic includes details about the Oracle Fusion General Ledger account analysis reports.
Overview
The account analysis reports assist in reviewing journals and related transactions. You can use these reports as part of your reconciliation process and in preparation of your closing entries.
The account analysis reports include:
| Account Analysis Reports | Description |
|---|---|
|
Account Analysis |
|
|
General Ledger Account Details Report |
|
|
Account Analysis for Contra Account Report |
|
|
Average Balance Audit Account Analysis Report |
Note: Use to research how average balances are calculated
for an account.
|
Key Insights
Before running the account analysis reports, verify that all subledger transactions have been imported and all journal entries for the period have been posted.
Parameters
The following table describes selected process parameters for the account analysis reports.
| Parameter | Description | Example |
|---|---|---|
|
Data Access Set |
Secures access to ledgers, ledger sets, and portions of ledgers using primary balancing segment values. |
US Ledger Set |
|
Encumbrance Type |
When Balance Type of Encumbrance is selected, gives the option to select Commitments or Obligations. |
Commitments |
|
Account Type |
Indicates whether the amounts represent Base, Period, Quarter, or Year to Date. For Average Balancing, indicates Period Average, Quarter Average, or Year Average to Date. |
PATD Period Average to date balance used in average balance processing. |
|
Includes User Transaction Identifiers |
List of identifiers linking the account generation and the transaction, for example PO Number. This helps in identifying the parent transaction. |
No |
|
Account, Account Range, or Contra Account Filters and Conditions Defined |
Restricts the data reported based on the entered filter and condition. |
Company equals 101 |
|
Additional Segment |
To run the report by account and another segment. |
Cost Center |
|
Document Sequence |
Uniquely identifies each business document or business event with a number if enabled. |
Invoice |
|
From and To Document Number |
Assigned unique document sequence number. |
101 |
Frequently Asked Questions
The following table lists frequently asked questions about the account analysis reports.
| FAQ | Answer |
|---|---|
|
How do find these reports? |
Schedule, run, and view these reports from Navigator > Tools > Scheduled Processes. |
|
Who uses these reports? |
|
|
When do I use these reports? |
|
|
What type of reports are these? |
Oracle Business Intelligence Publisher |
General Ledger Journal Reports
This topic includes details about the Oracle Fusion General Ledger journal reports.
Overview
The journal reports assist in reviewing journals activity. You can use these reports as part of your reconciliation process and in ensuring completion of posting of all entries before period close.
The following figure shows an example of the Journals Report. The report has columns for the line, account, transaction date, description, entered and accounted amounts, and units.
The following table describes each journal report.
| Name | Description |
|---|---|
|
Journals Report |
|
|
General Journals Report |
Provides journal activity for a given period or range of periods, balancing segment value, currency, and range of account segment values. |
|
Journals Batch Summary Report |
|
|
Journals Details Report |
Provides information about manually entered journals prior to posting, including field by field, all data entered into the application or data imported from external sources. |
|
Journals Day Book Report |
|
|
Reconciled Transactions Report |
Provides details of clearing account journal lines that have been successfully reconciled by both automatic and manual reconciliation, |
|
Unreconciled Transactions Report |
Lists all unreconciled clearing account journal lines, specifically the unreconciled lines that were not subjected to reconciliation yet, those lines rejected by previous automatic or manual reconciliation processes, and those lines that were marked as reviewed. |
Key Insights
Before running the journal reports, verify that all subledger transactions have been imported and all journal entries for the period have been posted.
Parameters
The following table describes selected process parameters from the journal reports and provides examples.
| Parameter | Description | Example |
|---|---|---|
|
Data Access Set |
Secures access to ledgers, ledger sets, and portions of ledgers using primary balancing segment values. |
US Ledger Set |
|
From and To Accounting Date |
Determines the date on which the journal is recorded. Must be in an open or future enterable accounting period. |
10-01-2014 |
|
Includes Adjustment Period |
Includes journals posted in adjustment periods, if selected. |
No |
Frequently Asked Questions
The following table lists frequently asked questions about the journal reports.
| FAQ | Answer |
|---|---|
|
How do I find these reports? |
Schedule, run, and view these reports from Navigator > Tools > Scheduled Processes. |
|
Who uses these reports? |
|
|
When do I use these reports? |
|
|
What type of reports are these? |
Oracle Business Intelligence Publisher |
General Ledger Trial Balance Reports
This topic includes details about the Oracle Fusion General Ledger trial balance reports.
Overview
The trial balance reports assist in reviewing general ledger balances. You can use these reports as part of your reconciliation process and in ensuring completion of the period close.
The following figure shows an example of the Trial Balance Report. The report has columns for the account, description, beginning balance, debits, credits, and ending balance.
The following table describes the trial balance reports.
| Name | Description |
|---|---|
|
Trial Balance Report |
Provides summarized actual account balances and activity by ledger, balancing segment, and account segment value. |
|
General Ledger Trial Balance |
Lists detail or summary actual account balances and activity by ledger, balancing segment, and account segment: the report prints the value, description, debit or credit balance for beginning and period end, and debits and credits for period activity. |
|
Trial Balance - Average Balances |
Note: Request additional information by specifying balancing
segments and account ranges.
|
Key Insights
Before running the trial balance reports, verify that all subledger transactions have been imported and all journal entries for the period have been posted.
Parameters
The following table describes selected process parameters from the trial balance reports and provides examples.
| Parameter | Description | Example |
|---|---|---|
|
Data Access Set |
Secures access to ledgers, ledger sets, and portions of ledgers using primary balancing segment values. |
US Ledger Set |
|
Balancing Segment Filter and Condition Defined |
Restricts the data reported based on the entered balancing segment filter and condition. |
Company equals 101 |
|
Account Filter and Condition Defined |
Restricts the data reported based on the account segment filters and conditions. |
Cost Center does not equal 100 |
|
Summarized By |
Determines if the report is only summarized by the Natural Account or the Natural Account plus another segment. |
Natural Account |
|
From and To Accounting Date |
Determines the date on which the journal is recorded. Must be in an open or future enterable accounting period. |
10-01-2014 |
|
Includes Adjustment Period |
Select to include journals posted in adjustment periods. |
No |
Frequently Asked Questions
The following table lists frequently asked questions about the trial balance reports.
| FAQ | Answer |
|---|---|
|
How do find these reports? |
Schedule, run, and view these reports from Navigator > Tools > Scheduled Processes. |
|
Who uses these reports? |
|
|
When do I use these reports? |
|
|
What type of reports are these? |
Oracle Business Intelligence Publisher |
General Ledger Reconciliation Reports
This topic includes details about the Oracle General Ledger reconciliation reports.
Overview
The reconciliation reports assist in reconciling journals and related transactions. You can use these reports as part of your reconciliation with your subledgers.
The following figure shows an example of the Receivables to Ledger Reconciliation Summary report. The report includes columns for the accounting beginning balance, receivables amounts, accounting amounts, and the difference.
The following table describes the reconciliation reports.
| Name | Description |
|---|---|
|
Cash to General Ledger Reconciliation Report |
Extracts cash management and general ledger accounting and transactional data for reconciling cash management to the general ledger. |
|
Payables to Ledger Reconciliation Report |
|
|
Receivables to Ledger Reconciliation Report |
|
Key Insights
Before running the reconciliations reports, verify that all subledger transactions have been imported and all journal entries for the period have been posted.
Parameters
The following table describes some of the process parameters from the reports and provides examples.
| Parameter | Description | Example |
|---|---|---|
|
Bank Account |
Account used for your cash transactions. |
Bank of America |
|
Include Intercompany Transactions |
Include or exclude intercompany transactions or run the report for intercompany transactions only. |
Yes |
|
Include Bills Payable |
Include the transactions, formerly known as future dated payments, which instruct banks to disburse funds to a supplier's bank on a specific date. That date is known as the maturity date. |
Yes |
|
Include On-Account Items |
Include items which are credits applied to a customer's account. |
Yes |
|
Include Unapplied and Unidentified Receipts |
Include receipts that haven't been applied to a customer's account. |
Yes |
|
Account Filters and Conditions Defined |
Restricts the data reported based on the entered filter and condition. |
Company equals 101 |
Frequently Asked Questions
The following table lists frequently asked questions about the reconciliation reports.
| FAQ | Answer |
|---|---|
|
How do find these reports? |
Schedule, run, and view these reports from Navigator > Tools > Scheduled Processes. |
|
Who uses these reports? |
|
|
When do I use these reports? |
|
|
What type of reports are these? |
Oracle Business Intelligence Publisher |
General Ledger Chart of Accounts Reports
This topic includes details about the Oracle General Ledger chart of accounts reports.
Overview
The account analysis reports assist in reviewing journals and related transactions. You can use these reports as part of your reconciliation process and in preparation of your closing entries.
The following figure shows an example of the Chart of Accounts Mapping Rules Report. The parameters for the report include the chart of accounts mapping name and the tree version effective date. The report provides the account rules and segment rules for the specified mapping.
The following table describes the chart of accounts reports.
| Name | Description |
|---|---|
|
Balancing Segment Value Assignments Report |
Note: The application does not check for overlapping balancing
segment values online.
|
|
Chart of Accounts Mapping Rules Report |
|
Key Insights
Before running the chart of accounts reports, verify that all subledger transactions have been imported and all journal entries for the period have been posted.
Parameters
The following table describes selected process parameters from the reports and provides examples.
| Parameter | Description | Example |
|---|---|---|
|
Value Set |
Enter the value set that contains the balancing segments you are reporting on. |
Corporate Company |
|
From and To Balancing Segments |
Enter the description of the balancing segment value to report from and to. |
Canada |
|
Assignment Status |
Select one of the following options: All, Ledger only, Legal Entity, only, or Unassigned to |
Ledger only |
|
Actuating Configuration |
Select the ledger you want to report on. |
US Primary Ledger |
|
Show Only Balancing Segment Values with Multiple Assignments |
Show only those balancing segment values that have more than one legal entity assigned to it. |
No |
|
Chart of Accounts Mapping |
Select the mapping you have defined. |
CN code to US Mapping |
|
Tree Version Effective Date |
Select the date that determines which tree version to use as one of the report's criteria. |
8/28/2016 |
Frequently Asked Questions
The following table lists frequently asked questions about the chart of accounts reports.
| FAQ | Answer |
|---|---|
|
How do find these reports? |
Schedule, run, and view these reports from Navigator > Tools > Scheduled Processes. |
|
Who uses these reports? |
|
|
When do I use these reports? |
|
|
What type of reports are these? |
Oracle Business Intelligence Publisher |
Overview of Smart View
Oracle Hyperion Smart View provides common Word, PowerPoint, and Excel interfaces designed specifically for Oracle Hyperion Enterprise Performance Management, Oracle Business Intelligence Enterprise Edition, and Oracle Fusion General Ledger. Using Smart View, you can view, import, manipulate, distribute, and share data in Excel, Word, and PowerPoint interfaces. Smart View is a comprehensive tool for accessing and integrating Enterprise Performance Management, Business Intelligence, and General Ledger content from Microsoft Office products.
Smart View provides the ability to create and refresh spreadsheets to use real-time account balances and activity. You can use Smart View for:
-
Analysis
-
Predefined form interaction
-
Report design
Analysis
Smart View uses the Excel environment to interactively investigate the data contained in the sources. You start with templates that begin the process or a blank sheet where you begin shaping and altering the grids of data.
Predefined Form Interaction
As an Oracle Fusion user who executes predefined inputs or reporting forms, Smart View a convenient way of completing tasks within Microsoft Office. Use Smart View to work in the Excel environment either for consistent experience or to tie other spreadsheet-based models into your process. For example, use Smart View with Oracle Hyperion Planning to incorporate data that is still housed in spreadsheet and workbook-based models.
Report Design
Report design is another dimension of Smart View, which leverages the capabilities of Oracle Fusion General Ledger data. Once the data is available within Smart View you can create reports as needed based on a combination of data sources. For example, planning and financial management data can be used to compare actual to budget. Reports can be made more complex by providing the ability to compare multiple scenarios for different periods. The power of Smart View is used to create reports and is refreshed periodically, as needed.
Smart View provides the ability to create and refresh spreadsheets to use real-time account balance information. You can use Smart View to:
-
Perform ad hoc multidimensional pivot analysis with full spreadsheet functionality
-
Drill down from any parent value to the next parent or child value
-
Perform drill down from any child value to detail balances, journal lines, and subledger transactions
-
Analyze actual, budget, and forecast information
-
Increase visibility with charts and graphs
-
Apply date effective hierarchies to past, present, or future hierarchies to change the financial data reported in your financial reports. For example, to compare 2014 to 2015 results, realign the data in your 2014 reports by applying the 2015 organization hierarchy.
For more information about Smart View suppression, see the Smart View Options chapter in the Oracle Smart View for Office User's Guide at http://docs.oracle.com/cloud/latest/epm-common/SVPBC/opt_data.htm#SVPBC-options_202.
For more detailed information about Smart View, refer to the Using EPM with Oracle Financials Cloud link on the Books for Oracle Financials Cloud page of the Oracle Help Center at https://docs.oracle.com.
Creating an Ad Hoc Analysis in Smart View
This example shows how to create an ad hoc analysis in Smart View.
Creating an Ad Hoc Analysis
This example includes steps for configuring the Smart View connection, selecting dimension members, creating separate reports, and formatting amounts.-
Navigate to the Financial Reporting Center.
-
Open the Tasks panel and click the Open Workspace for Financial Reports task.
-
Remove /index.jsp from the end of the workspace URL and copy the rest of the URL. For example, copy https://adc-fapXXXX-bi.oracledemos.com, where XXXX is the unique instance ID.
-
Open Excel.
-
Navigate to the Smart View ribbon and click Options.
-
In the Shared Connections URL field on the Advanced tab, replace everything before workspace with the copied URL. For example, https://adc-fapXXXX-bi.oracledemos.com/workspace/SmartViewProviders
-
Click OK.
-
On the Smart View ribbon, click Panel. The Smart View panel opens.
-
On the Smart View panel, click Shared Connections.
-
Sign in with your user name and password.
-
On the Smart View panel, select the drop-down list for the Select Server to proceed field.
-
Select Oracle@Essbase from the list of shared connections.
-
Expand the list of cubes for Essbase_FA_Cluster.
-
Expand the USChartofAccounts cube.
-
Select db. Don't expand the selection.
-
On the Smart View panel, click Connect, and then click Ad hoc analysis.
-
On the Essbase ribbon, click POV. The Point of View window enables you to select data dimension values, so you can pinpoint the information you want for the Excel analysis.
-
On the Point of View window, click the drop-down list for each dimension and select the ellipsis (...) to open the member selector. On the Member Selection window, remove the default selection for each dimension using the Remove icon. Select the dimension value and add it using the Add icon. Click OK. Complete the member selection, as shown in this table.
Field Value Company
[All Company Values].[101]
Line of Business
10
Account
11200-Cash
Cost Center
All Cost Center Values
Product
All Product Values
Intercompany
All Intercompany Values
Scenario
Actual
Balance Amount
Period Activity
Amount Type
Period-to-date
Currency
USD
Currency Type
Total
-
On the worksheet, click the Accounting Period dimension in row 3, column A.
-
Right-click and select Smart View, Member Selection.
-
On the Member Selection window, remove the default selection. Expand the Accounting Period dimension and add the period 12-16.
-
Click OK.
-
Double-click Ledger in row 2, column B, to zoom in. You can also zoom in by selecting the dimension, navigating to the Essbase ribbon, and clicking Zoom In
-
Click All Ledgers in row 2, column B, and click Zoom Out on the Essbase ribbon.
-
Right-click Ledger in row 2, column B, and select Smart View, Member Selection.
-
On the Member Selection window, remove the default selection of Ledger.
-
Select US Ledger Set and click the Add icon.
-
Click OK.
-
On the Point of View window, click Refresh.
-
Double-click US Ledger Set in row 2, column B, to zoom in.
-
Click the [US Ledger Set].[US Primary Ledger] cell.
-
On the Essbase ribbon, click Keep Only to remove the other Ledger members and keep only the selected member.
-
To create a separate report based on the Company dimension, on the Essbase ribbon, click Cascade and select Same Workbook. The Member Selection window opens.
-
On the Member Selection window, select [All Company Values].[102] and click the Add icon.
-
Click OK. A worksheet opens for company 102.
-
Click the All Company Values.101 worksheet.
-
Click the amount cell in row 3, column B.
-
Navigate to the Home ribbon and set the Accounting Number Format option to the dollar sign.
-
Navigate to the Essbase ribbon and click Preserve Format.
-
On the Point of View window, click Refresh.
Define a Report in Smart View with the Query Designer
This example shows how to define a report using the Query Designer in Smart View.
Defining a Report with the Query Designer
-
Open Excel and navigate to the Smart View ribbon. Click Panel.
-
Click Shared Connections.
-
Sign in with your user name and password.
-
Click the Select Server to proceed drop-down list.
-
Select Oracle Essbase.
-
Expand the Essbase_FA_Cluster and expand USChartofAccounts.
-
Highlight db. Don't expand db.
-
Click Connect.
-
Click the analysis link.
-
Navigate to the Essbase ribbon and click Point of View.
-
In the spreadsheet, click cell A2.
-
On the Essbase ribbon, click Query and select Query Designer. A new sheet named, Sheet1-Query is created and the Query Designer displays.
-
On the Point of View panel, move:
-
Account to the Rows section.
-
Accounting Period to the Columns section.
-
Ledger to the Point of View section.
-
Company to the Rows section, as the first dimension.
Note: You can also select the dimensions cells on the worksheet and use the Pivot command on the Essbase ribbon to perform these steps. -
-
Open the Amount Type dimension by clicking the drop-down list and selecting the Member Selection link. Remove the default placeholder using the Remove icon. Select the period-to-date value and add it to the panel of selected values using the Add icon.
-
On the Member Selection window, click the drop-down list next to the Amount Type dimension to toggle to each Point of View dimension. Complete the fields, as shown in this table.
Field Value Ledger
US Ledger Set > [US Ledger Set].[Us Primary Ledger]
Line of Business
All Line of Business Values
Cost Center
All Cost Center Values
Product
All Product Values
Intercompany
All Intercompany Values
Scenario
Actual
Balance Amount
Period Activity
Currency
USD
Currency Type
Total
-
From the POV Sheet1-Query_1 panel, click AccountingPeriod to open the Member Selection window. You can also open the Member Selection window from the worksheet by right-clicking the AccountingPeriod cell, and selecting Smart View, Member Selection.
-
Remove the default selection. Expand 2016. Select all four quarters and add the quarters to the selected panel.
-
Click OK.
Note: The four quarters become separate columns on the worksheet. -
Click the Company dimension.
-
Expand All Company Values.
-
Select [All Company Values].[101] and [Company]@[941] and add them. Remove the default value.
-
Click OK.
-
On the POV Sheet1-Query_1 panel, click Account in the Rows section.
-
Expand All Corporate Accounts-V1 > Account@[T] > 95001 - Net Income Current Year.
-
Expand 95011-Revenue and 95021-Expenses.
-
Remove the default value. Select the following values and add them to the selected panel:
-
95011-Revenue
-
40000-Revenues
-
95021-Expenses
-
50000-Material and Goods
-
60000-Operating Expenses
-
70000-Extra Charges and Tax
-
-
Click OK.
-
On the POV Sheet1-Query_1 panel, click Apply Query.
-
Save your report to the desktop as your initials Smart View Financial Report.
-
Insert 7 rows at the beginning of the spreadsheet.
-
Click in cell A3. Click the Insert tab and select Picture.
-
Select WINNT (D) > Labs > Fin_reporting > Vision Logo.
-
Click Refresh.
Note: You must refresh periodically to maintain the connection to the database. -
From the Insert tab, click Text Box.
-
Click cell C4 and type your initials Income Statement for Companies 101 and 941. Use excel formatting to format the text however you want.
-
Click Refresh.
-
Click Save.
-
Highlight all the cells that contain amounts.
-
Navigate to the Home ribbon, and from the Number section, select dollar sign.
-
On the Home ribbon, in the Cells section, select Format > AutoFit Column Width.
-
Navigate to the Essbase ribbon and select Preserve Format.
-
Click Refresh.
-
Click Save.
-
Insert three rows between the data for company 101 and 941.
-
On the first new blank row in Column B, type Total Income.
-
Add the following formula for the first column with data Qtr1-14: =C10+C15 where C10 is 95001-Revenue and C15 is 95021-Expenses.
-
Copy and paste that formula to the other columns.
-
Do the same formula for totals of Company 941.
-
Format all the total cells as Accounting. On the Essbase ribbon, select Preserve Format.
-
Click Save.
-
Insert 10 more rows between the data for Company 101 and 941.
-
Highlight all four rows of the expense data for all four quarters, including Material and Goods.
-
Navigate to the Insert ribbon and select the Line chart from the Chart section.
-
Select the first 2-D Line chart layout.
-
On the line chart, click the Legend that reads Series1, Series2, Series3, and Series4.
-
From the Chart Tools ribbon, select Select Data.
-
In the Legend Entries (Series) pane, select Series2 and click Remove. Series2 is a total of expenses and not appropriate for this chart.
-
In the Legend Entries (Series) pane, select each series individually and click Edit. Complete the series and names fields, as shown in this table.
Series Name Series1
Material and Goods
Series3
Operating Expenses
Series4
Extra Charges and Tax
-
In the Horizontal (Category) Axis Labels panel click the first one and click Edit.
-
Click the icon next to the Axis label range: field.
-
On the spreadsheet, highlight the four quarter headers and click the icon next to the Axis Labels field.
-
Click OK twice.
-
Move and resize the line chart to fit inside the blank lines.
-
Click Save.
-
Navigate to: Financial Reporting Center > Tasks panel tab > Open Workspace for Financial Reports.
-
Click Navigate > Applications > BI Catalog.
-
Expand Shared Folders > Custom > highlight Financial Reports.
-
From the Tasks panel, select Upload.
-
Click Browse and from your Desktop, select your Smart View Financial Report.
-
Click Open.
-
Click OK.
-
Close the EPM Workspace tab.
-
From the Financial Reporting Center, click the Search icon.
-
Enter Smart and click Search.
Note: Your report may not be found immediately. Wait a few minutes and try again.
Approval Groups Report
This topic includes details about the Approval Groups Report.
Overview
You can now use the Approvals Group Report to view the approval groups defined in Oracle Business Process Management (BPM). You can view the report output in both spreadsheet and XML format.
Key Insights
You can obtain the details of the group name, the approvers, and the sequence in which the approvers appear in the approval group.
Report Parameters
There are no parameters that you need to select for this report.
Frequently Asked Questions
| FAQ | Answer |
|---|---|
|
How do I find this report? |
Schedule and run this report from the Scheduled Processes work area on the Navigator menu. |
|
Who uses this report? |
Workflow Rules Administrator |
|
When do I use this report? |
Use this report to review approval groups defined in BPM. |
|
What type of a report is this? |
Spreadsheet, XML |
Workflow Rules Report
This topic includes details about the Workflow Rules Report.
Overview
You can now use the Workflow Rules Report to view the workflow rules configured in Oracle Business Process Management (BPM) for Payables Invoice Approval, General Ledger Journal Approval, and Expense Report Approval workflows. You can view the report output in both spreadsheet and XML format.
Key Insights
You can use this report to obtain details of the stage, participant, rule status, rule conditions, and approval routing for each rule.
Report Parameters
| Parameter | Description |
|---|---|
|
Workflow |
You can select the workflow for which you want to review the workflow rules. |
Frequently Asked Questions
| FAQ | Answer |
|---|---|
|
How do I find this report? |
Schedule and run this report from the Scheduled Processes work area on the Navigator menu. |
|
Who uses this report? |
Workflow Rules Administrator |
|
When do I use this report? |
Use this report to review existing workflow rules defined in BPM. |
|
What type of a report is this? |
Spreadsheet, XML |
Create a Financial Report
Define a Basic Financial Report
You can use Financial Reporting Studio to design traditional financial report formats such as balance sheets, profit and loss statements, and cash flow reports. You can also design nontraditional reports for financial or analytic data that include text and graphics.
In this example, you're designing a basic financial report. This topic is the first in a series of six topics on designing a financial report with Financial Reporting Web Studio. Because Financial Reporting Web Studio is web-based, you don't have to download and install an application.
-
From the Financial Reporting Center work area, select the Tasks panel tab and click Open Workspace for Financial Reports.
-
On the Tools menu, select Launch Financial Reporting Web Studio.
-
On the File menu, select New, Report.
-
On the toolbar, click the Grid icon. Grids are tables that contain data from external database connections.
-
In the design canvas, draw a box to create the grid. The Database Connection Properties dialog box opens.
Tip: When creating a grid, best practice is to leave space in the design canvas for other objects, such as a company logo and report title. -
Select the Data Sources list and select the data source for the chart of accounts that the report is based on. A unique cube exists for each combination of chart of accounts and accounting calendar.
Tip: Best practice is to always turn on suppression in financial reports at the Database Connection Server level. You can verify the setting by highlighting the grid, and then selecting Data Query Optimization Settings on the Task menu. For most reports, best practice is to turn on suppression for the entire grid. Then turn suppression off for columns and rows that must always display.For more information about suppression settings, refer to the Defining Basic Conditional Suppression section in the Financial Reporting Web Studio User's guide.
-
Click OK. The Dimension Layout dialog box opens.
Arrange the Dimensions
Use the Dimension Layout dialog box to arrange the dimensions on your report. For this report, the accounting periods display on the columns and the revenue and expense account information displays on the rows. The company appears on each page and can be selected at runtime.
-
Drag the Accounting Period dimension to the Columns axis.
-
Drag the Account dimension to the Rows axis.
-
Drag the Company dimension to the Page axis.
-
Click OK to close the Dimension Layout dialog box.
Define the Rows
Use the Select Members dialog box to define the revenue and expense account rows.
-
Select the revenue parent account.
-
Double-click the Account cell. The Select Members dialog box opens with a default member selected.
-
Remove the default Account selection from the Selected area by clicking it to select it and then clicking the Remove from Selected icon.
-
In the Search field, enter the value for the account that represents total revenue and click the Search icon.
-
Select the account from the search results and click OK. The account moves to the Selected area.
-
Click OK. The Select Members dialog box closes.
-
-
Now insert a text row to add space between the revenue and expense accounts.
-
Select the last row in the grid by clicking the row header.
-
On the Insert menu, select Row, then Text.
-
-
Insert a row for the expense accounts.
-
Right-click the last row header.
-
On the Insert Row menu, select Data. Notice the default value for the new row is the revenue parent account.
-
-
Select the expense parent accounts.
-
Double-click the account value in the new expense account row. The Select Members dialog box opens with the revenue parent account selected.
-
Remove the revenue parent account selection from the Selected area by clicking to select it and then clicking the Remove from Selected icon.
-
In the Available area, expand the Account member, and continue expanding until you find the expense parent accounts for the report.
-
Select the accounts and click the Add to Selected icon to move them to the Selected area.
-
Select the Place selections into separate rows option so each account appears in its own row on the report.
-
Click OK. The Select Members dialog box closes.
-
Save and Preview the Report
Save the report and leave it open for the next topic, which is adding a formula to a financial report.
-
Click the Save icon.
-
Select the folder with your name and enter the report name and description.
-
Click Save.
-
Optionally preview the report in HTML or PDF format using the File menu or toolbar.
Add a Formula to a Financial Report
In this example, you define a formula to summarize the expense account balances on your financial report. This topic is the second in a series of six topics on designing a financial report with Financial Reporting Web Studio.
Before you start, do the steps described in the Define a Basic Financial Report topic, then follow these steps.
-
Right-click the last row header, select Insert Row and select Formula.
-
Click in the empty cell in the new row.
-
In the Heading Row Properties pane, select the Custom Heading option, enter Total Expenses and click the Update icon. The new heading appears in the report.
-
Select the row header for the formula row. The SUM function appears in the design canvas.
-
In the Formula bar, click the Sum(0) button and enter the formula and cell references in the formula text box.
Because the expense rows appear one after the other, you can use the first row number and the last row number with a colon in between. For example, Sum([3:5]). If the rows weren't contiguous, you could put brackets around each row number and separate them with commas. For example, Sum([3], [5], [6]).
-
Validate the formula syntax by clicking the check mark icon in the toolbar. Validation checks the validity of the formula, not if the data is available.
-
Save the report and leave it open for the next topic, which is defining a range function. Optionally preview the report.
Define a Range Function for a Financial Report
In this example, you define a range function to report across multiple accounting periods. You configure the range to present balances for the last 12 months from the period selected at runtime. This topic is the third in a series of six topics on designing a financial report with Financial Reporting Web Studio.
Before you start, do the steps described in these topics.
-
Define a Basic Financial Report
-
Add a Formula to a Financial Report
Now follow these steps.
-
Double-click the Accounting Period cell. The Select Members dialog box opens.
-
Remove the default accounting period from the Selected area by clicking it to select it and then clicking the Remove from Selected icon.
-
Click the Functions tab.
-
Select Range from the list.
-
Click the Add to Selected icon. The Range dialog box opens.
-
Define the starting member for the range.
-
On the Start Member row, click the Lookup Selection icon in the Value column.
-
Click the Functions tab.
-
Select the Relative Member function to define the periods that display on the report relative to the period specified at runtime.
-
Click OK. The Relative Member dialog box opens.
-
On the Member row, click the Lookup Selection icon in the Value column.
-
Select Current Point of View for Accounting Period so you can enter the starting period for the report.
-
Click OK.
-
On the Offset row, enter -11 in the Value field.
The offset determines the first period of the range. The starting period in the range function is always the oldest period. Because this is a rolling 12 period report, enter -11 to include the 11 periods prior to the period you enter at runtime. The member selection for the End Member parameter determines period 12.
-
Click OK.
-
-
Define the ending member for the range.
-
On the End Member row, click the Lookup Selection icon in the Value column.
-
Select Current Point of View for Accounting Period.
-
Click OK.
-
Click OK to close the Range dialog box.
-
Click OK to close the Select Members dialog box.
-
-
Save the report and leave it open for the next topic, which is defining a grid point of view. Optionally preview the report.
Define a Grid Point of View for a Financial Report
In this example, you set user and grid point of views for a financial report. This topic is the fourth in a series of six topics on designing a financial report with Financial Reporting Web Studio.
All financial reporting reports have a user point of view and a grid point of view. Best practice is to use a combination of both.
If you want users to select certain dimension members at runtime, then those dimensions should be set in the user point of view. Selections for user point of view members are global for a user and data source. This means the application saves and applies them to any other report that has the same dimensions set to the user point of view. By default, all dimensions are set to the user point of view and must be selected at runtime. If you want your report to always use certain dimension selections, then select the specific members in the grid point of view.
In this example you set the Ledger, Scenario, Balance Amount and Currency dimensions to use the grid point of view.
Before you start, do the steps described in these topics.
-
Define a Basic Financial Report
-
Add a Formula to a Financial Report
-
Define a Range Function for a Financial Report
Now follow these steps.
-
Select the cell in the grid that represents the intersection of the rows and columns. The Grid Properties pane opens.
-
In the Grid Properties pane, click the Grid Point of View check box.
-
In the design canvas, click the Ledger: User Point of View for Ledger button. The Select Members dialog box opens.
-
Expand the Ledger member and continue to expand until you find the ledger to include on the report. Select the ledger.
-
Click Apply Selection.
-
Select Scenario from the Dimension list to select the type of balance to use on the report.
-
Expand the Scenario member and select Actual.
-
Click Apply Selection.
-
Select Balance Amount from the Dimension list.
-
Expand the Balance Amount member and select Period Activity.
-
Click Apply Selection.
-
Select Currency from the Dimension list.
-
Search for USD.
-
Click OK to accept the search result.
-
Click Apply Selection.
-
Click OK to close the Select Members dialog box.
-
Save the report and leave it open for the next topic, which is setting page and grid properties. Optionally, preview the report.
Set Page and Grid Properties for a Financial Report
In this example, you change the grid point of view setup and set the page member selection to a prompt on your financial report. This topic is the fifth in a series of six topics on designing a financial report with Financial Reporting Web Studio.
Before you start, do the tasks described in these topics.
-
Define a Basic Financial Report
-
Add a Formula to a Financial Report
-
Define a Range Function for a Financial Report
-
Define a Grid Point of View for a Financial Report
Now follow these steps.
-
Click the first cell in the grid to select all of the rows and columns.
-
Right-click and select Grid Point of View Setup from the list. The Setup Grid Point of View dialog box opens.
-
To prevent the Balance Amount dimension from being changed at runtime, select the Lock Member Selection option.
-
Click OK. The Setup Grid Point of View window closes.
-
-
In the Grid Properties pane:
-
Click the Drill Through option to allow drilling from the report to the General Ledger transaction data.
-
Click the Suppression section to view the suppression settings.
-
Enter 0 in the Zero Values field to set the text option for rows with zero values. If necessary, you could also suppress the display of rows with zero values, rows with missing data, and rows with errors.
-
-
Set a runtime prompt for the Company dimension so you have the flexibility of selecting any company or combination of companies at runtime. In this example, you want to restrict the valid list of companies that can be selected at runtime.
-
On the grid, click the Pages label. The Company dimension appears in the design canvas.
-
Click the Company button. The Select Members dialog box opens.
-
Remove the default Company selection from the Selected area by clicking it to select it and then clicking the Remove from Selected icon.
-
Select Prompt for Company.
-
Click the Add to Selected icon to move the selection to the Selected area.
-
Click OK. The Define Prompts dialog box opens.
-
Click the Lookup icon in the Choices List field. The Select Members dialog box opens.
-
Remove the default Company selection from the Selected area by clicking it to select it and then clicking the Remove from Selected icon.
-
Expand the Company member, and continue expanding until you find and select the default companies you want to display in the prompt.
-
Click the Add to Selected icon to move the companies to the Selected area.
-
Click OK. The Select Members window closes.
-
On the Define Prompts dialog box, click in the Member Name field and select Alias. The actual company label displays in the prompt list of values instead of a numeric company value, making the prompt more user-friendly.
-
Click OK. The Define Prompts window closes.
-
-
Click the Pages label on the grid. The Page Properties pane opens.
-
In the Page Properties pane, select the Alias: Default option so each page of the report shows the name of the company instead of the number.
-
-
Select the four data rows in the grid so all of the rows have the same settings.
-
In the Heading Row Properties pane, select the Alias: Default option in the Heading Row Properties pane to display the revenue and expense account names instead of the accounts.
-
In the Heading Row Properties pane, select the Allow Expansion option so you can expand the parent account values to view the detail child values.
-
-
Save the report and leave it open for the next topic, which is formatting your financial report and adding a graph. Optionally, preview the report.
Format a Financial Report and Add a Graph
In this example, you add formats and graphs to a financial report. This topic is the sixth and final topic in a series of topics on designing a financial report with Financial Reporting Web Studio.
Before you start, do the steps described in these topics.
-
Define a Basic Financial Report
-
Add a Formula to a Financial Report
-
Define a Range Function for a Financial Report
-
Define a Grid Point of View for a Financial Report
-
Set Page and Grid Properties for a Financial Report
Now follow these steps.
-
In the report object browser, select the name of the report, which is the first object.
-
Add a logo.
-
In the report layout, drag the header line to make space for the logo and title.
-
In the Header section, click the Add Report Object icon and select Image.
-
In the Image Properties pane, click Browse and select your company logo.
-
-
Add a title.
-
Select the name of the report in the report object browser.
-
In the Header section, click the Add Report Object and select Text. As an alternative, you can click the Text icon in the toolbar and draw the text box. The text box opens.
-
Enter the report title.
-
Select the title text and use the format toolbar to adjust the font size, center the text, and change the font style to bold.
-
-
Change the page orientation.
-
Select the name of the report in the report object browser.
-
On the File, menu, select Page Setup.
-
In the Page Setup dialog box, select the Landscape option.
-
Click OK.
-
-
Add a chart.
-
In the Body section, click the Add Report Object icon and select Chart.
-
In the Chart Properties pane, select the Line chart type.
-
To show only the expense account rows, deselect row 1, and select rows 3, 4, and 5 in the Data Range section.
-
Click the Format Chart button. The Format Chart dialog box opens.
-
In the Appearance tab, enter the title for the chart. For example, Expenses by Period.
-
Click the Legend tab and enter a title for the legend. For example, Type of Expenses.
-
Click the Axes tab and enter a title for the Metadata axis. For example, Period. Enter a title for the Primary Axis. For example, Dollars.
-
Click the Refresh Chart button to preview the chart on the Format Chart dialog box.
-
Click OK. The Format Chart dialog box closes.
-
-
Save the report and optionally, preview it.
For more information about Financial Reporting Web Studio, select the Using EPM with Oracle Financials Cloud link from the All Books for Oracle Financials Cloud page of the Oracle Help Center at https://docs.oracle.com.
Inquiries
View an Account Group from the Financial Reporting Center
This example shows how to view an account group from the Financial Reporting Center.
Viewing an Account Group
-
Navigate to the Financial Reporting Center.
-
Click the Sales Organization account group link.
-
Change the period to 03-17. The balances are automatically updated.
-
Click Show/Hide Segments.
-
On the Account segment, select Value and Description.
-
For the Product and Intercompany segments, clear Value.
-
Click Done.
-
Click View as Sunburst.
-
Click Show Description.
-
Select any cost center segment and click Move Ring Toward Center.
Note: You can also use the Segments section to move the rings. -
Click Done.
Inquire on GL Balances
Balances are preaggregated at every level of your account hierarchy and updated when a journal is posted. To inquire and analyze on real-time balances:
-
Access both active and prepublished reports and books using the Financial Reporting Center.
-
Review summary and detail information at any point in your account hierarchy using different points of view.
-
Drill down from the parent and detail levels into balances and transactions.
Drill Down to Balances
Use the Account Inspector to expand and drill down into an account balances. The default Account Inspector view includes the member values from the default account group selected in the Account Monitor.
-
Click the Navigator > Financial Reporting Center link.
-
Select one of the accounts in the Account Monitor.
-
Click the Inquire and Analyze Balances link.
-
Select:
-
All Cost Center Values
-
Your account
-
Company: All Company Values
-
All other segments fill with 0's or accept the default
-
-
Select an Accounting Period.
-
Click Refresh.
-
Click Expand on the Cost Center.
-
Review the balances.
-
Change the accounting period to see how the balances change.
How You Inquire on Detail Balances
You can run account inquiries using the Inquire on Detail Balances page. To get to the page, select the Inquire on Detail Balances task in the Period Close work area. You can also drill through to the page from Financial Reporting or Smart View.
Search Criteria
Here are some points to consider when entering search criteria.
-
Your data access set assignments determine the ledgers and ledger sets you can search on.
-
You can search only on accounting periods, not on years or quarters.
-
You can search any account hierarchy level in the chart of account fields, as long as the account hierarchies (tree versions) are published to the balances cube. If the search times out before returning results, set parent values for the chart of account dimensions to lower levels.
Saved Search
If you search often using the same criteria, create a saved search.
-
Enter your search criteria.
-
Click Save.
-
Enter a name for the search.
-
Optionally, make the saved search the default search. When you land on the Inquire on Detail Balances page, the default saved search criteria is populated automatically.
-
Click OK.
Search Results
Here are a few things you can do with the search results.
-
Drill to the journal lines (for actual balances) and then drill to the journal entry or subledger transactions.
-
Export the balances to Excel.
-
Hide, show, and manage the columns using the View > Columns and View > Columns > Manage Columns menu. For example, descriptions for chart of accounts dimensions are hidden by default. You may decide to display descriptions for the company, cost center, and account dimensions.
Drill Through from Financial Reporting and Smart View
If you're getting to the Inquire on Detail Balances page by drilling through from Financial Reporting or Smart View, consider these points.
-
Your data access set assignments determine whether you can drill through to the page.
-
Best practice is to set the point of view settings for chart of account dimensions to the lowest possible parent or detail value to avoid a timeout.
-
If you're drilling into a year or quarter Accounting Period dimension, the accounting periods are converted to the applicable range of accounting periods on the Inquire on Detail Balances page.
-
Your selections for rows, columns, page, and point of view display in the page search fields. You can refine the search criteria as needed and rerun the search.
-
From the inquiry page, you can drill to journal lines (for actual balances) and then drill to the journal entry or subledger transactions.