Data Spreadsheet Xls by bla19342

VIEWS: 7 PAGES: 26

More Info
									  Enhancing FRx Reports by
Integrating Spreadsheet Data


           Elisa R. Vick
     elisa.vick@cistech.net
Agenda
• Overview
• Combined Worksheet Link Example
• Using RPO (Row Period Offset) code in the
  link
• Separate Worksheet Link Example
• Using CPO (Column Period Offset) code in
  the link
•Q&A
Linking to External Worksheets
• Links established in row format
• 2 different methods used to define links
  – Combined Worksheet Link: combines external
    worksheet data in same link column as your
    general ledger account codes
  – Separate Worksheet Link: used to access
    multiple rows and columns of worksheet data
    and can reference various external
    spreadsheets for different units in your
    reporting tree.
Linking to External Spreadsheets
General Information
• The default path for saving Excel spreadsheets which
  you will be linking to is in the IO_Data Directory which
  should reside under your FRx Designer Install folder.
• To identify a specific worksheet within an excel
  workbook for your link, you will put the workbook name
  in brackets and the worksheet name immediately after
  the closing bracket. Your worksheet name should
  always be at least 2 characters (i.e. [BUDGET.xls]Plan
  where BUDGET is the workbook file name and Plan is
  the spreadsheet within the workbook that you are linking
  to.
• You cannot reference a range of cells from your
  spreadsheet in your FRx link.
Linking to External Spreadsheets
General Information (cont.)
• You cannot perform calculations within a single row’s
  link reference. However, various rows which specify
  links to external data can be made non-printing (or you
  can leave them to print) and then an additional
  calculation row can be added to the report to perform
  calculations on the non-printing rows.
• It is recommended to perform calculations within excel
  and then link to the calculated cell.
• If using various worksheets for different units within a
  tree, it is recommended to format the worksheets with
  the same data references in the same cells so you only
  have to create one external link column in your row
  format to be used by every unit in your tree.
Linking to External Spreadsheets
General Information (cont.)
• Columns in your column layout which will accept the
  external data are GL type columns and WKS type
  columns. If you attempt to place worksheet data in the
  column types CALC, DESC or FILL, that data will be
  ignored.
Combined GL + Worksheet Link
• Used for placing external spreadsheet data
  within the same column of your report as
  your GL data.
• Could be used to bring in values not
  stored in the general ledger but needed
  for statistical calculations on reports (i.e.
  units sold, production hours, records of
  asset aquistions and sales, etc.)
Combined GL + Worksheet Link




• To add the appropriate link type, double click on
  column H in the row format.
Combined GL + Worksheet Link




•   Click the dropdown arrow next to link type and select GL + Worksheet.
•   In the worksheet file name, if the workbook is in your IO_Data folder, you can simply
    specify the worksheet name such as Cashflow.xls. If the workbook resides
    somewhere other than IO_Data, you will have to specify the entire path as seen
    above. Clicking on the dropdown arrow allows you to browse to the path.
•   You can leave the link name at the default and the description is optional.
•   Once all information is added, click on Save to save this link and then click on Close.
Combined GL + Worksheet Link
• When using the combined link type, you must always
  specify @WKS prior to specifying your cell reference.
• You will use column placement references to place the
  data in the appropriate GL or WKS type columns of your
  report (i.e. @WKS (B=B10) would indicate that in
  column B of your FRx report, you are placing data from
  cell B10 of your excel spreadsheet.
• To link to spreadsheets that specify various periods
  where your report will pull from different columns of the
  spreadsheet depending on what period you are running
  the report for, you can use the CPO (Column Period
  Offset) or RPO (Row Period Offset) codes in conjunction
  with your link references.
Combined GL + Worksheet Link
 • Rows 490 to 610 contain
   GL account codes.
 • Rows 880 and 910 are
   referencing the
   worksheet data.
RPO
•   Indicates that your link will read the period
    from the column layout and pull the
    correlating cell reference for that period
    based on the cell reference specified in your
    link from the row format. To pull the
    appropriate period’s data, the spreadsheet is
    read from the specified cell down the number
    of ROWS correlating to the period. Used
    when the spreadsheet you are linking to has
    multiple rows that represent different
    periods.
•   Using the row format from the last slide, in
    row 880 the formula is @WKS B=H4, D=I4
    /RPO. In our column layout both columns B
    and D are GL type columns. Based on our
    spreadsheet, if we were running this report
    for April (period 4) column B of the report
    would count 4 rows down from cell H4 and
    the value (10,000) would show up in the
    report and column D of the report would get
    the value (10,000) which is 4 rows down
    from cell I4.
•   Note that you always want to reference the
    cell one row up from the cell that holds the
    data for your first period.
Combined GL + Worksheet Link
Combined GL + Worksheet Link




• Notice that with the combined link in your catalog you
  do not need to specify a tree. Your workbook path is
  specified in the row format directly on the link.
Separate Worksheet Link
• Used for bringing in large amounts of data
  not stored in the General Ledger from an
  excel spreadsheet.
• The data is placed in WKS type columns.
• Examples include Budget, Plan or Forecast
  data not stored in the G/L and data for
  subsidiary companies you wish to use in
  consolidated reports but which don’t use
  FRx for reporting.
Separate Worksheet Link




 • To add the appropriate link type, from the row
   format double click at the top of column H.
Separate Worksheet Link




•   From the GL Link type screen, click on New. This will bring up a blank link to
    screen.
•   From the Link type box, click on the drop down and select External Worksheet. You
    will notice that once this option is selected, the Worksheet file name field is input
    inhibited. You will specify your path to the excel spreadsheet in your tree when
    using an external link.
•   Once you have filled in the appropriate information select Save and then Close.
Separate Worksheet Link
 • When using the separate link method, you only need to
   specify the excel cell reference. You don’t need to use
   the @WKS code.
 • You can still use column placement but the separate
   worksheet link will only place values into a WKS type
   column in the column layout.
 • You must use a reporting tree in a report that utilizes
   this method.
 • You can have a maximum of 19 GL / Worksheet /
   GL+WKS links in one row format, but you can link to as
   many Excel worksheets as you want using tree
   methodology.
 • As with the combined method, with the separate method
   you can use CPO or RPO to easily work with multiple
   periods in a report linking to multiple periods in a
   spreadsheet.
Separate Worksheet Link
                    •   Column H contains GL data only.
                    •   Column I contains an external link which
                        will link to two worksheets in a workbook.
                        One for Denver budget data and one for
                        San Francisco budget data.
                    •   Column J is also an external link which
                        links to a worksheet in the same workbook
                        as the link from column I but the format of
                        the worksheet is different than the Denver
                        and SF format.
CPO
•   Indicates that your link will read the
    period from the column layout and pull
    the correlating cell reference for that
    period based on the cell reference
    specified in your link from the row
    format. To pull the appropriate period’s
    data, the spreadsheet is read from the
    specified cell across the number of
    COLUMNS correlating to the period.
    Used when the spreadsheet you are
    linking to has multiple columns that
    represent different periods.
•   Using the row format from the last slide,
    in row 370 the formula is A15/CPO. In
    our column layout, the columns
    alternate between GL and WKS type
    columns. A specific period is specified
    in each column, for example, column K
    is pulling data from the worksheet for
    period 5, column M for period 6, etc.
    Looking at the excel worksheet, cell A15
    is for Total compensation. Using the
    CPO code, for period 5 a value of
    47,000 is placed in column K of the FRx
    report and a value of 47,000 is placed in
    column M.
•   Note that you always want to reference
    the cell one column to the left from the
    cell that holds the data for your first
    period.
CPO (cont.)
• When using CPO, if you enter a Period code of
  BASE in a WKS column of the column layout and
  you enter YTD in the Current Per/YTD field, the
  YTD value is calculated from your spreadsheet
  based upon the period you are running the
  report for.
  – Example: Report ran for month of April
  – Row 370 of the above example will calculate a YTD
    value for Total Compensation of 194,539 without
    having to use a calculation column in the column
    layout.
Separate Worksheet Link (Catalog)
                  • The separate worksheet
                    link method requires that
                    you specify a tree in the
                    catalog.
                  • You also want to make
                    sure that you check the
                    box under the building
                    block Row format to ‘Use
                    row format(s) and
                    worksheet links from
                    reporting tree’.
Separate Worksheet Link (Tree)



•   In column I of the tree, you will specify which row format is to be used for
    this particular unit of the tree. Generally, the row format will be the same
    for all units. If nothing is specified here, the row format from the catalog
    will be used.
•   Each unit of the tree will specify the workbook/worksheet which it is pulling
    values from in column T of the tree. Note that in the above example the
    full path is not specified so the workbook must be saved in the IO_Data
    directory.
•   Column W of the tree specifies which column of the row format contains the
    specified links to the worksheet.
Separate Worksheet Link
Summary
• Two separate methods for pulling data from an excel
  spreadsheet into FRx:
   – GL + Worksheet combined link which combines GL data and
     external data into one column of report.
   – Separate worksheet link to pull in larger amounts of data into
     separate columns of the report
• For ease of use, be sure to format separate
  spreadsheets used in one report in the same manner.
• Use CPO or RPO when possible for reports including
  multiple periods.
• When saving workbooks used with FRx be sure to save
  them to either the IO_Data directory or a shared drive
  so anyone who needs to run that report will have
  appropriate access to the workbook.
How CISTEH Can Help
• Report specific assistance can be provided
  regarding Excel Linking in FRx via web
  sessions in hourly or daily increments.
• Web sessions can also be used for training
  on additional FRx features and functions
  to assist users in creating the most useful
  and maintenance free reports.
             Mike.taylor@cistech.net
                 423-822-6499

								
To top