204460 create s curve reports

Create S-Curve Reportsfor

P6R8.1 or P6R8.2 using

BI Publisher 11g

Presented by:

Paul G. Ciszewski, PMPDynamic Consulting

[email protected]

(920) 883-9048

mailto:[email protected]



Overview

• Oracle Business Intelligence Publisher (BIP)

is a tool to create pixel-perfect reports

• To modify existing P6 Web Reports and to

create new P6 Web Reports, you must use

BIP

• When building your reports, you should use

P6‟s new Px Extended Scheme (database)

• Px Extended Scheme is de-normalized and

has P6 calculated values

Overview (cont.)

• Tables in the Px Extended Scheme begin with “P6”• P6Project – list of all projects and related data by project (for ex: the “baseline planned labor units” for

project “TA Fall 13”)

• P6ProjectSpread - list of all projects and related data by project and date (for ex: the “planned labor

units” on November 15, 2013 for project “TA Fall 13”)

• P6Activity – list of all activities and related activity data for all projects (for ex: the “earned value labor

units” for activity “A1000” for project TA Fall 13)

• P6ActivitySpread – list of all activities and related activity data by date for all projects (for ex: the

“actual labor units” for activity “A1000” on November 15, 2013 for project TA Fall 13)

• P6ActivityCodeAssignment – list of all the activity codes and their values assigned to ALL activities.

If an activity has 5 activity codes assigned to it, then there would be 5 records in this table for the one

(1) activity

• P6ActivityCode – list of all activity codes and values

Note: There are no physical tables called P6Project (or P6Activity), the P6 tables are actually synonyms/aliases that point to

“Views”. “Views” are results from executed SQL statements. In the Px Extended Scheme, the “Views” are joins of native P6

tables (such as project, task) and new extended tables (such as projectx and taskx) – and the P6 security tables are

included.

For our presentation “SYNONYM” is the same as “TABLE”

BI Publisher - Introduction

• Two (2) main modules to create a BIP report

1. Data Model Editor – To extract data from

P6‟s Extended Px Scheme (database)

2. Create Report Layout – the presentation

of the data. But there are 2 tools

Report Layout Editor (tool is within BIP)

Word Template Builder (a MS WORD add-on)

BI Publisher – Presentation Objective

Presentation Objective

Go through all the steps to:

1. Develop the Data Model (SQL)

to extract the data from P6

2. Develop the S-Curve Template

in the WORD Template Builder

that uses the Data Model

3. Upload the Template to the

Report in BI Publisher

4. Move the Report and Data

Model to the P6Reports folder

5. Run S-Curve Report in P6 and

produce the same output as you

see on this slide

BI Publisher - Login

• User Name should exist in P6

• The user‟s security is applied

BI Publisher - Home

BI Publisher – Create Data Model (DM)

• Three (3) components needed to create the

DM for our examples

1. Create 1 List of Values (LOV)

2. Create 1 parameter

3. Create Data Set

BI Publisher – Create New DM

BI Publisher – Create New DM – P6 Tables

• Four (4) P6 Tables needed to create examples

1. P6Project (list of projects and all related data)

2. P6ActivitySpread (list of activities and activity data by date)

For version 1, I could have used P6ProjectSpread but P6ActivitySpread

works for both version 1 and 2

3. P6ActivityCodeAssignments (needed for version2)

4. P6ActivityCode (needed for version2)

BI Publisher – Create DM – Parameter

Desired “look and feel” in P6

BI Publisher – Create DM - Parameter

• First create list of values (LOV) for Projects

• Then create parameter using the LOV

• Parameter Label would be “Select Projects”

BI Publisher – Create DM – Create LOV‟s

SQL to retrieve the list of Project

ID‟s

BI Publisher – Create DM – Create Parameter

“p_project_id” is special parameter

recognized by P6

When “Parameter Type” is Menu,

user can select “LOV” value

BI Publisher – Create DM – Data Set

• Next, create a Data Set

• Data Set contains the SQL query/statements to

extract the P6 data (such as Baseline Planned

Labor Units) from the P6 tables

• In our example, our SQL query will sum the labor

units for all activities by date AND

• Our SQL statements will create running totals (see

next slide)

• The query result will be a record for each date of our

project(s) when work is planned or work is done

BI Publisher – Create DM – Data Set

DateACTSP_STARTDATE

Display DateDISPLAYDATE

Baseline Labor UnitsSUM_BLPLANNEDLABORUNITS

Running TotalsCUM_BLPLANNEDLABORUNITS

Feb 4, 2013 02/04 50 50

Feb 5, 2013 02/05 25 75

Feb 6, 2013 02/06 10 85

Feb 7, 2013 02/07 20 105

Feb 8, 201302/08 50 155

And so

BI Publisher – Create DM – Create Data Set

Then select “SQL Query”


You could use “Query Builder” for

simple SQL queries/statements.

“Query Builder” builds the SQL

query for us.

But for advanced/complex SQL

queries/statements, I just type it in

myself


I typed in the SQL

query/statement

BI Publisher – Create DM - Data Set / SQL-- Version 1 - just BL planned labor units

SELECT

TRUNC(ACTSP.STARTDATE) AS ACTSP_STARTDATE,

TO_CHAR(TRUNC(ACTSP.STARTDATE),'mm/dd') AS DISPLAYDATE,

-- BL planned labor units summed by date

SUM(ACTSP.BASELINEPLANNEDLABORUNITS)

AS SUM_BLPLANNEDLABORUNITS,

-- BL planned labor units - running totals by date

SUM(SUM(ACTSP.BASELINEPLANNEDLABORUNITS)) OVER

(ORDER BY TRUNC(ACTSP.STARTDATE)

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

AS CUM_BLPLANNEDLABORUNITS

-- Two tables needed for version 1

FROM P6PROJECT PRJ,

P6ACTIVITYSPREAD ACTSP

WHERE PRJ.ID IN (:p_project_id) AND

PRJ.OBJECTID = ACTSP.PROJECTOBJECTID

GROUP BY TRUNC(ACTSP.STARTDATE)

ORDER BY ACTSP_STARTDATE

Breakdown SQL Query

SQL query to create four (4) columns:

- ACTSP_STARTDATE

- DISPLAYDATE

- SUM_BLPLANNEDLABORUNITS

- CUM_BLPLANNEDLABORUNITS

Summed and grouped by

TRUNC(ACTSP.STARTDATE)


SELECT












FROM P6PROJECT PRJ,






Tables Accessed/FROM Clause

P6Project with an alias of ”PRJ”

P6ActivitySpread with an alias of “ACTSP”


SELECT












FROM P6PROJECT PRJ,






Conditions/Where Statement

p_project_id is the Parameter

Before the user runs the report, they will

select 1 or more projects.

This statement will extract all project

records from P6Project with matching

Project ID‟s.

PRJ.ID are the project ID‟s you see in P6

such as “Fall TA 2013” or “EC500123”.

Tables are usually linked together (joined

together) using the unique key for the

record. But first we must look up the

correct record by Project ID. In the Px

Extended Scheme, the unique key is

called “ObjectID”.

ID ObjectID (other columns…)

Fall TA 2013 4501

EC500123 1287

Test Prj 123 9333

P6Project PRJ Table


SELECT












FROM P6PROJECT PRJ,






Conditions/Where Statement

This statement will extract all activity

records from P6ActivitySpread with

matching project ObjectID.

However in the P6ActivitySpread table,

the project‟s unique key column is called

“ProjectObjectID”.

So first we move to the correct project record with “PRJ.ID IN (:p_project_id)“

then we get the ObjectID and use it to

extract activity records from the

P6ActivitySpread table using

ProjectObjectID


SELECT












FROM P6PROJECT PRJ,






Group Clause

The GROUP clause groups ALL activity

records together by STARTDATE.

The SUM() function, will sum all “Baseline

Planned Labor Units” from all Activities

that have the same STARTDATE.

Remember: The P6ActivitySpread table

has a record for each day of the Activity. If

the Activity has 24 budgeted hours over 3

days (Feb 1, Feb 2, and Feb 3), then there

will be 3 records in the P6ActivitySpread

table for that activity. Ex:

Activity Start BL Planned

ID Date Labor Units

A1000 2/1/13 8

A1000 2/2/13 8

A1000 2/3/13 8


SELECT












FROM P6PROJECT PRJ,






ACTSP_STARTDATE

This is the full date (without time)

used for sorting/ordering.

This value is not placed on the

chart/S-Curve.

In the P6ActivitySpread table,

ACTSP.STARTDATE is the start date

for the hours on this date


SELECT












FROM P6PROJECT PRJ,






DISPLAYDATE

This value will be shown across the

horizontal axis of the chart/S-Curve.

The date will be in “mm/dd” format

without the year to save space on the

report.


SELECT












FROM P6PROJECT PRJ,






SUM_BLPLANNEDLABORUNITS

This is the “Baseline Planned Labor

Units” from the Activity Spread

extended table summed by StartDate

(because of the “GROUP BY”

clause).

SUM() is a function.


SELECT












FROM P6PROJECT PRJ,






CUM_BLPLANNEDLABORUNITS

This statement will add up all “Baseline

Planned Labor Units” beginning at the first

row (UNBOUNDED PRECEDING) to the

current row (CURRENT ROW).

Using the STARTDATE for order (ORDER

BY TRUNC(STARTDATE))

The result is stored in

“CUM_BLPLANNEDLABORUNITS”

Notice: SUM(SUM())


SELECT












FROM P6PROJECT PRJ,






ORDER BY

Last, we will order all our records by

ACTSP_STARTDATE

BI Publisher – Create DM – Create XML Data

• After creating the LOV‟s, Parameters, and the

Data Set – you must generate XML data.

• To create the presentation part of the report,

you must have XML data

• You must save the XML data to the DM if you

are building the report in the Report Layout

Editor

• You must save the XML data to a file and

import it into your WORD document if you are

using the Word Template Builder


Data Set

The Data Set shows the four

columns being returned from the

SQL query/statement

Generate XML Data

Click this button to show the screen

when we can generate the XML.

BI Publisher – Create DM – Create XML Data1. Generate XML Data

Select 1 or more projects, then click

“Run”

2. XML Data

The XML data has

the tag names and

values.

3. Save XML

Either “Export

XML” to a file or

“Save As

Sample Data” to

the DM.


• Save the DM

• Next, build presentation part of report by first

creating a report called “SCurve-V1” and then

• Use Word Template Builder for layout

BI Publisher – Create Report

BI Publisher – Create Report – Select DM

BI Publisher – Create Report – Save Report in BIP

Report Layout Editor

If we were going to create the presentation

part in the Report Layout Editor tool, then I

would have selected a Template. However,

we will be developing the presentation part in

the “Word Template Builder” therefore we just

want to save the report.

BI Publisher – Create Report – Save Report

Enter Report Name

BIP – Create Report – Word Template Builder

• Switch to MS WORD

• Select BI Publisher Add-in tab

• Log into BIP

• Load XML file

• Create report‟s layout in WORD

• Add Report Title

• Add Chart (S-Curve)

• Save WORD file as RTF

• Upload Template to BIP Report


Log On

Enter Username

Enter Password

Enter IP of BIP server

BIP –Template Builder – Select Report

Select Report

Layout Templates

If there were templates in

BIP for this report, the

templates would be listed

here.

BIP –Template Builder – Load XML Data

Select XML File

This file was created

by the DM

BIP –Template Builder – Create Layout

2. Add Chart

Move cursor to

position in WORD

doc and double click

Chart object

1. Add Text

Using WORD‟s standard

features – such as

forecolor, center, font

BIP –Template Builder – Chart Properties


Chart/Line Properties

Change other properties

such as 3-D effect, line

color, line name

Chart/Line Properties

1.Drag/Drop DISPLAYDATE to

Labels

2. Drag/Drop

CUM_BLPLANNEDLABORUNITS

to Values

3. Change Type to Line Graph

4. Uncheck Group Data

BIP –Template Builder – Report


• To upload Template into BIP

• Save File in WORD as RTF

• Select “Upload Template As” on BI Publisher

Add-in tab

• Save Template to the BIP Report

• Login into BIP

• Open/Edit Report

• Select Template Layout (that we just uploaded from Tmp Builder)

• View Report

BIP –Template Builder – Create Layout

BIP – Open/Edit Report – View Report

BIP – Run Report from P6 to PDF Format

BIP – DM – Expand DM

• DM is currently plotting BL Planned Labor

Units

• Add Actual Labor Units and Earned Value

Labor Units

• Create new XML data file with new data

elements

• Import new XML into WORD Template Builder

• Modify Template to include new data elements

• Upload to BIP – run report

BIP – DM – Add More Data Elements

Original Section of SQL

Copy/Paste this section

New Section of SQL

Change

BASELINEPLANNEDLABORUNITS to

ACTUALLABORUNITS in 4 places

See Next Slide

BIP – DM – Add More Data Elements (SQL 1 of 2)

Original Section of

SQL

Copy/Paste this section

New Section of SQL

Change

BASELINEPLANNEDLABORUNITS

to ACTUALLABORUNITS in 4 places

-- Version 1 - BL planned Labor Units,

-- Actual Labor Units, and EV Labor Units

SELECT TRUNC(ACTSP.STARTDATE) AS ACTSP_STARTDATE,



SUM(ACTSP.BASELINEPLANNEDLABORUNITS) AS SUM_BLPLANNEDLABORUNITS,





AS CUM_BLPLANNEDLABORUNITS,

-- Actual labor units summed by date

SUM(ACTSP.ACTUALLABORUNITS) AS SUM_ACTUALLABORUNITS,

-- Actual labor units - running totals by date

SUM(SUM(ACTSP.ACTUALLABORUNITS)) OVER



AS CUM_ACTUALLABORUNITS,

BIP – DM – Add More Data Elements (SQL 2 of 2)

Another New Section of SQL

After copying the section in RED on

previous slide, change

BASELINEPLANNEDLABORUNITS to

EARNEDVALUELABORUNITS in 4

places.

-- (Continuation from previous slide)

-- EV labor units summed by date

SUM(ACTSP.EARNEDVALUELABORUNITS) AS SUM_EARNEDVALUELABORUNITS,

-- EV labor units - running totals by date

SUM(SUM(ACTSP.EARNEDVALUELABORUNITS)) OVER



AS CUM_EARNEDVALUELABORUNITS


FROM P6PROJECT PRJ,





BIP – DM – Add More Data Elements

New Data Elements in the Data Set

Generate XML

again with New

Data Elements

then Save It

BIP –Template Builder – Modify Template Add New Chart

Include all 3 running total

data elements.

Change Some

Properties

Color, labels, remove

3-D effect

BIP –Template Builder – Modify Template (show Project ID‟s)

Add Data Element

1. Click on Field

2. The Field Dialog box is

displayed

3. Double Click on data

element (such as

p_project_id)

4. The data element will

be added to the report

NOTE: p_project_id was

our parameter in our DM


• We must upload Template into BIP again

• Save File in WORD as RTF

• “Upload Template As” to save report

• Login into BIP


• Select Template Layout (that we just uploaded from Tmp Builder)

• View Report

• Move Report and DM to P6Reports folder

and reattach DM to Report

BIP – Run Report from P6

BIP – Expand DM/Report to include Activity Codes

• In many operations, the managers want to see

progress only for activities with a specific

Activity Code/Value combination

• For ex:

• By Phase: FOUND, DESGN, STRUC

• By Department: CON, PCH, ENG

• So, we need to add:

• List of Value: ActivityCodeLOV

• Parameter: p_activity_value

• SQL logic to the Data Set that uses new parameter


SELECT CODEVALUE FROM P6ACTIVITYCODE

WHERE CODETYPENAME = 'Phase' AND CODETYPESCOPE = 'AS_Global'


p_activity_value

1. Display “Select Phase”

2. Use ActivityCodeLOV

3. No Multiple Selection

4. Also changed the

values on p_project_id so

that the user can only

select one (1) project.

BIP – DM – Expand SQL (to Include Activity Code Logic)

Same SQL

The SQL above this point

is the same as the

previous SQL

New SQL for Activity

Code Logic

Need the Activity Code

Assignment table

-- EV labor units summed by date

SUM(ACTSP.EARNEDVALUELABORUNITS) AS SUM_EARNEDVALUELABORUNITS,

-- EV labor units - running totals by date

SUM(SUM(ACTSP.EARNEDVALUELABORUNITS)) OVER



AS CUM_EARNEDVALUELABORUNITS

-- Three tables needed for version 2

FROM P6PROJECT PRJ,

P6ACTIVITYSPREAD ACTSP,

P6ACTIVITYCODEASSIGNMENT ACTAS


PRJ.OBJECTID = ACTSP.PROJECTOBJECTID AND

ACTSP.ACTIVITYOBJECTID = ACTAS.ACTIVITYOBJECTID AND

ACTAS.ACTIVITYCODETYPENAME = 'Phase' AND

ACTAS.ACTIVITYCODEVALUE IN (:p_activity_value)


New SQL for Activity

Code Logic

Add condition so that the

results will only include

Activities with an Activity

Code of „Phase‟ and the

Activity Value selected

(which is stored in

p_activity_value)

BIP – Update Report

After making the changes to the DM

• Save DM

• Create XML data

• Import XML data into Template in WORD

• Modify Template

• Upload Template to BIP


• View Report

• Move Report to P6Report folder (reattached DM)

BIP – Update Report – WORD Template Builder

This is just a table in WORD

Using the Field

control on the BIP

tab, added

p_project_id and

p_activity_value to

table cells – then

change forecolor to

RED

No changes were needed on

the Chart

BIP – Running Report in P6

On Reports Tab in P6, find Report in

list and Right Click – select „Run

Report‟. Then the „Report Settings‟

screen is shown

Select Project

Select Phase from Dropdown List

Click the „Run‟ button

BIP – Running Report in P6

Looks good but we need to

stop the EV and Actual lines

on the Data Date

BIP – Advanced Topic for Chart

In our example, Actual Labor Units and EV Labor

Units are plotted to the end of the project. I want

them to plot only to the Data Date

Plot to End of Project Plot to Data Date

BIP – Advanced Topic for Chart

To add support so that lines are only plotted to

the Data Date, we need to:

1. Modify the DM/Data Set so that our running

totals for Actual and EV will be set to -1 when

the Date (Start Date) is greater than the Data

Date AND

2. Add an “if” condition in the XSL code behind

the Chart in the WORD Template Builder.

Behind most objects on the Template Builder, there is a section where

you can add additional code (it‟s XML-type code)

BIP – Advanced Topic for Chart – DM Changes




AS CUM_ACTUALLABORUNITS,

(CASE WHEN TRUNC(ACTSP.STARTDATE) <=

MAX(TRUNC(PRJ.DATADATE)) THEN




ELSE

-1

END ) AS CUM_ACTUALLABORUNITS,

Old SQL for Running Total

for

CUM_ACTUALLABORUNITS

New SQL for Running Total

for


The CASE is an IF statement.

When the StartDate is <= the

Data Date, continue adding to

running total ELSE set it to -1

We did the same thing for

the running total for EV

BIP – Advanced Topic for Chart – Template Changes

Steps

1. Open Template/RTF file in

WORD Template Builder

2. Double click on Chart

3. Select „Advanced‟ tab


XSL Code

Unformatted but

if you copy/paste

to an XML editor

or to a NOTEPAD

file with an XML

extension then

open it in your

browser, it looks

a little better.

See next slide


XSL Code

Formatted after:

1. copying/pasting XSL

Code to a NOTEPAD

file,

2. saving with an XML

extension, then

3. opening in a browser

IF Condition

We need to insert an <xsl:if>

statement round the Cell elements for

both

CUM_EARNEDVALUELABORUNITS

and



To modify, I just used NOTEPAD and put in

NEWLINES so that it was organized.

Then I modified it in NOTEPAD, save it to a

txt file, then copy/paste back to the

Advanced tab.


The Yellow highlight shows the sections that had to

be modified. Note the “<xsl:if” statement.

The Grey highlight shows that we did not modify the

logic for CUM_BLPLANNEDLABORUNITS


Note: EV and Actual stop plotting after 4/26

but BL Planned plots to the end of the project

BI Publisher – Presentation Objective

Presentation Objective

Go through all the steps to:

1. Develop the Data Model (SQL)

to extract the data from P6

2. Develop the S-Curve Template

in the WORD Template Builder

that uses the Data Model

3. Upload the Template to the

Report in BI Publisher

4. Move the Report and Data

Model to the P6Reports folder

5. Run S-Curve Report in P6 and

produce the same output as you

see on this slide

Wrap Up

When you download this PowerPoint

Presentation , you will also receive all Data

Models, SQL, Reports, and the XSL code with

the “IF” condition

End of Presentation

Questions?

Presented by:

Paul G. Ciszewski, PMPDynamic Consulting

[email protected]

(920) 883-9048


