Hi,
I have a simple report, that lists the number of projects that have a project status of green, yellow or red and want to show it as a pie chart.
As the project status is represented as a color it makes a lot of sense to show the pies in the same color.
And it’s easy to do that as long as all 3 colors are present by defining a custom color palette.
However if there if one project status is missing the color palette gets screwed up:
e.g. if there is no red project, there’s no entry for red and the yellow status is shown in red color - pretty confusing for the report reader.
Is there any way to allways assign the same color to a value - independent of it’s position?
Or how can I force a count=0 entry for missing entries?
can ANYONE post a working solution to this problem?
I have read in another topic about creating a colour dimension containing the values Red, Yellow and Green. I have done this using a derived table in the universe:
select 'Green' as COLOUR from dual
union
select 'Yellow' as COLOUR from dual
union
select 'Red' as COLOUR from dual
In my report i have 2 tables. One contains the source data and the other is for the pie chart.
I have used the colour dimension as the first column in the pie table.
I have then added a second column which needs to hold the quantity (or zero or 0.00001 if there is no data for a colour). This bit i can’t get to work. I always get a #CONTEXT error.
In the source table in the report i can get colour description for each record. I have to go through 2 or 3 variables to get there due to the way lower and upper limits are applied.
Is the #CONTEXT error just due to incorrect usage of extended contexts within that table or is there a more fundemental error here.
My formulea for counting the values goes something like
=Count([dim_1] in ([dim_1];[dim_2];[dim_3])) where ([dim_4] = [coulour_dim])
Can you not just create another query which returns just the main dimension used for the pie chart, create a merged dimension with this and the main query dimension, then use this and your calculated measure (e.g. if null then return a zero or miniscule amount) in the pie chart? No need for derived tables, etc.
Hi, i saw that link which was why i created the derived dimension in the universe, (to ensure that all three colours appeared even when one of them had no data).
My problem is at the moment i don’t have a common dimension to merge them on. Whether a record belongs in one band or another is based on parameters that the user can supply and alter at run time (via input controls). In the long run my intention is to replace the parameters by configurations stored on the database, so the user selects the relevant configuration when refreshing the data and the universe takes care of associating colours.
I suppose if i can’t resove the #context problem by any other means (intermediate variables or extended context) then i’ll have to implement configurations sooner than i had hoped.
(BOB member since 2005-11-22)
(BOB member since 2005-05-10)
(BOB member since 2008-10-09)