Consolidation and Close for Data Loaders


 

Statement of Confidentiality

This document and the information contained herein are company confidential and should not be disclosed or duplicated, in whole or in part, outside Huron Consulting Group, the University of Chicago, or the University of Chicago Medicine.

 

Document Control

 

Table of Contents

 

1.         Basic Navigation

1.1       Logging into FCCS

1.2       Overview of the Home Page

1.3       Preferences and Settings

2.         Dimensionality

2.1       About Dimensionality and Metadata

2.2       FCCS Dimensions

3.         Loading Data

3.1       Overview

3.2       Importing and Exporting Data – Data Exchange          

3.2.1   Setting up the Data Load Rule Point of View (POV)

3.2.2   Loading the Data (Direct Connection)

3.2.3   Loading the Data (Flat File)

3.2.4   Exporting the Data to the Application

3.3       Reporting on Data Exchange results

3.3.1   Running Excel Export of Workbench

3.3.2   Running Reports from Data Management

3.4       Resolving Errors During Load Process

           3.4.1   Reasons for Errors in Loading Data

           3.4.2   Resolving Load Errors

4.         Consolidation

4.1       Consolidating Data 

4.2       Review Status in Jobs Console

5.         Consolidation Journals

5.1       Overview of Journals

5.2       Creating Journals

5.3       Selecting Dimension Members in a Journal Row

5.4       Finalizing the Journal

5.5       Copying Journals

5.6       Creating Auto-Reversing Journals

5.7       Importing/Exporting Journal Details

5.8       Run Journal Reports          

6.         Cash Flow 

6.1       Overview – Cash Flow Design

6.2       Initial Population – Data Exchange Map 

6.3       Finalizing Input – Cash Flow Forms

6.3.1   Initial setup to use Cash Flow Forms

6.3.2   Opening the Cash Flow Input Forms

6.3.3   Navigating in the Forms

6.3.4   Inputting Data into Forms

7.         Translation

7.1       Basic Translation

7.2       Review FX Rates

7.3       Translation of P&L Activity

7.4       Translation of Balance Sheet Activity

7.5       Translation of Balance Sheet at Historical Rates

7.4.1   Using the Override Input Form in FCCS 

7.4.2   Cumulative Translation Adjustment (CTA)         

8.         Task Manager

8.1       Overview of Task Manager

8.2       Working within Task Worklist

8.3       Working within Schedule Tasks

8.4       Working with Tasks – Opening and Submitting

9.         Smart View:  MS Office Add-In

9.1       Introduction to Smart View

9.2       SmartView Ribbon

9.3       Customizing Smart View Using Options

9.3.1   Member Options

9.3.2   Data Options

9.3.3   Advanced Options

9.3.4   Formatting Options

9.3.5   Cell Styles Tab

9.4       Ad-hoc Analysis

9.4.1   Open Ad-hoc Analysis Grid

9.4.2   Selecting Members for Ad-hoc Analysis

9.4.3   Basic Member Selection

9.4.4   SmartView Ribbon – FCCS Ad hoc Functionality

9.5       Function Builder

10.       Reporting

10.1    Introduction to Web Reporting

10.2    Running a Web Report

 

1.      Basic Navigation

 

1.1     Logging into FCCS

The following are the relevant URL's for accessing the UChicago's FCCS environment:

TEST:  https://uchicagofccs-test-uchi.epm.us-phoenix-1.ocs.oraclecloud.com/epmcloud

Note: It is recommended to save the URL as a favorite. 

 

1.2     Overview of the Home Page

When users sign into FCCS, the Home page is what will initially display. The Home page provides easy access to features available in FCCS. Depending on a user's assigned role, they may see some or all of the features available.

 

 

 

ALWAYS formally log out when exiting the application.  To log out, select the down arrow next to your name in the top right corner of the page and select Sign Out.  Then select the OK button.

 

The table below lists the various features that can be found on the Home page in FCCS, as well as provides a brief description of their functionality.

 

 

1.3     Preferences and Settings

You can set certain preferences in how the application appears or behaves as you navigate and use its functionality.  To access the User Preferences page, from the main menu select the Tools icon, then User Preferences.

 

Or alternatively, you can also access the User Preferences page by selecting the Navigator icon (three lines that look like a hamburger at the top left-hand side of the screen).  Under the Tools section, find and select User Preferences. 

 

The preferences section can allow users to create preferences for multiple artifacts and notification elements in FCCS.  Below is a general list of settings that can be edited:

 

Below are screen shots of some of the preferences/setting pages.  Remember to always select the Save button after making any changes.  You can make changes to the User Preferences at any time.  The changes should display immediately.

 

The timezone setting is an important preference to change.  Many tasks use the date/time stamp noted in the user preferences, which defaults to GMT.   Also, whether you prefer to see the dimension member "name" (label) or "description" (alias).

 

The display section changes the way data looks in web forms and grids.

 

The Notifications are import if a user will be using Task Manager.  This page can help determine timing of the notifications from Task Manager.

 

 

2.      Dimensionality

 

2.1     About Dimensionality and Metadata

FCCS uses Metadata to define and parse stored data.  Metadata is defined as the structural fields of an application that describes an individual data element.  Metadata is essentially "data about data".  Dimensions are the identifiers of an applications metadata, and each dimension describes a specific aspect of a piece of data. Within each dimension are members that further describe details about each individual data point.

 

FCCS has 15 different Dimensions to describe each data amount.  Eleven of those dimensions are system-defined:  Scenario, Year, Period, View, Entity, Account, Intercompany, Consolidation, Currency, Data Source and Movement.  These dimensions come with most FCCS applications.  The last four are Custom dimensions, unique to UChicago.  These are:  Organization Cost Center, Fund, Purpose, and Future.  Below is a sample of FCCS described Dimensions and the data value these dimensions describe.

 

 

 

 

 

The reference points of metadata describe the amount of $ 2,288,204,156.81.  The dimension references in this example would be ENT_600 – UChicago and Ending balance for FCCS_ Total Assets (Total Assets) for June FY23 (June of fiscal year 2023).   Dimensions are used to define what data elements are required when building reports or analyzing data.

 

Members of a dimension are arranged in hierarchies to reflect reporting requirements.  As a consolidation system, FCCS uses these hierarchies to summarize and aggregate data to reflect UChicago's reporting requirements.  Upper-level members are called parent members.  A member immediately below a parent member is referred to as its child.  All members below a parent are referred to as descendants.  The bottom-level members of the hierarchy are called base-level or Level 0 members.  Base-level members represent the point of data input.  Parent members consist of an aggregation, or consolidation, of data from its base members.  Members that exist at the same level are called siblings.

Most hierarchies are populated upon loading data, such as the custom dimensions.  Those populated through calculations, like certain accounts, the Entity dimension, Intercompany, and some members of the Movement dimension, are only populated after running the Consolidation calculation rule.

 

2.2     FCCS Dimensions

In FCCS, there are eleven (11) system-defined dimensions and four (4) custom dimensions:

 

Below are details on each dimension:

 

Scenario: Represents a set of related data, such as Actuals, Budgets, Forecasts, etc. For example, the Actual scenario can contain data from a general ledger, reflecting past and current business operations. The Plan scenario can contain data that reflects the targeted business operations sourced from a Planning tool. Additional scenarios can be used to translate data using a different currency, called a constant currency scenario.  Data is typically copied from an existing scenario, such as Actual to another scenario which has different FX rates defined. In FCCS, multiple scenarios can be defined for an application.

 

View:  The View dimension describes calendar intelligence such as periodic, year-to-date, and quarter-to-date data frequencies. If you set the view to Periodic, the income

statement account values for each month are displayed. If you set the view to year-to-date (YTD) or quarter-to-date (QTD), the cumulative values for the year or quarter are displayed. Balance sheet accounts also use the View dimension to facilitate capturing flows, detailed in the Movement dimension, which represents changes in the balance sheet over a period of time.

 

Year: Represents the fiscal or calendar year for the data. An application can contain data for more than one year.  The UChicago application begins with the fiscal year FY23 and will span over 10 years and end at FY32. Additional years may be added by the administrator after go-live. Historical data will be included to test the application design for Jun FY23 (creates beginning balances) and forward.

 

Period: Represents time periods, such as quarters and months.  The UChicago's application will follow UChicago's fiscal calendar, which starts in July and ends in June with Quarter aggregations that roll into YearTotal. UChicago will not use the 13th period. A unique Data Source member will be used instead to capture any Period 13 adjustments from the cloud ERPs, or any other source system that captures these post-closing adjustments.  See the Data Source dimension section for more details on howPeriod 13 data will be stored.

 

Entity: Represents the various management and legal reporting structures in your organization. Base entities are at the bottom of the organization structure and represent the Accounting Packages. Data is loaded to these base entities. Parent entities contain one or more entities as children and typically represent a reporting requirement, such as segment or management reporting point.  The Entity dimension drives both the consolidation and translation process, with the hierarchy determining how data is aggregated and the Currency of each entity member, base and parent, determining how the data is translated.

 

Consolidation: Provides detail on how FCCS transforms data as it moves through the consolidation process, which is driven mainly by the Entity dimension.  Data populated either from a GL file or Journals is represented in Entity Input.  As the data is ansformed through translation, intercompany elimination or proportional representations, separate members capture the results of each process.  The ultimate sum is then Contribution, which is the amount that aggregates to a child entity's parent.  These results will flow to that parent entity's Entity Consolidation member.

The process then begins again for the next level Entity member, depending on additional transformations needed as the data consolidates to the very top consolidated Entity.

 

 

Intercompany:  Represents balances for transactions that occur between an organization's legal entities or operations. FCCS tracks and eliminates intercompany transaction through accounts defined as "Intercompany" and base entities turned on as "Intercompany partners".  This intersection of Intercompany account and Intercompany partner describes an intercompany transaction.  FCCS matches offsetting balances together automatically during the consolidation process and eliminates balances to specific accounts identified as "plug" accounts.  Reporting can then be done on these plug accounts to review and analyze differences and their source.  For all data that is NOT Intercompany, the data should be loaded and stored in the FCCS_No Intercompany member.

 

For each BASE entity switched on as an Intercompany Partner, a corresponding member is created in the Intercompany Dimension with the same "label" as the original base entity, but with a prefix of ICP_ to make the label unique.

 

Account: Represents an identification that describes the nature of a transaction, such as Cash, Notes Payable, or Payroll Expense.  Accounts store financial data and represent basic reporting requirements.  Each account has an Account Type that defines its accounting behavior as it aggregates, such as Revenue, Expense, Asset, Liability or Equity.  The Saved Assumption account type is used for statistical reporting data that represents UNIT type data and does not need to translate.  The FCCS chart of accounts comes with pre-defined members, called "seeded" members, that all begin with the prefix of "FCCS_".  All accounts represented by the University shared chart of accounts, start with the prefix of "ACT_".  The Oracle seeded members are needed because they are used by out-of-the-box calculations.  Thus the FCCS account structure will use the Oracle seeded members in place of their corresponding University chart members so the University of Chicago's FCCS application can use these essential calculations.

 

The FCCS chart of accounts utilizes built-in financial intelligence that recognizes an expense would subtract from a revenue, or a liability from an expense.  Thus, all of FCCS accounts represent data in REPORTING sign, with all account balances showing as positive unless a contra balance.  This is the opposite of how general ledgers show data, which is in TRIAL BALANCE sign.  Here Assets and Expenses are debits and shown with a positive sign, while Liabilities, Equity and Revenues are credits, and shown with a negative sign.

 

Data Exchange uses mapping to flip the sign of Trial Balance data as the data loads into FCCS. 

 

 

Movement:  The Movement dimension captures the flows of balance sheet changes and is used to facilitate cash flow reporting.  Seeded members (starting with "FCCS_") are automatically defined on application creation, but additional members were added as well to conform with UChicago's Cash Flow reporting.  The Movement dimension works in conjunction with the Account dimension, where the intersection of the two represents different "slices" of balance sheet account rollforwards.   Using this out-of-the-box design provides benefits to set up the Cash Flow process within FCCS quickly, but it can only produce an indirect cash flow.  This is why the rollforward detail for balance sheet accounts, captured in the Movement dimension, is a fundamental element of the FCCS Cash Flow design.

To help provide this key data, as the Period-End data is loaded from each source system to FCCS within Data Exchange, each balance sheet account is targeted to populate a specific Movement member.  For example, all Cash accounts would map to the Cash Change member (FCCS_Mvmts_Cash) within the Movement dimension and all P&L data would populate the movment dimension member FCCS_Mvmts_NetIncome.

 

FCCS automatically calculates the Opening Balance (Closing balance of prior period) and with the periodic month-end data loaded from the source system to the appropriate Movement below FCCS_Mvmts_Subtotal, the sum of these two balances equal the LTD Ending balance.

For more detail on how the Movement dimension creates Cash Flow reporting, see Chapter X on Cash Flow.  Below is the primary Movement dimension hierarchy.  Parent members that represent an aggregation of child members below are bolded with an explanation for the main members.

 

FCCS_Movements - Represents the YTD/LTD Ending Balance

 

FCCS_ClosingBalance – Same as FCCS_Movements (there should be no financial data input or loaded to FCCS_No Movement)

 

FCCS_OpeningBalance – Calculated out-of-the-box based on the prior period Closing Balance.  This is different depending on the VIEW dimension selection:

            FCCS_Periodic:  Opening Balance is the Closing Balance from the prior month

            FCCS_QTD:  Opening Balance is the Closing Balance from the prior quarter end

            FCCS_YTD:  Opening Balance is the Closing Balance from the prior year end

 

FCCS_Mvmts_Subtotal – This is the sum of all monthly periodic "movements".

 

FCCS_Mvmts_FX_Total – Out-of-the-box calculations that show the FX on the Opening Balance + FX on the Monthly Change (Movement).  This hierarchy also captures where any account's translation is not at EOM Rate, and pushes that to the CTA FX member, and then again to the FCCS_CTA account within the Equity section.  This allows users to track how CTA is calculated and populated in FCCS.

The Movement dimension consists of three separate hierarchies.  The primary hierarchy, discussed above, is the main hierarchy that is used when finalizing month-end data.  The second hierarchy, under FCCS_CashFlow, is created to facilitate the actual Cash Flow report.  This hierarchy shares members with the primary hierarchy, but flips the sign of the data so that is shows it in correct "cash flow"signage, with increases in Assets shown as negatives and increases in Liabilities or Equity as positive.  The third hierarchy calculates the changes in cash, including the FX effect for any data that was translated.

 

Below are the FCCS_Cash Flow reporting hierarchy and the FCCS_CashChange hierarchy.

 

Data Source:  This dimension serves to separate data by its source to create an audit trail of where data in FCCS originated from.  The table below describes the source of data and which Data Source dimension member that data will always populate.

 

In addition to the seeded Data Source members (seeded members always begin with the prefix of "FCCS_"), an additional custom Data Source member was added, D13_Adjustments, to capture data from the ERP Adjustment Ledger.  FCCS_Total Data Source then represents the aggregation of both the GAAP ledger and the adjustment ledger.  An alternate hierarchy, TOTAL_GAAP, then provides reporting on just GAAP ledger data by sharing the members from the primary hierarchy while excluding the member that represents the Adjustment ledger data.

 

Custom Dimensions

 

In addition to 11 predefined dimensions, FCCS can create custom dimensions to reflect business and reporting needs. Custom dimensions are associated with the Account dimension and provide additional detail for accounts. U Chicago will have four custom dimensions. The custom dimensions for UChicago 's FCCS application are:

 

Custom1 – Organization_CostCenter - Identifies the operational or administrative unit. Contains both the Universities Organization rollup AND the Medical Center's Cost Center rollup.  The prefix for University organization members is ORG_.  For the Medical Center's Cost Centers, the prefix is CC_.

 

Custom2 – Fund - Tracks spending restrictions and designations to categorize fund balance.  This should be a balanced dimension.  Members within the Fund dimension begin with the prefix of FUN_.  The Fund dimension, as defined in the ERP,  has more than 17,000 members and is expected to continue to grow.  Due to limitations in accepted custom dimension size by Oracle (Governor Limits), it was decided to summarize this dimension and start the base level with what are actually the first level parents of the full hierarchy, the "D" level parents.  Data Exchange can provide any reporting on what lowest level fund member rolls to these D level parents.

 

Custom3 – Purpose - Used to track the purpose/mission/NA CUBO classification/high-level activity.  The members in this dimension are prefixed with PUR_.

 

Custom 4 – Future - This is set up for future use.  Initially the only member will be the "No Future" member.

 

3.      Loading Data

 

3.1     Overview

Data Exchange is a data integration tool built within all Oracle cloud EPM tools, that allows for the import and transformation of source data into each EPM tool.  Integrations can be built to directly integrate with cloud ERP as a source as well.  Data Exchange allows for the transformation of source data that is defined in formats not native to each EPM tool such as FCCS, or conforming to its metadata (dimensions such as account, entity, fund, purpose, etc.).  Thus Data Exchange will be used to import, map from source to target and transform the data to FCCS format, then load this data into FCCS so that it can be incorporated into the University of Chicago total enterprise reporting.

 

3.2     Importing and Exporting Data – Data Exchange

Source data will be loaded into FCCS using Data Exchange.  To start the import of source data for a reporting/month-end period:

 

Data load rules are defined to allow a user to run the import and export process for a specific set of source data.  Data Exchange combines a data load rule and a location to determine how the source data is structured and how to transform it from source to target FCCS format.   Each location has one or more data load rules, depending on their purpose, but each data load rule only belongs to one location.

 

3.2.1  Setting up the Data Load Rule Point of View (POV)

Below is a table that should help you find the data load rule that corresponds to the location and mapping set up to load data from each source system, whether cloud ERP or a flat file. 

 

 

The Periods in FCCS are set to a Calendar Year Period Key.  These calendar dates are then mapped to a specific Period and specific Year in FCCS.  The periods in FCCS correspond to the University's close schedule, which runs on a Jun Year End.  Thus, the actual calendar period of July 2024, is actually mapped in FCCS to the Period JUL and the Year, FY25.  This is because the calendar date may be July of 2024, but this corresponds to the Reporting Period (which FCCS mirrors) of Jul FY25. 

 

Below is a sample of the Period Mapping in Data Exchange to show the correlation between Calendar date (Period Key), Data Exchange Period (Period Name), FCCS Year (Target Year) and FCCS Period (Target Period – Month).

 

3.2.2  Loading the Data (Direct Connection)

Once you make your selections for the Location, Period and Category, the next step is to execute the Import. There are two methods to import Data.  Direct Connection and Flat File.  All business recorded with the two cloud ERP's will use the Direct Connection.  All others, such as the University affiliates, will load using a flat file.  To Import using the Direct Connection:

 

 

 

 

 

3.2.3  Loading the Data (Flat File)

The University affiliate entities will use a flat file to upload to FCCS.  To upload a Flat File:

 

 

3.2.4  Exporting the Data to the Application

Note:  ENT_707 – UChicago Property Holding Corp is loaded by TWO separate data sources, UChicago's cloud ERP and an additional flat file.   Since this entity is being populated by data files from two locations, the correct option on Export is ACCUMULATE.  This is so the data loaded from the ERP is not cleared when the additional flat file trial balance file is loaded.

 

 

3.3     Reporting on Data Exchange results

To review the transformation of the source data, whether cloud ERP or flat file, the workbench provides a good place to quickly review and filter on specific members or intersections.  Oracle's cloud EPM cloud solution also provides reports that can be run and downloaded to review and archive copies of this transformation process. 

 

3.3.1  Running Excel Export of Workbench

To export the contents of the Workbench grid, which shows the source dimension information and it's corresponding target FCCS dimension members, navigate to Data Exchange and:

 

 

 

 

3.3.2  Running Reports from Data Management

Oracle has created multiple pre-defined reports to view the results of the data transformation as data is loaded to FCCS.  These reports are not in Data Exchange, but instead in the legacy integration module Data Management.  While Oracle has gradually migrated the integration process from Data Management to Data Exchange to provide more functionality in the integration process, some artifacts still remain in the legacy tool, Data Management.  To navigate to the Data Management reports, navigate to the Navigator icon, and from the Integration section, select Data Management.

 

Note: Data Management opens as a pop-up.  You may need to enable this specifically or all pop-ups for this module to open.

 

Once Data Management opens successfully:

 

 

 

3.4     Resolving Errors During Load Process

 

3.4.1  Reasons for Errors in Loading Data

There are several different types of issues that arise during the Data Load process that can lead to errors (white fish) during the execution of the load:

 

3.4.2  Resolving Load Errors

There are two ways errors can occur during the import/mapping/export process.  If Data Exchange does not find a mapping for a source data file member, it will appear as a second tab next to the Load Data tab and identify the mappings that are missing.  You can then correct these in the map.  When finished, re-select the Validate step to reprocess the map.

 

A second identification of errors happens during the export into FCCS.  This happens when wildcards are used in the mapping, thus Data Management can map an item, but there is no corresponding member in FCCS to load to.  This error will show as an error on the Export step. 

 

To review any errors that happen on Import, Validate or Export, use the Process Details page.  To navigate to the Process Details page: 

 

 

 

 

 

The log file can be a little difficult to read as it contains all the process details for the entire import, validation and mapping and export process.  Check to the right of the ID and see if there is a Download link.  If so, this will often provide a better summarization of any issues.  The Process log often puts metadata issues toward the bottom of the file.  Look for the words "Driver Member" and "failed".  This typically indicates which members in FCCS the data file could not find a home for during the load process.  If the error is related to a missing dimension member (mapping issues), you can additionally see a "Download" link to the far right of the screen that will allow you to download a log that ONLY shows the missing members.  This log is easier to review.

 

Upon review, you can either map the missing members to an existing member or add the new members into FCCS.  Once you have completed these steps, then select on the Validate step once again to reprocess and re-run the map.  When no errors exist on the last step, the process will show in Process Details with a green circle and a white check mark through.

 

4.      Consolidation

 

4.1     Consolidating Data

After data has been changed within FCCS, such as a data load or posting of a Journal, the data will need to be aggregated and calculated before it is ready for reporting.  The Job within FCCS that runs this process of aggregation is called: Consolidation.  Running the rule to Consolidate runs a set of built-in programs in FCCS that sum the data from where it is loaded in the base members of each dimension hierarchy into their respective parent members until it reaches the top-most member of each hierarchy.  The process also translates where a base member is defined in a different currency than its parent and eliminates any intercompany transactions at the common parent entity.

To Consolidate data, follow the steps below:

 

 

Note:  There are occasionally times when a consolidation gets stuck on a particular intersection.  This can happen if a consolidation was running during the backup or was cancelled.  If you get an error that mentions the consolidation process won't complete because it seems stuck on an intersection that you can clearly see is successfully consolidated, then you can use the Force Consolidate rule to bypass that.

 

Note:  If you have more than one Entity hierarchy, you can consolidate ALL entity hierarchies at once, use the "Entity" member.

 

Note:  If you receive the Information message below claiming the consolidation time has exceeded the maximum configured time, select OK to close the box.  If you have run multiple consolidations, you will sometimes get this message but the consolidation will still run.  You can select Refresh after about a minute (longer if running a consolidation for a full year).  When the status comes back OK it is done.  You can also close out of the Consolidation grid and check the status in the Jobs module.

 

4.2     Review Status in Jobs Console

To review the status using the Jobs Console, select the Applications icon to open the Applications functions. Then select the Jobs icon.

 

The Jobs Console will display.  If you just ran a consolidation, it is usually shown at the top of the list.  If it is not finished yet, you can wait and periodically select the Refresh button until it shows the status of Completed (as shown below).

 

To exit from the Job Console screen, select the Home icon.

 

When the data has been fully consolidated, the Consolidation grid should show a status of OK.

 

5.      Consolidation Journals

 

5.1     Overview of Journals

The journal functionality in FCCS is available to provide the ability to do topside adjustments as needed.  FCCS Journals can be used to perform reporting reclassifications, make adjustments, or alter data after a trial balance load has been completed and reconciled.  Journal entries will be used within the FCCS application to book the adjustments to provide an adequate audit trail for these types of adjustments that can then be viewed in a report format. 

 

There is a great deal of functionality offered in FCCS regarding Journals, such as:

 

Journal entries will be entered as monthly PERIODIC data in FCCS.  FCCS stores data as Periodic, so creating and posting Journals that are periodic will align with the design of FCCS data storage.

 

Adjustments can be booked as needed at the base entity level as well as at the parent entity level.  Journal entries booked in the FCCS application will be separate from the "Loaded" data as FCCS will automatically place these adjustments in a dedicated Data Source member.  This ensures they are not affected if using the load method "Replace".  The Data Source member defined for Journal Input is FCCS_Journal Input.

 

The Journals module establishes controls around the creation and posting of Journal adjustments in two ways:

  1. A Journal Workflow process that requires a separate user to Approve a Journal than the person who creates the journal.
  2. Requiring a journal period to be OPEN before any journals are approved and posted.  To prevent further adjustments once a close period is finalized, the period can then be CLOSED.

 

 

5.2     Creating Journals

To Create a journal:

 

Note:  If you prefer to see the dimension LABELS rather than the ALIAS (description), change this in User Preferences.  See Section 1.2 for how to change this.

 

 

5.3     Selecting Dimension Members in a Journal Row

To select dimension members for a journal row, use one of these methods:

 

Option 1:   Type the desired dimension member name in the text box for the corresponding dimension.

Option 2: Select the Actions icon found at the end of a row in the line item table.

 

Option 3:

The third option for selecting dimension members in a journal row is to copy and paste them once you have at least one row created.  This function can be used to take the dimension members from one row and copy and paste them to a new row.

 

 

 

5.4     Finalizing the Journal

Enter the adjustment amounts.

 

Once the journal has been created, it must go through the Workflow process to be posted and finalized.  Users can process and finalize journals one at a time or in batches.   The journal period must be open before users can post, and users must have access to the security classes for each dimension for the detail lines. The process is run from the Actions menu and is as follows:

 

As users work through the Journal Workflow process, emails are sent to identify what process is required next.  The journals come from the email address:  no-reply@oracle.com.  The email will note whether the Journal needs to be Approved, or has been Approved and can be posted.

 

The settings to ensure users get the necessary email reminders for Journal processing are found in the Tools menu, in the User Preferences card.  Once in Preferences, select Notifications in the menu panel to the left.   In the Notification Type section, drill below Status Change Notification, then Task Manager.  The email notifications for Journals are the last two items in the list.  You can then change your preference for when the email notifications come (Immediately, a certain time frame or Never) and for which role, Submitter and/or Approver.

 

 

The steps to Submit, Approve and then Post Journals are:

 

5.5     Copying Journals

Users can copy a journal to one or more periods so that they can easily create another journal with similar information. Users must have security rights to the journal and make sure that a journal with the same label does not already exist. For example, if users have a journal for January, they can copy the same journal to one or more periods. The journal is copied to the destination point of view and has a Working status.

 

To copy journals:

                             

 

To Edit the Copied Journal:

 

5.6     Creating Auto-Reversing Journals

Auto-reversing journals in FCCS affect two different periods.  The initial journal in the first period is considered the Reversal journal.  The journal in the second period (the next period) is considered the Reversing journal. 

 

 

 

 

5.7     Importing/Exporting Journal Details

You can Import and Export Journal details from flat files.  To Import/Export, navigate to Consolidation Journals.  In the Manage Journals screen, the Import and Export option can be selected from the Actions menu to the far right.

Below is a sample of an export file to show the structure.  Import files should match the same structure. 

 

Special Note on Importing files containing Auto Reversal detail:  The initial auto-reversal journal (created in the first period) can be defined in the Journal flat file.  But the Reversing journal created in the next period should NOT be contained in the file.  FCCS creates the reversing journal when the reversal journal is posted and the user selects the period the reversing journal should be posted to.  For this reason it is best to set the Status of the journals in the file to "W" (Working) so this process can be done correctly.

 

5.8     Run Journal Reports

Users can create journal reports to check the status of journals and to review journal adjustments.  They can select the POV, format options, and format type. These report format types are available:   HTML, PDF, XLS, and XLSX.

 

When Users open the Journal Reports page, the POV is displayed by default from the Manage Journals page.  Users can filter the list of journals and journal line items to display on the report. If you do not select filters, all items are displayed on the report.

To Create a Journal report:

 

From the Report Type, select an option:

 

6.      Cash Flow

 

6.1     Overview – Cash Flow Design

The Cash Flow reporting functionality in FCCS is driven by an out-of-the-box custom dimension called the Movement dimension.  This dimension calculates and captures the changes in the Balance Sheet accounts needed to populate the Cash Flow report.  Calculations populate the Opening Balance of every Balance Sheet account with the Closing Balance from the Prior Period. This is then aggregated with data loaded from source ledgers or input through web forms to populate the changes for the month and sum to the Closing (YTD/LTD) Balance.  These rollforward elements are then used to create a Cash Flow from the Movement dimension.  There are then three basic parts to finalizing the Cash Flow in FCCS:

 

  1. Base Balance Sheet account members are mapped in Data Exchange to a particular Movement dimension member below the parent FCCS_Mvmts_Subtotal.  This creates an initial DEFAULT population of the Cash Flow Report.
  2. Input through customized web forms set up to capture the Rollforward information of specific Balance Sheet accounts will then allow for the input of that information that allocates the DEEFAULT The sum of this "allocation" should be zero with the web form showing this sum as a Check within the form to prevent unintentionally changing the Closing Balance.
  3. Reporting created specifically to evaluate and report on Cash flow data.

 

6.2     Initial Population – Data Exchange Map

The assignment of a Movement dimension member to each Account, as it is being loaded from the source G/L, is created using the Data Exchange Map for the Movement dimension in Data Exchange.  As the source data is loaded as Periodic month changes, this data combines with the calculated Opening Balance in FCCS, in the Movement member FCCS_OpeningBalance, to create the new LTD Ending Balance.  Below is a screen shot of the cloud ERP mapping, which should serve as the foundation for all other data location mapping.

  

Most accounts are defined as ranges, so the map item is applied to any account within the range.  For instance, the first item shows a range between FCCS account 10000 to account 10990.  This means any account that falls within that range (inclusive) is mapped to FCCS_Mvmts_Cash.  Below is a table showing the current mapping of Source Account to the FCCS (target) Movement member:

6.3     Finalizing Input – Cash Flow Forms

Once data has been loaded and the initial (default) population of the Movement members has been created, customized web forms can be used to further refine the Cash Flow data.  The goal of the web forms is to allow a user to move amounts initially populated in the default movements to more appropriate  members depending on the transaction. Thus, the cash flow input forms function as a re-allocation tool.

 

There are two sets of forms, each within different folders.  The forms within the folder UC Cash Flow Input Webforms allow a user to input and re-allocate cash flow data at a base entity level.  The forms within the folder Segment Cash Flow Input Webforms allow a user to select a Segment (parent) entity, and the form automatically selects a specific base entity to hold the input.

 

6.3.1  Initial setup to use Cash Flow Forms

If the Segment forms will be used, setting the User Variable in the User Preferences page is necessary for the forms to work properly.  This User Variable links each segment-level parent entity with an appropriate base entity that will hold the cash flow input data.  The table below shows the relationship:

 

 

The selection of each Segment Parent in the first column as the User Variable, will then direct the form to choose the corresponding base entity in the Input Base Entity column.  This selection can be changed at any time.  In addition, User Preferences can be used to change how the data appears (is formatted) within the forms.  Form data formatting is driven by the formatting defined by an individual user in the User Preferences page and not by the design of the form itself.  To make the changes:

 

To save the changes, select the Save button in the top right-hand corner.  Then select OK when the Information box appears noting the change has been saved.

 

6.3.2  Opening the Cash Flow Input Forms

Grids and forms both are displayed in a folder structure.  The Cash Flow web forms are contained in two folders:  Segment Cash Flow Input Webforms (for input at a Segment parent-entity level) and UC Cash Flow Input Webforms (for input at an individual base entity).

 

 

6.3.3  Navigating in the Forms

All of the cash flow input webforms, both those created for segment or those for base entity input, have the same basic structure.  This structure is designed to aid in the correct re-allocation of the input and ensure the data remains in balance once input is finished.   

 

 

All forms are structured similar to the following form above:

 

Rows:           Balance sheet accounts

Column 1:    Total Opening Balance

Column 2:    Total Periodic month change populated through Data Exchange mapping (default Population).

Columns 3:  Same movement populated through initial default population, but with the input Data Source to allow the amount that should be re-allocated to the other movements.

Column 4-8: Input columns that should represent the other possible movements that data should be

 input to.

Column 9:     Calculates the sum of all the input to ensure data was fully allocated.  Should be zero.

Column 10:  Total Ending Balance.       

 

Some forms, such as PPE, may show accounts with different default movements.  For instance, the Gross PPE accounts populate the Purchases of PPE movement, while Accumulated Depreciation populates the Depreciation Expense movement.  This form will then have TWO default columns after the Opening Balance, and two corresponding "input" columns to allow for the movement out of either Purchases or Depreciation to more correct movements, such as Loss (Gain) on Disposal.           

 

The Point of View bar in these forms will change the period and year the form should target.  To change the selection:

 

 

Note:  the Segment forms do not have "Entity" as a selection on the POV bar.  Only the forms

designed for base entity input have this selection on the POV.  For the Segment forms, the User Variable serves as the Member Selector for this.

 

 

6.3.4  Inputting Data into Forms

Users can input data into Forms designed for this purpose if they have Write access to the data represented in the web form.  FCCS denotes input cells as white.  If a user needs to undo the input, they can backspace over the cell to undo or select Refresh.  Once the data has been input, the cell color will change to a light yellow indicating the data has been input but has not yet been saved to the database.  Click the Save icon to save the data.

 

Below is a table showing keyboard shortcuts users can use as they input data into forms.

 

Follow the steps to enter Rollforward adjustment data into the Cash Flow Input Forms:

 

Open the Data card and expand the folder:  Segment Cash Flow Input Forms.  Click on the PPE Segment Input form (tile icon) to open the form.

 

Example:  For account 19610, $15,000 needs to be in the Loss (gain) on disposal. 

                For account 19705, $(250,000) needs to be in the Loss (gain) on disposal.       

 

 

 

 

 

7.      Translation

 

7.1     Basic Translation

FCCS handles multi-currency translation during the consolidation process through use of the Currency dimension.  Data stored in the Currency dimension member Entity Currency for a base entity is in the currency defined for that entity (local currency).  During consolidation, the child entity's currency is compared to the currency of the parent entity.  If the currencies of each are different, then the child entity's data will translate to the parent entity's currency.  This translated data is then stored in the Parent Currency member of the Currency dimension. This translation happens automatically during the consolidation process.

 

FCCS uses several methods to translate data and the method used depends mainly on the Account attribute.  REVENUE and EXPENSE accounts translate using the Periodic Value method and the Average FX rates.  Most ASSET, LIABILITY and EQUITY accounts are translated using the Ending Value method and the Ending FX rates.  Certain ASSET, LIABILITY and EQUITY accounts can be also translated using a third method, Historical Translation.

 

7.2     Review FX Rates

The Exchange Rates are input to a web grid each month.  The rates used for both translating the Balance sheet (EOM Rate) and the P&L (AVERAGE Rate) can be viewed by opening the grids created to show the FX Rates.  To view the FX Rates:

 

 

7.3     Translation of P&L Activity

FCCS translates P&L accounts by using their monthly periodic activity and multiplies this by the Average FX rate specified for the month.  YTD translated data is then the sum of each of these individual monthly translations.  An example of this translation is shown below:

Note that the last column which represents the YTD USD translated values, is an aggregation of each months Periodic translated values:  Aug = Jul + Aug; Sep = Jul + Aug + Sep, Oct = Jul + Aug + Sep + Oct, etc.

 

7.4     Translation of Balance Sheet Activity

FCCS translates the majority of Balance Sheet accounts using a layered approach.  Because the Balance Sheet accounts in FCCS are linked to the Movements dimension to capture transaction detail required for populating a cash flow statement, the Movements dimension holds the Opening balances, the activity for the balance sheet account by period, and then the Closing balance.  Each of these is translated differently.

 

The Opening Balance reflects the translated USD balance from the prior period, depending on the view.  For the Periodic View, the Opening Balance equals the Closing Balance from the prior month.  For the YTD view, the Opening Balance equals the Closing Balance from the prior year-end.  For the QTD view, the Opening Balance equals the Closing Balance from the prior quarter end.  Essentially FCCS picks up both the local and the translated USD balances from the appropriate Closing periods and copies those balances to the corresponding Opening Balances.

 

 

The Activity for a period is always translated similar to the P&L activity.  Periodic values are translated at the Average Rate, and the sum of each months translated values represents the translated USD YTD values.  This data is captured in a relevant Movement dimension member below the parent FCCS_Mvmts_Subtotal.

 

FCCS Calculates the FX on each balance sheet account by splitting the FX effect on the Opening Balance into FCCS_Mvmts_FX_Opening and on the Periodic Change into FCCS_Mvmts_FX_Movement.

 

The sum of the translated Opening plus Activity balances and the translated FX Effect equals the Closing Balance.  The Closing Balance ALSO reflects the translation of that YTD (LTD) Ending Balance at the EOM Rate.  The translation of the Balance Sheet accounts can then be broken down as follows:

 

Closing Balance = Translated Ending balance.  This is the sum of the Opening Balance+Subtotal (changes) and FX.  It equals the local closing balance * EOM Rate for the period.

 

Opening Balance = Translated Ending balance from the prior month if Periodic view; Prior YE if YTD view.  For the QTD view, the Ending balance from the prior QTR End.

 

Total Movements = Translated local change * Cur Mth's Avg Rate.  YTD change is the sum of Jan to the current month periodic values (similar to the P&L).

 

FX on Opening =  FCCS_Mvmts_FX_Opening = (FCCS_OpeningBalance * (CM EOMRate – Prior Period EOMRate))

FX on Movement = FCCS_Mvmts_FX_Movement = (FCCS_Mvmts_Subtotal * CM EOMRate) – (FCCS_Mvmts_Subtotal * CM Avg Rate)

 

The Prior Period rate depends on whether you are reviewing the calculation for the Month (rate is prior month), Quarter (then rate is prior qtr end) or YTD (rate is Prior Year End)

 

7.5     Translation of Balance Sheet at Historical Rates

Some Balance Sheet accounts require translation at the spot rate on the date of transaction.  That spot rate must remain fixed over time, thus it is referred to as an historic rate, while the method to translate using this historic rate is referred to as the Historical Translation Method. FCCS has some difficulty translating at historic rates because one balance can be comprised of multiple historically translated balances, it is often easier to use an override method to translate these. An override amount consists of tracking the underlying detail for the correct translated amount offline and inputting this amount to FCCS to "override" the translation.  FCCS allows for two different override methods, an amount override and a rate override.  If the override is a rate, FCCS will translate using that historical override rate rather than the Ending FX rate to derive the correct translated balance.  If the override is an amount, during translation FCCS will "override" the balance translated using the Ending FX Rate and instead replace it with the override input amount. 

 

NOTE:  It is important to note that if an account is defined in FCCS with one of the "historical" attributes, any local amounts without an override will get translated using the Average rate.

 

7.4.1  Using the Override Input Form in FCCS

The Override method in FCCS is used to facilitate Historical translations.  These are typically for balances that are translated at the spot rate on the date of transaction.  Because FCCS does not translate using daily rates, a method that allows users to input the correct translated amount is used.  Once that amount is input and the Consolidation job run, FCCS will "OVERRIDE" the normal translation and use the amount of the input instead to represent the translated balance. 

 

To use the override method in FCCS, accounts that require historical translation should be defined with the Exchange Rate Type of Historical Amount Override.  Check with the Application Service Administrator, who can help determine if and what accounts are defined with this Rate Type. 

 

Once the accounts are appropriately identified as allowing "overrides" on translation, the seeded web form will allow users to input the USD translated balance.  Override input should match exactly the same intersection for all dimensions except Consolidation and Currency. 

 

To access the Override Input form, open the Data card.

 

 

When you have finished input, select the Save button at the top right-hand side of the screen.  You will need to run the Consolidation job to see the final results.  Once that has finished running, you can select the Refresh button to refresh the data on the screen.

 

7.4.2  Cumulative Translation Adjustment (CTA)

A local currency entity's balance sheet can go from being in balance at local currency, but out of balance after translation.  The result of this is due to the different rates applied to certain balance sheet accounts.  For most accounts, the EOM Rate for the current month is applied to the Closing balance.  But there are exceptions to this.  For instance, Equity accounts are typically translated at a historical rate.  Beginning Retained Earnings should translate at the PYE rate so that it matches the translated ending balance for the last month of the prior year.  Current Year Retained Earnings (Net Income in Retained Earnings) should be the result of a translation of the local balance at Average Rate.  The different rates applied to these accounts will cause the balance sheet to no longer balance.  To rebalance the balance sheet and capture this FX differential, FCCS automatically calculates the difference, and then populates this to a specific account in the Equity section, FCCS_CTA. 

 

The amount in FCCS_CTA can be analyzed by using the Movement dimension.  The FX is calculated and populated as a Movement by balance sheet account in the intersection of each account and the Movement member:  FCCS_Mvmts_FX_to_CTA.  During translation, account balances whose translated values diverge from translation at EOM Rate, are captured in this Movement member.  Below is a sample spreadsheet showing how this process works in FCCS:

The sum of those differences should be the amount in the FCCS_CTA account. Which cell G32 represents.  Col G shows that the Movement dimension member, FX_to_CTA, is also capturing those same amounts.  This allows users to drill on this data to analyze the CTA balance.

 

8.      Task Manager

 

8.1     Overview of Task Manager

Task Manager helps to define, execute, and report on the interdependent activities of a reporting period. It provides centralized monitoring of all close process tasks and provides a visible, automated, repeatable system of record for running close process.  Tasks are specific activities assigned to users to facilitate a process like month-end close and contained in FCCS within a Schedule. 

 

Tasks have due dates, an assignee and a backup (optional) along with other options such as approvers, instructions, parameters (for integrated type tasks) and can specify instructions, require the answering of question(s) or attachments.  Users interact with each task assigned to them and SUBMIT the task when done to indicate its completion, which are then captured in reports and dashboards to allow for monitoring of the process.

 

Schedules, as collections of tasks, provide the means to coordinate and monitor the overall progress of all tasks within the Schedule.

 

Users can interact with their assigned tasks in three ways:

 

  1. From a Worklist on the main desktop.  This will open the Task Worklist page.
  2. From an email notification (if email notification has been turned on)
  3. From within the Tasks card, in either the Worklist and Schedule Tasks pages.

The Worklist on the main desktop shows the status of tasks for the reporting period.  It is important to review the reporting period shown. Each task is part of an overall Schedule, which is assigned a specific reporting period within FCCS.  To change the reporting period, select the small down-arrows under the Year and Period to reflect the current reporting period.  The Worklist will show you the number of:  Late tasks, tasks due today, tasks due within the next 7 days and the total number of open tasks.  The "word" next to each number is a link you can select to open the Task card and take you to the entire worklist.

 

You can alternatively navigate to all your assigned tasks by selecting the Tasks card on the main desktop.

Within the Tasks screen, to the left are three icons you can use to navigate within Tasks:

  1. The first icon is the Worklist.  This shows all tasks for a period, regardless of Schedule and also includes Journals.
  2. The second icon is the Schedule Tasks.  This allows a user to separate viewing tasks not only by Schedule, but with an extensive list of filter possiblities that make this view the easiest to see what tasks need to be worked on.
  3. The third icon is Alerts.  This can be used by users to alert to roadblocks in completing their tasks, such as system downtime, issues with security, etc.

 

8.2     Working within Task Worklist

There are several ways to filter the list of tasks within the Worklist by selecting either an icon or hyperlink:

 

8.3     Working within Schedule Tasks

The Schedule Task page has more filters to allow users more options to view the tasks they want to work with or see.

 

 

8.4     Working with Tasks – Opening and Submitting

The Task details will show the timing of the task, priority and any comments, questions, attributes and alerts assigned.  As a user, you will complete the task as required.  If questions are needed to be answered, you will see a number above the Questions column, but can also see this on the right-hand side of the screen in the task icons to the left.  Any questions that must be answered will appear with a yellow triangle within the Questions icon.

Click on the Questions icon and answer any questions shown.  Most have a drop-down box that require a Yes/No response, but sometimes can ask you to provide information.

 

Note:  If for any reason you need to re-open it, contact your FCCS Administrator

The details for your task will now update to let you know it has been submitted and successfully closed.

 

 

9.      Smart View:  MS Office Add-In

 

9.1     Introduction to Smart View

Smart View is a Microsoft Office plug-in where you can view, import, manipulate, distribute and share data from FCCS in Microsoft Excel, Word, and PowerPoint interfaces.  Smart View benefits include:

 

9.2     SmartView Ribbon

The SmartView Ribbon displays all of the necessary features to connect UChicago's Oracle applications to Excel. 

 

9.3     Customizing Smart View Using Options

Options allow the user to customize the Smart View report for specific needs related to formatting, navigation, the hierarchy and display.  Access Options from the Smart View tab to view all sub menu option items.  Options include:  

 

9.3.1  Member Options

 

9.3.2  Data Options

 

9.3.3  Advanced Options

 

9.3.4  Formatting Options

 

9.3.5  Cell Styles Tab

 

9.3.5.1.        Setting Cell Styles

Smart View data source providers each have a set of default cell styles to identify cell types. For example, Planning designates dirty cells by a yellow background and read-only cells by a gray background. You can modify default styles in Smart View change cell styles by selecting different fonts, background colors, or border colors.

Note:  The Use Excel Formatting option overrides all style options set in the Cell Styles tab of the Options dialog box.

 

9.3.5.2.        Cell Style Precedence

Cells can be associated with more than one style; you can specify the order in which styles are applied in such cells.  To see a child member style, turn off the parent member style. To see a shared member style, turn off both parent and child member styles.

To change the order of precedence for member and data cell styles, select the member or data cell and then select Move Up or Move Down to give higher or lower precedence, respectively, to the style for that cell type.

 

Note:  To reverse cell precedence of cell styles to their defaults, select Default Styles.

 

9.4     Ad-hoc Analysis

In ad hoc analysis, use Smart View functionality with Excel spreadsheets to retrieve and analyze data by selecting members, using functions, and performing a variety of operations, including formatting, to design your reports. Ad Hoc grids are used to analyze data from a data source. Creating an ad hoc query enables data to be viewed for specific dimensions from FCCS without writing spreadsheet functions.

 

Use the Ad Hoc Analysis tool to:

 

9.4.1  Open Ad-hoc Analysis Grid

After logging into the SmartView connection, ensure that your connection is active. From the SmartView ribbon, select the Connections box, then Active Connections, and finally your connection name.  This will activate the connection.  A new SmartView ribbon will open, FCCS Ad hoc and you will see a check mark next to your connection name.

 

Once you have connected and activated the connection within your current spreadsheet, there are two options to create a new Ad-hoc Analysis grid:

 

 

9.4.2  Selecting Members for Ad-hoc Analysis

Once users have moved dimensions to the appropriate areas of the form, they will need to start defining the details for each dimension. Use the Member Selection dialog box to view options to manage dimension members. Users can filter the list of members and they can view labels or descriptions for dimension members. Users can select a maximum of 1,000 members for the ad hoc POV. Users can also search for dimension members.

 

 

9.4.3  Basic Member Selection

To select members for the Point of View (POV):

 

To select members for the rows and/or columns, 

 

9.4.4  SmartView Ribbon – FCCS Ad hoc Functionality

After Hyperion Smart View for Office is installed, the Hyperion Smart View toolbar is displayed in Excel automatically. Once an active connection to the database is established, the FCCS Ad Hoc tab will appear.

 

Below is a table explaining the functions contained within the FCCS Ad hoc tab that allow a user to manipulate an ad hoc query to facilitate reporting and analysis.

 

9.4.4.1.        Zoom in and Zoom Out

You can drill down to various levels of data in the grid by expanding the members.  When you zoom in on a member, it is expanded according to the options specified in the Options dialog box.  If you select the option to zoom in on all levels, the grid displays all descendants of the selected member.  The default zoom option is to zoom in to the next level, which will retrieve data for the children of the selected member.

 

To zoom in on a member, highlight the member and go to SmartView > Zoom In or icon shown on the ribbon.  You can also zoom in by double-selecting your left mouse button, but that is a setting in Options.

 

           

Users can drill back up to higher levels of data in the grid by collapsing, or zooming out to, parent level dimension members.   Zoom out will always go up one level.

 

To zoom out on a member, highlight the member and go to SmartView > Zoom Out or icon on the ribbon.  You can also zoom in by double-selecting your right mouse button.

 

9.4.4.2.        Pivot

Pivoting will change the orientation of the data on the worksheet. You can move dimensions between rows and columns and move dimensions in and out of the grid. When you move between rows and columns, the system moves the selected dimension to the outermost row or column on the opposite axis. For example, when you select a dimension in the column area to Pivot, the system moves the dimension to the far-left side of the grid, and when you select a dimension in the row area to Pivot, the system moves the dimension to the top of the grid.

 

To Pivot, highlight the cell with the dimension and go to SmartView > Pivot or icon on the ribbon.  All members associated with that dimension will moved, as well.

You can also select to pivot a dimension out of the grid to the POV.  Before you remove a dimension from a grid, you must make sure there is at least one dimension left for that area in the grid.

 

To Pivot to POV, highlight the cell with the dimension and go to SmartView > Pivot to POV.  All members of that dimension will be moved into the POV, as well.  You will see members originally in the grid listed in the drop-down box.

 

9.4.4.3.        Keep Only/Remove Only

When you select Keep Only, the system keeps only the selected member(s) (the active cell) or member range within the grid.  All unselected members are removed from the worksheet for that dimension.  The options that you select for member retention also affect the behavior of the Keep Only option.

You can select single member, use Ctrl to select multiple members, or highlight a range and go to SmartView > Keep Only or icon on the ribbon.  Or you can simply just delete the member(s) and associated data from the grid.

 

You can also modify the ad hoc grid by removing dimension members from the grid.  When you select Remove Only, the system removes the selected member(s) (the active cell) and data or member range and data from the worksheet for that dimension. All unselected members are retained in the worksheet.  You can select single member, use Ctrl to select multiple members, or highlight a range and go to SmartView > Remove Only or icon on the ribbon.  Or you can simply just delete the member(s) and associated data from the grid.

Note:  If you delete members from a grid using the Delete key (not using the Remove Only command), you must also delete the corresponding data points; otherwise, when you refresh the grid, the remaining cells are treated as comments.

 

9.4.4.4.        Refresh

You can refresh the grid at any time to retrieve data from the connected data source into the active worksheet.   Refreshing the grid also helps you to determine if you are pulling the correct data. For example, after you change the Point of View, you can refresh the grid to see the changes. If you want to save the Point of View settings in a worksheet, you must refresh the settings before you save the worksheet.

 

To refresh the grid, take the following action:

From the FCCS Ad Hoc ribbon, select the Refresh icon.       

 

9.4.4.5.        Alternative Drilldown methods

An additional method for drilling down into rows and columns from parent dimension members to lower levels is to simply double-select a member.  The driver of how far down the drill will take a user is dependent on the Member Options set in the Options panel selected.  For instance, if the Member Option is set to Next level, then the siblings below the member double-selected on will appear.   If All Levels is selected, then every level below the parent a user selected on will display.

 

Below is a sample of Dimension selections along with pivoting Entity to the column and double-selecting the top Balance Sheet account to Next Level.

 

9.5     Function Builder

Use Functions to retrieve data from a Microsoft Excel worksheet and save it to a data source, or to send data from a data source to a worksheet. Functions can also be used to display member descriptions and labels and send and retrieve cell text.  Below are the most commonly used functions:

 

To analyze data within a spreadsheet, all dimensions must be available for reference or member selection.  Members can be hidden for report distribution. If referencing cells, member names must be used; therefore, those rows/columns may be purposefully hidden and referenced by other cells to only show descriptions.

 

 

 

The Function Builder then creates a formula to reference where each dimension member a particular cell should use to define what data to pull from FCCS.  You can then add members to your spreadsheet for the rows or columns and then copy the formula to build your spreadsheet out.  You can also move dimensions from one place to another, for instance you could move the Entity down to the rows rather than have it in the Point of View section.  Be sure that as you move the formula, you review the cell references and any absolute ($) you fix to it so that the reference will change as you expect it to as you copy it.  Below is a sample of the final formula.  Note that each dimension is specifically referenced and then after each is the cell reference that denotes the member to pull data from.

 

10.    Reporting

 

10.1   Introduction to Web Reporting

Oracle's Web Reporting Studio is used to create standardized, highly formatted reports that all users can use to see data from FCCS.  The reports are typically created by someone designated as the Service Administrator, although a designated Power User can create and edit reports as well.  This functionality dictates how users see data as the reports are standardized in their format, so it is typically limited to a small group to ensure some degree of control over the process.  Most reports can be created to be fairly dynamic using functions built within the tool.  This allows for less modifications as the application (and the organization it serves) changes as time goes on.

 

10.2   Running a Web Report

To run web reports:

 

General Reports hold reports that have no logo (such as UC or UMC logo) and can be run for any Entity, Period or Year.  These reports can be used by anyone.

 

UC Reports holds all the reports for the University reporting.

 

UCM Reports holds all reports created specifically for the Medical Center reporting.

 

To run a report: 

 

 

 

Note:  Any dimensions not on the POV are hard-coded either in the Rows or Columns in the report, or in the Point of View.  For many reports, the Custom dimension selections should be the same, thus are hard coded to ensure standardization of the reports.

 

 

 

Below is a sample of running the report to Excel.