Distributing Reports in PS/nVision

This chapter provides an overview of report distribution in PS/nVision and discusses how to:

• Distribute reports for scopes based on tree nodes.

• Distribute reports for scopes based on value tables.

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 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, you specify the content of email and security templates in either of these ways:

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

2. 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 they 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:

1. Clone tree node table—create record DEPT_NODE_TBL.

2. Clone tree node page—create page DEPT_NODE_TBL.

3. Grant access to tree node pages.

4. Update tree structures to use the new node table and node page.

5. Populate routing information.

6. 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 entering 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_TB.

To move all nodes where the tree has detail values based on a given field (such as DEPTID), use the following script:

Note. You 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:

Note. You 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:

Note. You 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 the DEPARTMENT page to get a new DEPT_NODE_TBL (Department Node Table) page.

In this table, add new page fields (as in the Routing Information section of the example below) to be the data population area for record fields DISTLIST and EMAIL_TO.

Note. The page DEPT_NODE_TBL has associated PeopleCode to construct a single DISTLIST and EMAIL_TO field 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 and include the DEPT_NODE_TBL page in it. And then, make this new component DEPT_NODE a part of the Tree Manager page system.

This example illustrates the result:

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.

This is an example of the Page Permissions page (ACL_PAGES2) (PeopleTools, Security, Permissions & Roles, Permission Lists, Pages, Edit Components, Edit Pages) , which enables you to grand security permissions and access other pages to set distribution information:

To grant security permissions for the tree in PIA:

1. Select PeopleTools, Security, Permissions & Roles, Permission Lists.

2. Open a permission list.

   For example, to grant user VP1 to maintain the tree when VP1 belongs to the ALLPNLS permission list, open the permission list for ALLPNLS.

3. Select the Pages tab.

4. Find the TREE_MANAGER item in the list and click the Edit Components link.

   The Component Permissions page appears, and the Dept Node (Department Node) component should be in the list and not authorized.

5. Click the Edit Pages link in the row where the Dept Node component is listed.

6. Click the Select All button to grant access to the page, and then click the OK button.

7. Click the OK button on the Component Permissions page.

8. Click the Save button on the Permission List page.

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

Use the Tree Structure – Nodes page to modify the tree structure to use the new node table and node page.

This example shows a version of tree structure TREE_NODE_DISTRIB that was modified to use the new node table, DEPT_NODE_TBL, and node page, DEPT_NODE_TBL:

To set up Tree Manager in PIA to use the new page:

1. Select Tree Manager, Tree Structure.

2. Select a structure.

3. Select the Nodes tab to change where the node information is stored.

4. Modify the structure to use the new DEPT_NODE_TBL record and new DEPT_NODE_TBL page.

Populating Routing Information

Use the Dept Node Tbl (Department Node Table) page to enter either email addresses, 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:

1. Select Tree Manager, Tree Manager, and select the required tree.

2. Highlight the tree nodes and click the Edit Data icon to add the distribution routing information.

   The Dept Node Tbl page appears.

3. Add the distribution routing information.

4. Click the OK button to save.

Linking Routing Information Into PS/nVision

When Security and Tree Manager have the correct information, the final step is to connect it to PS/nVision.

This section describes the steps to complete this task:

• Check scope definition.

• Specify scope in report request.

• Specify email and security delivery template options.

Checking Scope Definition

This example shows the scope definition DIVISIONS, which 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 previously:

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.

To specify scope in nVision report request:

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

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

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

4. Click the Scope Definition link in the output page.

5. Enter the set ID and report scope, as needed.

   The Scope Definition page appears.

6. Specify the scope for which the report is being run.

Specifying Email and Security Template Options

The Email and Security template fields in the report request tell 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.

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

2. Click the Scope and Delivery Templates link.

3. To distribute the report by email, enter %DES…EMAIL_TO% in the Email Template field.

   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.

   Alternatively, to distribute the report via the web, enter %DES…DISTLIST% in the Security Template field.

   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 associated users of each node. Those users are specified in the Distribution ID in NODE_DEPT_TBL page.

See Also

Creating Scope Definitions

Distributing Reports for Scopes Based on Value Tables

If your scope is defined in 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.

In the following example, NVS_DEPT_VW below is a SQL View with all the DEPT_TBL characteristics and OPRIDs of department managers 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 example shows the SQL Viewer for Microsoft SQL database platform. You need to alter the syntax if you want to use it for 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 sent, 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 value allows PS/nVision to use the value in the OPRID field to distribute each department report to its department manager. The report is distributed by either email or web, based on the output type specified in your report request.