Secrets of the Master Row Set

in Oracle® E-Business Suite Session # 12250

Helene Abrams eprentise

eprentise Can… …So Our Customers Can: Consolidate Multiple EBS Instances Change Underlying Structures and Configurations Chart of Accounts, Other Flexfields Inventory Organizations Operating Groups, Legal Entities,

Ledgers Calendars Costing Methods

Resolve Duplicates, Change Sequences, IDs Separate Data

: Transformation Software for E-Business Suite

Reduce Operating Costs and Increase Efficiencies Shared Services Data Centers

Adapt to Change Align with New Business Initiatives Mergers, Acquisitions, Divestitures Pattern-Based Strategies

• Make ERP an Adaptive Technology

Avoid a Reimplementation Reduce Complexity and Control Risk Improve Business Continuity, Service Quality and Compliance Establish Data Quality Standards and a Single Source of Truth

Company Overview: Established 2006 Helene Abrams, CEO

Learning Objectives Objective 1: Learn the components of FSG: rows, columns and row or column sets. Objective 2: Learn the theory behind and importance of the Master Row Set. Objective 3: See techniques for building a Master Row Set. Objective 4: Learn how a single chart of accounts, with ranged values, promotes streamlined reporting. Objective 5: Learn how a Master Row Set enables you to provide flexibility to your users.

Agenda • Introduction.

• Financial Statement Generator (FSG)

components.

• Importance of a Master Row Set.

• Techniques of creating a Master Row Set.

• Streamlined reporting with a single chart of

accounts with ranged values.

What is the FSG? • “Custom” reports that allow you to report on General Ledger activity. • Generate financial reports, such as income statements and balance

sheets, based upon data in your General Ledger . • Copy to reuse for different ledgers, ledger sets, or reporting currencies or

report across ledgers in a ledger set. • Generate presentation quality financial reports with Business Intelligence

(BI) Publisher so that you can have more control with report formatting options, including changing font characteristics, inserting graphical images or logos, and adding color.

• Define segment value security rules to restrict financial information contained in FSG report output generated by specific users and responsibilities.

• Enable definition access set security to allow specific reports or report objects to only be used, viewed, or modified by certain users.

Mandatory Components of a FSG • FSGs are comprised of rows, columns and row or column

sets: • Rows - detail of your report (row headings, accounts or

flexfield assignments, calculations, totals). • Columns – different periods or companies or consolidations. • Report – a single combination of a row set and a column set. • Row sets and column sets are the two primary building

blocks of FSG reports.

Optional FSG Components • Content Sets – groupings of the same row sets and

column sets sorted by a flexfield segment. • Row Order – display and sorting characteristics of

rows in a report. Used primarily to insert a segment value (like the account number), or to utilize the description of a segment value.

• Display Set – used to block out ranges of rows or column sets simultaneously.

• Report Set – a group of reports that are run together.

Building a Basic FSG Report 1. Define which rows and columns you need to display in your

report.

2. Define the rows, columns and the attributes those rows and columns have.

3. Build a report using those rows and columns.

.

Example of FSG Components

Creating Rows

Display Options

Account Assignments

Calculations

Creating Rows

Set Account Assignments

Creating Rows

Calculations

Defining Row Sets • A powerful feature of FSG is the ability to collect

multiple row or column definitions into sets.

• Row set and column sets are the two primary building

blocks of FSG reports.

Define an FSG Report

Mandatory Components

Basic FSG Report Objects

CO CC ACT SUB PROD LOC PTD Actual Jan 2012

PTD Actual Jan 2013

Variance

01 000 5530 110 10 122 7,596 11,143 3,547 01 000 5599 110 10 122 159 175 16 01 000 5600 112 10 132 84,532 94,676 10,144 01 000 5610 115 10 132 652 574 (78) 01 000 5620 117 10 132 23,456 24,394 938 01 000 5630 125 10 145 23,152 25,467 2,315 01 000 5640 126 10 145 42,350 47,432 5,082 01 000 5700 127 10 160 72,354 63,672 (8,682) 01 000 5710 148 20 160 23,659 24,605 946 01 000 5720 148 20 160 453 498 45 01 000 5800 150 10 132 9,452 10,586 1,134 01 000 5810 152 10 132 95,325 83,886 (11,439) 01 000 5910 155 10 132 1,242 1,292 50 01 000 5920 176 10 132 4,523 7,689 3,166 01 000 5930 177 40 132 4,213 4,719 506 01 000 5930 189 40 260 8,526 7,503 (1,023) 01 000 5940 190 10 260 3,333 3,466 133 01 000 5999 191 30 260 12,564 17,967 5,403

409,945 429,744 12,203

Date: Jan-31-2013 13:32:55 Page: 2

(Periods) (Calculated)

Column Set

Columns US Global Operations

Comparative Expense Listing Current Period: Jan-2013

Title Headings

Currency: USD No Specific Company Requested

Row Definitions

Row Set

Row (Calculated)

Using the Master Row Set

Define the

Financial Report

Set

Using the Master Row Set

Name the Row

Set

Example of a Row Set

Importance of a Master Row Set

• A master row set is a super set of all the rows in all of your reports and includes the attributes or characteristics of every row and row set into a single, super, report.

• A master row set saves time, provides agility and consistency.

• Using a master row set enables you to use a top down approach vs. a bottom up approach when reporting with the FSG.

• Once a master row set is defined it will be virtually unnecessary to create additional row definitions for reports.

Defining the Master Row Set • Create an inventory of all the requirements

across all your reports by: • Defining a row set that contains the entire range of

account values, as well as the entire range of segment values for each segment in your chart of accounts, on the account assignment.

• For each row, determine all of the possible characteristics of that row.

Master Row Set Example

1. Give each report component a unique name. • For all reports you will need to copy the master row set and

then delete all the rows that are not needed for each new report (top-down approach).

2. Assign descriptive names to the row set being copied such as “Balance Rows” or “Expense Rows.”

7 Steps for Creating A Master Row Set

3. Note that the “Title” field is what shows up on the printed report.

• There is a single line for the title, and it will print in the center of the page underneath the “Set of Books Name.”

• The third title line of the report is the “Period” for which the report is run.

• The date and time of the report and a page number will print on the upper left corner of each page. Additional title lines can be typed into the “Column Set Heading” field.

7 Steps for Creating A Master Row Set

4. Draw each report on an Excel spreadsheet. • Note similar flexfield assignments, calculations, and totals for

each report. • Determine standard characteristics for the rows. For

example, you always group cash accounts together and report them as one total line, but you list expense accounts individually on all reports.

7 Steps for Creating A Master Row Set

5. Assign sequence numbers for each row to reflect the accounts in that row.

• Use the same number of digits as the value for the accounts in that row.

• The account number order is generally the order in which reporting is usually completed. Examples include: • Sequence numbers for all asset rows might start with a 1xxxx. • Rows with cash accounts might have a sequence number of

11xxx. • Rows with liability accounts might have a sequence of 3xxxx.

7 Steps for Creating A Master Row Set

6. The sequence number should reflect the type of row. • Rows with sequence numbers ending in 0 or 00 should be header rows or

subtitles. • Sequence number 11000 should be a header row with no account assignments

or calculations and should be titled “Cash Assets.” • Rows ending in 99999 or 9999 should be totals or subtotals. • Sequence 11999 would be a subtotal of all cash asset rows. • Sequence 19999 would be a net of all asset rows. • Parallel the names and numbers in the values for the account segment of the

flexfield and use the 9 as a parent or summary account Have the value description read “Total Current Assets” or “Net Accounts Receivable.” Enter the value set descriptions in a form that does not require substantial retyping.

7 Steps for Creating A Master Row Set

7. Autocopy completed row sets and then make modifications for each of the other reports. Autocopy all report components (column sets, content sets, row orders, reports and report sets).

• Use Autocopy to make a copy of your master row set. • Delete rows that are not necessary in the report you are

creating. • Create or use pre-defined columns sets to define the

amount types.

7 Steps for Creating A Master Row Set

• To leverage a master row set it is important to have a single COA organized into logical ranges for each segment.

• If each segment is not in a logical range, the “out of range” values increase the number of rows that are necessary to capture all account values making the use of a master row set more difficult.

• Multiple COAs may have different segments and values potentially eliminating the use of a master row set.

Streamlining the Development of a Master Row Set

Useful Row Set Reports • Row Set Detail Listing

• Review detailed information about a specific row set, or about all row sets defined for your chart of accounts.

• General Ledger prints the details of each row definition, with display and format.

• options for each row appearing in a box. General Ledger also prints your account.

• assignments and your calculation definitions, if any. • Row Set Summary Listing

• Review the names and descriptions of all row sets defined for your chart of accounts.

• General Ledger displays the chart of accounts structure associated with each row set.

Conclusion

• THAT’S IT! You have a top-down procedure for creating any type of report in a matter of minutes.

• Time-to-information has been reduced. • Management can now get the enterprise

information they need quickly and without coding, extracting, or transforming the data (or calling the IT department).

Conclusion • Building a master row set for your FSG reports

will: • Save time and money. • Provide agility and consistency. • Allow business users the ability to quickly create new

reports “on-the-fly.” • With a single chart of accounts with ranged values

you will be able to fully utilize this cost savings built-in feature of EBS.

• Build a master row set and be the master of your own reporting destiny.

Questions? Comments?

THANK YOU Helene Abrams, CEO

eprentise habrams@eprentise.com

www.eprentise.com

Visit eprentise at booth 1323!