Format background color of cell based on value

[Durai26]

Hi,

How to format the background color of the cell if the value of cell has specified text?

Ex: background color the cell which has value as "Pass" and "Fail".  Pass==> green color and Fail ==> red color

Here the report template has many columns, many tables and multiple pages page and overall report has many pages, how can we achieve without explicitly mentioning the names of table.

Anywhere in a table & in any page of reports, this formatting condition should apply.

I can use ondrawing.cell command. But for that we need to mention the table name and column. Is there any other option?

something like, if anywhere in a report a word "pass" appears in a table, apply green background color for that cell. Similarly for fail condition too.

regards,

Durai 

[AnJalpaka · Accepted solution]

It is not necessary to check the table names in the event. You can call the same event for each table, which will color each cell for all tables if it contains the desired texts. For this to work on all sheets in your layout, the Drawing function must be assigned to all tables once. When the layout is saved afterwards, the event for all tables is included in it.

Proceed as follows.

Step 1: This step is required only once when you save your layout afterwards. Execute the following function that assigns the Drawing event to all tables on all pages.

sub SetOnDrawingEvent
  dim Sheet, Ctrl, Column
  
  call Report.LoadLayout(CurrentScriptPath & "yourLayout.TDR")
  
  for each Sheet in Report.Sheets
    for each Ctrl in Sheet.Objects
      if (Ctrl.ObjectType = eReportObject2DTable) then
        for each Column in Ctrl.Columns
          ' Ensure that no existing event is overwritten.
          if Column.Settings.OnDrawingCell = "" then
            Column.Settings.OnDrawingCell = "OnDrawingPassFailCell"
          end if
        next
      end if
    next
  next
end sub

call SetOnDrawingEvent

Step 2: Save the following event. For example in "ReportEvents.vbs".

Sub OnDrawingPassFailCell(Context, Cell)
  dim crGreen : crGreen = RGB(0, 155, 101)
  dim crRed : crRed = RGB(255, 128, 128)
  
  if Cell.Value = "Pass" then
    Cell.BackgroundColor.SetRGBColor(crGreen)
    Cell.Font.Color.SetRGBColor(vbWhite)
  elseif Cell.Value = "Fail" then
    Cell.BackgroundColor.SetRGBColor(crRed)
    Cell.Font.Color.SetRGBColor(vbWhite)
  end if
End Sub

Step 3: Register the file ReportEvents.vbs as a user command file.

call ScriptCmdAdd("ReportEvents.vbs")

Step 4: Reload your report layout or refresh it with F5 or via script Report.Refresh.

The information of registered user command files is part of the Desktop file. If you save your desktop file (DDD), you do not need to do the registration with ScriptCmdAdd again later.

[Durai26]

Hi AnJalpaka,

Everything is working fine as per your suggestion, but continuously I am getting below error while doing copy paste operations in DIAdem. How to remove the event, once everything is done?. where to see which events are currently active?

I didnt see any user comments loaded currently.

regards,

Durai

[AnJalpaka]

Hello Durai,

you have saved your REPORT layout containing the event OnDrawingPassFailCell and DIAdem tries to use this event when you load your layout, but it cannot find them, because you have no registered script which includes it. You have to register your script which includes the event. If you want to keep the registration permanently and not repeat it after each start, you should save the desktop file you use. From then on, DIAdem will always load and find the event when a REPORT layout uses it.

Other layouts that do not contain events will not cause the error message. So you don't need to remove this from other layouts, because it is not automatically included there.

Unfortunately, there is no dialog that shows you all the used events in a layout. Only the events OnInitializeSheetRefresh and OnFinalizeSheetRefresh are shown in the Layouts Parameters settings dialog.

[Durai26]

Hi,

Thanks for your suggestions.

I am still bit confused. I don't want to load any usercommand [event OnDrawingPassFailCell ] on loading the layout. I want to load the required usercommand through the script. 

currently any layout I load, getting an error "Cannot execute event because the "OnDrawingPassFailCell" identifier was not found."

How do I avoid this error? Based on layout used, i want to choose the usercommand to be used through script.

How can i stop searching for "OnDrawingPassFailCell"for all layouts?

any suggestions please.

regards,

Durai

[AnJalpaka]

Hi Durai26,

Layouts that do not contain the event will not result in an error message when copying a REPORT object. Unless you have also executed my mentioned function SetOnDrawingEvent for other layouts and saved the layouts afterwards. If this is the case, you can remove the event from the layout by script as follows. After that you must save your layout again or add the Save command (Report.SaveLayout) to the function.

sub RemoveOnDrawingEvent(LayoutFile)
  dim Sheet, Ctrl, Column
  
  if LayoutFile <> "" then
    call Report.LoadLayout(LayoutFile)
  end if
  
  for each Sheet in Report.Sheets
    for each Ctrl in Sheet.Objects
      if (Ctrl.ObjectType = eReportObject2DTable) then
        for each Column in Ctrl.Columns
          ' Remove event function name
          if Column.Settings.OnDrawingCell <> "" then
            call LogfileWrite("Remove event: " & Column.Settings.OnDrawingCell & " in " & Ctrl.Name)
            Column.Settings.OnDrawingCell = ""
          end if
        next
      end if
    next
  next
end sub

call RemoveOnDrawingEvent(CurrentScriptPath & "yourLayout.TDR")

 The function prints in the DIAdem logfile each OnDrawingCell event that was removed.

For those layouts which use the OnDrawingEvent to highlight pass/fails cells you can add your user command script which includes OnDrawingPassFailCell with the following script command. Add this command to your script which loads the layout.

call ScriptCmdAdd(CurrentScriptPath & "yourReportEvents.vbs")