Custom Chart

[skyline666]

Hi,

I want to be able to make a custom chart, as none of the charts are what I need. Here is a picture of what im after, and I will explain afterwards:

9|      |      |
8|  O   |  R   |
7|      |      |  R
6|______|______|
5|      |      |
4|      |  O   |______
3|  Y   |______|  O
2|      |      |______
1|      |  Y   |  Y
 |______|______|______
    1      2      3

Ok, Y means Yellow background, O means Orangebackground and R means Red background. I have three fields that I need to populate this with. They are Risk Rating, Managability and RiskID. Risk Rating will go on the Y axis, and Manageability will go on the X axis. The RiskID will be the point (preferably a small square) on the chart. Risk Rating and Manageability are as the numbers you see on the chart above. RiskID is a numeric field.

Want I want to be able to do is if RiskID number 1 has a RiskRating of 3 and a Manageability of 2, then it will be a point in the Orange section in Manageability of 2. Another example is if RiskID is 2, Risk Rating is 5 and Manageability is 1, it will go in the Yellow section of Manageability of 1. When I say it goes in these sections, I mean it will go on that point (e.g RR = 5 and M = 1). Where the numbers are for Manageability (1-3), I want the value to go in the centre of the section (so imagine a line coming up from those numbers). Hope that all makes sense.

Can anyone help me on this?

Many thanks in advance,

Andrew

 

[SkipVought]

Hi,

What you've shown is a 100% Stacked Column chart with a ZERO gap.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[skyline666]

Hi Skip,

Ive been working on the graph since I posted last. Yes it is a 100% stacked column with no gap which I have now. What I have done to make the Y/O/R parts is make 3 new fields in the table to accomodate the values needed, and then used the 100% bit to make it.

What I need aswell though (as mentioned up there, but not on the diagram) is a Risk point on the Columns for where the RiskRating and Manageability lies. I have tried this but I don't think it can be done.

What I have tried is have the Sum(Y), Sum(R) and Sum(O) on the Y axis, and have the Manageability on the X axis (this gives the diagram). What I want then is to have just the normal Risk Rating on the Y axis aswell as a line graph minus the line (not the Sum of, as the sum of just shows 1 point). When I try this I get a message saying it can't be done, asthe Y axis needs to have a Sum value, not normal).

So if Risk Rating was 5 and Manageability is 3, it would be A on the diagram below, and if RR is 7 and Manageability is 2, it would be B on the diagram:

9|      |      |
8|      |      |
7|      |  B   |  
6|______|______|
5|      |      |  A
4|      |      |______
3|      |______|   
2|      |      |______
1|      |      |   
 |______|______|______
    1      2      3

Any suggestions?

Andrew

 

[SkipVought]

Use a Line chart for that series (with out line, with MARKERS) on a Secondary Value axis.

Maybe you want the Line chart to be the PRIMARY and the 100% Stacked Column chart to be the Secondary.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

BTW, just add this new series, SELECT whichever SERIES you want to be the SECONDARY, Format the Selected Series - Axis Tab - select Secondary.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

...duh, I am missing some essential elements.

When you Select the series you want to be a Line Chart, Change the Chart Type accordingly.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[skyline666]

Hi Skip,

Ive worked out how to do the secondary series on the axis, and I will be using a line chart, but im not sure how to implement the secondary part in terms of actually gettin it working, let me run through what I do and what error I get.

I use chart wizard and select the relevant table (BostonRisk). I select all fields (RiskID, Manageability, RiskRating, RiskRatingY, RiskRatingO, RiskRatingR, where Y/O/R are fields that store the upper value for that manageability). I choose a Column Chart. Manageability goes at the bottom, SumRiskRatingY/O/R go on the Y axis, and RiskID goes in the Series.

Now from here theres two things I can do for the RiskRating, either do it normally on Y axis or Sum of. If I do normal, a message comes up saying that it needs to be Sum of, so I put it in as sum of for now. So I have 4 things in the Y axis part. When I click next, a message appears stating "You can only specify 1 field as data if you have both an Axis and Series field. Remove the extra data field(s) or remove either the Axis or the Series field".

So I will remove RiskID for now as I definetly need Manageability in. I click finish, and then go to design view and make the Y/O/R fields to 100% stacked with no gap, and put RiskRating as line chart with no line. As its the Sum of, I only get one value in each manageability, but I need each RiskRating value instead.

The row source for the above is:

SELECT [Manageability],Sum([RiskRatingY]) AS [SumOfRiskRatingY],Sum([RiskRatingO]) AS [SumOfRiskRatingO],Sum([RiskRatingR]) AS [SumOfRiskRatingR],Sum([RiskRating]) AS [SumOfRiskRating] FROM [BostonRisk]   GROUP BY [Manageability];

I have another chart made which is just a line chart of RiskRating against Manageability, the row source is:

TRANSFORM Sum([RiskRating]) AS [SumOfRiskRating] SELECT [Manageability] FROM [Risk]   GROUP BY [Manageability] PIVOT [RiskID];

I then tried combining the two bits of code together like this:

TRANSFORM Sum([RiskRating]) AS [SumOfRiskRating] SELECT [Manageability],Sum([RiskRatingY]) AS [SumOfRiskRatingY],Sum([RiskRatingO]) AS [SumOfRiskRatingO],Sum([RiskRatingR]) AS [SumOfRiskRatingR] FROM [BostonRisk]   GROUP BY [Manageability] PIVOT [RiskID];

and it has kind of worked, but not fully. I now have all the values, but they arent in the correct position, they are all towards the bottom of the chart. It may have something to do with the 100% part. What I would like is to show the number 1-9 instead of 0-100%, is there anyway of doing this?

Many thanks,

Andrew

 

[skyline666]

Ok I have it almost done now, it was working before when I said it didn't, the problem was that the line was on the primary axis with the column chart, so made it secondary and it works. The 100% formatting can be ignored now as there is a second axis on the right of the graph that goes from 1-9 (well 10 so will have to tinker with the maximum values on both sides).

Can you hide the axis numbers (not label) at all do you know? I know you can hide the value axis, but this also messes up the chart.

Many thanks,

Andrew

 

[SkipVought]

Hide axis values: Make the font color same as chart area interior color.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

"...have to tinker with the maximum values on both sides..."

read the MaximumScale properties of each value axis and determine the largest. Assign that value to both MaximumScale.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]