Matrix colouring | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Matrix colouring

Is it possible to have a green-bar matrix work with collapsing and expanding groups? I have a matrix working with Chris Hay’s suggestions ( http://blogs.msdn.com/chrishays/default.aspx ) but with collapsing and expanding nodes they do not work on the higher groupings consistenly – only on the inner grouping.
Anyone else have this issue?

http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
Have you tried using IIF as per this reference?

you can use IIF or SWITCH
Yes, as explained I have that working already. It all works fine with all groupings expanded…the problem comes when you collapse the nodes of the groups. On the higher level groupings the colouring doesnt work.e.g. this is the colouring for the inner group with a 3 level grouping.
=iif(RunningValue(Fields!Group1.Value & Fields!Group2.Value & Group3.Value,
CountDistinct,Nothing) Mod 2, "White","Gainsboro")
Tha works fine when all groupings visible, but not when collapsing Group1 or Group2 nodes.. Effectively what I think I need to know is how to check if a grouping has been collapsed and set the colouring accordingly…something like:
IIf (Grouping3 is collapsed, iif(RunningValue(Fields!Group1.Value & Fields!Group2.Value,CountDistinct,Nothing) Mod 2, "White","Gainsboro"),
iif(RunningValue(Fields!Group1.Value & Fields!Group2.Value & Group3.Value,CountDistinct,Nothing) Mod 2, "White","Gainsboro")
)
Trouble is I dont know how to do the IIF (Grouping3 is collapsed) part of the statement.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |