IRIS Dashboard/Deepsee Queries

Question

Question

Virat Sharma · Apr 29

#Analytics #InterSystems Reports (Logi) #Caché #InterSystems IRIS #InterSystems IRIS BI (DeepSee)

Hi Everyone,

We are using SQL based KPI's to display reports in IRIS dashboards. We have applied the filter functionality in these SQL based KPI reports.

I need your help for below scenario.

Below is my full report. Filter is applied on Name and Subject Name in this report.

Now I have used filter on Name and selected values 'Virat' and 'Big Show'

2 entries were displayed from 5 records.

Now when I look in Subject Name Filter, it still display all the distinct values of Subject Name.

I want this to display only Chemistry and Social (as Name filter got applied).

In documentation, it is mentioned that we need to use %OnGetFilterMembers() to implement this functionality but I am unable to do so.

I have used below code for filter in KPI class

and used below method to modify the SQL to append filter logic.

Thanks in advance!

Product version: IRIS 2023.3

$ZV: IRIS for Windows (x86-64) 2024.1 (Build 244U) Tue Feb 20 2024 17:32:13 EST

Discussion (1)2

Log in or sign up to continue

score 0 · Answer 1 · 2024-04-29T13:31:45-04:00

Hi Virat,

In %OnGetFilterMembers, you will have access to pFilter, which will contain the name of your filter, "SubjectName" in this example. You will also have access to pMembers, which is an array of members that the SearchBox dropdown will use in the UI. By default, this will contain the results from sql="SELECT DISTINCT SubjectName FROM BI.Invoice". You will also have access to pFilterValues, which contains an array of already selected values. In your case, "Virat and Big Show". You would then need to use this information to remove values from the pMembers array to reduce the available members.

By default, IRIS BI does this "member filtering" for you. The use of KPIs is very customized, so it requires some additional custom logic to allow this behavior