USING A DATA VIEW IN A USER DEFINED REPORT (UDR)
The following steps outline how to include a Data View for use in a UDR. There may be instances where using the default Data Elements tab does not expose enough of the data for a specific UDR. In a case like this a Data View can be used to extract the data.
This example is counting the number of projects included in a portfolio scenario sheet.
The Data View needs to be created. In the Company Workspace > Data Structure Setup> Data Views node, click ‘New’.
The Data View will also require SQL. This is the code that allows querying of the Unifier database to extract the required data. (Please refer to a qualified consulting service if unfamiliar with how to write SQL code).
The SQL fields (Data Elements) are referenced from the Enterprise Views located in Company Workspace > Data Structure Setup > ER Views. This example uses Portfolio Manager Module data. Select Portfolio Manager from the ER log and click ‘OK’.
The pop up window lists all the tables and views needed to extract data (Data Element information) from the Portfolio Manager. From this information the required SQL code can be written.
Once the appropriate tables, views and fields are identified, the SQL code can be written.
Enter appropriate Name and Label for the Data View. Enter the SQL that will be used to extract the required data and click ‘OK’.
With the Data View selected, click Status > Published.
Note – If there are any errors in the SQL the Data View will not get published.
If Publication is successful, the data can be checked by clicking ‘Data’ after selecting the Data View.
The data returned from the SQL can be seen from the Data window. In this example four projects are included in Scenario 1 for the portfolio LA Capital Planning.
The published Data View is now be available for use in UDRs. Since this example is using the Portfolio Manager the Data View will be available for company-level UDRs.
The next step is creating the UDR. In Company Workspace > Reports > User Defined, click ‘New’.
The Data View can now be found within the list of Data Types.
Select the Report Type to Summary and click ‘OK’.
In the General tab enter the Report Name (Report Title will default to Report Name as well).
In the Data Elements tab click ‘Select’ for the Data Element in the 1st column section.
Since this example will count the number of projects for the scenario sheet, select ‘scenario_name’ and click ‘OK’.
Note – these are the fields that were available from the Data View.
In the 2nd Column section select ‘Summary Value’ and click ‘OK’
Back in the UDR log, click ‘Open’.
As an alternative to HTML or PDF format, select Report Format CSV and click ‘Run’.
Click ‘Open’ when prompted.
Excel will open and display the correct count for the scenario sheet.