After you create a data view, you must publish it to be able to use it in user-defined reports, data cubes, or custom reports. There are two things to know about using data views in user-defined reports:
- Data views must contain project_id in the SQL query for the data view to show up in a project or shell-level UDR data type list
- Data views published prior to the Primavera Unifier 9.5 release, must be republished (i.e., set to draft status and then reset to published status) before they show up in the UDR data type list
Depending on the data elements you choose, you might need to publish the metadata associated with the data view. For example, if the selected data element has a data definition type of pull-down Menu, you will have to publish the metadata to see the actual label of the pull-down rather than the value. In the case where a data element is defined as Actual Value? based on a Yes/No data element, with 0= No and 1=Yes, if Actual Value? is used in the data view, then you need to publish the metadata of Actual Value? to get the Yes or No value rather than 0 or 1.
To create Data Views
- Go to the Company Workspace tab and switch to Admin mode.
- Click Data Structure Setup > Data Views in the left Navigator. The User-Defined Data Views log opens.
- Click New. The User-defined View window opens.
- The Prefix is auto-populated with the company short name. Enter the name of the Data View, the label, an optional description, and the SQL query for the Data View. For further details on how to create SQL queries used in the creation and management of Data Views, please consult the Oracle Consulting Services team.
- Click OK.
Note: When creating data views, the column naming convention must follow the Oracle DB guidelines. Do not use the Oracle-reserved words such as "(", ")" and so forth to name the column. For details, refer to Oracle DB guidelines.
To publish Data Views
- Go to Company Workspace tab and switch to Admin mode.
- In the left Navigator, click Data Structure Setup > Data Views. The User-Defined Data Views log opens.
- Select an unpublished Data View.
- Click the Status button.
- Select Published.
- Click OK.
To publish metadata
You can associate metadata with its Unifier data set for both labels and values. Data views tend to contain technical shorthand from system and SQL abbreviations. While clear to the technical person who wrote the data view, report consumers will appreciate seeing the same field labels and field value selections they see in their business processes and managers.
For example, in your data view you might include the data element "year_pd," which represents a pull down menu with selections for different years assigned to its data set. In your user-defined report, you might want to see the label "Year" instead of "year_pd;" and you might want to query on meaningful values, such as 1999, 2001, 2002, etc., instead of the numeric values of the data definition, 1,2,3, etc.
- In the User-Defined Data Views log, select a published Data View.
- Click the Metadata button.
- Double-click in the Definition field for each data element and select the data definition for the selected element. The data definitions available for selection are filtered by the data elements used.
- Double-click the Label field for each element you want to use in the UDR. Rename it to whatever you want to see in the report.
- Click Publish Metadata.
Tips
The SQL definition will consist of the SQL query used to populate the report with specific data based on the desired output.
Note: If using the data view as a Main view in a Project level report, you must have a column named project_id. If using the data view in a Program level report, you must have a column named program_id.
For example, a Project level data view for a company registry "companyReg" can be like this below:
SELECT pu.PID as PROJECT_ID,
p.PROJECTNUMBER as PROJECTNUMBER,
c.COMPANYNAME as COMPANY_NAME,
c.SHORTNAME as COMPANY_SHORT_NAME,
c.DESCRIPTION as COMPANY_DESCRIPTION,
c.CONTACTNAME as COMPANY_CONTACT_NAME,
u.FULLNAME as USER_FULL_NAME,
u.USERID as USER_ID,
u.USERNAME as USER_NAME,
u.EMAIL as USER_EMAIL
FROM companyReg_SYS_USER_INFO_VIEW u,
companyReg_SYS_COMPANY_INFO c,
companyReg_SYS_PROJECT_INFO p,
companyReg_SYS_PROJECTUSER pu
WHERE c.COMPANYID = p.COMPANYID
AND p.PID = pu.PID
AND u.userid = pu.userid