Filters and breadcrumbs determine which data appears in a report. The level where a filter is applied affects which rows are displayed, while breadcrumb selections restrict results to selected identifiers.
Understanding how these features interact can help you filter reports accurately and interpret the results.
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 also displays filtering status and identifier selections.
- Filtered appears next to the specific level where a filter has been applied, including Detail when applicable.
- Identifier selections appear within the corresponding dimension level.
- Clicking a level allows you to manage totals, filters, selections, or identifiers.
To restore omitted totals, click the Hypercube name (such as SALES) and select Show Totals to Here. This option is available only when totals have been omitted.
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 specific 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.
Both filters and selections restrict which rows are displayed at their respective levels. However, they do not change the amounts displayed on higher-level rows.
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, a report might contain the following drilldown path:
Bill To Customer → Sales Rep → Detail
In the behavior described here, the filter dialog adds conditions to the last report level:
- When Detail is displayed in the Breadcrumb, conditions are applied at the Detail level.
- When Detail is not displayed, conditions are applied at the last dimension level, such as Sales Rep or Bill To Customer.
This distinction is important because applying a condition at the wrong level can hide detail rows without removing the corresponding higher-level rows.
| Where a customer condition is applied | Result |
|---|---|
| Detail level | Can hide individual Detail rows while leaving the Sales Rep and Customer rows visible. |
| 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.
How to apply customer conditions at the Bill To Customer level
To ensure a customer condition is evaluated at the Bill To Customer level:
- If a customer is already selected on Bill To Customer, choose Remove Selections at that level.
- Remove Detail, if shown, and the Sales Rep level from the Breadcrumb.
- Add the desired conditions while Bill To Customer is the last level.
- Review the filtered customer results.
- Use + Expand to drill back into Sales Rep without selecting an individual customer.
Important: Removing a level from the Breadcrumb also removes any filters and selections applied to that level.
Avoid clicking an individual customer's name when restoring the Sales Rep drilldown. Doing so selects that customer and restricts the report to that customer.
How filtering relates to aggregation
Aggregation combines values into a result for each report row. The dimension levels determine how those values are grouped.
In the screenshot below, Sales Rep Cooper, Jim displays an aggregated Sales amount of 345, while the visible Detail rows beneath that rep total only 48.
This difference is expected because the filter is applied at the Detail level. Filters and selections do not change amounts at higher levels, even when lower-level rows are hidden.
When Bottom Totals is enabled, those totals include only the rows still displayed at the last report level.
Note: This report contains a Sales Rep selection and a Detail-level filter. These restrictions determine which lower-level rows are displayed without changing the amounts on higher-level rows. The SALES cube row continues to display the total before filters and selections.
Troubleshooting
| Unexpected result | What to check |
|---|---|
| Customers remain, but their detail rows disappear. | Was the condition applied at Detail or Sales Rep instead of Bill To Customer? Conditions at lower levels can hide child rows without removing the customer. |
| One sales rep remains after removing filters. | Is that rep still selected in the Breadcrumb? |
| Only one customer appears after drilling down. | Was a customer selected by clicking their name? Use + Expand to drill down without selecting a customer. |
| A filter is applied at an unexpected level. | Is Detail shown in the Breadcrumb? Conditions are applied to Detail when it is displayed. |
| Parent amounts or totals differ from expectations. | Filters and selections do not change amounts on higher-level rows. Compare the parent amount with the visible Detail rows and check whether Bottom Totals is enabled. |
| Bottom Totals differ from the cube total. | Bottom Totals includes only the displayed rows at the last level, while the cube total reflects the total before filters and selections. |
| A filter or selection disappears after changing the drilldown. | Was the level containing that filter or selection removed from the Breadcrumb? |
By understanding where conditions are applied and how totals are calculated, you can better control report results and identify expected differences between displayed detail and aggregated values.
Comments
0 comments
Please sign in to leave a comment.