Project and Portfolio Management Practitioners Forum
cancel

cluster bar chart -need ideas

Highlighted
patrick-sa
Super Contributor.

cluster bar chart -need ideas

Hi,
i've got a requirement to show financial data in a clustered bar chart.i've done the sql to bring the figures in the format required for a bar chart,but i'm struggling with the filters.If i configure a project filter,ppm will add the (and pm.project_id =30033) at the bottom of this query and the query fails.how should i configure the filter?i'll require the program too,but this will easy after doing the project filter.any ideas welcome.

(query commented out and runs from dual)
SELECT
Category,
TYPE,
sum(TOTAL) TOTAL
FROM(

SELECT
'PROJECT_NAME' PROJECT_NAME,
100000 TOTAL, --CAPEX_BUDGET
'CAPEX' TYPE,
'BUDGET' Category
FROM
--kcrt_req_header_details req , --pm_projects pm
dual
WHERE 1=1 --req.request_id = pm.pfm_request_id
UNION
SELECT
'PROJECT_NAME' PROJECT_NAME,
80000 TOTAL, --OPEX_BUDGET
'OPEX' TYPE,
'BUDGET' Category
FROM
--kcrt_req_header_details req , --pm_projects pm
dual
WHERE 1=1 --req.request_id = pm.pfm_request_id

)
group by category ,type
4 REPLIES
Celil
Acclaimed Contributor.

Re: cluster bar chart -need ideas

Hi patrick,

I had same issue. Your solution is...

Add "pm.project_id" field to your all union queries. Than remove "pm." prefix from your filter. Now your filter run correctly.

if you any error please inform me.

Celil
Celil

IT Governance Professional
& PPM Solution Architect
TurboMan
Honored Contributor.

Re: cluster bar chart -need ideas

patrick-sa, I am not very familiar with the PPM tables.
From which tables do you get the fields from?
Regards
TM
patrick-sa
Super Contributor.

Re: cluster bar chart -need ideas

Hi Celil,

i fixed this by commenting out the where section in the global query.see below
--AND pm.project_id in ([P.PROJ_NAME])

Turboman,
kcrt_req_header_details and pm_projects tables.these are custom fields so you'd have to modify them to suit your environment.
patrick-sa
Super Contributor.

Re: cluster bar chart -need ideas

i have found solution as described above