Account Analysis Reporting (OracleFusion)

Context

This particular report came about when it was pointed out multiple times that the available reporting within the current financial system could accommodate their needs. Accounting needed a report that could extract the data from each ledger (or even multiple ledgers) and be able to reconcile the detail of certain account balances. The original account analysis report was unable to extract information from multiple ledgers without breaking down. It was also out of date. Based on the data this report would be expected to pull, the SQL syntax would be complex. It required the joining of multiple Oracle tables and ended up being a report that did take some time to run.

Tool: Oracle Fusion

Language: SQL

Scope

Data Format: Excel (when exported)
Platform: Oracle Fusion

Requirement/s: Vendor Name (when applicable), Invoice Number (when applicable), Journal Header, Journal Description, General Ledger account string (may contain multiple parts), Creator of the journal, Posting date of the journal, Journal Line Detail, Ledger Name, Ledger Currency, User who posted the journal (additional requirements may be added afterwards)

Language: SQL (to extract data)

Project Description: Create a data model utilizing SQL within Oracle Fusion and create a report that will pull the data within the fields specified in the requirements. Based on the amount of data being pulled, multiple data models may be needed. The report format and data model will be maintained and updated as needed. Even though the final output is an Excel export, users may want to “preview” their data in the Oracle Business Intelligence catalog so the “interactive” view needs to be activated when creating the report.

This is the backbone of how the report is built in Oracle Fusion Business Intelligence. You need to set-up data models with the SQL syntax to pull the data, list of values (that could be selected from the parameters – if needed) and the names of the parameters.

Data model visualization after SQL syntax is inputted.

A window to drop-in the SQL syntax.

A snippet of the SQL syntax for this report.

After data model is created, Oracle allows you to format the report in a table format and or pivot table format with some configurations.

This is the parameter screen after report is created. You can run the report from this window.