Decision Point: Web-App or Not…
As developers, a question we often hear from clients—and are then tasked with answering—is “are my reporting needs significant enough to warrant building a custom web app, or is this analysis better treated with a self-service or ‘blue-ribbon’ reporting approach?” Certainly, custom web apps are expensive, but they are also well suited to serving high volume and flexible analyses to users. On the other hand, traditional reporting (encompassing both self-service and “blue ribbon,” curated reports) are quick to build and provide a focused analysis of a specific topic. So, what are our options when a use case does not fit neatly into one of these buckets? Perhaps a client would like to build a report that behaves like a web app, offering flexible in-page navigation, but would like to build it on a budget. Maybe this report is a proof of concept for a future web app or is meant to serve every department in an organization with a broad and generalized scope of analysis.
While most Business Intelligence (BI) tools do not sell themselves as providing a solution to this problem, with some extra coding and the creative use of built-in features, we can bridge this “app gap” to provide what Verstand refers to as an “app-like” experience. Curated reports, what we traditionally think of as reporting, are particularly well positioned to fill this niche, due to their centralized nature and other factors detailed below. But with constantly shifting paradigms in enterprise reporting and the increased popularity of self-service tools, how do we best advocate for this “blue ribbon” approach?
Where do curated reports fit into an organization’s larger reporting structure?
With data generation occurring at every layer in an organization, many IT departments have adopted a self-service BI approach, in which embedded, department-specific BI teams leverage centralized reporting guidelines to create their own dashboards. This lessens the development burden on the centralized BI team and allows for solutions to be more tailored to specific stakeholders—certain reports can be developed closer to the source of the data. At Verstand AI, each of us in the BI department has seen dramatic improvements in our clients’ data literacy after adding a self-service BI layer to their existing model.
At Verstand we use (or at least our CEO does) the expression, “necessary but not sufficient.” In this case, Verstand has seen the self-service approach yield significant benefits—read, “necessary”, but “sufficient” is really covered by a team providing curated “blue ribbon” reporting. Why? Curated reporting can integrate data from across departments and knowledge silos to provide holistic insights that embedded BI teams often do not have the time or ability to provide. For example, an embedded BI team in the finance department may be able to deliver incredibly topical reports about year-on-year margin growth; yet they may not be able to integrate these insights with parallel insights from the marketing department’s ad-campaign dataset. Formal reporting from a centralized team is positioned to answer exactly these questions. For example, “how did the recent change in state laws affect our marketing decisions in this region, and what are the effects on our YTD revenue,” is a question that spans several datasets and knowledge areas, that may be easier to answer by a team with a broader mandate.
Why elevate simple reporting?
But if we still need “blue ribbon” style reports, even in the paradigm of self-service BI, how might these reports be designed? Because of their formal nature and broad audience, these reports must be intuitive to navigate and designed to inspire continuity with the apps and programs a user is already familiar with. A user ought to be able to open a report and very quickly understand the “rules” to finding their insights with minimal squinting and guesswork. As BI developers, we are also tasked with telling a story through our calculations and visualizations. And because our role blends both the rigid mechanics of statistics with the creative work of graphic design, we must use every technique at our disposal to convey this “data story,” whether it be through regression analysis or the rule of thirds.
What features promote an app-like look and feel?
To create this visual continuity and add a report seamlessly into a user’s workflow, we must take inspiration from modern app design. The good news here is that, as technology users, we have a good handle on this, we likely use dozens of apps per day! Think about the design commonalities of your favorite apps, to name a few: a collapsible side menu, tabs within pages to give alternate views, use of static and dynamic graphics used to guide the user’s eye, to tell a story. Once we’ve expanded our horizons of reporting beyond a handful of visuals on a page, we as developers can begin to blur the lines between a basic report, and a self-contained reporting suite—traditionally the purview of app developers. Adding to this further, in-page navigation with linked filters allows us to create a fluid experience within our report itself where the user can navigate page to page and retain the context of their analysis.
Combining several of these features together, a user can begin their analysis on the finance page of the report suite, finding some interesting trend at a given filter context. They can then expand the side menu out to reveal a set of pages where, on clicking one, the user navigates to the inventory page. Because their filters were retained between pages, the user can continue their analysis at the same filter context, but in the reference frame of inventory rather than finance. This style of report development quite literally breaks the information silos that are often discussed as being so detrimental to decision making. The design language of side menus, pages, and tabs is also familiar to most users—spending any time on a smartphone will easily demonstrate this. By shifting our mindset, and maybe learning a few extra coding tricks, we can make our reports more accessible, fluid, and useful to stakeholders. With this objective in mind, a centralized BI team can integrate their entire collection of “blue ribbon” reports into a single self-contained network of reports, as long as they share a core set of filters.
Example: Implementing ‘app-like’ features in a Looker dashboard
For this example, we will focus on building a dynamic navigation menu, however the HTML and Liquid techniques that work here will work for many other dashboard objects as well. In turning to Looker, we have an ability to embed the Liquid templating language within LookML constants. LookML constants are a special type of field that only exist in a project’s manifest file and are typically used to store static (read: constant) values, such as a hex color code. But because Liquid can be written into a string value and executed when a constant is invoked, we can essentially transform this static field type into a dynamic field. Secondly, we must also understand how Looker passes filter values into a dashboard in the first place; if you have access to a Looker dashboard, you can try this yourself! Open a dashboard, add a filter, and then inspect the URL generated after clicking update. You will see a URL in the following format (this will look slightly different if you are self-hosting Looker):
From this URL, there are four components that we need to dissect and rebuild using Liquid, which will then be passed into the user’s navigation using an HTML formatted measure.
1. As long as you have a single Looker instance, the first constant you will need to build simply contains the static host:
2. Next, we can both tell where a user is and modulate which page they will navigate to using the value of [DASHBOARD_ID]. You will use LookML constants containing the destination dashboard ID to pass this value into a Liquid script that will append it to the static host we defined earlier resulting in:
3. Now all that is missing is the query portion of our URL (the part that comes after the question mark). This is complicated as we need a way in Liquid to determine the names of the filters (what they are literally named in the filter configuration), the fully scoped name of the underlying field in the explore, and what values the filter is holding when the user chooses to navigate. To achieve this, we will create a constant that contains our filter mapping in the format of:
We can call this constant using a Liquid FOR loop and assign the source filters and their values to the destination filters and values.
Putting all these pieces together should result in a URL string identical to the one we started with, which will be dynamically generated using Liquid every time the LookML constant is invoked. We can even use Liquid to append user attributes to this string, tailoring the URL to each user or group, on the fly. Now this script is not much use sitting in a constant all on its own. We can call this constant, and thus the entire Liquid script that will build our link, inside of a measure’s HTML parameter. Because measures are simply arbitrary calculations that are applied on top of our filter context, they do not actually need to come up with any kind of numeric result. In this example, we will create a “dummy” measure that will simply resolve to the text “placeholder” each time it is evaluated:
Looker gives us the extremely useful ability to format the appearance of a measure’s output using HTML tags (https://cloud.google.com/looker/docs/html-sanitization). Note that Looker does not require that we actually display the output of the SQL we defined in the measure, and we will use this to our advantage here. Adding an HTML parameter to our measure, creating some in-line CSS styling, an anchor tag, and then calling our newly created link generating constant will result in a measure that now looks like this:
Adding this measure to a single value visual, like a card, will resolve to the text ‘placeholder’ in every case, and more importantly render our HTML and embedded link generator. Because we do not include any reference to the SQL value in our HTML, the placeholder text is hidden from the user and they are only presented with the HTML we have defined, a styled anchor tag in this case. Clicking on this rendered link will call the LookML constant containing the link generation script we created, which will navigate the user to a destination page populated with filter values from the URL. One important caveat to note is that this will only work if your filters have the exact same names between dashboard pages.
For example, if your source page has a filter called “Date” and the destination page has a filter called “Date Filter” the values will not pass through, as our script, and Looker itself, relies on the actual names of the filters, and these do not match. Here is what the final output will look like when added to a dashboard:
While this is a very rudimentary navigation bar, it demonstrates the possibilities of combining native Looker functionality, some creative Liquid scripting, HTML, and CSS into a very powerful drilling and navigation feature. By expanding on the code outlined above, you can turn something like this example into a full navigation panel complete with dynamically changing icons and user-attribute based styling, as we have done for our clients here at Verstand AI.
If you would like to know more or are interested in building custom reporting features cost effectively, don’t hesitate to contact us!
For more information on this topic or to discuss how Verstand can help you streamline the centralized reporting at your company, contact us at firstname.lastname@example.org.