Running PS/nVision Reports on the Web

This chapter provides an overview of PS/nVision web services and describes how to:

• Configure the report node.

• Create scope definition.

• Set up report requests.

• Submit report requests.

• Use report books.

• Use the PS/nVision DrillDown on the web.

Understanding PS/nVision Reporting on the Web

Users with Internet access can run, view, drill down into, and distribute PS/nVision reports through a browser, without needing a PeopleSoft application installed on client machines. Users can send reports as email attachments rather than requiring recipients to navigate to a location on a file server.

PS/nVision on the web includes:

• Internet versions of report requests, report books, and scope definitions.

• Output in both Microsoft Excel (XLS) and HTML formats.

• DrillDown for both Microsoft Excel and HTML reports.

• Email options to send PS/nVision reports to individual users or users in specified roles.

• Integration with PeopleSoft Process Scheduler and PeopleSoft Report Manager to send bursting report instances to designated users or users in specified roles.

• Support for multiple PS/nVision sessions when PS/nVision is invoked from PeopleSoft Process Scheduler.

• A restart feature that allows the system to restart a process if it is marked No Success.

Note. To take full advantage of the web features, you need Microsoft Office 2000 on the PS/nVision report server. Microsoft Office 2000 is not required on any other servers or on any computers used to initiate report books or report requests from the browser. A report server is a Windows NT machine with PS/nVision (PeopleTools client software), Microsoft Excel, and PeopleSoft Process Scheduler installed. Process Scheduler is required to schedule all PS/nVision processes. Microsoft Office 2000 is required for creating reports in HTML format.

See Also

Defining Process Definitions

Configuring PS/nVision on the Web

PS/nVision processes on the web are all run by Process Scheduler. All the equivalent settings that PS/nVision needs in the Windows client (such as layout directory and instance directory) are defined in the nVision section of the Process Scheduler configuration file. Other configurable options include:

• The EnableDrillDownDownForFile parameter, which supports DrillDown on File output type reports in the web-based nVision environment.

  The setting only takes effect when the report request is defined to run in File output type on the web. By default, EnableDrillDownDownForFile is set to be 0 (false). When the option is set to 1, users are able to access the report instances in the instance directory and drill down the report instances on the web. When the option is set to 0, the generated report instances are not drillable.

• The EnablePollDialogs and PollDialogSeconds parameters, which dismiss unattended PS/nVision dialog boxes.

  Note. Because this option can affect performance, it should be used with caution.

• The TraceLevel option, which enables you to generate and view trace logs specifically for PS/nVision.

• The SaveTempFile parameter enables you to save or delete the temporary reports that are created when running nVision to email, print, or web output.

  When SaveTempFile parameter is set to true (1), temporary reports will not be deleted. If run to email or print output, the reports will be saved to the folder specified in the Directory Name Template field of the report request. If that field is left blank, the report is saved to the nVision instance directory. If run to web output, the report is always be saved in the nVision instance directory.

See Also

Using the PSADMIN Utility

Setting Trace Files

Dismissing Unattended Dialogs

Using Configuration Manager

Configuring the Report Node

Both PeopleSoft Process Scheduler and Report Manager use report distribution nodes to deliver PS/nVision reports. The distribution node definition specifies the report repository location and the location of the stored content (the files) for Report Manager. When PS/nVision creates a report instance, it passes the physical file location information to PeopleSoft Process Scheduler. Process Scheduler then passes the file to Report Manager using the information defined on the Distribution Node page.

See Also

Setting Distribution Options

Defining Report Nodes

Creating Scope Definitions

Creating a scope definition on the web is similar to defining it in the Windows client with the following exception: You cannot specify an effective date for the tree node table or value table. The current date is used as the effective date.

Select Reporting Tools, PS/nVision, Define Scope to access the Scope Definition page where you can create scope definitions.

See Defining Report Scopes.

See Creating Report Requests.

The following example shows a Scope Definition page:

Scopes are defined based on a tree node record or a value table such as Department table or Product table. By defining your scope based on a table that includes an email address or user ID for each value you can direct each scope based-report instance to a different user. For example, the record shown below can be used with a scope definition as a value table to have each report instance sent by email or posted to a different user in Report Manager.

Scope variables are used in the Email and Security templates the same way that you use them to create unique file names with the File template or directory names with the Directory template in your report request. The Scope Descriptive Variable (%DES%) is used to select a related field value from the Scope Value table or the Tree Node table. You can use values for email addresses, user Ids, or role Ids. The general syntax of the %DES% variable is:

%DES.[scope field].[detail field].[node field]%

If your scope is based on a value table, replace [node field] with [detail field]. The [detail field] is repeated so that the basic format of the syntax remains the same.

Note. The %DES% variable requires the associated field value be in the same table that the scope is based on.

Email Template Variables

To have each report instance sent as email to the associated email address of department manager, you would enter the following values in your email template:

%DES.DEPTID.EMAILID.EMAILID%

This values enables PS/nVision to use the associated email address from the EMAILID field from the value table where the DEPTID is located. You need to specify email as the output type on the report request.

The scope descriptive variable (%DES%) is used to select a related field value from the scope value table. For example, if your scope were based on the record shown above, you would produce three report instances, and each instance would be sent to the associated email address. The report for department 12000 would be emailed to [email protected], and the report for 13000 would be emailed to [email protected]. The report for department 14000 would be emailed to several recipients—Kathy Lin, Peter Yu, and the email addresses for the users who belong to the role Asia Manager. You can specify the user email addresses associated with users and roles in the user profiles of Maintain Security.

Security Template Variables

Using the record above for the value table, you could post each report instance to Report Manager for the associated user. In this case you would use web as the output type and enter the following variables in the security template:

%DES.DEPTID.OPRID.OPRID%

The report for department 12000 would be posted to Report Manager for user JOHNSMITH, and the report for 13000 would be posted for user JANEWHITE. John Smith would not be able to access the Department 13000 report. The report for department 14000 would be posted to Report Manager for Kathy Lin, Peter Yu, and the other users belonging to the role Asia Manager.

Sample Record Definition

The record definition shown below was used to create a view that joins the department record (DEPT_TBL) with a PeopleSoft security record (PSOPRDEFN):

SQL View Text

The SQL view text joins the PS_DEPT_TBL.MANAGER_ID with the PSOPRDEFN.EMPLID in security. The email address and user ID (OPRID) are taken from PSOPRDEFN.

SELECT B.DEPTID 
, B.DESCR 
, B.DESCRSHORT 
, ‘U:’ + A.OPRID 
, A.EMAILID 
, A.EMPLID 
FROM PSOPRDEFN A 
, PS_DEPT_TBL B 
WHERE B.EFFDT = ( 
SELECT MAX(B_ED.EFFDT) 
FROM PS_DEPT_TBL B_ED 
WHERE B.DEPTID = B_ED.DEPTID 
AND B_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) 
AND A.EMPLID = B.MANAGER_ID AND B.MANAGER_ID <> ‘’

Note. This simple example uses Microsoft SQL database platform-specific syntax and should not be used without careful evaluation. You may get unpredictable results if the scope definition returns more than one row from the value table for each scope value.

Setting Up Report Requests

This section describes how to:

• Create report requests.

• Configure delivery templates.

• Use portal folders.

• Copy report requests.

• Set advanced options.

Creating Report Requests

To access the nVision Report Request page (NVS_REPORT_RQST):

1. Select Reporting Tools, PS/nVision, Define Report Request from PIA.

   The Report Request search page appears.

2. Search for an existing business unit, or enter a new one.

   The nVision Report Request page appears.

This is the example of the nVision Report Request page:

By default, report requests you create are secure, meaning that they are available only to you and to any users or groups you assign. You can make a report request public and allow access to any user.

The report request security options are configurable using the PeopleTools Options page.

See Creating Report Requests.

See Securing and Sharing Report Requests in PIA.

See PeopleTools Options.

Searching Report Requests

When you search for report requests, the search returns only the reports to which you have access. These include your own report requests, any to which you have been given access by other users, and any that are public.

Configuring Delivery Templates

To configure delivery templates:

1. Select Reporting Tools, PS/nVision, Define Report Request from PIA.

2. Search for an existing business ID, or enter a new one.

3. Click the Scope and Delivery Templates link on the nVision Report Request page.

   The nVision File / Printer Output page appears.

This is an example of the nVision File / Printer Output page (NVS_RQST_FILE_SBP):

See Creating Report Requests.

Configuring Report Request Template Options

Report request template options enable you to use PS/nVision variables or static text to specify report delivery options, including using portal folders. Using the nVision File / Printer Output page, you can also specify scope fields to personalize reports. Template options are displayed dynamically based on your output type on the PS/nVision Web Output page—only the options that you must specify for that output type appear. By combining scope and DES variables in templates, you can have different instances of the same report delivered to different email addresses, or posted to individual users in Report Manager.

To see all available template options:

1. Select Reporting Tools, PS/nVision, Define Report Request from PIA.

2. Search for an existing business unit, or enter a new business unit.

3. Select the Advanced Options tab from the nVision Report Request page.

4. Click the Scope and Delivery Templates - View All link from the nVision Report Request – Advanced Options page.

   The PS/nVision Report Templates page appears.

This is an example of the PS/nVision Report Templates page (NVS_RQST_TEMPL_SBP):

Using Portal Folders

You can use portal folders to organize nVision report results into meaningful groups. When creating a report request with the output option of Web or Window, you can select the top level folder, and then define a subfolder where the report result will be posted. Subfolders can be created dynamically by using nVision variables to generate the folder names, or statically by entering the actual folder name. Subfolders are created under the selected top level folder and can be viewed from Report Manager.

To create a subfolder in PIA:

1. Select Reporting Tools, PS/nVision, Define Report Request.

2. Enter report request information.

3. Select the Web or the Window option as the type in the Output Options section.

4. Click the Scope and Delivery Templates link to display the nVision Web Output page.

5. Select the Top Level Folder option from the Folder Name drop-down list.

6. Enter the name of your subfolder in the Directory Name Template field.

   You can use variables to create unique folder names. If the folder does not exist, PS/nVision creates the folder in Report Manager.

   For example:

   Reports\%SFV%- %RID%\My Folder\Finance Reports

Selecting Portal Folders

You can select portal folders from:

• Distribution Detail page.

  Select report request and run report. Change Output Type to Web or Window, and then click the Distribute To icon.

• Scope and Delivery Templates - nVision Web Output page.

• Process Definition page.

  Select PeopleTools, Process Scheduler, Processes.

  Select Process Type as nVision-Report and enter NVSRUN as the Process Name.

  Select the Destination tab.

Note. Subfolders can only be created from the Enter Delivery Options - nVision Web Output page.

At runtime, Process Scheduler creates the top-level folder based on the following priority order:

• Distribution Detail page.

• Enter delivery Options - nVision Web Output page.

• Process Definition page.

If a folder has not been selected on any of the above pages, Process Scheduler will use the default folder defined by the administrator.

See Also

Working With Processes and Reports

Copying Requests

To access the Copy Report Request page (NVS_RQST_BUCPY_SBP):

1. Select Reporting Tools, PS/nVision, Define Report Request.

2. Enter report request information.

3. Select the Copy to Another Business Unit/Clone link from the nVision Report Request page.

   The Copy Report Request page appears.

This is an example of the Copy Report Request page:

Business Unit	Select a business unit to copy the report request to. To clone the request, select the same business unit as the original request.
Report ID Text Box	Enter a new report ID to copy the report request to another business unit. To clone the request, enter the same report ID.

Security and Copying Report Requests

When you copy a secure report request, all the groups and users associated with that request are copied from the original request to the new copy:

• If you want to copy a report request to another existing report request, the existing request must either be public or you must have access to it.

• If you do not have access, an error message will display.

• If you copy a report request to another report request, only those users and groups with access to the original will have access to the target after the copy.

• Any users or groups who had access to the target prior to the copy but not the original are removed from the access list; if you want them to have access, you must reassign it after the copy is complete.

For example, assume users A and B have access to Report Request 1, and users A and C have access to Report Request 2. If User A copies Report Request 1 to Report Request 2, then following the copy only users A and B will have access to Report Request 2—user C no longer has access.

Setting Advanced Options

To access the Advanced Options page (NVS_REPORT_RQST3):

1. Select Reporting Tools, PS/nVision, Define Report Request.

2. Enter report request information.

3. Select the Advanced Options tab from the nVision Report Request page.

   The Advanced Options page appears.

This is an example of the Advanced Options page, which enables you to change high level specifications for your report request:

See Creating Report Requests.

See Also

Using PS/nVision Reporting for Global Implementations

Creating Report Requests

Setting Query Prompts

The Query Prompts page (PTNVSREPORTRQST4) is available if the Report Layout uses query as data source. This page displays all run-time parameters of queries that are used in the layout.

To access the Query Prompts page:

1. Select Reporting Tools, PS/nVision, Define Report Request in PIA.

   The Report Request search page appears.

2. Select an existing report or add a new one.

3. Select the Query Prompts tab.

   The Query Prompts page appears.

This is an example of the Query Prompts page:

Update Parameters	Click to access a subpage where you can update the query prompts values with the most updated parameters of the query.
Prompt Value	Enter prompt values that are used for mapping the parameter with the query at runtime.

A query report that runs from PIA and requires run-time prompt input may receive the following errors:

• Missing bind value.

  Indicates that the query used in the report layout is modified or a different query was used (there are mismatches between prompt details).

• Empty bind value.

  Indicates that an empty value was provided for a prompt in PIA.

• Invalid bind value.

  Indicates that there is an invalid value for a prompt.

See Also

Submitting Report Requests

Submitting Report Requests

This section describes how to:

• Run reports.

• Set report distribution.

Running Reports

Changes to the nVision report request are not saved when the user clicks the Run button, thus enabling the user to run ad hoc reports as they would from the nVision windows client. If changes have been made to the report request and the user attempts to navigate to a different page, such as using the Process Monitor link, then the user is prompted to either save or discard the changes made to the report request.

To access the Process Scheduler Request (PRCSRQSTDLG) page:

1. Select Reporting Tools, PS/nVision, Define Report Request in PIA.

2. Select a report ID or add a new one.

3. Click the Run Report button.

   The Process Scheduler Request page appears.

This is an example of the Process Scheduler Request page:

Select the Default option in the Type and Format fields to use the options specified in the report request, or override the request by selecting other options.

PS/nVision reports have two different formats for creating report instances: HTML and Microsoft Excel. Both formats are valid with all output types. To use HTML, Microsoft Excel 2000 or later must be installed on your PS/nVision report server.

The Type menu includes these choices:

Setting Report Distribution

The Distribution Detail page enables you to select the recipients of your report instances separate from what you have specified in your report request. If the output type is email, you can enter email addresses, email subject, and message. If you are entering a list of email addresses, make sure to use a semicolon (;) to separate each address from the others.

To access the Distribution Detail page:

1. Select Reporting Tools, PS/nVision, Define Report Request in PIA.

2. Select a report ID or add a new one.

3. Click the Run Report button.

   The Process Scheduler Request page appears.

4. Select a report type, and click the Distribution link.

   The Distribution Detail page appears if the output type is web, window, or email.

This is an example of the Distribution Detail page:

Note. To distribute reports to a role ID or a user ID using email, all recipients must have an email address entered in their Security user profile.

See Also

Administering User Profiles

Using Report Books

This section provides an overview of report books and describes how to:

• Define report books.

• Run report books.

• Set type and output options.

• Run multiple report books.

Understanding Report Books

You can group multiple report requests into a single report book. Report book features include:

• An interface to Process Scheduler Manager.

  Using a browser, you schedule a group of reports to run using a Process Scheduler request. Your reports are executed on the server in the format that you select, and you can run the entire report book with the same output type and format without changing any individual component requests. You can also schedule your report books to run at a particular time on a regular basis.

• An effective status associated with each request in the report book.

  You can set individual report requests within a report book as inactive. Inactive report requests remain part of the report book but are not run as part of a request.

• Security associated with each request in the report book.

  Each individual report request in a report book has its own security setting that allows it to be accessed by specific users and groups or by the public.

• A method for individual report requests to inherit dates from the report book.

  You can run an entire report nook using the same as of date, without changing any individual component requests.

• Multiple nVision sessions can be scheduled on a single report server.

  Each nVision session is associated with its own Excel executable. This enables you to maximize use of your report server without requiring multiple report servers for multiple report books.

• Robust batch processing of message logging.

  You can generate an entire set of reports without the process being interrupted due to an error condition.

Note. Date and time variables are resolved based on the calendar being used by the PS/nVision report server that runs the report.

Defining Report Books

Before creating a report book, make sure you have report requests defined for all the reports you want to include in the book. The report book is a list of requests, not a substitute for the request. You might also want to review the requests you plan to use to ensure they use the desired scope and layout, and ensure that you have access to all the reports you want to include in the report book.

To access the Report Book page:

1. Select Reporting Tools, PS/nVision, Define Report Book.

2. From the Define Report Book page, search for an existing report book or create a new one.

   The Report Book appears.

This is an example of the Report Book page:

As Of Date	Available options are: Default: To use the As Of Date from the report request. Today’s Date: To use the current date for the report. Business Unit Reporting Date: To use the date specified for the business unit. Specify: To enter the date you want to report on.
Tree Effective Date	Available options are: Default: To use the specified Tree Effective Date from the report request. Specify: To enter the date you want to report on. Use As of Reporting Date: To create the report based on the As Of Reporting Date that you specified in the report.
Ignore Runtime Errors	Select to keep the entire Report Book from stopping if one of the reports encounters an error. Note. In such a case, the completion status will be Success, even though one or more reports may have had errors.
View	Click to launch another browser window displaying the report request.
Business Unit	Select the business unit of the report requests for this report book. To reorder your entries, insert or delete rows as necessary.
Report ID	Select the report ID of the report requests for this report book. To reorder your entries, insert or delete rows as necessary.
Run	Click the Run button for all reports you want to run when this book is requested. For example, you might define a book of reports to be run at month end, and include a report that is only run quarterly, but select its Run button indicator only at quarter end.
	Click the Add icon to add a new blank row, and you are enable to enter a new report request. Note. You cannot add a report request to a report book if you do not have access to the report book.
	Click the Delete icon to delete the current row.

Running Report Books

Access the Report Book page (PRCSRQSTDLG) by selecting Reporting Tools, PS/nVision, Define Report Book. In the Report Book page, you can run all the reports in a group or select specific reports by selecting their check boxes in the Run column.

Note. If the report book contains secured report requests to which you do not have access, these report requests appear inactive and you are not able to select them.

Report books are run through Process Scheduler Request page. However, when the report begins to run, you are returned to the Report Book page. If you want to check the status of your job, click the Process Monitor link. If you want to view a list of reports that you have access to, click the Report Manager link.

If you run all report requests in a report book that contains secured report requests, only those requests to which you have access are run. If you run reports that you do not have access to, those processes are not success and an error message appears in the error log.

See Also

Submitting and Scheduling Process Requests

Securing and Sharing Report Requests in PIA

Selecting Type and Output Options

To select type and output options for nVision reports:

1. Select Reporting Tools, PS/nVision, Define Report Book.

2. From the Define Report Book page, search for an existing report book or create a new one.

3. Click the Run Report button.

4. Select the Default option in the Type and Format menus to use the options specified in the report book, or override the report book by making new choices.

See Running Reports.

Running Multiple Report Books

To define your process server to handle multiple report book jobs:

1. Select PeopleTools, Process Scheduler, Server.

2. Search for the server name that you are using for your report books.

   In general, your server name is PSNT. However, a list of available servers appears for your selection.

3. In the process type named nVision–Report Book, ensure that the Max Concurrent field is set to the required number.

   This setting allows multiple report book jobs to run concurrently.

Using the PS/nVision DrillDown on the Web

This section provides an overview of drilldown on Microsoft Excel reports and describes how to:

• Install required software to drill down on Excel reports.

• Register DrillDown layouts.

• Use DrillDown.

Understanding DrillDown on Microsoft Excel Reports

From Microsoft Excel report, you can perform nVision drilldown by either:

• DrillToPIA add-in.

• nVisionDrill VSTO add-in (Visual Studio tools for Microsoft Office SE Runtime).

Note. DrillToPIA and nVisionDrill VSTO add-ins do not coexist. You can use only one add-in at a time.

Understanding Drilldown with PS/nVision Drilldown (DrillToPIA add-in)

DrillDowns are run on the PS/nVision report server – like Report Requests and Report Books – and are accessible through Report Manager. You can also select to run the DrillDown using the output type of Window, which automatically delivers the results to a new browser window. A copy of the results will also be accessible through Report Manager.

You can drill down on individual cells within the report by clicking the amount cells within an HTML report or by selecting the cell and using Drill from the nVisionDrill menu for a Microsoft Excel report.

Note. A drilldown result report inherits the output format of its parent report. So, if the parent instance is in Excel format, then the drilldown result is in Excel format.

DrillDown in a web browser does not include the AutoDrill, Drill-to-Query, and Drill-to-Panel options.

Understanding PS/nVision Drilldown Using Web Services (nVisionDrill VSTO Add-in)

Starting with PeopleTools 8.50, you are able to use the web service capability when drilling from summarized to detailed nVision reports using the nVisionDrill VSTO add-in.

To enable drilldown to use web services, these software items must be installed on the machine where drilldown is performed:

• Microsoft Excel 2007.

• Visual Studio Tools for Microsoft Office SE Runtime (VSTO add-in).

• Microsoft Office 2007 Primary Interop assemblies.

• nVisionDrill add-in.

In addition, you must set up and configure Integration Broker to use the nVision Drilldown feature as a web service. For more information, see the Setting Up Integration Broker for Using Web Service Capability with nVisionDrill Add-in section.

These are the supported drilldown add-ins in Microsoft Excel:

• Microsoft Excel 2007: If VSTO add-in was installed, the nVisionDrill add-in runs and the VSTO drilldown menu is available when Microsoft Excel opens.

  Optionally, you can disable the nVisionDrill add-in and run the DrillToPIA add-in.

  Note. To disable the nVisionDrill add-in and use the DrillToPIA add-in, access the Add-Ins dialog box and select the DrillToPIA check box. This selection replaces the VSTO add-in with DrillToPIA add-in, and the DrillToPIA drilldown menu appears until you reinstall the VSTO add-in.
  
  To reinstall the VSTO, double-click the setup.exe file and select the Repair option.

• Microsoft Excel 2003: Only the DrillToPIA add-in runs and the DrillToPIA drill menu is available when you open Microsoft Excel.

Understanding Security for Drilldown Using nVisionDrill VSTO Add-in

Since users can perform drilldown without having to access PIA pages, they must be signed into a PIA system when they drilldown. Because the parent report is not necessarily generated at the time of drilldown, PS/nVision security mechanisms ensure that only valid PS/nVision users (not just valid PeopleSoft users) can perform drilldown from PS/nVision reports.

After the nVisionDrill add-in is installed on Microsoft Excel environment, it will be available to all Microsoft Excel users. Hence, the functionality of the nVisionDrill add-in can be accessed only by PS/nVision users. When users run the nVisionDrill add-in for the first time, they will be prompted for a user ID and password. If users have a valid user ID and password, they are able to generate subreports using the nVisionDrill add-in.

When users repeat the functionality from the same report instance, they will not be prompted for the credentials again because they would have already been authenticated. However, when users repeat the same functionality from the drilldown reports that were generated, they will be prompted for the credentials again because each drilldown report is opened in a new Excel instance.

Note. All web service calls between the Microsoft Excel and PeopleSoft applications are SSL-enabled.

You must set up and configure Integration Broker to use the nVision Drilldown feature as a web service.

Installing Required Software to Drill Down on Excel Reports

This section discuss how to:

• Install the DrillToPIA add-in on the Microsoft Excel environment.

• Install the nVisionDrill add-in on the Microsoft Excel environment.

• Set up PeopleSoft Integration Broker for using web service capability with PS/nVision drilldown.

Installing the DrillToPIA Add-in on the Microsoft Excel Environment

To drill down on Excel reports, the Microsoft Visual Basic Application (VBA) add-in DrillToPIA.xla file needs to be installed on Microsoft Excel environment. This file is stored in the %PS_HOME%\Excel directory on the Application Server. Your System Administrator needs to distribute a copy of this file to all users who need to drilldown on Excel reports on the Web. The file is not required if you are drilling down from reports that are in HTML format.

Note. If a non-English version of Microsoft Excel is used, translated versions of DrillToPIA.xla can be found in the %PS_HOME%\Excel\%LANG% directory on the Application Server.

In Apple Macintosh systems, nVision DrillToPIA add-in launches Internet Explorer for the drilldown page when drilling is performed on an Excel report, regardless of the browser from which the original report is opened.

To install the add-in DrillToPIA.xla file into the Microsoft Excel environment:

1. Insert the DrillToPIA.xla file into the Excel add-in directory.

   If Microsoft Office is installed in %MS_OFFICE%, the Excel add-ins directory is %MS_OFFICE%\Office\Library.

2. Launch Microsoft Excel and select Tools, Add-ins from Excel toolbar.

3. Select the DrillToPIA option in the Add-ins dialog box.

   The nVisionDrill menu appears in the Excel menu bar.

Note. To remove the add-in from the Excel menu, clear the DrillToPIA option from the Add-Ins dialog box.

Installing the nVisionDrill Add-in on the Microsoft Excel Environment

To install nVisionDrill add-in on the Microsoft Excel environment:

1. Access <PeopleTools Build>\setup\nVisionDrill.

2. Run the nVisionDrillSetup.msi file.

   If all required software items have been installed, the nVisionDrill add-in installation will run to success.

   If any of the items, for example, Visual Studio 2005 SE Runtime vstor.exe or Microsoft Office 2007 PIA o2007pia.msi, are not installed on the machine, the add-in installer displays an appropriate message that asks you to run the corresponding executable.

Note. vstor.exe and o2007pia.msi are available in <PeopleTools Build>\setup\nVisionDrill.

Setting Up Integration Broker for Using Web Service Capability with nVisionDrill Add-in

You must set up report node configuration to enables nVision drilldown to use web services because nVision drilldown uses HTTPS Report Node as prerequisite distribution node.

To set up Integration Broker for using web service capability with PS/nVision Drilldown:

1. Select PeopleTools, Integration Broker, Configuration, Quick Configuration.

2. Ensure the gateway URL points to the PIA web server that is being used.

   Note. The Gateway URL must include the complete URL with domain name of the process scheduler and the https port number.

   This example shows the Integration Broker Quick Configuration page:

3. Ping the gateway to get a success message.

   If this environment is new, you might have to ping the gateway more than one time.

4. Select PeopleTools, Integration Broker, Service Operations Monitor, Administration, Domain Status.

5. Purge the unnecessary domains and enable the required domain.

   You should be able to see three dispatchers under the dispatcher status. This is required for running asynchronous requests through Integration Broker.

   Note. PeopleSoft Integration Broker must process all nVision web service requests that are sent from nVisionDrill VSTO add-in, so the Local Peoplesoft Node of PeopleSoft Integration Broker gateway must include three dispatchers.

6. Set the node configuration.

   The node information is available by pressing CTRL + J on the PeopleSoft Node Configuration page.

   This example shows the PeopleSoft Node Configuration page:

7. Ping the node for successful message.

8. For the Anonymous node, set a valid PS/nVision user ID as the default user ID.

   In this example, QEMGR is a default user ID:

Integration Broker is now set up for using web service capability with PS/nVision Drilldown.

Note. On the server side where the PeopleSoft application resides, the usual logging provided by Integration Broker that can trace the incoming and outgoing flow of requests and responses is provided. PS/nVision also provides its own log files in %PS_SERVDIR%\log_output.

See Getting Started with PeopleSoft Integration Broker Administration.

Registering DrillDown Layouts

All DrillDown layouts are created in the Windows version of PS/nVision, but can be used in both Windows and a browser.

When you register a DrillDown layout, you associate it with a Process Scheduler server. You can have a set of DrillDown layouts specific to financial applications registered to a Process Scheduler server that is dedicated to financial applications and have a set of DrillDown layouts specific to Human Resources registered to its own Process Scheduler server. To make a DrillDown layout available to multiple Process Scheduler servers, register the DrillDown layout on each server. When you drill down, you can select a report server to run the report.

To register a DrillDown Layout:

1. Select Reporting Tools, PS/nVision, Register Drilldown Layout.

2. Enter a new server name, or use the Search button to select one from a list of available servers.

   The Drilldown Layouts page appears.

3. Add a new row to the Drilldown Layouts section.

4. Enter the name of the DrillDown layout and its description.

   The name of the layout must match the name of the DrillDown layout that you created in Microsoft Windows.

5. Click the Save button.

The following example shows a DrillDown Layouts page:

See Also

Setting Report Distribution

Using DrillDown Layout Formats

DrillDown and Summary Ledger

Using DrillDown

After a report has been run as web, window, or file output, you can open it and run DrillDown on specific amount fields.

To drill down on a report that will be run as file output, you must first set the Process Scheduler configuration for the nVision - EnableDrillDownForFile option as 1.The default value is 0, which means drilldown is disabled.

If you drill down on a report that has been run to file output, and you are not logged onto a PeopleSoft domain—or the PeopleSoft web server domain that you are logged onto is not the web server domain of the report server—and your ByPassSignOn Web server configuration property is set to True, then you will not be able to access the drilldown results. The system will return the signon page with an error message indicating that your default password is not authorized.

When you use http/https as the transfer protocol (in report node), there is an additional criterion that needs to be met to enable drill down from a report sent to file output. The security template of the parent report request should be set to specify which Users or Roles will have the authorization to be able to perform a web drilldown on the report. If the Security Template is blank, then only the user that ran the parent report will be able to perform a web drilldown on it. When using file output type, you can access the security template by going into the advanced tab of the report request, and clicking on the Enter Delivery Template Options - View All link.

You can use the web drilldown from an Excel report (using the DrillToPIA add-in) if the report sheet name had spaces in it. However, do not use special characters such as ‘ , ” , < , > , [ , ] , / , \ , * , ?.

A PeopleTools upgrade may cause the web server domain name, port number, or servlet path to be required to access web server resources in the PeopleSoft PIA to change. As a result, PS/nVision drilldown operations on reports that were created prior to upgrade would fail, primarily because drilldown links are by design hard-coded into PS/nVision reports. This utility is located in UpdateNvsDrill.xls file. Use the simple search and replace utility that is provided to replace old links with new ones. This Microsoft Excel macro is located in the PS_HOME\Excel directory.

See Editing the PeopleSoft Process Scheduler Configuration File.

Performing nVision Drilldowns on a HTML or Excel Report Using DrillToPIA Add-in

To perform nVision drilldown on a HTML or Excel report using the DrillToPIA add-in:

1. Open the report in Report Manager.

2. Click the link for the field that you want to drill on for a HTML report, or highlight the field you want to drill on for an Excel report.

3. Click the Drill menu when the cell is selected.

   For HTML reports, a field is drillable if you pass your cursor over the field and the cursor changes to a hand.

   For Excel reports, if the selected cell is not drillable, you receive a warning message that says: DrillDown only works at the intersection of an amount row and column.

   The Run Drilldown page appears in a new browser window, as shown in this example:

4. Select output type Window or Web from the Type drop-down list box.

5. Select the server that you want your report to run on.

   The Server drop-down list box only includes the servers that have the associated layout registered with them.

6. Click the Run Drilldown button for the drilldown layout that you want to use.

   • Web: The Report Manager Report List page appears again.

   • Window: Result of the drilldown is automatically delivered to a new browser window, and a copy is sent to Report Manager.

     Note. When you select to run the drilldown, a new browser window opens and displays the processing page. When processing is complete, the results of the drilldown are delivered to this window. If the window is closed before processing is complete, you need to go to Report Manager to view the results.

7. On the Report Manager – Report List page, click the Refresh link.

   After your drilldown runs, it appears as a report in your report list. Drilldown report descriptions always include DR, the parent process instance number, and the original report description.

8. Click the Report link for the drilldown to display the Report Index page.

9. Click the link for the drilldown to see the drilldown results.

Web-based drilldowns persist. They are files that permanently exist on your report server, not just temporary files as in the Windows client. Drilldown instances in the Web can be viewed and distributed in the same manner as any other report that you run on the browser.

Performing nVision Drilldowns using nVisionDrill VSTO Add-in

To perform nVision drilldown on an Excel report using the nVisionDrill VSTO add-in:

1. Generate the parent report to perform the drilldown.

2. Open the parent report from the nVision Instance folder.

3. Highlight the field that you want to drill on for an Excel report.

4. Expand the Add-ins tab in the Ribbon bar to view the nVisionDrill VSTO menu.

   Note. The nVisionDrill VSTO menu appears under the Add-ins tab only if nVisionDrill add-in was successfully installed.

5. With a drillable cell in the report selected, click the nVisionDrill VSTO menu.

   A login page appears.

6. Enter a valid PeopleSoft user ID and password.

   If the entered ID and password are valid, a list of drilldown layouts appears.

   This example shows the list of drilldown layouts from within the nVisionDrill menu:

   Note. When you click the nVisionDrill menu, the status of the Process Scheduler server appears to inform you whether the Process Scheduler server is running. If the Process Scheduler server is down, a message appears warning: Process Scheduler is down. nVision Drilldown is not possible at this time.

7. Select an appropriate layout option from the layout list.

   The status bar displays the message: nVision drilldown has been submitted.

   The nVisionDrill runs and a Save As dialog box appears.

   Note. If the selected cell is not drillable, you receive a warning message that says: nVision drilldown ran to Error. Check Server log for more details.
   
   After you submit a drilldown request, the drilldown process gets submitted at the server. The status of this drilldown process is polled at specific time intervals. This polling interval value (PollingTime) is configurable in the nvsdrill.cfg file, which is located in the add-in installed directory. The polling interval value is read once during the opening of the Excel file.

8. Click the Save button to save the generated drilldown report.

   Note. You must save the generated drilldown report before it is opened in a new Microsoft Excel window.

   This example shows an nVision drilldown report in Microsoft Excel window:

Note. By default, the drilldown report is opened in a Microsoft Excel window. You can view the drilldown report in real-time while remaining in Microsoft Excel. The report is also posted to the Report Manager.

Error Messages While Performing Drilldown on a Report

When you are performing a PS/nVision drilldown on an Excel reports, the system may display the following error messages:

• DrillDown only works at the intersection of an amount row and column.

• No criteria; cannot drill down from this cell.

• 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.

• DrillDown can only be performed on a Matrix report.

• nVision drilldown failed. Check Server log for more details.

• nVision drilldown ran to No Success. Check Server log for more details.

• nVision drilldown ran to Error. Check Server log for more details.

• Error retrieving drilldown report.

• Drilldown report is not saved.

• Error opening drilldown report.

• Your User ID and/or Password are invalid.

• Error while retrieving drilldown options.

• Error while Authorization. Try logging in again.

• Communication is secured using SSL. Import Certificate and try again.

• nVision drilldown has been submitted.

• Problem occurred during drilldown. Check Server log for more details.

• Drilldown execution in progress.

• Retrieving drilldown report.

• Process Scheduler is down. nVision Drilldown is not possible at this time.