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

Groovy

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

Groovy

if (api.isInputGenerationExecution()) {
    api.abortCalculation()
}

Products Query

Groovy


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 controller

Customer Query

Groovy


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 controller

Result

See Also