Using a pivot as a RAG report tool - how do I display a RAG status at a higher level based on values 'below'

[#1]

Hi all,

I am using a pivot table in a project RAG report dashboard.
We have projects and subprojects and the pivot table goes down to sub-project level.

a Project may have more than 1 sub-project, and I need to analyse the RAG status of the sub-projects in order to set the value of a field at project level.

For example, if any of the sub-projects have a RED, then the Project RAG field should display RED.
If any of the sub-projects are amber then the Project level RAG should be Amber etc.

I have no idea how I can acheive this.

Is there a way in the script where I can loop through each project and analyse the sub-projects, inserting an appropriate value in a field?

Or does anyone have any other ideas?

Thanks in advance,

Matt

Thanks in advance,

Mat

[#2 · Accepted solution]

If projects and subprojects are already linked, then you can use a MAX(RAG) as an expression,

This will retrieve the max number (and so the max colour) included in the projects, sub projects, etc...

[#3]

Hi,

this doesn't seem to work.

If I put =max(BV_RAG) as a calculated dimension in the pivot table, it says //error in calculated dimension.

I have attached the qvw showing the pivot table.

as you can see, the expressions are at sub-project level and scrolling along to the right will show the BV RAG value (Budget Variance)

I need to show at project_level (the project numbers being in the format D03639-A) what the maximum BV_RAG1 value is from the lower levels.

oh... it seems I cant add an attachment... could someone tell me how!

thanks in advance,

Matt

[#4]

Hi,

Can you please provide us sample of data...?

so that it would be easy to analyze

- Sridhar

[#5]

hi there,

i'm not sure how to attach things.

it wont seem to let me...

can you advise please?

[#6]

Hi,

At right hand side top u can find 'Reply' below to Suggest an Answer.Click on that , it will take you to one more page where you will find an option to load the files i.e. 'OPTION' button, once you click that option button it will take you to one more page where you can see Browse button, with the help the browse button, browse your file and click save. After that it will take you to one more page where you can see the option to post your query and attachment i.e. Post button.

All d best

- Sridhar

[#7]

now with attachment 🙂

[#8]

hi, does anyone have any time to look at this for me?

i'm pretty stuck on this

thanks in advance,

Matt

[#9]

I'm sorry, I seem to have had a successful brain wave.

I performed a resident load of my main table with a max statement and grouped by the ProjectNumber (rather than sub-project)

this then linked to my main table et voila!

Thanks to anyone who spared time looking at this.

Matt