Dashboard Filter Query Analytics
The following code example fetches all rows of the Product and Customer Data Sources belonging to the particular DataSlice.
Code
Filter Input Definition
def dmCtx = api.getDatamartContext()
api.inputBuilderFactory()
.createProductGroupEntry("ProductGroup")
.setLabel("Select Product Group")
.getInput()
api.inputBuilderFactory()
.createDmFilterBuilder("ProductCustomFilter", dmCtx.getDataSource("Product").getSourceName())
.setLabel("AND Custom Product Filter")
.getInput()
api.inputBuilderFactory()
.createCustomerGroupEntry("CustomerGroup")
.setLabel("Select Customer Group")
.getInput()
api.inputBuilderFactory()
.createDmFilterBuilder("CustomerCustomFilter", dmCtx.getDataSource("Customer").getSourceName())
.setLabel("AND Custom Customer Filter")
.getInput()Abort on Input Generation
if (api.isInputGenerationExecution()) {
api.abortCalculation()
}Products Query
def filterFromGroup(qapi, group, table) {
if (group == null) {
return qapi.exprs().bool(true)
} else {
def dSlice = api.getDatamartContext().newDatamartSlice()
dSlice.include(group)
return qapi.exprs().fromDataSlice(table, dSlice)
}
}
def filterFromDmFilter(qapi, filter, table) {
if (filter == null) {
return qapi.exprs().bool(true)
} else {
def dSlice = api.getDatamartContext().newDatamartSlice()
dSlice.addFilter(filter)
return qapi.exprs().fromDataSlice(table, dSlice)
}
}
def qapi = api.queryApi()
def table = qapi.tables().dataSource("Product")
def pgFilter = filterFromGroup(qapi, ProductGroup.fromMap(input.ProductGroup), table)
def dmFilter = filterFromDmFilter(qapi, api.filterFromMap(input.ProductCustomFilter), table)
def skus = qapi.source(
table,
[table.ProductID],
qapi.exprs().and(pgFilter, dmFilter)
).stream { it.collect { it.ProductID } }
def controller = api.newController()
controller.addHTML("""
<h1>Result found ${skus.size()} ProductIDs</h1>
<ul>
<li>${skus.join("</li><li>")}</li>
</ul>
""")
return controllerCustomer Query
def filterFromGroup(qapi, group, table) {
if (group == null) {
return qapi.exprs().bool(true)
} else {
def dSlice = api.getDatamartContext().newDatamartSlice()
dSlice.include(group)
return qapi.exprs().fromDataSlice(table, dSlice)
}
}
def filterFromDmFilter(qapi, filter, table) {
if (filter == null) {
return qapi.exprs().bool(true)
} else {
def dSlice = api.getDatamartContext().newDatamartSlice()
dSlice.addFilter(filter)
return qapi.exprs().fromDataSlice(table, dSlice)
}
}
def qapi = api.queryApi()
def table = qapi.tables().dataSource("Customer")
def pgFilter = filterFromGroup(qapi, CustomerGroup.fromMap(input.CustomerGroup), table)
def dmFilter = filterFromDmFilter(qapi, api.filterFromMap(input.CustomerCustomFilter), table)
def skus = qapi.source(
table,
[table.CustomerID],
qapi.exprs().and(pgFilter, dmFilter)
).stream { it.collect { it.CustomerID } }
def controller = api.newController()
controller.addHTML("""
<h1>Result found ${skus.size()} CustomerIDs</h1>
<ul>
<li>${skus.join("</li><li>")}</li>
</ul>
""")
return controllerResult
