How to Create Cascading Filters

You can define cascading filters for dashboards, where the selection made in one filter determines the options available in the dependent filter.

The following code sample displays to the end user a clickable 'Generic filter' link, which opens a FilterBuilder window.

def ctx = api.getDatamartContext()
// without initial filter: def filterBuilder = api.datamartFilterBuilderUserEntry("Filter", "Transaction DM")
def filter = Filter.equal("InvoiceDateYear", "2015")
def filterBuilder = api.datamartFilterBuilderUserEntry("Filter", "Transaction DM", filter)
api.trace("filterBuilder", null, filterBuilder)

def dm = ctx.getDatamart("Transaction DM")
def q = ctx.newQuery(dm)
q.select("Country")
q.select("CustomerID")
q.select("ProductID")
q.select("SUM(InvoicePrice)", "Revenue")
q.select("SUM(Quantity)", "Volume")
q.where(filterBuilder)

def result = ctx.executeQuery(q)
api.trace("Data", null, result?.data)

return result

The following sample shows 2 dim filters (without default values) to the end user.
The (optional) underlying generic filter (country == Italy) is hidden and not modifiable by the user.
Note that the dim filter fields are in a sub-section with the user entry's name as the title.

def ctx = api.getDatamartContext()
def dimFilters = ["CustomerID", "ProductID"]
def filter = Filter.equal("Country", "Italy")
def filterBuilder = api.datamartFilterBuilderUserEntry("Italy", "Transaction DM", dimFilters, filter)
api.trace("Filter", null, filterBuilder)

def dm = ctx.getDatamart("Transaction DM")
def q = ctx.newQuery(dm)
q.select("Country")
q.select("CustomerID")
q.select("ProductID")
q.select("SUM(InvoicePrice)", "Revenue")
q.select("SUM(Quantity)", "Volume")
q.where(filterBuilder)
api.trace("Query", null, q)

def result = ctx.executeQuery(q)
api.trace("Data", null, result?.data)

return result

The following sample shows two dim filters with default values (CD-00155, MB-0005) to the end user.
The (optional) underlying generic filter (country == Germany) is hidden and not modifiable by the user (do not confuse with the section title, which is taken from the user entry name).

def ctx = api.getDatamartContext()
def dimFilters = ["CustomerID": "CD-00155", "ProductID": "MB-0005"]
def filter = Filter.equal("Country", "Germany")
def filterBuilder = api.datamartFilterBuilderUserEntry("Germany", "Transaction DM", dimFilters, filter)
api.trace("Filter", null, filterBuilder)

def dm = ctx.getDatamart("Transaction DM")
def q = ctx.newQuery(dm)
q.select("Country")
q.select("CustomerID")x§
q.select("ProductID")
q.select("SUM(InvoicePrice)", "Revenue")
q.select("SUM(Quantity)", "Volume")
q.where(filterBuilder)
api.trace("Query", null, q)

def result = ctx.executeQuery(q)
api.trace("Data", null, result?.data)

return result