bar chart, order 1

[mcaizpp2]

Hello, I have a bar chart problem which is -

The chart is to have 4 bands (1-4) within each band there are six groups(1-6) so the chart has the following properties

on change of -
Band
PCTotal

Count of @PCTotal

The formula for PCTotal is -
count({VIEW_RPT_ScoreByBand.PCTotal},{VIEW_RPT_ScoreByBand.PCTotal})

I set up 2 group fields on the report (which i suppressed) for both the band field and PCTotal.

The calculations are correct but I want the chart to look like -

BAND A - 1: 2: 3: 4: 5: 6

Band B - 1: 2: 3: 4: 5: 6

Band C - 1: 2: 3: 4: 5: 6

Band D - 1: 2: 3: 4: 5: 6

The prolem i am getting is that for each band the PCTotal field is not in the order 1 - 6 (at the moment it is -

Band - 1: 2: 3: 4: 6: 5

I have tried to specify the order on the PCTotal field in the chart but nothing changes.
I also tried order the view in which the report is coming from but nothing.

Does anyone have any suggestions?

Thanks in advance

 

[lbass]

It is unclear whether you are using the count formula for your "on change of" field. If you are, you shouldn't be--you should be using {VIEW_RPT_ScoreByBand.PCTotal} as your second "on change of" field.

Also, there is no way that I can see that you could use a count of {@PCTotal}, so I'm guessing you are actually using {@PCTotal}, unsummarized.

-LB

 

[mcaizpp2]

Hi lbass, thanks for responding to this and the other post.

What I didnt think was important is very important. There will always be 4 bands, but depending on the data returned in (I have about 6 filters on the report) There wont always be 6 segments (1-6) for each band.

so when 6 segments are not returned for the top band (A) the ordering is changed.

What I was hoping you may have an answer to is I need to create something else (formula, sql expression etc) that has 6 rows with the values 1-6.

I can then use that in the on change property of the chart expert?

Thanks lbass for your help.

 

[lbass]

This is an entirely different question. You can't report on data that isn't there. If you had a table that contained all bands and all six inner values per band, you could do a left join from that table to the one containing the summary field and use that for charting. Or, instead, you might be able to do this with running totals, although I'm not sure whether you can create stacked bars with running totals--never tried it.

-LB

 

[mcaizpp2]

Hi lbass,

I'll try to explain a bit more about my problem so you get a better idea of what i am trying to achieve -

Observations
In the chart if the Band A bar has values for each segment then the ordering is correct for that bar (A) and for all the rest of the bars as well.

But if bar A has only values for segment 1,2,5 & 6 then the ordeing of the segments on all bars including (bar A) is wrong.

All I want is a means of getting the correct ordering on all the segments.

Do you have any other ideas?

Thanks lbass.

 

[lbass]

If you used topN sorting on the band field, using "All", descending, then the topmost row with the most PCTotal fields would drive the ordering, and it would always be correct--except that then the band field might be C,A,B,D.

-LB