Distributing Reports in PS/nVision

This chapter describes report distribution in PS/nVision.

Understanding Report Distribution

Email and security templates in a report request are the key entries for PS/nVision report distribution. In most of cases you will use the scope variable %DES% for the distribution setting so the reports can be distributed to different recipients according to different values in its scope field. However, you can also override the distribution recipients in Process Scheduler Request page for a particular running of a report request.

In general, there are two ways to specify the content of email and security templates:

You can specify the same set of recipients for all report instances from a report request. In this way, you specify a set of user ids (U:<user id>) or role ids (R:<role id>) in the email or security template, or a set of email addresses for the email template.

If you use user ids or role ids in the email template, you need to make sure that the corresponding users have valid email addresses specified in their User Profile settings.
You can use scope variable %DES% in the email and security templates to select a related field value from a value table or a tree node table that the scope field is based on.

The field values in the scope table need to be populated in the same way, as you would specify them in the first option. The general syntax of the %DES% variable is the following:
```
%DES%.[scope field].[detail field].[node field]
```
PS/nVision evaluates the %DES% variable to the value of the detail field in the value table or the node field in the tree node table for the scope field. In all cases, the detail fields or the node fields are the email and web distribution fields and must be presented in the scope tables.

Distributing Reports for Scopes Based on Tree Nodes

If your scope is defined based on PeopleSoft tree nodes, you can attach routing information to the tree that can be used for bursting PS/nVision reports.

The following section provides an example how to distribute PS/nVision reports for scopes based on tree nodes. The basic steps include:

Clone tree node table - create record DEPT_NODE_TBL.
Clone tree node page − create page DEPT_NODE_TBL.
Grant access to tree node pages.
Update tree structures to use the new node table and node page.
Populate routing information.
Link routing information into PS/nVision.

In this example, you have a scope DIVISIONS with scope field DEPTID based on the node table in DEPARTMENTS tree. The tree structure for DEPARTMENTS tree is DEPTID. In tree structure DEPTID, the node table is TREE_NODE_TBL, the node page is DEPARTMENT and the detail field is DEPTID.

Cloning Tree Node Table - Creating Record DEPT_NODE_TBL

Since your scope is based on tree nodes, the key to get appropriate distribution information into the tree is to modify the tree node table to include additional distribution fields. You need to add the additional fields, DISTLIST and EMAIL_TO, to the node table to be the distribution fields for web and email distribution separately. You can run the following SQL scripts to move the nodes from record TREE_NODE_TBL to record DEPT_NODE_TBL, while adding the two new fields to record DEPT_NODE_TBL.

To move all nodes where the tree has detail values based on a given field (such as DEPTID), use the following script. You will need to change the field name in quotes to the field you want to key from.

INSERT INTO PS_DEPT_NODE_TBL
SELECT DISTINCT A.SETID, A.TREE_NODE, A.EFFDT, A.EFF_STATUS,
 A.DESCR, ' ', ' '
  FROM PS_TREE_NODE_TBL A, PSTREENODE B
  WHERE A.TREE_NODE = B.TREE_NODE
     AND B.TREE_NAME IN (SELECT C.TREE_NAME
  FROM PSTREEDEFN C, PSTREESTRCT D
  WHERE D.TREE_STRCT_ID = C.TREE_STRCT_ID
    AND ( C.EFFDT = B.EFFDT
     AND C.SETID = B.SETID
     AND D.DTL_FIELDNAME = 'DEPTID' ))

To move all nodes for a given tree name, use the following script. You will need to change the tree name in quotes to the tree name you want to key from.

INSERT INTO PS_DEPT_NODE_TBL
SELECT DISTINCT A.SETID, A.TREE_NODE, A.EFFDT, A.EFF_STATUS,
 A.DESCR, ' ', ' '
  FROM PS_TREE_NODE_TBL A, PSTREENODE B
  WHERE A.TREE_NODE = B.TREE_NODE
     AND B.TREE_NAME = 'DEPARTMENTS'

To move all nodes where the tree is based on a specific tree structure ID, use the following script. You will need to change the structure name in quotes to the structure ID you want to key from.

INSERT INTO PS_DEPT_NODE_TBL
SELECT DISTINCT A.SETID, A.TREE_NODE, A.EFFDT, A.EFF_STATUS,
 A.DESCR, ' ', ' '
  FROM PS_TREE_NODE_TBL A, PSTREENODE B
  WHERE A.TREE_NODE = B.TREE_NODE
     AND B.TREE_NAME IN (SELECT C.TREE_NAME
  FROM PSTREEDEFN C
  WHERE C.TREE_STRCT_ID = 'DEPTID'
    AND ( C.EFFDT = B.EFFDT
     AND C.SETID = B.SETID ))

Cloning Tree Node Page - Creating Page DEPT_NODE_TBL

In Application Designer, clone DEPARTMENT page to a new DEPT_NODE_TBL page.

Add new page fields (Routing Information section below) to be the data population area for record field DISTLIST and EMAIL_TO in the new node table DEPT_NODE_TBL.

Note. Page DEPT_NODE_TBL has associated PeopleCode to construct a single DISTLIST and EMAIL_TO for each node in the node table with the multiple recipients (Email ID or Distribution ID) that users enter in the Routing Information of the page.

Also in Application Designer, create a new component DEPT_NODE to include page DEPT_NODE_TBL. Then make this new component part of Tree Manager page system. See following example.

Granting Access to Tree Node Pages

Make the new node pages available to users when they maintain the tree, and add distribution information to the nodes.

To grant security permissions for the tree:

Select PeopleTools, Security, Permissions & Roles, Permission Lists.
Open a permission list.

For example, to grant user VP1 to maintain the tree when VP1 belongs to ALLPNLS permission list, open the permission list for ALLPNLS.
Select the Pages tab.
Find the TREE_MANAGER item in the list and click the Edit Components link.

The Component Permissions page appears and the Dept Node component should be in the list and not authorized.
Click Edit Pages in the row where the Dept Node component is listed.
Click Select All to grant access to the page, and then click OK.
Click OK on the Component Permissions page.
Click Save on the Permission List page.

Updating Tree Structures to Use the New Node Table and Node Page

Modify the tree structure to use the new node table and node page. Below is a sample version of tree structure TREE_NODE_DISTRIB tree structure that has been modified to use the new node table DEPT_NODE_TBL and node page DEPT_NODE_TBL.

To set up Tree Manager to use the new page:

Select Tree Manager, Tree Structure.
Select a structure.
Select the Nodes tab to change where the node information is stored.
Modify the structure to utilize the new DEPT_NODE_TBL record and new DEPT_NODE_TBL page.

Populating Routing Information

Enter either email addresses or distribution IDs or a combination of both in the new node page DEPT_NODE_TBL. When populating distribution IDs, you are populating the users or roles that Report Manager uses.

To add distribution route information to a tree:

Select Tree Manager, Tree Manager, and choose the required tree.
Highlight the tree nodes and click the Edit Data icon to add the distribution routing information.

The Dept Node Tbl page appears.
Add the distribution routing information.
Click OK to save.

Linking Routing Information Into PS/nVision

When Security and Tree Manager has the correct information, the final step is to connect it to PS/nVision. This section describes to the steps needed to complete this task:

Check scope definition.
Specify scope in report request.
Specify email and security delivery template options.

Checking Scope Definition

The following is an example of the scope definition DIVISIONS. It is on the field DEPTID against all nodes at the level DIVISION using the DEPARTMENTS tree. Verify that DEPARTMENTS tree uses the modified tree structure from above.

Specify Scope in Report Request

Specify the scope for which the report is being run. The scope drives the distribution process. So in this example, make sure scope DIVISIONS is used in your report request.

Select Reporting Tools, PS/nVision, Define Report Request. Click on the Scope and Delivery Templates link.

Specifying Email and Security Template Options

The Email and Security template fields, in the report request, tells PS/nVision who to distribute the report to, and by what route, for example, email or via the Web. To route each scope based report instance to the person associated with each scope value, that drives the generation of the report, use the %DES% variable to pick a field value from the tree node table.

Select Reporting Tools, PS/nVision, Define Report Request. Click the Scope and Delivery Templates.

If you want to distribute the report by email, enter the following value in the Email Template field:

%DES…EMAIL_TO%

When you run the report, PS/nVision bursts report instances based on the nodes in DIVISION level of the DEPARTMENT tree and delivers the results through email to the email addresses associated with each node. Those email addresses are the ones specified in the Email ID in NODE_DEPT_TBL page.

If you want to distribute the report by web, enter the following value in the Security Template field

%DES…DISTLIST%

When you run the report, PS/nVision bursts report instances based on the nodes in DIVISION level of the DEPARTMENT tree and delivers the results to the Report Managers of each node’s associated users. Those users are the ones specified in the Distribution ID in NODE_DEPT_TBL page.

Distributing Reports for Scopes Based on Value Tables

If your scope is defined on a value table such as Department table or Product table, you can create a database view based on the value table to include additional email and web distribution fields. For each scope field value in the value table you can direct each scope based-report instance to a different user. For example, NVS_DEPT_VW below is a SQL View with all the DEPT_TBL characteristics plus department managers’ OPRIDs as distribution fields.

SELECT B.DEPTID 
, B.DESCR 
, B.DESCRSHORT 
, 'U:' + A.OPRID AS 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 is an example of SQL Viewer for Microsoft SQL database platform. You need to alter the syntax if you want to use it on other database platforms.

You can now create a scope with scope field DEPTID based on the value table and use this scope in your report request. To have each department report instance to send, either by email or web distribution, to each associated department manager, you would enter the following value in your Email or Security template:

%DES.DEPTID.OPRID.OPRID% or %DES…OPRID%

This tells PS/nVision to use the value in OPRID field to distribute each department report to its department manager. The report will be distributed as either email or web based on the output type specified in your report request.