nVision Virtual Ledgers ReportBooks

REPORTING: VIRTUAL LEDGERS
Further information can also be found in PS/NVision PeopleBooks > Advanced PS/nVision Options

Introduction
A virtual ledger is an alternate view of ledger data for purposes of PS/nVision reporting. This is not a concept supported by the PeopleSoft General Ledger system, and is thus only useful for PS/nVision reporting.

It is occasionally useful to provide users with alternate views of ledger data. For example, a user may wish to join data from another table (such as a product category table) with ledger data at reporting time.

This concept may also be useful for providing alternate security views for different users. For example, you might want to provide an alternate view of the LOCAL ledger for users who need data secured by PROJECT, while other ledger users have access secured by DEPTID. You can set up a virtual ledger called PROJLOCAL, which is a view of the LOCAL ledger secured by PROJECT.

The following example shows the steps for creating the virtual ledger:

A customer wants to provide an alternate view of the ACTUALS ledger for a class of users who need data secured by PROJECT, while other ledger users have access secured by DEPTID. They set up a virtual ledger called PROJACTLS, which is a view of the ACTUALS ledger secured by PROJECT.

A. Create Authorization Table:
——————————————-

1. Since the Virtual Ledger will be used to implement alternate security, in Application Designer, we need to create an authorization table (we’ll call it AUTH_PROJ_TBL) with OPRID and PROJECT columns.

2. Create a page to maintain the authorization table.

(PeopleSoft delivered a similar page and record to maintain nVision security: PS_LED_AUTH_TBL and LEDGER_SECURITY (Page). Navigation: Setup Financials/Supply Chain > Security > nVision Ledger Security)

B. Create a Virtual Ledger:
————————————–

1. Open the LEDGER record and use Save As to create a new record. Name this new record LED_PROJACT_VW.

2. Delete any unnecessary columns and create any additional columns.

In this example, we’ll add OPRID, since this view is used for security, and we’ll delete PROCESS_INSTANCE and TIMESTAMPS since these aren’t used for reporting.

3. The view SQL definition must contain the following features:

a. Since we’re using this view for security, it must deliver the OPRID (or OPRCLASS or ROWSECCLASS) field. This is the first item in the SELECT list below.

b. To get security by Project, the view joins the project authorization table to the ledger, making only the rows with matching projects visible for each user.

c. The SELECT list must deliver the name of the virtual ledger to satisfy the PS/nVision “…LEDGER=’PROJACTLS’…” criteria, even though the rows retrieved are really ACTUALS. (See the third item in the SELECT list of the sample view text that follows.)

d. The WHERE clause must include “… AND LEDGER = ‘ACTUALS’ …” to select the desired rows from the real ledger .

e. The view SQL definition will look like this:

select b.oprid,
a.business_unit,
‘PROJACTLS’,
a.fiscal_year,
a.accounting_period,
a.account,
a.deptid,
a.product,
a.project,
a.affiliate,
a.currency_cd,
a.statistics_code,
a.posted_total_amt
from ps_ledger a,
ps_auth_proj_tbl b
where a.ledger=’ACTUALS’
and a.project=b.project

4. Save the record definition and create the SQL View.

5. Create a ledger definition for PROJACTLS, specifying LED_PROJACT_VW as the reporting view record name. The other record and field names can be the same as for ACTUALS. Use the Ledgers for a Business Unit panel to associate the new ledger with the business units that will be using it.

a. Create a new Ledger Template (we’ll call it NVSPROJECT) (you can also use STANDARD template, if you haven’t used the Reporting View field).

b. Specify LED_PROJACT_VW as the reporting view record name.
When you define a ledger, you specify the physical table that stores the ledger data in the database. You can also define a record (view) for reporting purposes. If you define a reporting view, PS/nVision uses this record in place of the physical Ledger table.

c. Create a new Detail Ledger for NVSPROJECT template (we’ll call it PROJACTLS Ledger).

d. Create a new Ledger Group for PROJACTLS Detail Ledger (we’ll call it Ledger Group PROJACTLS).

e. Attached the new Ledger Group on the Business Unit. Setup the Open Period from and To.

6. In PS/nVision layouts, use PROJACTLS, rather than ACTUALS, in the Ledger criteria for reports to be run by the project accounting users.

nVision Drilldown is not allowed for this file from Web

Getting error that DrillDown is not allowed for this file from Web

PeopleTools 8.44 and above when running nVision drilldowns from the web

Users trying to run an nVision report to a type of WEB and output of XLS and when they go to drilldown they get an error:

DrillDown is not allowed for this file. It is generated with no DrillDown Option. To Enable the DrilDown, re-run the report with EnableDrillDownForFile = 1 in the Process Scheduler Configuration File.

This error is very confusing because it should come up if you run to a file and not to the web. The error is not a valid error.

Note: This is only an issue when using the lateset version of the DrillToPia.xla. If using an older version of the DrillToPia.xla the problem doesn’t occur. The older versions of the DrillToPia.xla do not work when using Excel 2003.

The problem was found to be when drilling down on reports that have a sheetname in them with a spaceor a dash (-). If the name of the sheet on the report includes a space or dash (-) the nvsDrillHyperlink doesn’t know how to handle the space and then the error occurs.

The problem will also occur if the report has both a matrix and a tabular report sheet in it.

In the later release of PeopleTools a newer DrillToPia.xla was delivered that resolved the sheet name problem. This DrillToPia.xla should work with previous versions as well. PeopleTools 8.47 and 8.22 on delivered newer versions of the DrillToPia.xls file that fixed previous issues.

The issue with the matrix/tabular reports giving error with drilldown will require a code change for the fix. This is fixed in the 8.47 and 8.22 release.

If you are on earlier versions of PeopleTools where this problem occurs you can attempt to fix it by renaming the sheet so it doesn’t contain a dash or space in the filename.

Create an “NvsDrillHyperLink” variable on the matrix sheet (Use Insert->Name->define, from the menu). Make sure that you add this variable specifically for the Matrix Sheet, by specifying the variable as ”MatrixSheetName!NvsDrillHyperLink” (where ‘MatrixSheetName’ is the name of the sheet that has the matrix report). Then copy the value from the “NvsDrillHyperLink” variable on the tabular sheet to the same variable on the matrix sheet.

nVision Copy Formulas in nPloded cells

How to Copy Forumlas in nPloded Cells
When creating an nvision layout in PeopleTools 8.19, selecting the “Copy formulas to nPloded cells” checkbox in the criteria dialog generates an assertion error similar to the following:

“PeopleTools Assert Triggered”

“Assertion (m_eDefnType == LYTDEFNAMOUNT || m_eDefnType == LYTDEFNNONE) Failed at E:\pt819rc3-retail\SRC\psnvd\nvldefn.cpp, line 153. See trace file. Cancel for debugger”

In PeopleTools 8.4x, no error message is received, but the checkbox doesn’t stick in there. If the user checks it and closes and goes back in,it’s no longer checked.

The fixed in PeopleTools 8.22 release as well as 8.44, came back in some cases in 8.45.

Manually type in the “%,C” criteria in the row or column.
————-

There is some more information related to “How to copy formulas to nploded cells “

You can copy formulas to nPloded data if you have rows or columns that contain only formulas, such as variance or total columns, and you 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 containing the formula you wish to copy. After the row/col is selected, navigate to Nvision >> Layout Definition >> Source

Check the Copy Formulas to nPloded cells box. Keep in mind that the row/col you highlight must contain only formulas; you do not want to select the row/column where you specified criteria and nPlosion. As long as the row/column you select intersects with the column or row where you specified criteria and nPlosion, nvision will copy the formula to each nploded detail value row/Column.

In some previous cases, when there is a really long formula on the tree node rows in the layout. On turning on “Copy Formula to nPloded Cells” the same formula is getting copied but nVision is cutting off over half of the characters.

“nVision is truncating cell size to 256 even though Excel 97 allows 32,767 characters.” The bug is fixed in release 8 of Tools.

The workaround is to shorten the formula. There are ways the formulas can be shortened such as breaking it up into multiple cells and referencing the pieces or using SUM or other functions that will reduce the number of characters in a cell.

nVision Customize NVUSER.XLM

QUESTION: How to customize the NVSUSER.XLM

ANSWER: From People Books

The creation of push buttons to run a macro is actually a Visual Basic/ Excel process. A brief example is also given in the file.

PSnVision offers options when it comes to report distribution. One of the questions you had was regarding methods being used by other clients.

There typically is not just one method being used:

Centralized processing/storage/retrieval process -Often the bulk of the reports
Ran centrally using report books
Using wide area networks, websites or other centralized storage areas
Standardized naming conventions (often created using PSnVision variables)
Central responsibility for maintenance/archiving
Users access on an as needed basis

Decentralized processing/storage/retrieval process
Run for a smaller group of users on-line or by report book
Using local area network
Standardized naming convention/set of directories
Users responsible for accessing/maintenance

Individual processing/Adhoc reporting
Run by an individual user for their own use
Stored on hard drive or local area network
Adhoc name
Individual user solely responsible

Following is a recap of how to customize NVSUSER.XLM. It is recapped from the Reporting and Analysis Tool PsnVision PeopleBooks. A walkthrough is also included for changing the point and click buttons.

Customizing NVSUSER.XLM
The NVSUSER workbook provided by PeopleSoft includes macros that make it easy to customize the user menu and toolbar. This section gives instructions for using these features to customize the user interface with little or no macro programming.

You can customize these features in the NVSUSER workbook:

? The actions performed by the spreadsheet buttons.
? The options available on the menus.
? The icons on the toolbar.

Note. You customize these items by assigning different Excel macros and other actions to the existing buttons and menu items or set up new ones.

For more information about the PS/nVision API functions that you can call from an Excel macro, see PS/nVision API Functions.

Topics
Customizing the Point and Click Buttons
Customizing Menus
Customizing the Toolbar

Customizing the Point and Click Buttons

Each button on the NVSUSER sheet has a macro assigned to it to perform some common function. As delivered, NVSUSER has buttons grouped into Run, View and Command groups. You are free to change these groupings and the meanings of the buttons. You can format the button sheet to customize its color and appearance.

To change the macro a button is assigned to

1. Position the mouse pointer over the button (it becomes a hand), but don’t click it.
2. Click mouse button two (usually the right mouse button), and from the shortcut menu, select Assign, Macro.
3. Enter the name of the macro you want to run when the button is clicked.
Click OK to complete the assignment.

Note. When creating macros to be assigned to push buttons, menus, or toolbar buttons, it is usually helpful to identify them as “Command” macros. When defining the name for the cell, in which the macro begins, select the Command type radio button on Excel’s Define Name dialog box.

Customizing Menus

To add or modify menu items to your DrillDown or other menu, you simply add entries to the table on the Menu worksheet in the NVSUSER workbook (select the Menu tab). The Menu worksheet has these options:

Option Description
Application Used to identify which application uses this menu item. This makes it easier to enable or disable the actions associated with an application by setting its Active flag. The application is not used by the supplied macros, but acts as documentation.
Active Y to make the menu item active; N to keep it in the table, but deactivate it.
Command The text of the command to appear when the user pulls down the menu.
Description Descriptive text; appears in the status bar when the user highlights the command.

To have the macros create a DrillDown or other menu for you
1. If desired, edit the name of the menu (the default is Drill) in the Menu Name box.
2. Edit the table of menu items, adding, changing and deleting items to get the menu you want.
3. For each menu item (command) you add, specify one of the following:
? A layout, present in your DrillDown layout path, to be used in a matrix or tabular DrillDown.
? A query name to be used in a QueryLink-style tabular DrillDown.
? The name of a macro you want to run when the item is selected. This enables you to associate macro code with a menu item.
4. When the table is as you want it, click Rebuild Menu to build the new menu.
Customizing the Toolbar

NVSUSER can build a custom toolbar from a table similar to the table that defines a custom menu.

To customize the toolbar
1. Select the Toolbar tab in the NVSUSER workbook.
2. If desired, edit the name of the toolbar in the Toolbar Name box.
3. Set the location of the toolbar in the Toolbar Location box.
You can enter left, right, top, or bottom to have the toolbar docked accordingly, or select float to have the toolbar float over the spreadsheet.
4. Edit the table of tool commands, adding, changing and deleting items to get the toolbar you want.
5. For each tool you add or change, specify one of the following actions:
? A layout, present in your DrillDown layout path, to be used in a matrix or tabular DrillDown.
? A query name to be used in a QueryLink-style tabular DrillDown.
? The name of a macro you want to run when the tool button is clicked. This allows you to associate macro code with a toolbar button.
6. For each tool, also specify the following:
? Active-Y to make the tool active; N to keep it in the table, but not in the toolbar.
? Face-The name of the picture which identifies the tool. Either use a picture already defined on the Toolbar sheet, or design your own using a drawing program such as Paintbrush. The picture must be 16 pixels square. Copy the picture from the drawing program and paste it onto the Toolbar sheet. While it is selected, enter a name for it, and enter this name in the Face column.
? Gap-To leave a gap between tools, enter “gap” in the Face column.
? Status text-Descriptive text; appears in the status bar when the mouse rests over the tool.
? Tip text-Brief description; appears in a “tool tip” when the mouse rests over the tool.
When the table is as you want it, click Rebuild Toolbar to build the new toolbar.

Changing the Startup File

When you start PS/nVision, it tries to open NVSUSER.XLM by default. But what if you don’t want it to open NVSUSER? What if you have your own custom workbook that you’d like PS/nVision to open?

If you start PS/nVision from a Microsoft Windows icon (or “shortcut”) or the Windows Start menu, you can specify a different startup file on the PS/nVision command line. All you need to do is include the -SF argument, followed by the name of the startup file. For example, to have PS/nVision open the file NVSDEV.XLS, you’d enter a command similar to the following in the icon definition:
c:\pt700\bin\psnvs.exe -SFNVSDEV.XLS

This feature enables you to provide different startup files for different classes of users.
Note. PS/nVision searches for the startup file in the same directories where it looks for its default startup file.

For more information, see Changing PS/nVision Options with Configuration Manager.
PS/nVision API Functions
PS/nVision has an API function interface. The Excel macros that you write can use these functions to gain access to PS/nVision features. This table describes the API functions. Note that none of these functions may be called within a macro called via NvsInstanceHook.

Walk Thru

To customize the push button screen
? Could be as easy as going into this tab and changing ie. ViewBalsheet
? =OPEN(“your network drive\user\whatever directory \name of your file.xls”)
? To add a new macro/button
? Prepare your macro
? Create a new macro at the bottom of the NvsUser.xlm macro tab -sheet
? Copy and change an existing macro
? Reference your file name inside the parentheses( ) and quotes ” “Name the cell where the macro(that you copied and modified) resides with a name of your choice ( Just as PS had named a macro ViewBalSheet)
? Record a macro
? Plan what you want to do before turning on record.
? With your cursor in a row after the last macro on the Macro worksheet of the NvsUser.xlm file
? Select from the Menu-Tools, Macro, Record New macro
? Give your macro a name
? Go through the keystrokes you want to execute when clicking the button
? Opening a certain file
? Run a certain report
? Write it yourself
? Name the macro
? Highlight the cells on the macro sheet where the macro is stored
? Click the menu-Insert, name, Define
? Type your macro name in the top box
? See “refers to” box at bottom of box
? Should be “=Macros!”your highlighted range”
? The Macros! Indicates which sheet the macro range is on
? Click the Command Macro radio button
? This defines the range as a macro with out this step your name will not show up when you try to assign the macro to your button
? Now you should notice that when you have your cursor on the first cell of the macro you will see your macro name show up in the Name box on the formula Bar at the top of the Excel worksheet.
? Add a new push button using Insert on the menu, Picture and auto shapes.
? Pick a shape
? Select a place for your “button”
? Be creative
? Once your button is ready
? Type text on the button to identify its purpose
? Position your mouse arrow over the button
? Right click, select Edit Text
? Type your text
? Right click, Exit Edit Text
? Assign a macro
? Right click, Assign Macro
? Select macro from drop down
? Test your button

nVision Errors running Queries to Excel

PeopleTools 8.1x and 8.4x for Query on the client (2-tier/3-tier)
One solution included here is for after 8.19 and 8.43, the other solution is prior to 8.19 and 8.43

Users try to run Query to Excel on their workstation and get errors. The common errors are:

A). Failed to start PSNVS.DLL or PSNXL.DLL.’

B). “Assertion (m_pNam != NULL) Failed at E:\pt814RC7+retail\SRC\psnvd\nvdnam.cpp, line 181, See trace file. Cancel for debugger’.

C). Assertion (SUCCEED(hr)) Failed at E:\pt814rc7-retail\SRC\psqed\qeddoc.cpp, line 906. see trace file. cancel for debugger

After PeopleTools 8.19 and 8.43 these errors generally come up when the workstation is not installed with the proper DLL’s necessary to run the Query to Excel on the client. When running Query to Excel on the client, it invokes nVision so therefore all the necessary nVision paths must be correct and DLL’s must be set. If specific workstations receive this error the administrator of the machine needs to do an ‘Install Workstation’ through Configuration Manager. They should make sure to check the two boxes to install ODBC as well.

If the user is still getting the error, they should make sure they can launch nVision manually.

Prior to PeopleTools 8.19 and 8.43 there was a bug that caused these errors. The following problems could occur prior to these releases:

The Main problem in this they need to give nVision access to all the users who want to run Query to Excel.

Citrix Related Issues:

1). Major Issue: In all issues customers are not able to run nVision or Query to Excel as a Citrix User.

2). If they logon Citrix as Administrator then they can start nVision and can run Query to excel,
in some cases they need to keep the nVision open all the time to run from query to Excel.
If they close nVision in middle then they can’t run query to Excel.

Windows Related Issues:

3). For Windows users while starting nVision they are getting an Error : ‘ Failed to start PSNVS.DLL or PSNXL.DLL.’

4). Ct is on Windows 2000 workstations.
Only the PS user id can run Query to Excel can run it and they have SysAdmin access.

5). One customer reported: “In 3-tier, we can run query to excel only when excel is open.”

Workaround:

Give users security to access nVision. Sometimes it will be necessary to leave nVision up. (This should only be for prior to 8.19 and 8.43)

nVision Summary Ledger

1. THE SUMMARY LEDGER

This document explains how to create summary ledgers and summary trees for nVision reporting.

A summary ledger sums up the details of a general ledger. One row of data on the summary ledger is the sum of many rows of data on the detail ledger. For example, if an nVision report requires an account total for several departments, the summary ledger could be constructed so that this data is summed up into one row.

The purpose of a summary ledger is to reduce the processing time for nVision reports. When you run a report, it will search for one row on the summary ledger rather than many rows on the detail ledger. If one row on the summary ledger equals 10 rows on the detail ledger the searching time would be reduced by 90 per cent.

A summary tree allows users to retrieve the details from the general ledger. Without a summary tree, the user only sees the summarized data when he uses the drill down feature of nVision. The summary tree connects the summarized data with its related details and the user sees the details during drill down.

2. CREATING AND PROCESSING SUMMARY LEDGERS

This section explains the steps involved in creating and processing the summary ledgers.

a. Verifying the Structure of Detail Trees

A detail tree is the basis for populating a summary ledger record. A detail tree also is the basis for a related summary tree. A summary tree provides the link between the data of the summary ledger and the detail ledger.

You will need to verify that the detail trees used in summary ledger definitions have nodes at every level above the detail level. The detail values will need to reside at the lowest level of the tree. This may require the insertion of dummy nodes because the data may not fit precisely into the logical groupings defined by the tree levels.

Values contained in the PeopleSoft tree tables make up PeopleSoft trees. These tables are: PSTREEDEFN, PSTREELEVEL, PSTREEBRANCH, PSTREENODE, PSTREELEAF, PSTREESTRCT, PS_TREE_LEVEL_TBL, and PS_TREE_NODE_TBL. The values in these tables must be kept in synch in order for the trees to work properly. Under normal operations, the system should keep these tables in synch without any problem. However, it is always a good idea to run the DDDAUDIT and SYSAUDIT procedures provided with the system on a periodic basis. These procedures identify many different inconsistencies between tables in the system. Any inconsistencies identified by these procedures should be corrected as soon as possible. (See your system documentation for detailed information on how to run and correct errors found by these procedures.)

b. Defining and Creating Summary Ledger Records

The next step after validating the detail trees is to define the tables that will contain data for your summary ledgers. New tables are created in the PeopleSoft system using Application Designer (Go – PeopleTools – Application Designer – File – New). An example of a summary ledger table containing the DEPTID_SUM summary chartfield is shown below. This example creates a new record (S_DEPTROLLUP_PC) by copying and modifying an existing PeopleSoft record (S_LEDGER_ACTDIV).

Notice the DEPTID_SUM field has 20 characters. Tree nodes can be up to 20 characters in length. Therefore, the fields holding summary values need to be 20 characters in length.

After defining the summary ledger table in Application Designer, you will save it and then build the SQL. Insert it in the QUERY tree for GL (Go – PeopleTools – Tree Manager – GL Section – Insert a Sibling) and save the tree.

c. Defining a Ledger Template

The next step in the creation of summary ledgers is to define a ledger template. The ledger template tells the system the name of the table in which to store the data for summary ledgers using this particular template. New templates are defined using the Ledger Template panel (Go – Process Financial Information – Maintain Ledgers – Use – Ledger Template – Record Definitions – Add). All that needs to be specified on this panel is the description, setting the Type equal to Summary and specifying the name of the summary ledger table to use. AT SAMPLE, WE USE THE S_DEPTROLL LEDGER TEMPLATE TO CONNECT THE SUMMARY LEDGER DEFINITION TO THE RECORD S_DEPTROLLUP_PC.

d. Creating Summary Ledger Definitions

The next step in the process is to create the summary ledger definitions. It is very important to evaluate your reporting needs prior to defining your summary ledgers. You will want to summarize the chartfield data to the highest level possible, while still meeting level of detail required for the majority of your reports. For example, if the data at level 5 of the DEPTBYLOC tree is what most reports reference, then that is the level you will want to summarize to. Keep in mind that you can create multiple summary ledgers to meet a variety of reporting needs. However, summary ledgers take time and resources to process so you will want to evaluate each one individually. Also remember that your summary trees will allow you to rollup this summarized data in different ways and allow you to drill down to the detail level in the detail ledger.

Summary ledgers are defined through the Summary Ledger Definition panels (Go – Process Financial Information – Maintain Ledgers – Use – Summary Ledger – Add).

The Summary Ledger panel defines the ledger template used by the summary ledger, the name to the detail ledger to extract data from, the description and effective date of the summary ledger. This panel also defines the fields on the detail ledger map to the summary ledger fields. It also defines how you are specifying the level of summarization for that chartfield.

BE SURE TO SPECIFY THE RADIO BUTTON (LEVEL OF DETAIL) AND THE DETAIL LEVEL CHARTFIELD FOR EVERY SUMMARY LEDGER CHARTFIELD. Use the outer scroll bar to move to the next field on the summary ledger.

Remember that Summary ledgers are effective dated. Make sure that the effective date is > or = the effective dates of the chartfield values being summarized.

e. Making a New Summary Ledger Available to the Business Unit

You will not be able to run the summary ledger process until you tie the new summary ledger to the business units that will be using it. Go to the Ledgers for a Unit panel (GO – Define Business Rules – Establish Business Units – Use A-M – Ledgers for a Unit – Definition). You will need to add the new summary ledger to the all Business Units that will use the summary ledger by inserting a new row containing the new summary ledger information.

BE SURE THAT THE SETID FOR THE SUMMARY LEDGER IS THE SAME AS THE SETID FOR THE BUSINESS UNIT’S DETAIL LEDGER. THIS SETID IS SPECIFIED IN THE TABLESET CONTROL FOR THE RECORD GROUP FS-04, DETAIL AND SUMMARY LEDGERS (GO – PeopleTools – Utilities – Use – TableSet ID – Update/Display).

IF THE SETID’S ARE DIFFERENT, YOU NEED TO POINT THE TREE TO THE SAME SETID AS THE SETID OF THE DETAIL LEDGER. GO TO THE TABLESETID PANEL (GO – PeopleTools – Utilities – Use – TableSet ID – Update/Display) AND ENTER THE SETID OF THE DETAIL LEDGER IN THE DIALOG BOX. GO TO THE TAB MARKED TABLE SET TREE CONTROLS, ENTER THE SETID OF THE TREE IN THE BOX ON THE RIGHT SIDE OF THE PANEL, AND THEN USE THE DROP DOWN BOX ON THE LEFT SIDE TO FIND THE NAME OF THE TREE. SAVE. THIS WILL ALLOW THE TREE TO BE USED WITH THE SETID OF THE DETAIL LEDGER. NOW YOU NEED TO GO BACK AND CREATE A NEW SUMMARY LEDGER USING THE DETAIL LEDGER’S SETID IN THE DIALOG BOX.

Also specify the calendar that the summary ledger is to use.

f. Creating a Summary Calendar

If you plan to summarize the accounting periods in the summary ledger, you have to create a new summary calendar if one doesn’t already exist to suit your needs. This is done through the Summary Calendars panel (GO – Define Business Rules – Define General Options – Use R-Z – Summary Calendar).

Summary calendars are based on detail calendar definitions. You specify the fiscal year as well as the from and through periods from the detail calendar that you want your summary calendar to summarize. Summary calendars can be used to summarize monthly accounting periods into quarterly or annual amounts in your summary ledgers if so desired.

g. Running the Summary Ledger Process

To populate the summary ledgers with data, you must run the summary ledger process for each of the summary ledgers (Go – Process Financial Information – Maintain Ledgers – Process – Summary Ledger). Multiple summary ledgers can be specified within one Run Control. To add additional summary ledgers to a specific run control simply hit the F7 key while within the scroll bar on the panel. F8 will delete a summary ledger from the run control.

Click on the Year End Adjustment radio button to process period 998 and the Balance Forward radio button to process period 0. These will be processed after the prior year has been closed. Otherwise, you will only need to summarize the current period or any of the periods required to be processed for reporting purposes.

This process runs online by clicking on the traffic light icon on the menu bar. You may want to schedule the summary ledger processes to run in your nightly batch process immediately following the edit and post processes. The summary ledger process is a Cobol program. You should be able to set it up in your scheduling system in the same manner that you have done other Cobol processes.

WHEN YOU FIRST USE THIS COBOL PROCESS YOU MAY GET THE FOLLOWING ERROR MESSAGE ON THE MESSAGE LOG; “DYNAMIC SQL ERROR WHILE CREATING A SUMMARY LEDGER. REVIEW THE PSPDYSQL.LOG FILE TO DETERMINE THE STATEMENT IN ERROR (5840,2).”

THIS MEANS THAT THE SQL PROGRAM CANNOT TRANSFER THE DATA FROM THE DETAIL LEDGER TO THE SUMMARY LEDGER.

THE RESOLUTION FOR THIS ERROR WAS TO FOLLOW A STRICT PROCEDURE WHEN SETTING UP THE SUMMARY LEDGER RECORD (SEE SECTION 2B). WE HAD TO FOLLOW THIS SEQUENCE: OPEN THE PEOPLESOFT DELIVERED RECORD (S_LEDGER_ACTDEP), SAVE AS A NEW RECORD, DELETE THE UNWANTED CHARTFIELDS, INSERT THE REPLACEMENT CHARTFIELDS, ADD KEYS AND SEARCH KEYS, BUILD AND SAVE.

3. SETTING UP A SUMMARY VIEW AND A SUMMARY TREE

A summary view and a summary tree link the summary ledger with the detail ledger. They allow the user to see the details when he uses the drill down feature of nVision. Without a summary view and a summary tree, the user only sees the summarized data. The summary view and the summary tree connect the summarized data with its related details and the user sees the details during drill down.

a. Creating a Summary View

The summary view is used by the summary tree structure to establish a link between the summary tree and the detail tree. The detail tree must be defined with strictly enforced levels in order to be able to build the view and the summary tree.

NAVIGATE TO THE SQL VIEW (File – Object Properties – Type) AND RECORD THE FOLLOWING SQL STATEMENT, MODIFYING IT TO SHOW THE CORRECT SETID, TREE NAME AND TREE LEVEL NUMBER:

SELECT A.SETID, A.TREE_NODE, A.EFFDT, B.DESCR
FROM PSTREENODE A, PS_TREE_NODE_TBL B
WHERE A.SETID = ‘XXXXXX’
AND A.TREE_NAME=’XXXXXXXXX’
AND A.SETID = B.SETID
AND A.TREE_NODE = B.TREE_NODE
AND A.TREE_LEVEL_NUM = 5
AND B.EFFDT = (SELECT MAX(EFFDT)
FROM PS_TREE_NODE_TBL WHERE SETID = B.SETID
AND TREE_NODE = B.TREE_NODE
AND EFFDT or = the detail tree from which it is based. Also, be sure to refer to a summary tree structure id like the one created in the previous step.

Once defined, add the levels, nodes and detail to the summary tree just as you would to a detail tree. Remember that the detail values for the summary tree are the summary values from the summary ledger (e.g., DEPTID_SUM). You can have multiple summary trees based on the same detail tree. This allows you to rollup the data in the summary ledger is different ways to suit your reporting requirements.

The SUM_DEPTBYLOC summary tree has been partially created in the SAMPLE COMPANY database (not all of the detail values have been added at this time). It can be used as a model for creating other summary trees if required.

D. Modifying nVision Reports to Reference the Summary Trees and Summary Ledgers

After completing the previous steps, you will need to modify the criteria in the summary nVision reports to refer to the new summary ledgers and new summary tree nodes using Layout Definition nVision menu option. If you are very familiar with the criteria coding in row 1 and column A of nVision layouts, you can use the Excel ‘find and replace’ functions to help expedite the conversion on previously created reports.

The nVision nPlosion feature can be used to expand down to the lowest level of detail in the summary ledger. By referencing the summary tree nodes in your report criteria, you can drilldown to either more detailed information in the summary ledger (if available) or to the detail ledger information once the report is run. You can also have a mixture of detail ledger and summary ledger criteria in the same report if necessary. You will want to minimize the use of detail ledger criteria as much as possible to improve report performance.

BE SURE TO MODIFY THE NVISION SCOPE DEFINITIONS TO REFLECT THE SUMMARY TREES BEING USED.

e. Performing Database Tuning on Summary Ledger Tables

Some database tuning will be required on an ongoing basis in order to optimize performance of your nVision reports. You will need to work closely with the DBA staff to identify and tune SQL statements that have excessive run times. Your DBA may need to modify existing indexes on the summary ledger tables or add new ones.

nVision Timeouts

nVision Reports/Report Books Timeout
After upgrading to PeopleTools 8.44 or higher,if a users run nVision reports for more than 15 minutes they go to no success status in Process Monitor.The error in the message log states, “nVision process is erroring out process exceeded max processing time (15 minutes)”.

Starting in PeopleTools 8.44, nVision users can specify the maximum duration each of the report requests are allowed to run. By default,the report request is set to blank which means it will timeout after 15 minutes.

If the report is going to run over the 15 minutes,the users can specify the maximum duration under the Advance Options on the nVision Report Request page.

Users can also change the Maximum Processing time under the nVision Process Definition. If it’s set to blank it will default to 15 minutes. Here is some information in Peoplebooks regarding that:

################

Error Handling (Timeout)

Use this option to specify the maximum processing time for this process. The system will kill the process if processing exceeds this time. The default value is specified in the Process Definition. If a nonzero value is specified in the report request, that value is used if the report request’s value is zero, the value from the Process Definition is used. If no value is specified in the Process Definition, a default value of 15 minutes is used.

###############

If the users are running a ReportBook,the timeout value is for each individual request in the reportbook.If everything is left blank and any of the requests run over 15 minutes, it will timeout.

If users wish to change the Max Processing Time for the Process Definition,you will want to change it for the NVSRUN definition.If Drilldowns are going to process for over 15 minutes,the DRILLDOWN Process Definition will need to be changed as well.

You will need to bounce the Process Scheduler for the changes to take effect.

nVision Memory Errors (Leaks)

Memory Leaks/Errors
Problem with memory errors when running nvision on the process scheduler. The problem may be seen during high volume times when multiple nVision reports are being run.

The following information is gathered from the Red Paper out on Customer Connection titled, “nVision in the PeopleSoft Internet Architecture”. For more information you can reference that Red Paper. The applicable information from there for this issue is below:

Please check the following information. Please go thru all the documents very carefully and we recommend that an NT Admin perform the following tasks.

1)Apply Microsoft hotfix for the GDI resource leak problem in Windows operating system.
The Report server used for testing nVision reports crashed over a period of time ( 24- 48 hrs), and the problem was due to a GDI resource leak in windows operating system. You may also experince GUI painting issues and the system not responding to mouse events. This is a Microsoft issue and a hot fix is available from Microsoft. Please refer to Microsoft KB article Q826518 for more details.
http://support.Microsoft.com/default.aspx?scid=kb;en-us;826518
To get the hotfix, call Microsoft at 1-800-936-4900, select option 3 and request hotfix Q826518. (specify Windows 2000 or XP)

2)Increase the shared section memory heap for running multiple nVision reports on background mode.
When nVision runs in interactive mode (foreground), it runs within the 3 MB of desktop heap and in non-interactive (background) mode it runs within 512 KB of desktop heap. When nVision and Excel run, they use a fair amount of desktop heap space. Programs run in non-interactive mode gets only 512 KB of heap size and this may not be sufficient if you run large number of programs. If there is not enough heap for running nVision reports on the background mode, some reports may not run, hang during the initial stage, and may block other nVision reports.
For running the same reports in foreground and back ground mode, you need to increase the shared section value for the background heap to the same as the foreground value. The shared section heap value used for testing 30 nVision reports and other applications was 3072.(3 MB)
The shared section value needs to be changed in the system registry and users will have to change this value using registry editor. The MS article 126962, explains this in detail and contains instructions for changing the shared section value. http://support.Microsoft.com/default.aspx?scid=kb;EN-US;126962
A problem incident has been opened in PeopleTools to automate this change and will be addressed in a future release.
The following section explains the registry values for shared section memory.
Windows uses different shared section memory heap for interactive and non interactive programs. The size of the desktop heap allocated for a desktop associated with a window station is stored in the registry, in:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows
The default values are,
SharedSection=1024KB (common), 3072KB (interactive), 512KB (non-interactive)
· The first SharedSection value (1024 KB) is the shared heap size common to all desktops.
· The second SharedSection value (3072 KB) is the size of the desktop heap for each desktop that is associated with the “interactive” window
· The third SharedSection value, (512 KB ) is the size of the desktop heap for desktops created in noninteractive window stations.
3) Change the Performance Options to optimize performance for Background services.
By changing this setting, the system will assign equal amounts of processor resources to all programs. (Foreground and Background)

To change the value of this entry, in Control Panel, double-click System, click the Advanced tab, click Performance Options, and then, in the Application response section, select either Applications or Background services.
Clicking Applications sets the value of this entry to 100110, and provides for short, variable length processor intervals in which foreground processes get three times as much processor time as do background processes.
Clicking Background services sets the value of this entry to 011000, and provides for longer, fixed-length processor intervals in which foreground processes and background processes get equal processor priority.

nVision Prompts

nVision Tabular layout with prompts does not work in web mode, it works in 2-tier.

When trying to run nVision Report (with query that contains prompts) in web, the report is processing forever and the prompts got stuck in the process. The report works fine in 2-tier

nVision tabular layouts that require user input do not work in web mode. An example is the delivered report id “ADHOC”. The process monitor always shows the status of processing but no prompt screen is presented on the browser so the user can enter the parameters. (The prompt appears on the server instead)

When a NVision report is executed using PeopleSoft Query in PIA ,ps/nVision display’s a dialog box with prompt values on the report server not on client station .
Steps for replicating the problem
1. Create a query with a prompt
2. Create a Tabular layout using this query
3. Create a Report request
4. Run this report thru the web
and the prompts come up on the server where the Process Scheduler is running.

Here are two possible workarounds. The easier approach is to use scopes.

(1). Use scope:
Users are able to define and modify nVision scopes and report requests the web. When using scopes to filter the results, users should create separate scopes for the values they want to use. During runtime, users can select a proper scope on the report request page and run the report. The drawback for this approach is that when the number of values users have to use is high, or when users need to use combinations of values on different fields, they have to manager a large number of predefined scopes. In addition, there is a known issue using nVision scopes with a query that has a union – nVision only apply scope criteria onto the first select statement.

(2). Rewrite the queries to join to a custom table where the runtime parameters can be stored.
This requires customer to create a custom record that contains the fields users need to provide values. As users do not have direct access to databases while running reports on the web, a custom PIA page also needs to be created for users to enter values for those fields, which need to be populated before nVision reports are run.

Making runtime parameters specific for a given user and a given report request can help avoid contention and overwriting issues. To accomplish this, users may key the parameter table by operator ID and then by report request ID. Row level security will be invoked to filter appropriately on operator ID. In order to filter on the report request, PeopleCode on the report request page may need to be modified to create a scope automatically to filter on the report request ID field using currently active report request value. The modified PeopleCode also needs to apply the automatically created scope to the existing report request when running the report, so that the values for the fields under the particular operator ID and report request ID on the parameter record are used to filter the final result.

nVision Tree Selector Tables

Subject: Rebuilding Tree Selectors for nVision

Content: The following are 2 methods in rebuild tree selectors.

Method 1: nVision will automatically rebuild selectors if the tree is re-saved after deleting a node and re-adding it,
or if the tree is copied to a new effective date.

Method 2: delete the selector control entries associated with the ACCOUNT trees, forcing nVision to rebuild them. The following SQL statement should do this, assuming that the ACCOUNT trees have a tree structure called ACCOUNT (if not, change the structure ID):

delete from pstreeselctl
where tree_name in
(select tree_name from pstreedefn
where tree_strct_id=’ACCOUNT’);

One additional note: deleting the Selector Controls forces nVision to rebuild them from the other tree tables. It also leaves all the old selector rows orphaned in PSTREESELECT06 (if ACCOUNT is 6 char. long). But it doesn’t affect the node name lookup, which is done either on the system node table (PSTREENODE) or in a cache (swap file) of nodes nVision has already looked up. I have heard of cases where that cache was not rebuilt after a tree change, but that only happens when the nVision user stays logged on while the tree change takes place. Logging off and logging back on should solve this, and is less disruptive than deleting swaps.

————————————————-

More information regardint PSTREESELECT table.

Resolution 2541
NV:when does PSTREESELECT get updated?

The PSTREESELECT# tables are used to speed tree-based data selection. The table gets rows inserted into it the first time a tree is used by an nvision report.

PSTREESELCTL is the table which controls whether or not to refresh data in the selector tables based on the Tree effective date.
If you modify a tree and save it, the pstreeselect tables will get updated when you run an nVision report that uses that tree

The indexes need to be tuned based on how they use it. PSG goes out and does this a lot. It is covered in Chapter 12 of the Using your GL system

======================================================

Resolution 38452
NV: How does ct repopulate the tree selector tables without a database backup?

PROBLEM: How does ct repopulate the tree selector tables without a database backup? Customer accidently deleted them.

SOLUTION: The selector tables should be recreated each time the nVision reports are run. Basically what happens is that nVision looks to see if the selector table values exist for items in the trees. If they are not there, the process recreates the static selector table values. You should never have to copy or create selector table values, the system will do it for you. Unfortunately, if you have changed the trees between the time the selector tables were deleted and the time they are rebuilt, you may have ‘orphaned’ records which may impede performance. This can be identifed and deleted using SQR.

If you are running with Dynamic Selectors on in the Performance Options, the selector values for the trees being used only exist for the duration of the report run. They will not remain after the run completes.