Clarity

Hi,

i have been reewriting some Querys that was used for portfolios in 13.1 to 13.3, but have dificoulties getting the Investment total cost (the cost inside the Portfolio range as in the provider).

In 13.1 this was easy due to the @Investment_sql@. I know this is because it's a blob Field so i wonder if someone know how to use this or if there is any equivalent.

Example Query:

select
       @SELECT:DIM:USER_DEF:IMPLIED:PORTFOLIO:portplan.investment_id:NOKKEL@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.investment_id:investment_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.inv_id:inv_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.investment_name:investment_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.alli:allignment@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.cost:cost@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.portfolio_ranking:portfolio_ranking@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.plan_ranking:plan_ranking@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.above_waterline:above_waterline@

FROM

(SELECT NVL(ppi.id,pi.id) investment_id,
       pi.investment_id inv_id,
       NVL(ppi.name,pi.name) investment_name,
       pi.alignment alli,
       pi.plan_ttl_cost cost,
       pr.rank portfolio_ranking,
       NVL(ppr.rank,pr.rank) plan_ranking,
       NVL(ppr.above_waterline,pr.above_waterline) above_waterline
FROM   pfm_portfolios port
       INNER JOIN pfm_portfolio_ranking_v pr ON port.id = pr.context_id
                                            AND pr.context_type = 'pfm'
       INNER JOIN pfm_investments pi ON pr.context_id = pi.portfolio_id
                                    AND pr.instance_id = pi.id
       INNER  JOIN pfm_user_selected_plan usp ON USP.PORTFOLIO_ID = port.id
          AND USP.USER_ID = @WHERE:PARAM:USER_ID@
       INNER  JOIN pfm_plans pplan ON port.id = pplan.portfolio_id
            AND PPlan.ID = USP.PLAN_ID
       LEFT OUTER JOIN pfm_plan_ranking_v ppr ON pplan.id = ppr.context_id
                                             AND ppr.context_type = 'pfmplan'
                                             AND pi.id = ppr.instance_id
       LEFT OUTER JOIN pfm_investments_plan ppi ON ppr.instance_id = ppi.id
                                               AND ppr.context_id = ppi.plan_id
WHERE port.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@

UNION ALL

SELECT pi.id investment_id,
       pi.investment_id inv_id,
       pi.name investment_name,
       pi.alignment alli,
       pi.plan_ttl_cost cost,
       pr.rank portfolio_ranking,
       pr.rank plan_ranking,
       pr.above_waterline above_waterline
FROM   pfm_portfolios port
       INNER JOIN pfm_portfolio_ranking_v pr ON port.id = pr.context_id
                                            AND pr.context_type = 'pfm'
       INNER JOIN pfm_investments pi ON pr.context_id = pi.portfolio_id
                                    AND pr.instance_id = pi.id
WHERE port.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@
and (select usp.plan_id from pfm_user_selected_plan usp where USP.PORTFOLIO_ID = port.id
          AND USP.USER_ID = @WHERE:PARAM:USER_ID@) is null) portplan

Where @filter@

Hi All - Any ideas here for Sigurd? Thanks! Chris

Sigurd Fjeld wrote:

 

Hi,

i have been reewriting some Querys that was used for portfolios in 13.1 to 13.3, but have dificoulties getting the Investment total cost (the cost inside the Portfolio range as in the provider).

In 13.1 this was easy due to the @Investment_sql@. I know this is because it's a blob Field so i wonder if someone know how to use this or if there is any equivalent.

 

Example Query:

select
       @SELECT:DIM:USER_DEF:IMPLIED:PORTFOLIO:portplan.investment_id:NOKKEL@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.investment_id:investment_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.inv_id:inv_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.investment_name:investment_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.alli:allignment@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.cost:cost@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.portfolio_ranking:portfolio_ranking@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.plan_ranking:plan_ranking@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORTFOLIO:portplan.above_waterline:above_waterline@

FROM

(SELECT NVL(ppi.id,pi.id) investment_id,
       pi.investment_id inv_id,
       NVL(ppi.name,pi.name) investment_name,
       pi.alignment alli,
       pi.plan_ttl_cost cost,
       pr.rank portfolio_ranking,
       NVL(ppr.rank,pr.rank) plan_ranking,
       NVL(ppr.above_waterline,pr.above_waterline) above_waterline
FROM   pfm_portfolios port
       INNER JOIN pfm_portfolio_ranking_v pr ON port.id = pr.context_id
                                            AND pr.context_type = 'pfm'
       INNER JOIN pfm_investments pi ON pr.context_id = pi.portfolio_id
                                    AND pr.instance_id = pi.id
       INNER  JOIN pfm_user_selected_plan usp ON USP.PORTFOLIO_ID = port.id
          AND USP.USER_ID = @WHERE:PARAM:USER_ID@
       INNER  JOIN pfm_plans pplan ON port.id = pplan.portfolio_id
            AND PPlan.ID = USP.PLAN_ID
       LEFT OUTER JOIN pfm_plan_ranking_v ppr ON pplan.id = ppr.context_id
                                             AND ppr.context_type = 'pfmplan'
                                             AND pi.id = ppr.instance_id
       LEFT OUTER JOIN pfm_investments_plan ppi ON ppr.instance_id = ppi.id
                                               AND ppr.context_id = ppi.plan_id
WHERE port.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@

UNION ALL

SELECT pi.id investment_id,
       pi.investment_id inv_id,
       pi.name investment_name,
       pi.alignment alli,
       pi.plan_ttl_cost cost,
       pr.rank portfolio_ranking,
       pr.rank plan_ranking,
       pr.above_waterline above_waterline
FROM   pfm_portfolios port
       INNER JOIN pfm_portfolio_ranking_v pr ON port.id = pr.context_id
                                            AND pr.context_type = 'pfm'
       INNER JOIN pfm_investments pi ON pr.context_id = pi.portfolio_id
                                    AND pr.instance_id = pi.id
WHERE port.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@
and (select usp.plan_id from pfm_user_selected_plan usp where USP.PORTFOLIO_ID = port.id
          AND USP.USER_ID = @WHERE:PARAM:USER_ID@) is null) portplan

Where @filter@

This may be helpful.

Use 'Action Tracing' to see if you can simulate some queries that are used by the application

Look at some of the new NSQL Portlet queries that provide portfolio dashboard portlet data

Hi Kathryn

Sorry, I cannot find any NSQL queries that support Portfolios. All the new Portfolio Dashboard portlets uses the system providers. These gives you access to Total /portfolio) cost and much more, so t´hey are adequate for most Portfolio reporting.

Unfortunately we need to compare Portfolio total cost with the "real" project's total cost, and that cannot be accomplished. But that not for this thread, I assume.

I think you shoud rewrite your portlets to use the new Portfolio System Providers. They contain - as far as I can see - all the information you need, and then you can discard your queries.

Rgds JorgenR

Hello FrodeKommode,

Have you solved your problem?
I have the same problem, I do not know how to consult the investment costs of the portfolio.
Please, if you know the answer, answer me in the following thread:

CA PPM How can I read with a query the budgeted cost of the investment object of the portfolio?