def SetFilterTypes():
# ***********************************************
# Purpose:
# Converts all string column filters to:
# 1) "Listbox" if more than 7 discrete values
# 2) "Checkbox" if 7 or less discrete values
# 3) "Checkbox" always for "Cohort+Dose Level" or "Cohort + Dose Level"
# 4) "Listbox" always for "Record ID"
# 5) "Listbox" always for "Subject List"
# 6) "Range Filter" for "Age"
# ***********************************************
from Spotfire.Dxp.Application.Filters import FilterTypeIdentifiers, CheckBoxHierarchyFilter
from Spotfire.Dxp.Application.Visuals import HtmlTextArea
from Spotfire.Dxp.Data import DataValueCursor
# ================================================================
# CONFIGURATION
# ================================================================
# Toggle: set to True to APPLY changes, False to only AUDIT
applyChanges = True
# Report mode:
# "page" -> page-wise separate details
# "global" -> distinct column names only (default)
#reportMode = "global"
reportMode = "page"
# Exception columns that must always be CheckBox (regardless of data table)
exceptions = set(["COHORT+DOSELEVEL"])
# Columns that must always be RangeFilter (regardless of data table)
range_columns = set(["Age"])
# Columns that must always be ListBoxFilter (regardless of data table)
listbox_columns = set(["Record ID", "Subject List"])
# Page names to skip (setup pages)
skip_pages = set(["Dashboard", "General Help"])
# ================================================================
# INTERNAL STATE
# ================================================================
unique_cache = {}
max_distinct_by_column = {}
normal_rows = {} # pageTitle -> list of rows
missing_rows = {} # pageTitle -> list of {"Message": ...}
def get_unique_count(dataTable, tableName, columnName):
key = (tableName, columnName)
if key in unique_cache:
return unique_cache[key]
if not dataTable.Columns.Contains(columnName):
unique_cache[key] = -1
return -1
col = dataTable.Columns[columnName]
cursor = DataValueCursor.CreateFormatted(col)
distinctRows = dataTable.GetDistinctRows(None, cursor)
cnt = 0
distinctRows.Reset()
while distinctRows.MoveNext():
cnt += 1
unique_cache[key] = cnt
return cnt
# ================================================================
# PASS 1: compute max distinct count per column name
# ================================================================
for i in range(Document.Pages.Count):
page = Document.Pages[i]
if not page.Visible:
continue
if page.Title in skip_pages:
continue
filterPanel = page.FilterPanel
for tableGroup in filterPanel.TableGroups:
if not tableGroup.Visible:
continue
tableName = tableGroup.Name
dataTable = Document.Data.Tables[tableName]
# Top-level filters
for fh in tableGroup.FilterHandles:
if not fh.Visible:
continue
f = fh.FilterReference
if f is None or isinstance(f, CheckBoxHierarchyFilter):
continue
colName = f.Name
cnt = get_unique_count(dataTable, tableName, colName)
if cnt >= 0:
prev = max_distinct_by_column.get(colName, -1)
if cnt > prev:
max_distinct_by_column[colName] = cnt
# One-level subgroups
for fg in tableGroup.SubGroups:
for fh2 in fg.FilterHandles:
if not fh2.Visible:
continue
f2 = fh2.FilterReference
if f2 is None or isinstance(f2, CheckBoxHierarchyFilter):
continue
colName2 = f2.Name
cnt2 = get_unique_count(dataTable, tableName, colName2)
if cnt2 >= 0:
prev2 = max_distinct_by_column.get(colName2, -1)
if cnt2 > prev2:
max_distinct_by_column[colName2] = cnt2
# ================================================================
# Helper for PASS 2
# ================================================================
def process_filter_handle(fh, pageTitle, tableName, dataTable,
normal_rows, page_found_musthave):
if not fh.Visible:
return
f = fh.FilterReference
if f is None or isinstance(f, CheckBoxHierarchyFilter):
return
colName = f.Name
# Track must-have presence
if (colName in exceptions or
colName in range_columns or
colName in listbox_columns):
page_found_musthave.add(colName)
uniqueCount = get_unique_count(dataTable, tableName, colName)
globalDistinct = max_distinct_by_column.get(colName, uniqueCount)
expectedType = None
expectedCheckbox = False
if colName in range_columns:
expectedType = "RangeFilter"
elif colName in exceptions:
expectedCheckbox = True
expectedType = "CheckBoxFilter"
elif colName in listbox_columns:
expectedType = "ListBoxFilter"
else:
expectedCheckbox = (globalDistinct != -1 and globalDistinct <= 8)
expectedType = "CheckBoxFilter" if expectedCheckbox else "ListBoxFilter"
if getattr(f, "TypeId", None) is None:
currentType = ""
else:
currentType = str(
f.TypeId.Name.replace("Spotfire.", "").replace("Filter", "")
)
expectedTypeForCompare = expectedType.replace("Filter", "")
if applyChanges and currentType != expectedTypeForCompare and getattr(f, "TypeId", None) is not None:
if expectedType == "RangeFilter":
f.TypeId = FilterTypeIdentifiers.RangeFilter
elif expectedType == "CheckBoxFilter":
f.TypeId = FilterTypeIdentifiers.CheckBoxFilter
elif expectedType == "ListBoxFilter":
f.TypeId = FilterTypeIdentifiers.ListBoxFilter
currentType = str(
f.TypeId.Name.replace("Spotfire.", "").replace("Filter", "")
)
is_match = (currentType == expectedTypeForCompare)
currColor = "green" if is_match else "red"
if pageTitle not in normal_rows:
normal_rows[pageTitle] = []
normal_rows[pageTitle].append({
"Column": colName,
"UniqueCount": uniqueCount,
"CurrentType": currentType,
"ExpectedType": expectedTypeForCompare,
"Color": currColor
})
# ================================================================
# PASS 2: audit + apply
# ================================================================
for i in range(Document.Pages.Count):
page = Document.Pages[i]
if not page.Visible:
continue
if page.Title in skip_pages:
continue
pageTitle = page.Title
filterPanel = page.FilterPanel
page_found_musthave = set()
for tableGroup in filterPanel.TableGroups:
if not tableGroup.Visible:
continue
tableName = tableGroup.Name
dataTable = Document.Data.Tables[tableName]
for fh in tableGroup.FilterHandles:
process_filter_handle(fh, pageTitle, tableName, dataTable,
normal_rows, page_found_musthave)
for fg in tableGroup.SubGroups:
for fh2 in fg.FilterHandles:
process_filter_handle(fh2, pageTitle, tableName, dataTable,
normal_rows, page_found_musthave)
must_have_cols = exceptions.union(range_columns).union(listbox_columns)
missing_on_page = must_have_cols.difference(page_found_musthave)
if len(missing_on_page) > 0:
if pageTitle not in missing_rows:
missing_rows[pageTitle] = []
for colName in sorted(missing_on_page):
missing_rows[pageTitle].append({
"Message": "Configured Filter '%s' is NOT present on this panel." % colName
})
# ================================================================
# BUILD HTML
# ================================================================
html_parts = []
if reportMode == "page":
# Page-wise output in dashboard order
for i in range(Document.Pages.Count):
page = Document.Pages[i]
if not page.Visible or page.Title in skip_pages:
continue
pageTitle = page.Title
if pageTitle not in normal_rows:
continue
html_parts.append(
'<span style="font-weight:bold;font-size:13px;color:blue;">Page: {}</span><br>'.format(pageTitle)
)
for row in normal_rows[pageTitle]:
colName = row["Column"]
uniqueCount = row["UniqueCount"]
currentType = row["CurrentType"]
expectedType = row["ExpectedType"]
color = row["Color"]
line = (
'<span style="margin-left:20px;">'
'Filter: <b>{col}</b>; '
'Unique Count: {uc}; '
'Current Type: <span style="color:{c}">{ct}</span>; '
'Expected Type: {et}'
'</span><br>'
).format(
col=colName,
uc=uniqueCount,
c=color,
ct=currentType,
et=expectedType
)
html_parts.append(line)
html_parts.append('<br>')
else:
# Distinct-column-only summary
global_summary = {}
for pageTitle, rows in normal_rows.items():
for row in rows:
colName = row["Column"]
uniqueCount = max_distinct_by_column.get(colName, row["UniqueCount"])
currentType = row["CurrentType"]
expectedType = row["ExpectedType"]
color = row["Color"]
if colName not in global_summary:
global_summary[colName] = {
"UniqueCount": uniqueCount,
"CurrentType": currentType,
"ExpectedType": expectedType,
"Color": color
}
html_parts.append(
'<span style="font-weight:bold;font-size:13px;color:blue;">Filter Summary (distinct columns)</span><br>'
)
for colName in sorted(global_summary.keys()):
info = global_summary[colName]
uniqueCount = info["UniqueCount"]
currentType = info["CurrentType"]
expectedType = info["ExpectedType"]
color = info["Color"]
line = (
'<span style="margin-left:20px;">'
'Filter: <b>{col}</b>; '
'Unique Count: {uc}; '
'Current Type: <span style="color:{c}">{ct}</span>; '
'Expected Type: {et}'
'</span><br>'
).format(
col=colName,
uc=uniqueCount,
c=color,
ct=currentType,
et=expectedType
)
html_parts.append(line)
# Missing exceptions at bottom, grouped per page
if missing_rows:
html_parts.append('<hr>')
html_parts.append(
'<span style="font-weight:bold;font-size:12px;">_Missing Exceptions</span><br>'
)
for i in range(Document.Pages.Count):
page = Document.Pages[i]
if not page.Visible or page.Title in skip_pages:
continue
pageTitle = page.Title
if pageTitle not in missing_rows:
continue
# Extract distinct filter names from messages
missing_filters = []
for row in missing_rows[pageTitle]:
msg = row["Message"]
start = msg.find("'")
end = msg.find("'", start + 1)
if start != -1 and end != -1:
missing_filters.append(msg[start+1:end])
else:
missing_filters.append(msg)
if not missing_filters:
continue
uniq = sorted(set(missing_filters))
if len(uniq) == 1:
combined_msg = "Configured Filter '{}' is NOT present on this panel.".format(uniq[0])
else:
combined_msg = "Configured Filters '{}' are NOT present on this panel.".format(", ".join(uniq))
html_parts.append(
'<span style="color:red;">Page: {}</span> - '
'<span style="color:red;">{}</span><br>'.format(pageTitle, combined_msg)
)
HtmlOut = "".join(html_parts)
SpecLogOutput("Set Filter Types", HtmlOut)