CHAPTER
13
Building Financial Reports and Analysis Dashboards
Oracle Fusion Financials contains different types of reporting tools, such as Fusion Financials Reporting Studio, Smart View, Account Monitor, Account Inspector, Oracle Transaction Business Intelligence (OTBI), and Business Intelligence Publisher (BI Publisher).
It is important to understand the reasons why Oracle has incorporated various reporting methods within the same product and how these methods work while understanding their use cases.
Different Types of Reports in Fusion Financials
Every enterprise can have various different types of roles that want to do reporting. For example, the CFO, Controller, General Accounting Manager, General Accountant, and Financial Analyst would all be the consumers of the financial reports, but each has a different reporting need. Some want reporting to manage day-to-day operations, some want to perform an analysis of how different lines of business within an organization are performing, and some want to publish the financial state of the organization to the regulators.
Broadly speaking, the reports in Fusion Financials can be categorized into the following different types:
Report documents (cannot be changed once published)
Printed boardroom-ready financial reports
Local statutory reports
Printed operational or transaction reports
Interactive reports (published reports to which you can make certain changes)
Online interactive financial reports
Exception-based financial reporting
Transactional reporting
Ad hoc analysis (reports that allow ad hoc queries for retrieving data)
Excel-based ad hoc analysis (some people like to use Excel)
Web-based ad hoc financial analysis (some people like to use the Web)
Ad hoc operational analysis
The usage of these different reports in the context of Fusion Financials is shown in Figure 13-1.
FIGURE 13-1. Different types of reports in Fusion Financials
Data Sources in Fusion Financials
Oracle Fusion Financials has primarily two types of data sources. The first are multidimensional cubes, and the second are the traditional relational database tables for operational and transactional reporting. In Fusion Applications, multidimensional reporting is made possible because the GL balances are embedded within Fusion General Ledger, using the Essbase multidimensional cubes. This multidimensional data structure allows the users to perform multidimensional analysis on live data. It allows users to pivot and drill to any level from anywhere within the date-effective hierarchies. The multidimensional balances are updated during the GL Posting process, thus ensuring accurate reporting on live balances. You can also drill down into the underlying journals and transaction details from the multidimensional balances.
The multidimensional cube is automatically set up when you create a Chart of Accounts in Fusion General Ledger. If you add a cost center or modify a hierarchy, then Oracle automatically creates or modifies the cube hierarchy in Essbase. There is no batch program that has to be run to update the cube balances. As when you post the journals or transactions, Fusion General Ledger automatically posts them to the analytic cube, ensuring that the balances are always in synch. The balances are also pre-aggregated at every possible summarization level. You can analyze real-time balances by slicing and dicing different dimensions. You can pivot from segment to segment and drill into any Chart of Accounts segment from any level in the hierarchy.
Given the different needs of different roles and reporting data sources, Oracle has produced six different tools for reporting in Fusion Applications, as listed in Table 13-1. In this chapter we will learn about each of these reporting tools.
TABLE 13-1. Reporting Tools in Fusion Applications
Financial Reporting Studio Fusion Edition
Financial Reporting Studio is a Hyperion suite of tools that is used to produce high-resolution boardroom-ready reports. These reports can be accessed via the Financial Reporting Center in Fusion Applications. You can publish these reports in PDF format or HTML or Excel output. You can either use this tool to report on live data, or you can use this tool to view the data at a specific point in time. You can either make the reports a static PDF, or you can make them interactive in HTML, allowing you to expand from one parent value to another level in the hierarchy and also drill down into transactions.
You can also group the reports into books to view them at the same time, or you can group them into batches to schedule them at different times. For example, you may run the report for different lines of business and send the report to the CFO of that line of business. Likewise, you may want to run some reports at the cost center level so that different cost center managers can receive reports of the financial activities of their team via emails.
Financial Reporting Studio is a client-based report-authoring tool where you can develop the reports for the Financial Reporting Center. This tool needs to be installed on your desktop, following which you can drag and drop to insert titles, logos, graphs, and columns and introduce calculations. The tool allows you to insert rows and columns, with column values being sourced from data, or formulas or static text. You can select dimension members and add calculations or mathematical functions. This is a very easy-to-use tool, and it allows you to create a basic balance sheet or income statement within 15 to 20 minutes.
The reports developed and deployed via this tool can be accessed from the Financial Reporting Center in a browser, which allows you to access all your reports from the repository. The Financial Reporting Center ensures that different people will see different sets of reports to which they have been granted access. You can give access to senior executives, and they can view the reports in PDF or HTML format. In either case, the reports will display the latest balances from Fusion General Ledger.
Some reports can contain multiple components; for example, the first section of the report can contain the Financial KPIs such as Return on Assets (ROA) and Return on Equities (ROE), the second section can display the expense analysis, and a third section can show a graph for profit trends by the line of business.
The reports developed in Financial Reporting Studio will have some default dimensions such as Ledger or Ledger Set, Accounting Period ranges, currency, and the segments in the Chart of Accounts. The Reporting Center gives you the capability to search across any dimension and provides an easy front end to apply further filters on the results via the browser. In addition, a drill-down can be made available out of the box from the balances in the GL cubes to the accounting transactions in Subledger. The Reporting Center also allows further drill-downs from the accounting transactions into the Subledger transactions such as invoices, payments, purchase orders, and so on.
The Financial Reports Center functionality includes
Running live reports and books in various formats
Viewing published snapshot reports from scheduled batches in various formats
Creating embedded charts and graphs
Refreshing report data using run-time points of view or parameters
Expanding or drilling down from any parent value to the next parent value or to the child value
Expanding or drilling down from any child value to detail balances, journal lines, and Subledger transactions
Building multidimensional reports, with multiple hierarchies, using a client-based tool, Oracle Hyperion Financial Reporting Studio, Fusion Edition
Distributing reports automatically across your organization using email or other distribution mechanisms
Storing reports in a repository folder structure, using various formats, including PDF, HTML, and spreadsheets
Installing Financial Reporting Studio
You can download the reporting tool from Fusion Applications itself. Navigate to Navigator | Financial Reporting Center | Open Workspace For Financial Reports. In the Workspace, choose Tools | Install | Financial Reporting Studio as shown in Figure 13-2.
FIGURE 13-2. Installing Financial Reporting Studio from Fusion Applications
At the time of writing this book, the Workspace is compatible only with Internet Explorer and Firefox browsers. After completing the install, launch Financial Reporting Studio and provide your Fusion Applications User ID, Fusion Applications password, and the server URL. Your Fusion Applications administrator should be able to provide the URL for the Financial Reporting Studio Business Intelligence server. Typically this will be http(s)://<hostname>:<PortNumber>. You must contact your administrator for the port number.
Compatibility with Internet Explorer
Internet Explorer (IE) Version 9 is supported by Fusion Applications Release 7. IE 10 and above are supported by Fusion Applications Releases 8 and 9. Ensure that the Workspace web server name is added to the list of Trusted Sites in Internet Explorer, using the browser menu: Internet Options | Security | Trusted Sites | Sites.
Compatibility with Firefox Browser
For Firefox, an add-on named Remote XUL Manager must be installed from https://addons.mozilla.org/en-US/firefox/addon/remote-xul-manager/.
Once this add-on has been installed, navigate to the Developer menu in Firefox, and for Remote XUL Manager, click Add and enter the URL for the Fusion Applications server. For step-by-step instructions, see Oracle Support Note Using Hyperion Workspace With Recent Firefox Versions (Doc ID 1447453.1).
Build GL Report Using Financial Reporting Studio
After the Financial Reporting Studio Fusion Edition has been installed, you need to use your Fusion Applications username and password to log in to the reporting tool, as shown in Figure 13-3, and click OK.
FIGURE 13-3. Log in to Financial Reporting Studio using your Fusion Applications credentials.
After logging in, right-click anywhere on the tool pane, and select New | Report as shown in Figure 13-4.
FIGURE 13-4. Creating a new report in Financial Reporting Studio
Following the step in Figure 13-4, you will be presented with a blank template where you can define the regions, text, chart, and images as shown in Figure 13-5.
FIGURE 13-5. Creating a new region by selecting New Grid
To create a new region, right-click anywhere on the template and select New Grid. Next, click your mouse and drag it to release it so as to create a rectangular area where the region will be built. After the rectangular region has been defined, you will be prompted to enter your credentials and select the database. The database that you select here is the Essbase database that corresponds to your GL Chart of Accounts Instance, as shown in Figure 13-6.
FIGURE 13-6. Selecting the Essbase database to be used for reporting
After selecting the Essbase database for your GL Chart of Accounts instance, you can drag and drop components into the pane to build your report, as shown in Figure 13-7.
FIGURE 13-7. Drag and drop dimensions from Point of View into columns, rows, and pages.
Click and drag the Company into the Pages area, Account into the Rows area, and Accounting Periods into the Columns area. Select the cells in the grid for Account, Company, and Accounting Period. For every cell that you select, its corresponding Heading Row Property window will be displayed on the right-hand side. If you wish to allow drill-down from a cell, then select the check box Allow Expansion to allow drill-downs into the hierarchy nodes. For example, you may want to drill down from Quarter into a period, and from the parent Asset account value to an individual asset account to see the breakdown of the balances. This property can be set as shown in Figure 13-8.
FIGURE 13-8. Property grid cell and entire grid
In order to restrict the dimension values shown in the report, select the relevant dimension cell within the grid, and then click the button in the top-left corner, for example, the Account button in Figure 13-8. This will open the Select Members window as shown in Figure 13-9, allowing you to select the member values in that dimension. Bring the dimension values over by shuttling them to the right-hand side. You can select the option All Company Values or All Account Values depending on the dimension for which you are selecting the members. Alternatively, click the + icon within the dimension and select individual members before shuttling them to the right-hand pane. You need to do this step one by one for each dimension of interest as shown in Figure 13-9.
FIGURE 13-9. Selecting dimension members for reporting
There are many advanced features, such as conditional formatting and suppression of duplicates, which can be set up by selecting the property of the member.
After the report has been developed, click Save and you will be presented with folders to save into. These folders are the folders on the Fusion Applications BI server as shown in Figure 13-10. You can make the report private by saving it into My Folders, or you can save the report within the Custom folder as shown in Figure 13-10.
FIGURE 13-10. Saving a Financial Studio report on the server
Once you have developed your report, you can click File | Preview HTML or click the icon as shown in Figure 13-11, and it will run the report and show the report with all the points of view at the top to allow slicing and dicing of the report output. As shown in Figure 13-8, you can select the entire grid and select the property of the entire grid. When you select the entire grid, it then allows you to select Text Options. Here you can override the text that is shown to the user when the balance for an account is zero, or if no balance exists, or the text string seen when in error. It is common for you to replace #ZERO with 0, and #MISSING with 0 in the grid properties.
FIGURE 13-11. Running a Financial Studio report in preview mode
To view the report from Fusion Applications, first log in to the application, then click the General Accounting tab, and then click View Financial Reports to see the report saved from the tool as shown in Figure 13-12.
FIGURE 13-12. Running a Financial Studio report from Fusion Applications
Account Monitor
Account Monitor, as the name suggests, allows users to proactively monitor changes to the balances of the key account. You can compare the balances quarter by quarter or year by year without doing any run-time aggregation. The aggregation is done automatically by the Essbase cubes without having to run any background processes. It is common to use this utility for key accounts such as expenses or revenue that is being monitored. For example, the company may have introduced a new travel policy, and you may want to monitor the expenses of flight costs year by year to see if travel-related cost cuts are taking effect in the organization. You can also monitor the exceptions by defining deviations above x percent from last period or last year, or you can monitor expenses going beyond the budgets defined in the system. The drill-down facility allows you to further interrogate the exceptions in account balances by drilling down to the journals and transactions. The system can also automatically mark the deviations in accounts in red or green depending upon whether the deviations in balances are adverse or favorable for the organization. Account Monitor therefore facilitates self-service definition of tolerance rules by the users in order to monitor key accounts.
Account Groups
Account groups can be created for the Chart of Accounts values for which balances need to be monitored. Click the General Accounting tab, and in the Account Monitor region, navigate to View | Account Group | Create as shown in Figure 13-13.
FIGURE 13-13. The Account Monitor region in Fusion General Ledger
An account group is a set of Chart of Accounts values for which you want to monitor the changes proactively. As shown in Figure 13-14, you can create an account group named ACME Travel Expense Account and add entries in the account region to track a set of Chart of Accounts values for which expenses have gone up by more than x percent. Alternatively, you can also track changes by amount for both increase or decrease and compare against the prior year, or prior quarter, or prior period balances as shown in Figure 13-14. To make account creation easy, Oracle allows you to select values such as All Company values, or All Cost Center values for the record when creating Account Groups. Of course, you can include specific segment values as well for account monitoring.
FIGURE 13-14. Defining an account group with monitoring options
The system can decide according to the account type whether the changes are good or bad for the organization: for example, increasing sales are a good sign, but increasing expenses are not a healthy sign. Therefore, the system can automatically mark the record in green or red.
OTBI (Oracle Transaction Business Intelligence)
OTBI enables transactional reporting from live data with self-service capabilities. You do not need to develop ETL processes for OTBI reports because this technology does not use a data warehouse. In simple words, OTBI is the OBIEE embedded into Fusion Applications. The question readers may ask is: Why is this so special when many customers already have OBIEE? The key value added by OTBI is that it comes bundled with hundreds of predeveloped reports that users can customize further by dragging and dropping. OTBI inherits all the application and data security features of Fusion Applications. Therefore, you do not need to configure security separately for OTBI. Who can see which data is controlled by the data security policies defined in APM, which is explained in Chapter 7. Thus, OTBI presents a real-time snapshot because the information is prepared on the go when requested by the user via their browser. It supports Fusion extensibility such as flexfields, trees, multilanguage features, and so on.
In the cloud implementations, customizations are not possible on the presentation layer. On SaaS you will have a fixed set of presentation layer and subject areas because you cannot customize the presentation layer in SaaS implementations. However, in the on-premise implementation you can use Oracle’s BI Administration tool to do customizations on the presentation layer. Further, you can create your own views of transactional data and present them as subject areas by deploying your development. By doing so, you can introduce new presentation layers. You can build the real-time reports in BI Publisher as well, but OTBI gives self-service ad hoc analysis capability that is not present in BI Publisher.
The OTBI Security model consists of users, job roles, duty roles, and privileges. A user can be assigned to one or more job roles. A job role is descriptive of the user’s job function, such as General Ledger Clerk. A user is granted a job role, and a job role has one or more associated duty roles. A job role can span all the applications, whereas a duty role is specific to an application. Job roles are grouped hierarchically to reflect lines of authority and responsibility. Privileges allow specific access to an application or reporting objects and data sets; for example, read access to a report, or read access to a table, and so on. Privileges are associated with duty roles. As soon as a user logs in to Fusion Applications, the system knows the function and data security applicable for that logged-in session. For example, when a finance manager views a list of journals pending approval using an OTBI report, the manager will only see the count of journals that belong to the ledger to which they have access. This is made possible because OTBI respects the data security constraints on the underlying data objects, which are also used by the data entry and inquiry screens in Fusion Applications.
To ensure that the users are enabled for baseline OTBI reporting access, a role named FBI_TRANSACTIONAL_BUSINESS_INTELLIGENCE_WORKER must be assigned to a user. Also, the relevant duty roles can be assigned to the users to grant them access to a group of reports. For example, OTBI Duty Role FBI_GENERAL_LEDGER_TRANSACTION_ANALYSIS_DUTY has access to the following subject areas:
1. General Ledger - Balances Real Time
2. General Ledger - Journals Real Time
3. General Ledger - Period Status Real Time
4. General Ledger - Transactional Balances Real Time
5. Subledger Accounting - Journals Real Time
6. Subledger Accounting - Supporting References Real Time OTBI Function
Security job roles and their associated duty roles and privileges are assigned to users of Oracle Fusion Applications. The implementation team usually indicates which users can access which application menu or page. This level of security is known as function security. This function security also secures access to OTBI reporting objects by assigning BI-specific duty roles to BI-specific job roles. In Fusion Applications, you will find that for every given subject area, usually a single BI duty role is defined, using the naming convention “<xyz>Analysis Duty.” Some examples are Account Analysis Duty and Expense Analysis Duty roles. These BI duty roles are mapped to the subject areas using a file known as RPD in the BI Administration tool. The mapping of the BI duty roles to various subject areas comes prepackaged in Fusion Applications. OTBI comes packaged with an Analysis Editor for presenting data in graphs, pivots, tables, and so on, and it is called BI Answers. You also have the BI Composer Wizard, which allows you to quickly create and edit reports. There are multiple subject areas. Within the subject area you have Dimensions, Attributes, and Facts/Measures.
OTBI reports can either be created when logged in to Fusion Applications or from the BI Analytics dashboard. To create an OTBI report from within Fusion Applications, click Navigator and select the menu item Reports And Analytics under the Tools section. Click Create, and you will be presented with all the subject areas available to you based on the roles assigned to your username as shown in Figure 13-15.
FIGURE 13-15. Creating a new self-service report dashboard
As seen in Figure 13-15, the product comes out of the box with General Ledger Balances, Journals, Period Status, and Transactional Balances subject areas. You can select the desired subject area on which the self-service analysis report needs to be developed. Each subject area consists of various facts and dimensions as shown in Figure 13-16. You can shuttle the desired dimensions and facts into the report that you want to build. It is also possible to enable drill-down to the journals and transactions using the drop-down list Interaction.
FIGURE 13-16. Selecting dimensions and facts for reporting
After selecting your dimensions, click Next, and in the Select Views section, enter a title and select the option of Tabular or Graphical format. Apply sort and filter conditions as desired, and save your report. The report can either be saved in your personal area, which is named My Folder, or it can be saved in a shared area under Shared Folders. A good video demonstration for this can be found at https://goo.gl/ct7mu1.
When saving your custom reports that you want to share, you must create them under a subfolder within a custom folder. This will ensure that your reports are easily accessible and will never be overwritten by Oracle patches.
Create a Simple OTBI Report
Let us assume that you as a user want to send the list of journal batches created on a daily basis straight into your mailbox. In order to implement this requirement, first you have to set your default delivery profile. Log in to Analytics Dashboard using the URL http(s)://host:port/analytics. Click your login name in the top-right corner, then My Accounts, and navigate to the tab Delivery Options. Select Email from the drop-down Devices list and click the + sign to create a new device with Name Send to myself, Category Email, and Device Type HTML Email, followed by the preferred email address, as shown in Figure 13-17. Click OK and set this device to be the default. In OTBI, the term “device” simply means a medium used to deliver content to the users. The contents of an agent can be delivered to devices such as an email or SMS message.
FIGURE 13-17. The end users can set their default delivery preferences.
Next, define a new report. Click Analysis under Create as shown in Figure 13-18 and then scroll down to select General Ledger – Journals Real Time in the pop-up window titled Select Subject Area.
FIGURE 13-18. Creating an analysis for interactive reporting using OTBI
Double-click Journal Batch Description under Journal Batches | Batch Details as shown in Figure 13-19. Double-click Accounting Date, Currency, Journal Status, and Period Name underneath Journal Headers | Header Details. Click Save and give this self-service report a name: ACME Journal Batch Listing. The path for saving this report is/Shared Folders/Custom/Financials.
FIGURE 13-19. Creating a new OTBI report for Journal Batch Listing
OTBI has a component named Agents, which delivers analytics to users based on scheduled or triggered events. Delivery can be a pop-up message on Dashboard via Alerts or as email. To deliver this report either to the Dashboard or via email to yourself, click New | Agent as shown in Figure 13-20. Click the Schedule tab and select the agent’s delivery frequency, such as Never or Daily or Weekly or Monthly.
FIGURE 13-20. Creating a new agent for delivering the report to yourself
Click the Delivery Content tab and give this delivery agent a Subject “Journals created today.” Click Browse to select the report that was created for the journal listing, as shown in Figure 13-21.
FIGURE 13-21. Selecting the report that will be delivered by this agent
Click the Recipients tab and chose your default username. In the Agent’s Actions tab, select the radio button Specific Devices, with the Email check box enabled. Click the Save button, and save this under My Folders, giving it the name My Scheduled Journal Batch Emails. Click OK and as per the schedule, you will receive an email in your mailbox with the journal listing as shown in Figure 13-22.
FIGURE 13-22. Journal listing delivered automatically to your mailbox
BI Publisher Reports in Fusion Applications
The BI Publisher Reports tool is for high-resolution, pixel-perfect reports. You can customize the output by changing the template to produce a PDF or in Excel or HTML or DOC format, and so on. This report is also good for high-volume transactional data reporting. Fusion comes out of the box with many BI Publisher reports. You will typically use BI Publisher reports for documents that are meant to be printed.
The traditional financial landscape used a separate and independent work area for Business Intelligence and reporting. However, in Fusion Applications, both the operational and analytics reports are available directly within Fusion Applications itself. Further, these integrated reports adhere to the underlying security principles of Fusion Applications for report access and data. The Reports And Analytics pane and work area is the integration point between Oracle Fusion Applications and Oracle Business Intelligence. Oracle Fusion Applications does a good job of making the Business Intelligence available to the end users directly in their application user interface. This allows senior management to make informed decisions based on the embedded analytics and dashboards. It also drastically improves BI participation from the casual users by making the report launch with just one click. The BI reports are either directly embedded within the Fusion Applications UI, or they are accessed from the Reports And Analytics pane and work area.
To facilitate rapid development of BI Publisher reports, Fusion Applications comes bundled with a data model builder, allowing you to create groups based on SQL statements and to define relationships between groups. This data model builder can be accessed from the browser, which means that you do not need the database connection credentials for developing reports because SQL queries can be designed straight into the browser-based data model editor. The key difference with OTBI is that with OTBI, you as a user will be agnostic to the SQL statements that are executed on database tables. In the case of OTBI, the SQL statements are hidden under the hood of the subject areas model within the RPD file. Another key difference is that the data model builder in Fusion Applications can source the data from a variety of sources as listed in Table 13-2.
TABLE 13-2. Different Data Sources for Building BI Publisher Reports
As seen in Table 13-2, in order to report on a piece of data, it needs to be defined as a data source. The BI Publisher engine converts the results of the data source to XML and passes them to a BI report template. This template contains the design and presentation logic to achieve the desired layout and formatting of the final output.
Scheduling Capabilities in BI Publisher
The scheduling capabilities of BI Publisher reports are the same as for OTBI reports. You can conditionalize the execution of a scheduled report based on an event. To implement this option, you can define an event trigger of type=Schedule. This event trigger can be associated with a SQL query. If this SQL query returns no record, then a scheduled run of the report will not be executed. Therefore, just prior to running the scheduled job, the associated SQL query in the event trigger will be executed. If no data is returned from the SQL query, then the job instance report is not run. If data is returned, the job instance runs as scheduled. One example of this feature is where you want to email the exceptions in Financial Accounting Hub of a nightly batch process after the import process is completed. If there are no exceptions in the exception tables, then you can skip running of the exception listing report. On similar lines, you may want to skip the execution of a scheduled import process for journals if there is no data in the corresponding GL_INTERFACE table.
For on-premise implementations, the technical developers in your team can also write custom PL/SQL code snippets to be executed before or after the execution of a data source. The Before Data trigger is executed before the data set is fetched, and the After Data trigger is executed after the BI Publisher engine has generated the XML from all the data sets within the data model. The PL/SQL function used for these triggers must return TRUE or FALSE. In a SaaS-based environment, the customers are not allowed to write their own PL/SQL, and therefore there is a restriction on the usage of before and after event triggers for SaaS customers for their custom reports. As an example, this feature can be useful in scenarios where you want to create an audit entry specific to your needs after the exceptions have been reported.
Bursting the Report Output with Multiple Delivery Options
The BI Publisher reporting engine also has a feature known as bursting. This is used to split data into smaller pieces based on specified criteria. After the split of data has been achieved, you can then deliver those smaller pieces of data to multiple locations. Bursting definitions contain instructions for splitting data, generating a document in the desired format for each split section, and then delivering the output to the desired destination using a desired mechanism. For example, you can split the manual adjustment journals created by cost center to advise each cost center manager via email that adjustment journals have been created for their respective cost centers. Using bursting, each cost center manager will then see the affected balances for just their cost center, even though the data source of the BI Publisher report will return adjusted values for each cost center. Also, bursting gives you the option of applying a different layout template and delivery mechanism to each split section of the XML generated by the data model.
Templates for Formatting the Output
Reporting templates are used to format the data for presentation. Oracle BI Publisher provides an add-in for Microsoft Office to facilitate the coding of layout instructions into Office documents. Most of the templates delivered in Oracle Fusion Applications are RTF templates. An RTF template is a Rich Text Format file that contains the layout instructions for BI Publisher to use when generating the report output. RTF templates are created using Microsoft Word. Oracle Fusion Applications also comes with an embedded template builder tool that can be used for generating interactive HTML-based reports.
Report Output
A report is the final output where the user can view the data in the desired format. Some of the key output formats supported by BI Publisher are listed in Table 13-3. The complete list can be found in Oracle product documentation, as Oracle can add new supportable formats.
TABLE 13-3. Formats Supported by Oracle BI Publisher
Create a BI Publisher Report to List Journal Batches for the Last Day
In this section you will find a step-by-step approach to create a data model, template, and report with an example. The Fusion Applications BI Publisher environment can be accessed using a URL similar to https://<host:port>/analytics//. This is the same URL that was used to develop an OTBI report. In this simple example, a report will be developed to list the journal batches that were posted during the last day. The steps for developing this report are as follows:
1. First, we need to create the data model. Click the menu item New and then the menu item Data Model under Published Reporting, as shown in Figure 13-23.
FIGURE 13-23. Creating a new data model
2. To extract the data for journal batches, you need to know the relevant tables, which can be found in the standard Oracle documentation. Create a new set using the SQL statement shown in Figure 13-24. You can click the menu item View Data to see the sample results of this query in tabular format. Click the Save button to save the data model. At this point the system will prompt you to select the data model location. Given that this is a custom data model, select the folder structure/Shared Folders/Custom/Financials, giving it the name XXACME_LAST_DAY_BATCHES_DM. Don’t save your data model in My Folders, as it will only be visible to the user who created it.
FIGURE 13-24. Creating a data set for a journal batch listing
3. Next, we need to create a BI Publisher report that uses this data model. Click the New Report button, select the data model created in the previous step, and then click the Use Report Editor radio button as shown in Figure 13-25 and click Finish.
FIGURE 13-25. Creating a new layout using an existing data model
4. When you click Finish, you will be prompted to save the report. In this example, we will save the report in the same directory where we saved the data model, giving this report a name, ACME Last Day Batches BI Report, as shown in Figure 13-26.
FIGURE 13-26. Save the report into a custom folder.
5. When you click the OK button, you will be presented with a window for creating or uploading a template. You can use one of the template designs to develop your layout in the built-in editor. Alternatively, you can upload the layout template that you develop offline in third-party tools such as Microsoft Office. The Basic Templates provide a few built-in basic layout formats for building the report. In this example we will first create the template using the Embedded Template Builder. In order to do so, click the Header And Footer (Landscape) template.
6. Click the Insert tab and then click Data Table. Alternatively, from the left-hand pane Components section, click Data Table. This will create a placeholder for a data table in your layout. Now, from the left-hand pane under the section Data Source, drag and drop all the columns into the data table as shown in Figure 13-27.
FIGURE 13-27. Drag and drop columns from the data set into the data table.
7. Click the Save button and name your file ACME Last 1 Day Role Template. Click the Return button, and click the View Report button to see the output.
8. You can also select the menu option Report And Analytics in Fusion Applications and navigate to the Finance section within the Custom folder and run this report. The output can be presented in various formats as shown in Figure 13-28.
FIGURE 13-28. Viewing output of the report in various formats
Smart View
Smart View is an ad hoc analysis tool. It allows you to view General Ledger balances in Excel. You can drag and drop and slice and dice to report on balances. It also allows you to drill down into transactions, as we saw in the Financial Reporting Center. The key difference from other reporting tools is that Smart View is an Excel-based tool, allowing you the functionality offered by a spreadsheet, for example, the flexibility to build graphs, pivot tables, add formulas, and so on. You can save the Excel file on your desktop and use email to deliver the information to the recipient. If the recipient does not have Smart View on their desktop, then they will see this Excel file as a static piece of data. However, if they have Smart View installed with appropriate access, they can refresh the Excel balances from their desktops. If any of the balances have been changed since the Excel file was saved by the sender, then the recipient can still see the up-to-date balances. Also, the recipient can further refine the report using slice and dice. A comparison of the Financial Reporting tool and Smart View is listed in Table 13-4.
TABLE 13-4. Comparison of Financial Reporting Tool and Smart View
Building a Simple Report Using Smart View
In order to build reports using Smart View, you first have to install a Smart View plug-in for Excel from the Oracle website. A quick way to find this plugin is to search for Oracle Smart View for Office Downloads. Alternatively, type in this URL: http://www.oracle.com/technetwork/middleware/epm/downloads/smart-view-1112x-2412371.html
Next, you need the URL of the Smart View workspace on your Fusion Applications instance. Typically this will be https://<host>/<port>/workspace/SmartViewProviders. You must speak to your administrator to obtain the port number because this port will be different from your normal port used for application access.
After you have installed the Smart View plug-in, you will see a tab named Smart View in Excel as shown in Figure 13-29. Click Options, and under the Advanced settings, enter the Smart View workspace URL in the Shared Connections URL drop-down. Click OK.
FIGURE 13-29. Smart View Options, Advanced settings
In your right-hand pane you will now find a Smart View window. Click the drop-down option Shared Connections as shown in Figure 13-30. This will prompt you to log in using your application login and password. Authentication at this stage ensures that Smart View can report on those Chart of Accounts values for which you have access. Next, select Oracle Essbase as shown in Figure 13-30. This will show a list of all the Essbase cubes in your Fusion Applications instance. Expand Essbase_FA_Cluster and select ACMEInsuranceCOAInstance and double-click db. This will open the Essbase tab in Excel.
FIGURE 13-30. Opening the Essbase tab in Excel
Click POV (Point of View) under the Essbase tab in Excel, and drag Account Dimension in front of AccountingPeriod. To drag from POV to Excel, hover the mouse over POV Dimension until you see the mouse arrow, and then drag the Account POV entry in front of AccountingPeriod. Select the cell in Excel that contains the text AccountingPeriod, right-click, and drag over Ledger. Right-click Ledger in Cell B1 and move that into the POV pane. Your Excel screen should then look similar to the image shown in Figure 13-31.
FIGURE 13-31. Smart View cells
Click the Ledger drop-down and shuttle ACME US Insurance Ledger to the right as shown in Figure 13-32. Click the small drop-down beside the text Ledger below the window title Member Selection, again shown in Figure 13-32. Select Scenario, and shuttle Actual to the right, because we want to show actual GL balances. Next select POV member Balance Amount and shuttle Period Activity to the right. Select Amount Type and shuttle Base to the right. Select Currency and shuttle USD to the right. Select Currency Type and shuttle Total to the right-hand pane.
FIGURE 13-32. Selecting values for dimensions in POV
Navigate to the Essbase tab in Excel, highlight the AccountingPeriod cell, click Member Selection, and select the periods ACME-Jan-14, ACME-Feb-14, and ACME-Mar-14 on the right. Select the Account cell and click the Member Selection menu item again to shuttle 10020 and 10010 to the right. Click the Refresh menu item within the Essbase tab, and this will show the GL balances as shown in Figure 13-33.
FIGURE 13-33. Balances in Smart View
You will notice that periods that have 0 balances show the text “#Missing” in those cells. In order to replace #Missing with 0, click Options in the Smart View tab, and then select Data Options, and in the Replacement section, select a value of #NumericZero for field #NoData/Missing Label:. Refresh the Smart View, and this will show the final output as per Figure 13-34.
FIGURE 13-34. Smart View report output
Account Inspector
The Account Inspector tool is similar to Smart View, but it runs on the browser to explore GL account balances. Some people prefer to stay in the browser rather than going to Excel, and they can use this tool. The features in Account Inspector are similar to Smart View; that is, you can drag and drop the dimensions into the Account Inspector pane to perform analysis on your GL balances. Of course, you will not get the features that are native to Excel in Account Inspector.
To access Account Inspector, log in to the application, click General Accounting Dashboard in the Navigator, and select the menu item Inquire And Analyze Balances. Expand the Account Values, and you will see the account values as shown in Figure 13-35.
FIGURE 13-35. Account Monitor in Fusion Financials
Summary
In this chapter you have learned about various reporting tools in Fusion Financials and their usage. You have also learned the steps to build simple reports using a variety of toolsets. The architecture implemented in Fusion Applications reporting ensures that the central data security policies are applied across all reporting tools, regardless of the nature of the tool used for reporting in Fusion Applications.
Oracle Fusion Financials delivers a feature-rich reporting platform that is built on top of an analytic data model. Fusion Financials delivers a multidimensional reporting and analysis platform that provides real-time access to financial information at your fingertips. End users can build their own reports and dashboards very quickly by leveraging the self-service BI capability of the product.