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.