FSG Report Writing Basics by mre15330

VIEWS: 113 PAGES: 20

									Oracle
General Ledger




Financial Statement Generator
FSG Report Writing Basics
FSG Definition Screens
   NAV : General Ledger : Reports > Define
   This document details the configuration of FSG reports in the
    core general ledger screens. It has been written based on Oracle
    11i functionality, but most features are applicable to previous
    versions.
   Although possible, the writing of FSG reports using ADI is not
    covered by this document, though many of the principles and
    suggestions still apply.
   The document has been written in the context of the
    recommendations in the more detailed ‘Report Writing
    Guidelines’ document.
   The XML publishing feature available with 11.5.10 is not covered
    in this document.
   At a high level the recommended report writing process is as
    follows.
          Define report on paper or spreadsheet with user.
          Define Row Set in development environment and create a
           test report with existing seeded column sets to review data.
          Once the Row Set data is correct work on the formatting
           such as spaces, indents and descriptions.
          Create the column set and apply to the test report.
          Create optional features such as content sets, row orders
           and display sets.
          Once the report has been fully tested and signed off,
           migrate to production using the standard FSG transfer
           program.
FSG Definition : Autocopy
   NAV : Reports > Autocopy
   All report components can be copied using the ‘Autocopy’ feature for which most of the screens discussed
    below have a button.
   Component : Whatever screen you access the autocopy from you will need to select the component you
    want to copy from the pick list. All report components, including whole reports can be copied.
   Source : Select the name of the component you want to copy.
   Target : type in the new name of the component.

   When you press the [Copy] button a concurrent request will create the new components so this may take a
    minute or two.
   Once the request is complete you can then query your new component in the normal screens just like any of
    the existing ones.

   Where possible make full use of the ‘Autocopy’ feature instead of writing components from scratch.
Row Sets : Definition
   NAV : Reports > Define > Row Sets
   Open the Row Set window below to either define a new Row Set or select an existing one to update.
   The screen works like a standard Oracle form so you can use either F11 or the torch button to query an
    existing Row Set.
   When you have selected the Row Set press the ‘Define Rows’ button to open the next screen.
Row Sets : Row Definitions 1
   NAV : Reports > Define > Row Sets
   Use this screen to define you rows within your Row Set. A Row Set can have from one to several
    hundred rows.
   Line : This is the row number that is used to reference the row in calculations. It is recommended that
    you use sequences 10,20,30,40 and then restart major areas of the report in a new range. For example
    if writing a Balance sheet, start the Assets at 10 and the Liabilities at 500,510,520.... This will give you
    room to make adjustments later if needed.
   Line Item : This is the title of the row that will appear in the report. You can enter around 130 characters
    but bear in mind how this will appear on the report.
   Format Options
         Indent : Number of characters from left that row
          description will appear. Decide in advance how
          many total and sub-total levels you report will
          have. The highest level will have an indent of 0
          and the lowest level of detail with have the
          highest indent. The values 0,1,2 & 3 are typical
          for a P&L report.
         Lines to Skip : This is the number of lines before
          or after the row to leave as a blank. This is used
          to show a visual split between different areas of
          the report.
         Underline Characters : Used on totals. Such as
          ‘-’ or ‘=‘
         Page Breaks : Tick the box to page break before
          or after row
Row Sets : Row Definitions 2
   NAV : Reports > Define > Row Sets
   Advanced Options
         Row Name : Max. 30 Characters. This is used to reference the row in calculations
         Percent of Row : Used to reference another row that will be used to calculate a percentage
         Override Column Calculations : Refer to FSG guidelines document for more information on this.
   Balance Control : These can all be left blank unless required
         Amount Type : PTD, YTD, QTD, Actual, Budget
         Currency : Leave blank to pick up total functional balance, or enter a value to pick up a translated balance.
         Control Value : Used for budgets and entered currency. Refer to report writing guide for more information.
         Offset : This is a reference to the number of periods from the period the report is submitted for. For example 0 is
          current period. -12 is same period last year.

   Display Options : These are mostly defined in
    Column Set.
        Format Mask : eg. 999,999 or 999,999.99
         Generally used on column sets not row sets so
         can leave blank.
        Factor :Units, Thousands, Millions. Generally
         used on column sets not row sets so can leave
         blank.
        Level of Detail : Used with display sets.
        Display Row :Leave ticked unless this is a
         calculation to hide
        Display Zero : Generally no, but can be ticked
         to create fixed layout reports.
        Change Sign : eg. To show revenue as
         positive on P&L
        Change Sign on Variance : As above.
Row Sets : Account Assignments
   NAV : Reports > Define > Row Sets > [ Account Assignments ]
   For most reports the account assignments are the main feature of the row set, and are key in defining the structure
    and content of the report. To open the account assignment screens press the button shown on the previous page.
   You can define multiple account assignments for each row, but where possible you should make use of the parent
    accounts in you chart of accounts rather than hard-coding the detail into the report.
   Sign : ‘+’ or ‘-’ to indicate if the balance of the account range should be added or deducted.
   Accounts Low – High :Always enter the minimum needed for the report. For example for a P&L, just enter the
    specific natural account and leave the cost centre and balancing segment blank. The less you put in here the lower
    the maintenance and greater the future flexibility of the report.
   Display :T,E or B. Generally leave this as ‘T’ for total as you can use content sets to expand the report at run time
    if needed.
   Summary : Only used with summary accounts. Tick to pick up summary account balance in the account range
    defined.
   Activity :Usually ‘Net’ but can be ‘Dr’ or ‘Cr’ if needed.
   Set of Books : This is generally left blank to pick up the books of the responsibility the report is being run from, but
    you can report across multiple sets of books in a single report as long as they share the same chart of accounts (
    Same ID not, not just common structure )
Row Sets : Calculations
   NAV : Reports > Define > Row Sets > [ Calculations ]
   Calculations and Account Assignments are mutually exclusive. This means that each row can only have one or the
    other, but not both. To open the calculation screen press the ‘Calculation’ button on the ‘Rows’ window.
   You can define multiple calculations for each row, and you have the full range of mathematical operators available
    to define you formula. If the formula is complex the work through it on a spreadsheet first before creating in an
    FSG.
         Seq : This is the sequence of the calculation line. Use the 10, 20, 30, 40 sequence to allow you to make
          amendments later if needed.
         Operator : ( +, -, *, /, %, Average, Enter, Median, StdDev, Abs ) Generally the first value line should be ‘Enter’
          otherwise it can cause erroneous results with complex calculations. (Refer to FSG Report Writing Guidelines
          document for more information )
         Constant : This is an absolute value. For example you may enter a ratio figure or percentage to use.
         Low / High : This is the row numbers. ( You will see here the importance of the row name in selecting the
          right range of rows here.
         Row Name : Used to reference a specific row within the row set.
Column Set : Definition
   NAV : Reports > Define > Column Sets
   Open the Column Set window below to either define a new Column Set or select an existing one to update.
   If you are defining a new column set then follow the standard naming conventions for your organisation. ( Refer to FSG
    Report Writing Guidelines document for more information )
   The screen works like a standard Oracle form so you can use either F11 or the torch button to query an existing Column
    Set.
   When you have selected the Column Set press the ‘Define Columns’ or ‘Build Column Set’ button to open the next screen
    and continue with the definition.
   The ‘Define Column Sets’ window works like the Row Set on described already, and can be used to define all information
    relating to a column set. The ‘Build Column Set’ window is a graphical layout for the initial structure definition of the column
    set and for the final formatting of column headings.
    Column Set : Column Definition 1
    NAV : Reports > Define > Column Sets > [ Define Columns ]
    Column Sets are the other required component of every FSG report. Whilst Row Sets are used mainly for defining the
     accounts, Column Sets are used mainly for defining the period, balance type or a secondary account segment such as cost
     centre.
    Position : This is the position in number of characters that the first column is from the left side of the page. All column sets
     should use one of a few values for the first column to match the standard suite of row orders defined. For example 40, 80 &
     130 characters.
    Sequence : This is the reference number for the column order and calculations. Follow the 10,20,30 sequence so that you
     have room to make changes later if needed.
    Format Mask : Controls the format of the data in the column. For example 999,999 or 999.999,99 or 999,999.99
    Factor : If left bank then default is ‘Units’ but can also select from Thousands, Millions, Billions and Percentiles



   Balance Control :

         Amount Type : This is usually required on
          a column set. Examples are :PTD, YTD,
          QTD, Actual, Budget
         Currency : Leave blank to pick up total
          functional balance, or enter a value to pick
          up a translated balance.
         Control Value : Used for budgets and
          entered currency.
         Offset : This is a reference to the number
          of periods from the period the report is
          submitted for. For example 0 is current
          period. -12 is same period last year. If left
          blank then the default is a 0 offset.
Column Set : Column Definition 2
   NAV : Reports > Define > Column Sets > [ Define Columns ]
   Advanced Options
         Column Name : Max. 30 Characters. This is used to reference the column in calculations
         Description : Used with above to give additional information. Does not appear on the actual report.
         Percent of Column : The sequence number of the column you want to use as the denominator ( the 100% column) for
          a percentage calculation on this column.
         Override Value : Works with the segment override field on the column set definition. It is used to assign a specific
          value.
         Override Row Calculations : Tick to set your column calculations to override any row calculations. ( Refer to FSG
          Report Writing Guidelines document for more information ).

   Display Options :

         Level of Detail : Used with display sets.
         Display Column :Leave ticked unless this is
          a calculation to hide
         Display Zero : Generally no, but can be
          ticked
         Change Sign : To switch +ve values to –ve
          and vis-versa.
         Change Sign on Variance : As above

   Account Assignments & Calculations:
         The account assignment and calculation
          features for column sets are the same as
          those described for Row Sets already so
          please refer to previous pages.
         Remember that if using assignments on rows
          and columns the report will show the
          intersecting value of the two.
Column Set : Exceptions
   NAV : Reports > Define > Column Sets > [Define Columns] > [ Exceptions]
   Exceptions are an optional component and not generally used on standard FSG reports. There usefulness is limited
    because they are based on constant values that would need ongoing review and maintenance.
   Define column exceptions to highlight account balances to management for additional review. Exceptions will appear
    with a ‘flag’ next to them in the report, or you can choose to submit reports to only show exceptions.
   Flag : Enter the character used to highlight the exceptions such as * # x
   Condition : Select one of the conditions ( = , < , <=, > >= and <>)
   Constant : Enter a value for the condition to match.
   You can enter multiple conditions, but in order to work, then any value in the report must match all of the conditions.
Column Set : Build Column Set
   NAV : Reports > Define > Column Sets > [ Build Column Set ]
   Define or review the column definitions in a more graphical format.
   Updates made here are applied to the column definitions in the previous screen and vis-versa.
   Use the folder tool buttons in the top left to change the column orders and width.
   You can create default column headings, but it is likely that you will want to enter your own as described below.
         Sequence, Name, Amount Type & Offset : These are all taken from the main column definition screen
         &POI : Period of Interest, is a reference to the period name that will appear on the column heading based in the offset
          and the period the report was run for.
         &BUDGET : This will show the budget name based on the control values used.
         [More Column Options] : This shows some additional setup fields that are also available in the main column definition
          screen so refer to previous pages for details.
         Heading : This is the four rows that define the individual column headings. These must be defined in this screen as they
          cannot be defined anywhere else. You can press the button [Create Default Headings] and then update manually.
         Format Mask : This is taken from the main column definition screen.
Content Sets : Definition
   NAV : Reports > Define > Content Sets
   Content Sets allow you to control the layout and level of detail of existing reports when they are submitted. This effectively
    allows multiple reporting styles from a single report definition. Content sets determine which specific account segments are
    expanded in more detail and how that expansion occurs ( By row, column, page or report )
   Refer to the document ‘FSG Report Writing Guidelines’ for suggestions of how to define a suite of standard content sets that
    can be used to add flexibility to your reports.
   Name & Description : Enter a name and description for your content set following a standard naming convention so that
    the definition can be understood by the users .
   Type : This determines how the report will be processed by the server. Sequentially will be slower but use less resources.
    Generally leave this as Sequentially.
   Account Assignments : A content set can have multiple account
    assignments. As with row sets only select values in specific
    segments and leave other segments blank.
        Seq : Enter the sequence order for the content set
         assignments. This can be in the 10,20,30 sequence.
        Low / High : Enter the low and high range for specific account
         segments.
        Display :The default is ‘N’ which means no override
            –   CT : Total by Column.
            –   PE : Expand by page ( or spreadsheet TAB in ADI).
            –   PT : Total by page ( or spreadsheet TAB in ADI).
            –   RB : Both expand and total by row.
            –   RE : Expand by row.
            –   RT : Total by row.
         Summary : Normally left blank, but select yes to base the
          content set on summary accounts within the ranges defined for
          each line.
Row Order : Definition
   NAV : Reports > Define > Order
   Refer to the report writing user guide for suggestions of how to define a suite of standard row orders that can be used to
    add flexibility to your reports.
   Name & Description : Enter a name and description for your row order following a standard naming convention so that
    the definition can be understood by the users ( refer to report writing user guide)
   Rank By Column : This is used to specify which columns values in a report are used to order the expanded rows.
         Name : Enter column name or, Order : Column number as it appears in a report. This may vary when different
          column sets are used.
         Ranking : Ascending or Descending

   Account Display : This defines how the detail of rows
    expanded by a row set or content set will show on the
    report. It only has an impact on rows and account
    segments that have been expanded.
         Seq : Enter the sequence order for each segment
          to be displayed in. Every segment must have a
          value, but this doesn’t have to match the order on
          the CoA definition.
         Segment : Select the segment name
         Order by : Value or Description
         Display : Value, Description or Both
         Width : Enter the width in characters for each
          segment. Enter ‘0’ for segments you do not want
          displayed.


   NOTE : Be careful to match the total width of the descriptions for the row order to the size of your pre-defined column sets.
    For example 40, 80 & 130 characters. Have more or less characters that the column set will lead to unexpected results on
    the report output. Refer to the report writing user guide for more information on this.
Report : Definition 1
   NAV : Reports > Define > Report
   FSG reports should be defined for the key monthly reports to group together report components ( Row, Column & Content
    Sets ) so that users can run them quickly and consistently.
   Every report must have at a minimum a Row Set and a Column Set. All other components are optional, or can be left as
    default.
   Consider giving users access to this screen even if they are not allowed to define other components.

    Name Title & Description: Enter a report
     name, title and description following a standard
     naming convention so that the definition can be
     understood by other users. The report name and
     description are visible in the screens when
     selecting which report to run, and the title
     appears on the report output.
    Required Components : Select an existing
     Row Set and Column Set from the pick lists.
     Multiple reports can share the same
     components.
Report : Definition 2
   NAV : Reports > Define > Report
   Optional Components : Optionally select the additional report components.
        Content Set : If the content set is expanding rows then select a row order that controls the same segments.
        Row Order : Although the segments must match the content set they can be in a different order. 1,2,3 or 3,1,2 would
         both work if the Content Set is expanding segments 3,2 & 1. If you are not using a content set then the Row Order
         works with any expanded rows.
        Display Set : These work independently to control the parts of the report visible to different users.
   Other Components : Optionally select values for these fields.
    All of these are available each time you run a report.
          Segment Override : You can hard code a segment
           override into a report. For example to look just at a specific
           Cost Centre, but it is normal to leave this field blank have
           only select when required at the time the report is being
           run.
          Currency : This is usually left blank to pick up the
           functional currency of the set of books, but you can also
           enter a specific currency , but only if translated balances
           exist for that currency. If you want to report entered
           currency then refer to the control values section on the
           following page.
         Rounding Options : It is sometimes statutory requirement to perform this in a certain order. ‘Round then Calculate’ or
          ‘Calculate then Round’.
         Level of Detail : This works with the level of detail on the row set definition to have different reports from the same
          definition. This can be left blank and the default will be ‘Financial Analyst’
         Output Option : The default is text and this will work with publishing the reports as text direct from the apps and with
          publishing to spreadsheets via ADI. The other options are to change the format of reports published from the apps, but
          are redundant if ADI is being used.
Report : Control Values
   NAV : Reports > Define > Report > [ Control Values ]
   Control Values are used to add Budget, Encumbrance and Currency information to FSG reports. They are
    referenced on the columns and/or rows as number and then in the report this numbers are linked to budgets to
    currency’s.
   The advantage of this method is that you can hard code the control values in the detailed components ( Rows &
    Columns ) and then each year when the budget changes just update the control values on the report once and link
    that value to a new budget.
   Access the this screen by pressing the control values button on the report definition screen.
   Note that it is only available when control values exist in one or more of the report components.
   You can enter multiple control values if you want to use more than one currency or budget.



   When using control values to define currency, you have
    to select a currency type of Entered or Translated.
   This will only apply to the rows or columns with the
    matching control value. It is different to selecting the
    currency on the report definition, which applies to the
    whole report and only works with translated balances.
Report : Submission
   NAV : Reports > Request > Financial
   Report : select the name of the pre-defined report.
   Period : This is the accounting period the report is to be run for. This can be any period in the past or future ( Doesn’t have
    to be open ), and is used as the basis for the ‘Offset’ period in the report definitions. Columns with an offset of ‘0’ will show
    data for the accounting period selected here.
   Currency : This is the base currency for the report. Default is the functional currency but any other currency for which
    translated balances exist can be used. If no translation exists then there will be no balances
   Request ID : This is populated automatically when the report is submitted
   Segment Override : This can be used to select specific segment values such as accounts or cost centres to filter the report
    by at run time. The same report can be run many times with different overrides.
   Content Set, Row Order & Display Set : You can add or change the optional report components at run time
   Rounding Options : The default rounding option can be updated at report run time.
   Exceptions : You can tick the box to only show the pre-defined report exceptions in the output.
   Output Option : This is defaulted from the report definition but can be updated at report run time
Report : Submission of Ad-Hoc Reports
   NAV : Reports > Request > Financial > [ Define Ad-Hoc Report ]
   Use Ad-Hoc reports to define one of reports using any of the existing pre-defined report components. This can be
    used to meeting reporting requirements without the need to define new reports.
   Report : Select the name of a pre-defined report similar to the layout you want to define before you press the ad-
    hoc button.
   In the Create Ad-Hoc Report screen below you will see it has been pre-populated with the Title, Row Set and
    Column Set from the report selected above.
   You can update any of these to create your out version of this report and it only applies to this submission. It will not
    update the original report.
   Select the row set and column set you want using the pick list, then enter any control values required.
   Once this is done, press ‘OK’ to return to the report submission screen
   You can then add the optional components in the normal way described on the previous page before submitting
    your ad-hoc report.

								
To top