OnDrawingCell appears to be deleting all cell values

[gizmogal]

I am drawing a 2D able on a report which I have successfully filled with the output I want. It may be important to note that all the cell values are expressions, not channels. It looks like this:

My final step is to change the background color based on the cell value.

Following the example "Using User Commands for Trend Displays in Tables", I saved a separate script with the new command and set up just one column to use it. On testing the script, ALL non-header cell contents disappeared from the table! Now it looks like this:

The new user command is in the file HealthCellBackground.vbs as follows. Note that currently all the contents of the command are commented out, which would theoretically result in no changes to the table, and theoretically couldn't have any hidden bugs in it.

sub TabHealthOnDrawingCell(Context, Cell)
  dim dCurrVal
 
'  dCurrVal = val(Cell.Value)
'  If Context.Col >= 2 AND Context.Col <= 9 Then
'    If dCurrVal >= 100 Then
'      Cell.BackgroundColor.SetRGBColor(RGB(50, 150, 50))
'    ElseIf dCurrVal >= 25 Then
'      Cell.BackgroundColor.SetRGBColor(RGB(175, 230, 200))
'    Else
'      Cell.BackgroundColor.SetRGBColor(RGB(255, 50, 50))
'    End If
'  End If
  
end sub 'TabHealthOnDrawingCell

The command is registered as:

' Register a REPORT user function, 
ScriptCmdAdd("D:\LabVIEW\Project\DIAdem\HealthCellBackground.VBS")

The script contents for that column are:

Set o2DTableColumnExpression = o2DTable.Columns.Item(2)
Set oColBG = o2DTableColumnExpression.Settings.BackgroundColor
oColBG.ColorIndex = eColorIndexFillEffects
oColBG.RGBFilling = RGB(147,225,225)
oColBG.RGB = vbGreen
oColBG.GradientDirection = eColorGradientHorizontal
oColBG.GradientMode = eColorGradientModeFromInside
o2DTableColumnExpression.Settings.OnDrawingCell="TabHealthOnDrawingCell"

Simply commenting out the last line in the final code snippet above will restore the cell values. The images above were generated by toggling just that one comment character.

Just for sanity, I tried using the command from the example, TabTrendOnDrawingCell_Case1, and it gave the same results. Based on the evidence so far, either something is going wrong with the registration, or it's the cell contents as expressions that cause the issue.

Any ideas?

[Pesmith8]

GizmoGal

Sounds like a nice challenge.  I have done something similar, I hope what I used will be helpful.

My need was to set the color of each cell of a table to different colors.  ( it would be straightforward to also set the background color.) For my version of DIAdem this required making a different table for each row of the table.  (DIAdem 2012 only let the background/font color to be set for the whole column) Each Table that makes up what appears to be one table to the user, what built from a  report configurator class that I made. Where I can specific the location and size of the table.  (class is attached.)

It worked out nice to use Text List instead of  a channel.(mainly because the person initially building the table could fill the values up manually and see how it would look.)  Mostly these columns that I am changing are text-pass/fail columns.

I know very little of your requirements, But I would try to just set the cell colors/font once in a script.  It gets pretty hard to troubleshoot a Registered command that is in a expression.

Below is  a sample of the code that sets the font color based on the text is  text list.

Again hopefully this helps some.

Paul

The code below shows how I set the background colors.   (the code is not operational, it is just a sample of how did it)

 If Not Iteminfoget("gdPassTextColor") Then  ' color of a valid or passed text color
  Call GlobalDim("gdPassTextColor")                
End If

If Not Iteminfoget("gdFailTextColor") Then  ' color of a Invalid or Failed text color
  Call GlobalDim("gdFailTextColor")                
End If

If Not Iteminfoget("gsPassText") Then  'Text for PASS
  Call GlobalDim("gsPassText")                
End If

If Not Iteminfoget("gsFailText") Then  'Text for FAIL
  Call GlobalDim("gsFailText")                
End If
 
gsNAText   = "N/A"
gsPassText = "PASS"
gsFailText = "FAIL"

gsValidText = "VALID"
gsInValidText = "INVALID"

gdNATextColor   = RGB(100,100,100)  ' black
gdPassTextColor = RGB(50,150,90)
gdFailTextColor = RGB(255,0,0)
 
dim oTextCol, oTextCol4
    if IsReportObject("Verifications","2DTable5") then
      set oTextCol   = report.Sheets("Verificat").Objects("2DTable5").Columns.Item(5)
      set oTextCol4  = report.Sheets("Verificat").Objects("2DTable5").Columns.Item(4)
   
  
      oTextCol.TextList.Item(1).Text  = sVerifyVal
      oTextCol4.TextList.Item(1).Text = str(dBSFC_min_,"dd.d")  & " - " & str(dBSFC_Max_,"dd.d")
      oTextCol.Settings.Font.Bold = true
    end if
   
    if sVerifyVal = gsValidText  then 
      dColorVal = gdPassTextColor
    elseif sVerifyVal = gsInValidText  then
      dColorVal = gdFailTextColor 
    elseif sVerifyVal = gsNAText  then
      dColorVal = gdNATextColor 
    end if
   
    if IsReportObject("Verificat","2DTable5") then
      call oTextCol.Settings.Font.Color.SetRGBColor(dColorVal)
    end if

[Pesmith8]

aa

[Pesmith8]

aa

[gizmogal]

Hi Pesmith8,

Thanks for the hint. I went on to discover yesterday that there was an error in the expressions that may have been the culprit. I need to show a different channel group in each row of the table, and I am having trouble finding a way to do that in an expression. Currently I am looking into getting rid of the expressions and putting the data into channels which seems to fit the expectations of report tables better.

The user command does work if the expressions are adjusted to be functional (but no longer showing the desired data).

Basically the requirements are: For up to 400 DUT's, which may have gone through 0-4 tests over the course of months, show the overall health of each test. The first image above is a small sample of those results.

I will keep your method in my back pocket for a possible solution.

Thanks,

Gizmogal

[Brad_Turpin]

Hi Gizmogal,

Yeah, with 400 UUTs, it probably does make sense to create a separate grroup for the table with dedicated channels in it for each column.  Still, there are cases where referencing properties with expressions in the table makes sense.  One trick you can use in some cases is to refer to the table row index with "D2TabRow" and the table column with "D2TabCol"-- most often these are uses inside array references.

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[gizmogal]

Hi Brad,

It was D2TabRow that was causing the problem. I must be using it wrong. Here's what I'm trying to do, and along with the error below I get blanks in the rows. I think originally the D2TabRow did work until I started messing with the script to create the color background based on cell value. Right now the only way I can display a group name is to use (1) instead of (D2TabRow)

I just completed my change to create values for each test for each channel, where 100 = pass and 0 = fail. The CCh("test",0) function then gives me the mean, which is a good approximation of heatth. JPP100755B152 gets 92.5% for the Static Pressure test, which is the same thing I was calculating and storing as a property before.

Now I am struggling to properly set up the report table in anticipation of getting back to changing the background colors, eventually.

1. I want a row for each channel group, with the name of the channel group in the first column. Other than D2Tab row, is there some way to get it into the cell? I was looking at assignment channels but the help does not explain their use sufficiently.
2. For each channel in a group, I want to display the mean of the channel values. It looks like the expression "@@CCh("Static Pressure",0)@@" gets me that (curious why the same expression as data type Variable checks out okay in the text editor but results in a blank column), but I can't be sure which group's channel it's picking up. Maybe the answer to #1 will illuminate this.
3. For some reason, the Blower_Hours channel created from MyDataFinder searches in a script, displays "No Value" in the table until it's clicked on in the data portal followed by a table refresh. Is there some way in the script to "touch" the channel as if it has been clicked, or cause it to calculate? (Tried loading the navigator but that loads all the found data and will be too time consuming when all 400 DUT's are being checked.)

Looking forward to your wise words of advice!

Cheers,

Gizmogal

[gizmogal]

PS, log file says:

378  11:30:16 AM Error:
Undefined variable in "D2TabRow" command.
379  11:30:16 AM Error:
380  Invalid variable access (D2TABROW). 
     Parent object open?
381  The error occurred in object "2DTable1", type "2D-Table".

[Brad_Turpin · Accepted solution]

Hi gizmogal,

I reproduced a table with the group name expression in your post and also had trouble until I changed the scaling of the whole table to have the correct fixed number of rows.  If all you have are expressions, then there is unfortunately no way around assigning a static number to the row count of the table.  The Root.ChannelGroups.Count expression works to the right of the expression in the configuration dialog you posted, but it doesn't work in the global table scaling tab, and that has to be right.  By default, DIAdem tables list 10 rows-- my guess is that you had fewer than 10 groups in the Data Portal when you got this error.

All 3 of your table configurations are expressions, so you need to list them as expressions, not variables.  A variable would be the name of a global variable which contains the info you want to display.  Unless you've created the global variable and filled it with the value you want to show, you can't use a variable type configuration.

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[gizmogal · Accepted solution]

Hey there, Brad,

I made a lot of progress since my last post. I reviewed some more examples and noticed one that was very similar to my desired output. When I looked closely at that, I saw that the results were in their own channel group with each channel containing its row's information. So I massaged my data into the same format - all channels, no expressions. So, for instance, I have a DUT channel with 5 serial numbers, a Last Test Date channel with 5 dates, a Blower Hours channel with 5 values, and ditto for the test result success percentages. Here is the result (tadaaa!)

This is much more workable than the expressions.

My next challenge is to sort my channels before reporting on them - in excel it would be sorting the entire selection above first by "Last Test Date" and then by "DUT" so that the batches of 20 that were tested on a given day will display together.  I'm sure I'll figure it out eventually, but as always if you have a quick answer that will be much appreciated.

BTW, back to the original post, after going to this new channel format, I did introduce an error in the "OnDrawingCell" user command event script, and the result was that no formatting was done, the numbers did display, and logging showed that an error had occurred. I now feel comfortable using that mechanism.

Cheers,

Gizmogal