Overview of n Vision
Refer below link to create new nvision report from scratch highlighted in blue
N-Vision Reports in PeopleSoft
Understanding
PS/nVision
PS/n vision (nVision)
enables you to retrieve information from People soft Tables or from Tree into a
Microsoft Excel spreadsheet, as information in a form that helps you see the
big picture while exploring the details.
You can use familiar Excel commands to format and analyze the data. By using nVision, you can spend your time
analyzing results, rather than summarizing data and entering it into
spreadsheets.
nVision does not just work with spreadsheets, it
works within them. You access the nVision
features directly from a special nVision menu within Excel. Because nVision works from within Excel,
you'll use the familiar Excel commands for inserting formulas, formatting, and
graphics.
Understanding
Reports and Layouts
Each Excel
spreadsheet created with nVision is a report layout. A report layout is a template used to create
a report with data from Tree or Query.
Every report created is based on a report layout. When a report is run, nVision uses the
specified layout to determine what data to retrieve for the report and how to
display it. The following will better explain nVision components you will have
to understand to crate successful report layouts.
Layout
- The ‘blueprint’ for your report within
nVision.
- Contains the elements of your report (both
nVision elements (i.e. criteria) and Excel elements (i.e. formatting).
-Has an .xnv
extension.
Scope (optional)
- Filters for information regarding what data
will be included in a report.
- Can be used to create multiple instances of
the same report, for different Criteria.
Report
Request
-
Place where the run time specifics are stored for
the report – business unit, as of date, report instance name, etc.
Navigation:
n visionàReport Request
-
Instance
- The result of running the report request with
the layout, the actual report.
- Is a Microsoft Excel spreadsheet (.xls
extension).
Review of Time Spans, Ledgers, and Chart
fields and Trees
Most nVision reports require TLC – Time span,
Ledger, and Chart field. Here is a review
these fundamental concepts.
Ledger
PeopleSoft
General Ledger stores the results of transactions on the Ledger table. Within the Ledger table, there is a field
called LEDGER. Information is grouped by
different ledgers, depending on the transactions.
Time spans
The ledger
data is stored as net activity for a specific period and accounting
period. To pull Ledger information into
an nVision report, a tool is needed to extract data for a specific period or
range of periods. Time spans are this
tool.
Some common time spans used in Aptara include
–
• YTD - Year
to date transactions (period 1 to current period)
• BAL -
Balance sheet information (period 0 to current period)
• PER – One
period information where n = period
There are
also some time spans that contain period 998 (adjustment) information.
Navigation: N vision àLayout DefinitionàSource Tab
Chart fields
Chart fields
are commonly known as the accounting string.
Chart fields include Account, Fund, Department, Fund, Program, Class,
and Project.
Trees
A tree is
used to view a data hierarchy. All trees
are built on a single field (such as Account or Department). nVision reports can use trees to select
specific ranges of Chart fields as opposed to hard coding this information into
the nVision report. This also limits the
maintenance of the report; once the tree is updated, the report is also
updated.
When defining field criteria to use in a
layout, there are 3 options:
1. Selected
Tree Nodes,
2. Selected
Detail Values, or All Detail Values. The first option uses a tree to specify
data.
3. The other
options, Selected Detail Values and All Detail Values, retrieve data from the
table you specify instead of a tree.
Navigation:
PIAàMain MenuàTree Manager
Chart field information is stored in
the following tables:
Account –
GL_ACCOUNT_TBL
Department –
DEPT_TBL
Fund –
FUND_TBL
Project –
PROJECT
Program –
PROGRAM_TBL
NPlosion
& types
NPlosion can
automatically create columns or rows on the report for each value that uses a
tree in your criteria. You must use a
tree for your chart field if you want to use nPlosion. You can also use nPlosion to expand
multi-period Time Spans. NPlosion will
automatically create these values. The
detail rows will be inserted above the row where the Nplodded criteria are
specified.
NPlosion
types
There are four types of nPlosion that can be
used in your layout. The default
nPlosion is ‘to details only’.
·
To details only – This nPlods all detail values
(actual chart field) from a given node.
If the nplodded data contains multiple nodes, the details will be listed
sequentially.
·
To immediate children - This will nPlode one level
below selected node.
·
To specified level – This method allows you to
nPlode down the tree from a specified node.
You can retrieve any branch of data from the tree.
·
To bottom of tree – This allows you to nPlode all
the way to the bottom of the tree from a specified node. If you include detail with this option, all
child nodes will also be displayed.
There are
also several nPlosion options
·
Include underlying detail – When nPlodding from a
tree node to a lower node or to the bottom of the tree, these options will also
include all of the detail and summary child nodes for each node selected.
·
Suppress nodes without data – If a node has a zero
value, it will not be seen in the report instance
·
Exclude intermediate levels – When nPlodding from a
node to another level or to the bottom of the tree, the intermediate children
will be excluded.
·
Utilize outline feature – This will use the Excel
outline functionality to roll up the detail information. It also gives you the option to collapse or
open the outline to a defined level.
Copy formulas to nPloded cells
Layout may contain formulas. If this is the case, and nPlosion is used,
make sure to check the box ‘copy formula to nPlodded cells’.
Labels
Labels can
be used with nPlosion or without. The
labels feature will add descriptive information that corresponds to the filter
criteria defined for the rows and columns. nVision will use the tree node or
detail value to look up additional information in the value table. Report
labels are useful when using nPlosion, as the labels will be dynamic and
created at run time. Labels are defined on the Label tab.
If criteria are defined in a row, the
corresponding labels would be placed in a column. Use the ‘Field on Detail
Value Table’ if you want to label the n Plodded rows or columns of node-based
filter criteria. Enter either the field name itself or a descriptive field from
the detail value table, which is defined in the tree structure.
If you are labeling filter criteria based on
tree node values, use the Field on Tree Node Table field to specify a label
source. For detail or summary tree node values, enter either TREE_NODE or
DESCR, as these are the only descriptive fields on the TREE_NODE_TBL (as
delivered).
For
node-oriented trees, enter either the field name itself or another descriptive
field from the table that supplies the node values, as defined in the tree
structure.
Navigation:
n visionàLayout DefinitionàLabel Tab
Drilldown
Drilldown
gives the user the ability to slice and dice information to analyze the data as
they need to. Drilldown enables you to
select a cell in your report and expend it according to new criteria contained
in the drilldown layout. On the client
version of nVision, drilldowns are run using a built-in Excel menu (Drill).
Scopes
Scopes allow
you to create multiple instances of the same report using a single report
request. Each instance will contain data
for a specific chart field value, such as fund, department, program, or a
combination of field values. The reports
will share the same layout, but contain data unique to the scope’s value. Whenever you use scope to produce multiple
instances of a report, use PS/nVision variables in the layout headings to
identify the content of each report. As
noted above, scopes are optional when creating an nVision report.
When you define a new scope, you must decide:
- The field(s) the scope should be based upon
field or not.
- Whether the instances should reflect detail
or summary data
- How many instances should be generated?
NVision Variables
You insert
PS/nVision variables into the layout to display heading information that might
change from report to report, or between report runs. For example, you could
use a variable to automatically insert the report ID you specify in the Report
Request dialog box, so you do not restrict this layout to a single purpose.
Remember that your layout may be used with a scope that changes its contents,
which could make a hard-coded title misleading.
You can
define variable criteria at the cell level only—one variable per cell—and the
variable must be the only element in that cell. When inserting a variable into
a cell, you select it from the Variable tab of the Layout Definition dialog
box. Because there are many different variables to choose from, the dialog box
displays them by category. Tables describing the variables in each category
follow.
When you use a variable, it must be enclosed
within percent signs (i.e. %RTT%).
Report Request Variables
Most of the
values returned by these values are defined on the Report Request dialog box.
Date and Time Period Variables
These values help you label layouts where
different accounting periods are reported in each instance.
APA- Period
Abbreviation.
APN - Period
Name.
ASD - As of
Reporting Date.
FY2 - Year
(YY).
FY4 -Year
(YYYY).
PED-End Date
of Current Period.
PER-Accounting
Period.
Scope-Related Variables
These values
help you label layouts for which you have defined a report scope. A scope is
used to define multiple instances of a report based on different field values.
For example, you could produce an instance of an expense report for each
department, or an operations summary for each business unit.
Some of the examples are:
SCD- Scope
Description.
SFN- Scope
Field Name.
SFV- Scope
Field Value.
Variables from Report Request
RID
RTT
LYN
SCN
IDN
IFN
ASD
nVision design rules/tips
- Different
ledgers cannot be combined in one field (i.e. ACTUALS and Corporate).
- Different Time Spans cannot be combined in
one field.
- nPloded rows or columns cannot be combined
with non-nPloded rows or Columns.
- If any field criteria are specified in an
individual cell, all field criteria should be specified in the cell, no field
criteria will be inherited from the row, column, or worksheet.
- If you are using trees in your nVision
layout, do not use more then 3 trees for performance reasons.
- Take advantage of report variables and
relative time spans to minimize layout maintenance.
- When creating trees, try not to use special
characters in the descriptions,
- Try to create your nVision report in the
upper left hand corner on the Excel
Developing an nVision report
The majority of work in creating an nVision
layout is in the planning stage. The Following
steps should be followed when creating nVision layouts. The more layouts built,
the quicker the planning steps will take, but they are still important to
review.
1. Develop a layout blueprint
2. Determine if the layout will be a Matrix or
Tabular layout
3. Determine and document all necessary criteria
and Excel formulas Planning Stage.
4. Navigate to nVision
5. Create a layout
6. Create a Report Request
7. Add a scope (if necessary)
8. Run the Report
Develop the layout blueprint
The first
step in creating an nVision layout is to develop the layout blueprint. Figure out how the report should look. For our example, let’s say we want to create
a report that shows the Budget Amount, Posted Total Amount, encumbrances, and
the remaining balances for several departments.
Determine if the layout will be a
Matrix or Tabular layout:
There are
two types of report layouts in PS/nVision - tabular layouts and matrix
layouts. The major difference between
these layouts is how they specify what data to retrieve from the database. In general, tabular layouts lend themselves
to detailed reporting, while matrix layouts are more appropriate for summarized
reporting. Tabular layouts are the simpler of the two layout types. They are created using a predefined
query. The columns in the report
correspond to the fields returned by the query; the rows in the report
correspond to the rows in the query result set.
Tabular layout will essentially return the
same information as a PeopleSoft Query, however, you can format, create totals
and include a pivot table in the nVision layout, so when you run the query,
these are automatically created.
Matrix layouts have data selection criteria
associated with columns and rows in the spreadsheet, creating a criteria
matrix. The data retrieved for an
individual cell is determined by an intersection of the criteria for its column
and its row. Like tabular layouts, a
matrix layout can use a column from a predefined query (this is commonly called
a mixed layout). Most matrix layouts
based on the ledger table. Matrix
layouts require at least one Time Span, at least one Ledger, and at least one Chatfield
as criteria. Drilldown capabilities are
only available with matrix layouts.
In this step, determine what criteria are
needed for the layout: Query for tabular layouts, Time Span, Ledger, and Chatfield
for Matrix layouts, along with variables and labels for both.
Criteria for Matrix layouts
Use criteria
at the worksheet level to specify defaults for the entire worksheet. This is done by entering data in Cell A1,
known as the brick. Criteria conflicts based on its placement. Worksheet criteria is the weakest, followed
by column, row, and finally, cell criteria is the strongest .This shows that if
something is entered as a default for the entire spreadsheet and nothing else
conflicts with the entry, that setting is global for the entire worksheet. However, if something is defined at the row
level that conflicts, then that row’s criteria is reflected for that row, but
the global worksheet setting is shown for the rest of the worksheet. When planning your template, putting the
global settings in once at the worksheet level will save set-up time and
processing time.
Placing criteria in an individual cell affects
the efficiency of the report, so use them only when necessary.
Navigate to nVision and create
layout
nVision
layouts are created in the client version of People Tools, and not on the
web. To create a new layout, click on
Add-Ins nVision New Layout. Enter a
layout name and press OK. The main
difference in this spreadsheet is row 1 and column A is hidden. This is where n vision puts its code, so
these are hidden from your view (to view these, select Add-Ins nVision Options…
from the Excel menu, then check the box that says ‘Show Row and Column
Criteria’.)
If a
blueprint spreadsheet was created in Excel, this can be converted; however, no information
can be entered in row 1 or column A. To
save this as an nVision layout, select Add-Ins nVision Save as layout from the Excel menu. The file will have an .xnv extension. DO NOT USE THE EXCEL SAVE BUTTON TO SAVE AN
NVISION LAYOUT, as it will be saved as an excel spreadsheet, and not an nVision
layout.
Creating a Matrix Layout -
walkthrough
Data
selection is the heart of the PS/nVision layout. As previously mentioned, the
database values retrieved for a matrix-based report are the results of
intersecting criteria.
Defined in the matrix layout. These
criteria tell PS/nVision exactly which database values to retrieve and where to
put them. You can specify data selection criteria at the level of the entire
spreadsheet, a row or column, or an individual cell. Generally, you specify
criteria at the highest level applicable. So, if you have criteria that apply
to the entire worksheet, you specify them at the worksheet level; if you have
criteria that are unique to a single cell, you apply them to that cell
only. With the layout open, double click
on a cell to begin. The Layout Options
box will appear (or select nVision Layout Definition):
Specify the type of nVision layout created
(Matrix or Tabular) as well a Set ID, effective date, and Business Unit. These are used for chart field values and
trees used within the nVision layout. It
is recommended for the Set ID and business unit to enter your corresponding
abbreviation. The effective date entered here will be used as the effective
date for chart fields and trees. You can
only use chart fields and trees that are active on the effective date
entered. Also, you cannot use anything
that is future dated, such as a tree.
nPloding rows
This report
will use nPlosion, which will automatically create individual rows or columns
for each detail value defined in the criteria.
The layout contains one division, but the layout should bring back the
details, and not one number. To enable nPlosion, navigate back to the Filter
tab where the column is specified.
Click on the
specified line, and press the nPlosion button.
Select ‘to Details only’, then press OK.
Create the report request and run
the request to test the layout
nVision
layouts do not contain data; they are simply a reusable template for different
reports. To use a layout and create a
report, you will need a report request.
The navigation is nVision Report Request…
There are many fields on the report request,
but it is pretty straightforward, and not every field needs to be
completed. Each field is defined below:
Request Name – Identified the report name. A unique report name must be used for each report
request, and are only 8 characters long.
·
Report Title – This title is what lists allowing you
to select the request appear on prompts.
·
Requesting Business Unit – The Business Unit the
report request belongs to. You can retrieve
information for this business unit along or for multiple business units,
depending on your security. This field
corresponds with the ‘Data from Requesting Business Unit only’.
·
Layout – The layout the report request is
referencing.
·
Scope – This is where to define the scope used in
the report (optional)
·
Directory Template – Specified the directory in
which to place the report instance.
·
File Template – Specifies the name of the .xls
report the report request creates. By
default, it is the report name.
·
Language Template – Not used.
·
E-Mail template – Not currently used.
·
Description Template - Specifies the name that will appear in the
Report Manager when the report request is run via the web.
·
Security Template – Not currently used.
·
Enable nPlosion – If you are using nPlosion in your
layout, this checkbox needs to be selected to enable nPlosion for the report
instance
·
Type – Specifies the output option. If running via the client, select file. If running via the web, select web or
window.
·
Format – Specifies whether the report will be saved
as an Excel (.xls), or .html file.
·
Data from Requesting Business Unit only – Specifies
whether only information for the Requesting Business unit will be shown in the
report. Typically, this box should be
checked unless you are doing cross reporting.
·
Translate Summary Ledgers to Detail – If you are
using a summary ledger in your layout, selecting this would bring back the
details as opposed to the summary information.
This is uncommon to check this box, and user will likely not create
reports via summary ledgers.
·
·
As of Date for Trees – Specifies the date to use for
the trees in your layout. Typically,
this would stay at
·
Run the Report -Complete the fields on the report
request, then press Run. The completed
report will run in the window and be an Excel spreadsheet.
Steps for Creating n vision report:
Step-1;
Open the pre
existing report, and select the entire column.
Setp-2: select the
appropriate chart field from the n vision menu.
n vision àLayout Definitionà Filter Tab àadd
Step-3: After selecting the appropriate chart field (chart
field1), click on ‘selected detail values’ from the ‘Filter options’ and click
on ‘OK’ button.
Step-4: Then you
will see the following window.
Step-5:
Click on the Question mark Button (‘?’), and
select the ‘CHARTFIELD1_TBL’ from the Edit table.
Step-6:
Click on
select values button (‘?’) and then select the Market Segment from the list of
Market Segments then click on “OK” button.
Step-7:
Then you will see the following
screen shot. Now save the nVision Layout.
Step-8: Likewise design the layout for
the rest of the Market segments for as many as the market Segments you need.
Step-N:After completion the layout design,
save the Layout design and run the n vision report.
Navigation:
n visionàReport Request
The Output:
When you run the N-vision report, you will
observe that the following output will be generated.
Subscribe to:
Posts (Atom)