Advanced Howto Request for Conditional formatting with Custom Code

RS List,
Well, I managed to get all the mdx pieces of my problem resolved. Now I
need to solve a tricky conditional formatting problem. Let me provide some
info first and then I'll ask a few questions.
The report I have generated is a very concise matrix. It basically assigns
ranks to failure modes by product family and displays product families side
by side (ascending alphabetically along columns) with failure modes for each
family ascending by rank (rows). A simple ASCII version of the matrix layout:
| product_family
--
rank | failure_mode
yields:
| pfC | pfE | pfF | pfH |
pfJ |
----
1 | mode45 | mode13 | mode94 | mode45 | mode08 |
2 | mode13 | mode26 | mode13 | mode13 | mode45 |
3 | mode77 | mode91 | mode27 | mode74 | mode26 |
4 | mode26 | mode45 | mode12 | mode13 | mode39 |
5 | mode68 | mode94 | mode24 | mode68 | mode27 |
6 | mode94 | mode12 | mode83 | mode94 | mode12 |
The report requestor needs the ability to pick a given product_family, lets
say pfH for now, as a color coding standard. Then the top 10 modes for pfH
will be assigned color codes (background and complementary font) based on a
standard. Based on the standard, matching modes in all product_families are
color coded the same. This has the effect of being able to show where
failure modes in one family fall within all the other families.
I have a function, setcolor() that I can use to set the background (BG) and
font (FT) colors. Now, I need to figure out how to read the modes for a
given family into an array (?) and then pass the array (?) to setcolor(). I
have created a separate hidden table that houses the basis family (parameter
driven) with its modes and I am trying to read that into an array.
I would like to be able to have yet another hidden table in the report
holding 10 rows with BG and FT the way I would like them. These properties
would be read into an array along with the modes for the basis family. Then
I would pass the modes, BG and FT to setcolor() and be fine.
Is it possible to read BG and FT properties of a matrix cell/textbox into an
array in code?
Right now it looks like I'll be hardcoding the BG and FT properties in an
array and then combining these with the modes for our basis family in another
array. From here, I'll have to see how to get this into setcolor().
Does anyone have any recommendations, thoughts or pointers for this.
Thanks!For those that may struggle through something similar...
To summarize, the requirement was to color code a matrix based on a basis
family. The top 10 modes for the basis family need to be used to color code
the modes of all other families. This gives the users an easy visual way to
see how the top modes of one family fall out in the other families.
First I did this all in embedded code as I don't see using this in other
places, if that changes then the code would need to be refactored.
Additionally, there are assuredly other ways of doing this.
Anyway, like I said in the first post, the first thing I had to do was
declare my array. I needed it to be global so I could address it from
different functions, so:
'Declare global 2D array Standard()
Private Shared Standard(10, 3) As String
Then I hard coded an array initialization scheme such that Standard(n,1) had
the background color and Standard(n,2) had the font color. For the moment I
left Standard(n,3) alone:
'Function to Init Standard(), this function must be called separately
before use
Shared Function InitStandard()
Standard(1, 1) = "Navy"
Standard(1, 2) = "White"
Standard(2, 1) = "Cyan"
Standard(2, 2) = "Black"
Standard(3, 1) = "PeachPuff"
Standard(3, 2) = "Black"
Standard(4, 1) = "Honeydew"
Standard(4, 2) = "Black"
Standard(5, 1) = "Black"
Standard(5, 2) = "White"
Standard(6, 1) = "MediumTurquoise"
Standard(6, 2) = "White"
Standard(7, 1) = "Olive"
Standard(7, 2) = "White"
Standard(8, 1) = "Thistle"
Standard(8, 2) = "Black"
Standard(9, 1) = "Lime"
Standard(9, 2) = "Black"
Standard(10, 1) = "Yellow"
Standard(10, 2) = "Black"
End Function
Next, we needed to get our Modes into the array (that is where Standard(n,3)
comes in). So to do this, I wrote a function that is to be used in the
hidden table to get the modes for the basis family, update Standard() and
return the Mode. The basis family is selected by a parameter then the hidden
table returns the family, the Mode, the result of the following function
(basically the Mode again, but I include both columns as a sanity check) and
XRank:
'Function reads in Modes, updates Standard() with Mode and Returns Mode
Shared Function GetModes(ByVal XRank As String, ByVal Mode As String)
Dim idx As Integer
idx = CInt(XRank)
If idx <= 10 Then
Standard(idx, 3) = Mode
End If
Return Mode
End Function
At this point we have read in the Top 10 Modes from the basis family. That
was the hard part. Now, all we need to do is color code the modes in the
result table with all families based on the basis family. For this we will
be calling another function from the Background Color and Color Properties of
the mode textbox in the result matrix:
'Function to Set background color (BG) and font (FT)
Shared Function SetColor(ByVal XRank As String, ByVal Mode As String,
ByVal Param As String) As String
Dim BG As String
Dim FT As String
Dim idx As Integer
Dim i As Integer
InitStandard()
idx = CInt(XRank)
For i = 1 To 10
If Standard(i, 3) = Mode Then
BG = Standard(i, 1)
FT = Standard(i, 2)
Exit For
End If
Next i
Select Case Param
Case "Background"
Return BG
Case "Font"
Return FT
End Select
End Function
Basically, I read in the Mode, loop through Standard(i,3) looking to find a
match and if/when I do hit a match I set BG = Standard(i, 1) and FT =Standard(i, 2) and exit the loop. Note that I must call InitStandard() first
to initialize it befoe I do anything. Without this call the global array is
never initialized. Additionally, I used Case here to determine if I am
setting BG or FG depending on how the function was called. Looking at the
code now, I don't think I need to pass in XRank as I never end up using idx.
Looks like I have a little bit of cleanup left.
Anyway, that wraps it up. To recap, we define a global array, use a
hard-coded array initialization function and a hidden table to populate the
global array, then we read through the array looking for our matching mode to
color code our resultant matrix. All in all not too bad. If I had been
asked to do this in our previous toolset, I would have had to say it was
impossible.
Cheers,
"asnewbie+rs=argh" wrote:
> RS List,
> Well, I managed to get all the mdx pieces of my problem resolved. Now I
> need to solve a tricky conditional formatting problem. Let me provide some
> info first and then I'll ask a few questions.
> The report I have generated is a very concise matrix. It basically assigns
> ranks to failure modes by product family and displays product families side
> by side (ascending alphabetically along columns) with failure modes for each
> family ascending by rank (rows). A simple ASCII version of the matrix layout:
> | product_family
> --
> rank | failure_mode
> yields:
> | pfC | pfE | pfF | pfH | pfJ |
> ----
> 1 | mode45 | mode13 | mode94 | mode45 | mode08 |
> 2 | mode13 | mode26 | mode13 | mode13 | mode45 |
> 3 | mode77 | mode91 | mode27 | mode74 | mode26 |
> 4 | mode26 | mode45 | mode12 | mode13 | mode39 |
> 5 | mode68 | mode94 | mode24 | mode68 | mode27 |
> 6 | mode94 | mode12 | mode83 | mode94 | mode12 |
> The report requestor needs the ability to pick a given product_family, lets
> say pfH for now, as a color coding standard. Then the top 10 modes for pfH
> will be assigned color codes (background and complementary font) based on a
> standard. Based on the standard, matching modes in all product_families are
> color coded the same. This has the effect of being able to show where
> failure modes in one family fall within all the other families.
> I have a function, setcolor() that I can use to set the background (BG) and
> font (FT) colors. Now, I need to figure out how to read the modes for a
> given family into an array (?) and then pass the array (?) to setcolor(). I
> have created a separate hidden table that houses the basis family (parameter
> driven) with its modes and I am trying to read that into an array.
> I would like to be able to have yet another hidden table in the report
> holding 10 rows with BG and FT the way I would like them. These properties
> would be read into an array along with the modes for the basis family. Then
> I would pass the modes, BG and FT to setcolor() and be fine.
> Is it possible to read BG and FT properties of a matrix cell/textbox into an
> array in code?
> Right now it looks like I'll be hardcoding the BG and FT properties in an
> array and then combining these with the modes for our basis family in another
> array. From here, I'll have to see how to get this into setcolor().
> Does anyone have any recommendations, thoughts or pointers for this.
> Thanks!