Backstop reports are hugely useful in getting data out in a meaningful way. The Report Builder packs lots of firepower, although the user interface (UI) reminds me of the blockbuster Back to the Future (1985).
One feature on my Christmas wishlist (still pending) is the capability to re-order filtering criteria. Until that functionality is added, we can hack around it using Advanced Query Expression. It isn’t heavy coding but be prepared to play around with a bunch of curly braces and parentheses!
Let’s start gently and build a report to grab all Contacts with Company Name containing ABC.
Easy peasy. We get results after a couple of minutes, depending on the size of your dataset. Without a Contact Type filter, both Organizations and Persons are returned, as expected.
This report will take longer as your data grows. We will cover optimization tricks to maximize reporting performance in the next few steps.
How best do we grab only Organizations? Simply append the second filter? That is technically sound, but performance will be compromised. Think about it: is it faster to find a missing sock fresh out of the dryer, or after the pile has been neatly sorted in His/Her piles?
In this specific instance, the report gains speed by first screening out a large number of records upfront (i.e. Persons) before doing a text search, which is an expensive computing operation.
One last fine (and nerdy) point on performance: a number of the built-in columns are indexed in database to optimize searching. Prime examples are Backstop Party ID, Contact Type, and Categories. Filtering by these indexed columns (and earlier) will drastically enhance speed.
Long story short, we are to filter first by Contact Type, then Company Name.
With only two filters, manual shuffling those filters is still viable. Ideally, you simply drag the second filter up one slot. Hope the Backstop product team will bring upon an early Christmas!
Anyhow, click on View Advanced Filter Options to reveal the expression textbox.
Coming from a relational database background, I wish the Report Builder leveraged SQL (Structured Query Language). Nonetheless, these expressions are virtually self-explanatory. Backstop has kindly added a glossary next to the expression windows as a reminder.
We need to swap the ordering of the two filters, such that Contact Type appears before Company Name. After a couple of attempts, you ought to have the new expression like below. Then hit Validate Filter Expression. Congratulations. You just pulled off an advanced query expression!
Things escalate fast. We are now tasked to revise the report below to include Organizations containing ABC, DEF, and XYZ.
This report uses a Filter Group (beyond the scope of this article). Shuffling that grouped filter requires enough mouse clicks to cause arthritis. Let’s tap into our newfound knowledge of expressions. You are fast becoming a Backstop Reporting Jedi!
Also published on Medium.