NVISION VARIABLES Variable Name Sample Value REPORT REQUEST VARIABLES 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 DATE AND TIME RELATED VARIABLES 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 SCOPE VARIABLES 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% 11/01/2006 C:\Docstoc\Working\pdf\d446ac88-60a2-420d-b641-14fc5326a41f.xls NVISION ELEMENTS EXCEL Text Format Formulas Graph Macros Pivot Table LAYOUT Criteria Timespans Ledger Chartfields Query Variables Labels Layout names end in xnv and are stored where user specifies Order of Precedence for Criteria Worksheet WEAK Column Row 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 Source Query, Ledger, TimeSpan, and Reverse Sign option Liabilities, Equity, and Revenue stored as Credit values Filter ChartField criteria and nPlosion options 11/01/2006 C:\Docstoc\Working\pdf\d446ac88-60a2-420d-b641-14fc5326a41f.xls Label 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 Variable 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 Example: ="As of : "&TEXT(ASD,"mm/dd/yy") 11/01/2006 C:\Docstoc\Working\pdf\d446ac88-60a2-420d-b641-14fc5326a41f.xls Formats: 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 Example: ="Run: "&text(NvsEndTime,"MMMM DD, YYYY at HH:MM am/pm") Run: March 11, 2002 at 02:47 PM SCOPE 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. 11/01/2006 C:\Docstoc\Working\pdf\d446ac88-60a2-420d-b641-14fc5326a41f.xls Scope Variable in a cell: %SFV,QDEPTID% Scope Variable in the Directory or File Template fields: %SFV.QDEPTID%.xls REPORT REQUEST 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 INSTANCE Output (xls or htm) Stored where user specifies 11/01/2006 C:\Docstoc\Working\pdf\d446ac88-60a2-420d-b641-14fc5326a41f.xls STEPS IN BUILDING A REPORT 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 STEPS IN BUILDING A SCOPE 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 11/01/2006 C:\Docstoc\Working\pdf\d446ac88-60a2-420d-b641-14fc5326a41f.xls CODES 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 NVISION OPTIONS 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 11/01/2006 C:\Docstoc\Working\pdf\d446ac88-60a2-420d-b641-14fc5326a41f.xls NPLOSION 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 Steps: 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 OTHER TIPS Balance Forward stored in Period 0 Adjustments stored in Period 998 11/01/2006 C:\Docstoc\Working\pdf\d446ac88-60a2-420d-b641-14fc5326a41f.xls
"Scope of Work Worksheet - Excel"