Filters and breadcrumbs determine which data appears in a report. The level where a filter is applied affects which rows it removes, while breadcrumb selections restrict results to selected identifiers.
What is MQL
MQL is the query language used to request data in White Cup BI Reports and Dashboard Objects. Like SQL, it uses keywords and a defined structure to specify the hypercube, drilldown path, columns, conditions, sorting, and display settings.
Reviewing a report’s MQL can help identify its selections, filters, and the levels where conditions are applied.
You can access a report's MQL by clicking the settings icon and then selecting "Edit MQL..."
The MQL editor window will appear:
What is the Breadcrumb
The Breadcrumb is a trail of links showing the Report’s Hypercube and Drill Down Levels. It displays “Filtered” when a filter is applied, and the filter remains as you Drill Down. Click a level to manage totals, filters, selections, or identifiers. Click the Hypercube link to restore all totals.
How filters and selections differ
The Breadcrumb can show both filtering status and identifier selections. These restrictions serve different purposes:
| Restriction | Purpose | Example |
|---|---|---|
| Identifier selection | Limits results to selected members of a dimension. | Select one sales rep. |
| Column filter | Applies a condition to a column at a report level. | Include customers created after a specified date. |
MQL represents identifier selections as restrictions on dimension members and column filters as conditions on column values.
Why the filter level matters
Dimensions organize data by identifiers such as Customer, Sales Rep, or Product. A report can contain several levels. For example, Bill To Customer followed by Sales Rep.
In the behavior described here, the filter dialog adds conditions to the last report level. If Sales Rep is last, a condition on a customer field is applied at the Sales Rep level.
| Where a customer condition is applied | Result in the described report |
|---|---|
| Sales Rep level | Can hide Sales Rep rows beneath customers while leaving the customer rows visible. |
| Bill To Customer level | Determines which customers qualify to appear, removing those that do not meet the condition. |
A field’s name does not necessarily identify its dimension. For example, a customer’s Sales Rep Name field belongs to the customer dimension.
To apply customer conditions at the Bill To Customer level:
- Remove the Sales Rep level from the Breadcrumb.
- Add the conditions while Bill To Customer is the last level.
- Review the filtered customers.
- Drill back into Sales Rep.
How filtering relates to aggregation
Aggregation combines values into a result for each report row. The dimension levels determine how those values are grouped.
For example, a customer with $6,000 in sales under Rep A and $4,000 under Rep B has $10,000 in total sales. Drilling into Sales Rep shows those contributing amounts beneath the customer.
However, hiding a lower-level row does not, by itself, establish whether the parent amount or report total changes.
Troubleshooting
| Unexpected result | What to check |
|---|---|
| Customers remain, but their detail rows disappear. | Was the customer condition applied at a lower level? |
| One sales rep remains after removing filters. | Is that rep still selected in the Breadcrumb? |
| Parent amounts or totals differ from expectations. | How does the filter affect aggregation at each level? |
Comments
0 comments
Please sign in to leave a comment.