Learning Center
Plans & pricing Sign in
Sign Out

Scope of Work Worksheet - Excel


Scope of Work Worksheet document sample

More Info
									                                           NVISION VARIABLES
   Variable           Name                                            Sample Value


   RID                Report Name (Request Name)                      COMPINC
   RTT                Report Title                                    Comparative Income Statement
   LYN                Layout Name                                     COMPINC
   RBU                Requesting Business Unit                        UMS08
   RBN                Requesting Business Unit Description            UMS System Office
   OPR                User ID                                         VP1
   OPC                User Class                                      ALLPNLS
   OPL                User Language                                   ENG
   DTS                Detail or Summary (nPlosion)                    S
   ICT                Instance Counter                                1
   IDN                Instance Directory Name                         C:\USER\NVISION\INSTANCE
   IFN                Instance Output File Name                       COMPINC.xls
   LAN                Language Code                                   ENG
   RBK                Report Book Name                                Month-End


   ASD                As of Reporting Date                            2001-06-15
   AST                As of Tree Date                                 1900-01-01
   PER                Accounting Period                               12
   APN                Period Name                                     June
   APA                Period Abbreviation                             JUN
   FY2                Fiscal Year (YY)                                01
   FY4                Fiscal Year (YYYY)                              2001
   PED                Period End Date                                 2001-06-30


   SCN                Scope Name                                      SALESDPTS
   SCD                Scope Description                               Sales/Marketing Departments
   SFN                Scope Field Name                                DEPTID
   SFV                Scope Field Value                               21400
   SFD                Scope Field Description                         Southern Sales Region
   STN                Scope Tree Name                                 UMS_DEPARTMENT_ORG
   STD                Scope Tree Description                          Manufacturing Departments
   SLN                Scope Tree Level Name                           DIVISION
   SLD                Scope Tree Level Description                    Division
   BUV                Business Unit                                   UMS08
   BUN                Business Unit Description                       UMS System Office
   DES                Scope Descriptive Field                         Baker,Matt

   Variables have a format of: %RID%

                                           NVISION ELEMENTS


   Pivot Table



   Layout names end in xnv and are stored where user specifies

   Order of Precedence for Criteria

         Worksheet      WEAK



         Cell           STRONG

         Lower level overrides higher level (Cell overrides Row)

   Layouts can be:
       Matrix (Data source = Ledger or Query)
       Tabular (Data source = Query)

   If Layout is Matrix need Timespan, Ledger, and Chartfield

         Query, Ledger, TimeSpan, and Reverse Sign option
         Liabilities, Equity, and Revenue stored as Credit values
         ChartField criteria and nPlosion options

     Descriptive field values from either tree nodes or detail values
     Work by intersecting criteria
     Rules for Label placement:
          Place labels on entire row/column
          Never place labels in a cell or at worksheet level
          If field criteria placed on rows, then place label criteria on a column
          If field criteria placed on columns, then place label criteria on a row
          Cannot place labels in a row/column that already contains criteria
     Typical Fields used for Labels from the ACCOUNT table (Detail Value Table)
          DESCR                       Account name, up to 30 characters
          DESCRSHORT                  Account short name, up to 10 characters
          ACCOUNT                     Account value
     Typical Fields used for Labels from the DEPARTMENT table (Detail Value Table)
          DESCR                       Department name, up to 30 characters
          DESCRSHORT                  Department short name, up to 10 characters
          DEPTID                      Department value
          MANAGER_NAME                Department Manager name, up to 30 characters
     Typical Fields used for Labels from the Tree Node table
          DESCR                       Node description, up to 30 characters
          TREE_NODE                   Node value
          ACCOUNT                     Account value
     Can specify a special label for TimeSpans in the format YYY-PP (year-period)
     by entering ACCOUNTING_PERIOD as the Field on Detail Value Table
     (can only be used with an intersecting row/column with criteria that nPlodes a TimeSpan
     On the Report Label Criteria box, Put Labels in Blank Cells Only
          System generates labels only where the intersection of the criteria and and report
          label definition is a blank cell, leaving any custom lables you typed into the layout intact
     Resize Columns for Lables
          Changes the column width to best fit the longest retrieved label
          Deselect when you have labels in multiple columns and the leftmost column is to be
          outdented from the other columns to the right
     PS/nVision Variables - can only be placed in a cell
     Must be the only thing in the cell
     Combining Text With PS/nVision Variables
          Place Variable in a cell outside the print range
          Define a Name for the cell containing the Variable
                    Insert, Name, Define
          Place an EXCEL formula in the cell where want heading to appear
                    Text should be enclosed in quotes
                    Concatenate using &
                    Use to name of the cell containing the Variable
                              ="As of : "&TEXT(ASD,"mm/dd/yy")

                     M                 1,12
                     MM                01,12
                     MMM               JAN, DEC
                     MMMM              January, December
                     D                 1,31
                     DD                01,31
                     DDD               Mon, Wed
                     DDDD              Monday, Wednesday
                     YY                99,00
                     YYYY              1999, 2001

                     Date and Time Stamping
                            ="Run: "&text(NvsEndTime,"MMMM DD, YYYY at HH:MM am/pm")
                            Run: March 11, 2002 at 02:47 PM


   Can create multiple instances from a single report request
   Each instance contains data from a single field value or a combination of field values
   Each instance shares the same layout, but contains data unique to a scope's particular field value(s)

   Stored on database

   Two of the most useful Scope variables are SFV (Scope Field Value) and
   SFD (Scope Field Description)

   When using a scope, nVision will save only the last instance generated and overwrite all other
   instances. To avoid this, give each instance a unique Directory or File name.

   When using scopes, Always use Scope Variables in the File or Directory template on the
   Report Request to differentiate and store each instance

   Scope Descriptive Field (DES) variables are user-defined, and retrieve miscellaneous
   information from either a detail value table or a tree node table. Define the DES variable
   in the Variables dialog box.

   If Scope is based on two or more fields, a report instance is produced for each combination of
   the selected nodes or detail values for all specified fields.

   When using more than one ChartField in a Scope, must indicate which ChartField contained in
   the Scope is to be resolved using the Scope Variables. Qualifying a Scope Variable indicates
   the Scope Field from which to retrieve the Scope Value or Description.

   Scope Variable in a cell:
   Scope Variable in the Directory or File Template fields:


   Request Name (RID)
   Report Title (RTT)
   Requesting Business Unit (RBU)
   Layout (LYN)
   Scope (SCN)
   Directory Template
        Directory in which the report instances are placed
        If blank, instances placed in default instance directory specified in Configuration Manager
        Can include PS/nVisionVariables to dynamically name Directories
   File Template
        Name of instance file
        Can include PS/nVisionVariables to dynamically name instance file names
   Language Template
   Email Template
   Description Template
   Security Template
   Output Type
        File, Printer, Email, and Web
   Output Format
        XLS or HTML
   Enable nPlosion if specified in in Layout
   Data from Requesting Business Unit Only
        When checked the report only reflects data from requesting Business Unit
   Translate Summary Ledgers to Detail
   Main As Of Date
        Used to determine relative meaning of relative periods or years within TimeSpans
   As Of Date For Trees
        Specifies effective date of Trees to be used

   Stored on database


   Output (xls or htm)

   Stored where user specifies


   1.   Sketch the desired result

   2.   Determine Layout Type (Matrix or Tabular)
            Matrix Layout relies on physical intersection of criteria to achieve results
            Tabular Layout requires a query and generates a list report filtered by scopes
            Rules for Criteria in Matrix Layouts
                    Use Criteria at the worksheet level to specify defaults for the entire worksheet
                    Criteria at the worksheet level is combined with criteria for columns and columns
                    If criteria is specified for an individual cell, all criteria must be specified in that cell or
                    at the worksheet level
                    Avoid redundant placement of criteria whenever possible

   3.   Determine Source (Ledger/Query)

   4.   Place Text on Layout

   5.   Document TLC and place on Layout at worksheet, column, row, and cell level
            Highlight entire worksheet to place worksheet criteria

   6.   Place Variables on Layout (always in cells)

   7.   Place Labels on Layout (always row or column criteria - opposit of chartfield value)

   8.   Place Excel formulas and formats on Layout

   9.   Define Scope

   10. Define Report Request


   1.   Determine field to build scope on

   2.   Determine if instances are detailed or summarized

   3.   Determine if instances are dynamic or finite

   4.   Put scope variables on your Layout

   5.   Put scope variable in your Report Request


   nVision Code                         Meaning

   %,                                   nVision Criteria
   S                                    TimeSpan
   L                                    Ledger
   F                                    Chartfield
   T                                    Select Tree Nodes
   M                                    Selected Summary Chartfield Nodes
   V                                    Select Detail Values
   _                                    All Detail Values
   R                                    Reverse Sign
   N                                    Tree Node

   X                                    nPlode
   C                                    Copy Formula for nPlosion

   Q                                    Query
   C                                    Column (Aggregate field from Query)

   Labels: A#$

   # = Labels in Blank Cells Only
        T = True
        F = False

   $ = Resize Columns for Labels
        T = True
        F = False

   Example AFT = Lables, Blank Only = False, Resize = True

   F = Field on Detail Values Table

   U = Field on Tree Node Table


   Aids in troubleshooting Layouts
   Show Warning Messages
   Show Report SQL
   Suppress Amount Retrieval
   Show Row and Column Criteria
        Displays Row 1 and Column A that are reserved for worksheet, column, and row criteria


   Rows are inserted above the row where the nPloded criteria is specified
   Columns are inserted to the left of the column where the nPloded criteria is specified
        Specify nPlosion for a specific row/column or all rows/columns
        Activate nPlosion for the report on the Report Request
        Only one field may be nPloded per row/column
   Define nPlosion in the Layout definition under the Filter tab
   The nPlode TimeSpans appears under the Source tab next to the TimeSpan field
   nPlosion only occurs on the rows or columns where it is specified
   Suppress Detail
        If entire row/column is zero
        If no data for entire detail row/column
        Never - shows all detail
   Copy formulas to nPloded data if have rows/columns that contain only formulas and want the
   results of the formula to appear for each nPloded detail value
        For nPloded columns, select the row that contains the formula you wish to copy
        For nPloded rows, select the column that contains the formula you wish to copy
        Check the Copy Formulas to nPloded Cells box
   DTS Variable used to suppress heading depending on value of DTS
        D if nPlosion on the Report Request box has been checked
        S if nPlosion on the Report Request box has not been checked
              Place the DTS variable outside the print range
              Name the cell containing the DTS variable
              In the cell with the heading that is to be displayed or hid place formula
                      =IF(DTS="D","your heading","")
   nPlosion Types
        To details only
        To immediate children
        To specified level
        To bottom of tree

   nPlosion Options
       Include underlying detail
       Suppress nodes without data
       Exclude intermediate levels
       Utilize outline feature


   Balance Forward stored in Period 0
   Adjustments stored in Period 998


To top