-
Hi Yvonne,
The usual reason for wanted a column hidden is have the column affect the table in order to achieve the lower level granularity of the table without showing driving driving dimension, development_phase in this instance.
Are development phase, phase I, phase II etc. members of the same dimension? I.e. is this a cross tab type table?
If you could share a screen shot of the table in Discover, showing the drop zones etc., we may be able to help you better and find a way to deliver what you need by an alternate approach.
Thanks,
Ian
-
Hi Yvonne,
One way you could accomplish this is to create a calculation that returns a value if the cell is not empty (i.e. the cells that get a background colour), setting the value depending on the column header. Then export the grid to Excel and use Excel's conditional formatting to set the foreground and background colour depending on the cell value.
Here's an example.
My grid shows colours based on product category. It colours only those cells where there is a value, just like your grid:
Create a calculated measure, Colour, that sets its value based on the column, for example 1 for Accessories, 2 for Bikes, 3 for Clothing and 4 for Components. In your case this would be the Development Phase.
CASE(
criteria(
[Products].[Product Category].currentmember.caption="Bikes",
[Products].[Product Category].currentmember.caption="Accessories",
[Products].[Product Category].currentmember.caption="Clothing",
[Products].[Product Category].currentmember.caption="Components"),
criteriaresults(
If([measures].[Data Sales] = null,0,1),
If([measures].[Data Sales] = null,0,2),
If([measures].[Data Sales] = null,0,3),
If([measures].[Data Sales] = null,0,4)
),
0
)I.e. if the cell in the Bikes column is null, set the value to 0 otherwise set it to 1, Accessories column to 2 and so on.
Put the calculated Measure Colour in the grid:
Export to Excel:
Then use Excel conditional formatting to colour both the foreground text and the background to the same colour based on the cell's value:
Add a new rule for each value 1 through 4 and you end up with a grid in Excel which is similar to your original Pyramid colour coded grid:
Hope that helps.
Ian