Clarity

Hello,

I am trying to retrieve the list of investments that have been associated with a specific Portfolio Plan in 14.2. I am able to retrieve the Portfolio, and link it to a specific Plan version. I see that there are tables for Portfolio Investments, but they appear to be at the Portfolio level, whereas I would like to see only the Investments with a specific Portfolio Plan version.

thanks!

You are looking for CMN_INSTANCE_RANK and CMN_RANK_CONTEXTS (You'll want to join this on pfm_plans)

The great news is there is a view PFM_PLAN_RANKING_V, which has most of it ready for you.

Here is an example query which will return all plans and investments

select p.name, inv.code, inv.name, r.rank, r.above_waterline from pfm_plans p inner join pfm_plan_ranking_v r on p.id = r.context_id inner join pfm_investments port ON r.instance_id = port.id inner join inv_investments inv on inv.id = port.investment_id

Hope this is a good starting point for whatever you need!

Thanks Andy for the reply and query!

I don't think my question was very clear. Below I have screenshots from a standard Portfolio in the CGUC Community sandbox. I am testing out the Portfolio Plan features but I am still unable to retrieve a list of Investments that meet a certain criteria. In this case, not necessarily if the investment is above/below the waterline. I am looking for Investments that are flagged as "Approved" = True in a specific Portfolio Plan version. I attached screenshots below for clarification. The first screenshot shows the list of Plans for a specific Portfolio. Notice on the right side there are labels for "Plan: v1" or "Plan: v2". When I navigate to the Investments tab on the Portfolio, I am able to toggle between the two Plan views. Each view in this case (v1, v2) has different "Approved" Investments. Notice that Plan "v1" has the first 6 Investments flagged as "Approved" where as the Plan "v2" has the last 6 Investments flagged as "Approved".

My goal is to retrieve a list of "Approved" Investments from the Portfolio Plan "v2".

Andy M wrote:

 

You are looking for CMN_INSTANCE_RANK and CMN_RANK_CONTEXTS (You'll want to join this on pfm_plans)

The great news is there is a view PFM_PLAN_RANKING_V, which has most of it ready for you.

 

Here is an example query which will return all plans and investments

select p.name, inv.code, inv.name, r.rank, r.above_waterline from pfm_plans p inner join pfm_plan_ranking_v r on p.id = r.context_id inner join pfm_investments port ON r.instance_id = port.id inner join inv_investments inv on inv.id = port.investment_id

Hope this is a good starting point for whatever you need!

Michael,

That's in pfm_investments_plan. port_inv_status

You'll notice that unless you make a change to a column in the plan, a row won't exist. Therefore pick up the "approval" status first from pfm_investments_plan, and if null use pfm_investments

answering the exam question

select

x.code,

x.name

 

 

FROM

(select

p.name

plan_name,

inv.code,

inv.name,

CASE WHEN plan.port_inv_status is NULL THEN port.port_inv_status ELSE plan.port_inv_status END is_approved

from

pfm_plans p

inner join pfm_plan_ranking_v r on p.id = r.context_id

inner join pfm_investments port ON r.instance_id = port.id

inner join inv_investments inv on inv.id = port.investment_id

left outer join pfm_investments_plan plan on plan.id = port.id

) X

WHERE

IS_APPROVED = 1

AND PLAN_NAME = 'v2'

Again I suspect the query is not very efficient and secondly you'll want to pass the portfolio ID into this as plan name is not unique!

Thanks again Andy,

Here goes my next question. . .

In addition to the "Approved" flag that has been checked/unchecked in this specific Portfolio Plan Scenario, I have also updated planned dollars. Let's say I am moving $1 from year 1 to year 2. Where would I be able to see both the original year1 and year2 and the updated year1 ($1 subtracted) and year2 (with $1 added)?

I can see many BLOB fields, but am wondering if there is an easy way to extract dollars for reporting along with the "Approved" flag.

This is held in in pfm_clob_curves

How do I access clob data from PFM_CLOB_CURVES? Is there any slices tables for this?

Thanks Again Andy,

Is there an API/(XOG ) for importing Portfolios and/or Portfolio Plans for v14.2? I searched the XOG folder contents and only found an XML file for reading portfolios from Clarity, but none for importing.

cheers!

Andy M wrote:

 

This is held in in pfm_clob_curves

How do I access clob data from PFM_CLOB_CURVES? Is there any slices tables for this?

Use the sample 'read' XOG file to XOG out an existing portfolio to see how the 'write' file can be constructed.

Once you do this , you will now have a 'sample' write file that can be modified to import a new portfolio.

Thanks Kathryn!

Kathryn Ellis wrote:

 

Use the sample 'read' XOG file to XOG out an existing portfolio to see how the 'write' file can be constructed.

Once you do this , you will now have a 'sample' write file that can be modified to import a new portfolio.

MichaelVonBrunak wrote:

 

Thanks Kathryn!

Kathryn Ellis wrote:

 

Use the sample 'read' XOG file to XOG out an existing portfolio to see how the 'write' file can be constructed.

Once you do this , you will now have a 'sample' write file that can be modified to import a new portfolio.

Hi Kathryn,

Another question on this topic. . .

Is the "Approved" flag the best way to filter projects that are or are not part of a specific Portfolio Plan? I remember in previous Portfolio Scenarios (pre-version 13.2), you could mark a specific investment that is part of the Portfolio to be excluded from the "Scenario" (now "Plan"). Is there a "Included in Plan" attribute that serves this purpose, or is "Approved" the best substitute?

thanks

Mike

Hi Kathryn,

Another question on this topic. . .

Is the "Approved" flag the best way to filter projects that are or are not part of a specific Portfolio Plan? I remember in previous Portfolio Scenarios (pre-version 13.2), you could mark a specific investment that is part of the Portfolio to be excluded from the "Scenario" (now "Plan"). Is there a "Included in Plan" attribute that serves this purpose, or is "Approved" the best substitute?

thanks

Mike

Kathryn Ellis wrote:

 

Hi Michael, I have an idea on what you are talking about. Can you clarify for me what you are trying to achieve? Do you want to query the list of investments on the portfolio compared to a query of the list of investments on a portfolio plan?

Hi Kathryn,

Thanks for your excellent support, as usual.

My direct number is 714-781-6204 if you are able to call . . .

The image should show the Portfolio in the first section, followed by Portfolio Plan 1, Portfolio Plan 2 and finally the Report output.

I marked the revisions from Plan 1 to Plan 2 in Bold.

The Portfolio has 4 total investments (4 projects, 1 idea).

Plan 1  has 3 investments.3 projects approved, 1 idea unapproved

Plan 2 has 3 investments, 1 Idea approved, 1 project unapproved.

In the report, I would like to show the differences in Investments (compare "Portfolio Plan 1" vs "Portfolio Plan 2") in Costs ,Program and Approved. Is the "Approved" attribute the proper field to reference in the new Portfolio functionality? Or is there another way to mark "Project C" as "excluded" and "Idea 1" as "included" in "Portfolio Plan 2"?

Also, since Portfolio Management costs are still limited to Calendar Periods (and not Fiscal), is there a workaround that CA has been recommending to customers until the enhancement is delivered sometime next year? For example, should we be creating "proxy" attributes (example: FY 2015_Q1, FY 2015_Q2) at the Portfolio Investment object level to store Fiscal Period data? Or can I update the Portfolio Investment time-scaled values with fiscal period data using XOG or other approach?

thanks again!

Michael

MichaelVonBrunak  This is a great write up illustrating your use case.

1. The Portfolio Planning functionality does not have the 'include in scenario' type of attribute like the old functionality had.  You could use the 'approved' attribute in the manner you described.  Alternatively, you could add an attribute on the abstract investment object (so it is available to all investment types) and have it default to true on all investments and register the attribute, you could use it in the same manner as the investment approved attribute as you described. Either method should allow you to see the differences in the plans and allow you to query and filter data in the tables.

2. As for the Fiscal Periods in the application for the Portfolio Functionality ; that is currently not available.  However, in building your queries to extract the data in your reporting example, it is possible to link the Portfolio Plan TSV slice data to a Fiscal Calendar.  This is something that I am working on for a future tech tip.  We will explore the database functions that have a prefix PFM and share this information with the community soon.

Kathryn Ellis wrote:

 

MichaelVonBrunak  This is a great write up illustrating your use case.

 

1. The Portfolio Planning functionality does not have the 'include in scenario' type of attribute like the old functionality had.  You could use the 'approved' attribute in the manner you described.  Alternatively, you could add an attribute on the abstract investment object (so it is available to all investment types) and have it default to true on all investments and register the attribute, you could use it in the same manner as the investment approved attribute as you described. Either method should allow you to see the differences in the plans and allow you to query and filter data in the tables.

 

2. As for the Fiscal Periods in the application for the Portfolio Functionality ; that is currently not available.  However, in building your queries to extract the data in your reporting example, it is possible to link the Portfolio Plan TSV slice data to a Fiscal Calendar.  This is something that I am working on for a future tech tip.  We will explore the database functions that have a prefix PFM and share this information with the community soon.

Thanks again.

I have a follow-up question (in two parts):

If I were to create a new attribute on the Investment object called "FY16 Costs" and add it to the Portfolio Investment Registry as mentioned, what table contains the original and current values for this new attribute on a specific Portfolio Plan version Investment?

Is this attribute available in a view and/or table for use in the creation of a JasperSoft domain or is it accessible only via a SQL query due to the join logic?

thanks!

Michael

It is only in the PPM Database Schema.  It is not in the DWH Schema.

Andy M wrote:

 

answering the exam question

select

x.code,

x.name

 

 

FROM

(select

p.name

plan_name,

inv.code,

inv.name,

CASE WHEN plan.port_inv_status is NULL THEN port.port_inv_status ELSE plan.port_inv_status END is_approved

from

pfm_plans p

inner join pfm_plan_ranking_v r on p.id = r.context_id

inner join pfm_investments port ON r.instance_id = port.id

inner join inv_investments inv on inv.id = port.investment_id

left outer join pfm_investments_plan plan on plan.id = port.id

) X

WHERE

IS_APPROVED = 1

AND PLAN_NAME = 'v2'

Again I suspect the query is not very efficient and secondly you'll want to pass the portfolio ID into this as plan name is not unique!

Thanks again Andy!

Is the "Approved" flag the best way to filter projects that are or are not part of a specific Portfolio Plan? I remember in previous Portfolio Scenarios, you could mark a specific investment that is part of the Portfolio to be excluded from the "Scenario" (now "Plan"). Is there a "Included in Plan" attribute that serves this purpose, or is "Approved" the best substitute?

In the current version ; all investments in the Portfolio Content Editor criteria are included in the 'Plan' contents.  There is no way to 'remove' them like you could in the previous version (maybe log a new idea?)

I think the 'approved' flag or 'required' flag on the investment may be useful for this ; or a custom attribute.  Any numeric attribute would be helpful because then you could use the attribute in the Ranking Rules.

Hi Kathryn,

Thanks again for your assistance.

We have crafted a workaround to the inability to track Portfolio costs in Fiscal periods in the 14.2 and 14.3 versions. I was hoping you might have some time to review the results of our findings and get your feedback.

Thanks again!

Michael

Hi Kathryn,

I read briefly through this post and understand that there was no way to extract the inv specific dollar values as seen on the waterline view.

Do you have any inputs to understand how can I extract those? This is needed to replicate waterline view in Jaspersoft by including more columns needed by business.

Tried converting the query to NSQL for a portlet, but it returns "ERRORNPT-217: This query produced duplicate dimensional data."

select
@SELECT:DIM:USER_DEF:IMPLIED:PLAN:p.name:plan_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PLAN:inv.code:inv_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PLAN:inv.name:inv_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PLAN:r.rank:rank@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PLAN:r.above_waterline:above_waterline@

from pfm_plans p
left outer join pfm_plan_ranking_v r
on p.id = r.context_id
inner join pfm_investments port
ON r.instance_id = port.id
inner join inv_investments inv
on inv.id = port.investment_id

where
P.PORTFOLIO_ID = 5001021
AND @FILTER@