Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

cluster bar chart -need ideas

Highlighted
patrick-sa
Regular Collector

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
Esteemed 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
Member

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
Regular Collector

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
Regular Collector

Re: cluster bar chart -need ideas

i have found solution as described above
//Add this to "OnDomLoad" event