Example of building analytics for drilldown from a static report to an interactive dashboard

When analyzing data, there is often a need to look at specific indicators more thoroughly and to highlight sections of information of particular interest to a user.

 For instance, examining the data dynamics for specific regions or dates can help us uncover some hidden trends and patterns that will allow us to make an informed decision about our project in the future.

To conduct such a detailed data analysis, the most effective thing to do would be to use the "fractional analysis" method or drill-down analysis. This method allows you to "dive" into the data, moving from the big picture to tiny fragments.

In this article, we will give you a clear look at how to organize navigation with the help of drill-down techniques between reports in DSW (DeepSeeWeb) and InterSystems Reports (powered by Logi Reports) using filters in links.

Why did we choose this solution?

While performing our tasks, we faced the necessity to simplify navigation from general reporting to more detailed data, considering different categories such as dates, regions, or participants.For that reason, we decided to find a way to move from a general report to a detailed one with a specific date or other characteristics in just one click.

A solution employing filters in links proved to be the most suitable option for this task. The DSW platform provides a wide range of tools that let us flexibly configure filter parameters and pass these filters into URL links to reports. In turn, it makes the navigation between reports comfortable while preserving context for more complex data analysis.

How did we implement the solution?

To organize drill-down data analysis in DSW utilizing filters, we followed the next steps:

1. Creating reports with date detail

At the beginning of the process, we create reports that can provide more explicit information on various aspects of the data. In our case, we have built reports that drill down into the data by date, including those on individual months and days.

Then, we create a dashboard with the "New Members" graph in DSW:

 

For each level of detail, we set up corresponding reports in InterSystems Reports. For example, for the "New Members" graph report at the month level, we did the following:

• Added a date field at the month level.
• Set up a graph that displays new members by the given date.

2. Creating a link with filters

At this stage, we need to set up filters in DSW that will be applied when you click the links. In our example, the filter should select data by date. To adjust it, follow the steps mentioned below:

• You must pick the field with the "MonthYear" filter.
• Tick the "NOW" checkbox. It will set the filter to the current date.
• Click "Apply".

 

After right-clicking the graph and selecting the "Share" field in the menu, the window as shown below will appear:

In the opened window with the link, copy the link by clicking on the "Copy" button:

 

At this point, we have configured filters in DSW. They will be applied when clicking on links from the general and detailed reports. For instance, to select data for a specific date, we performed the following steps:

• Picked the field to filter by date "[DateDimension].[H1].[MonthYear]".
• Set the filter to the current date by ticking the "NOW" checkbox.
• Clicked "Apply" to create the filter.
• Copied the link that contained the filter parameters to the report in DSW with the applied filter by clicking the "Copy" button.

*Use Base64 in links with filters

To provide even more flexibility and reliability in passing filter parameters in URL links, we can use Base64 encoding. It is a convenient addition that allows us to avoid conflicts with such characters as "&" or "=" that may appear inside the filter data.

How does it work?

While in the window with the link, tick the "Filters as Base64" checkbox:

When using Base64, filter parameter values get converted to a specific format that does not contain characters that interfere with the URL link. Thus, we get a safe and reliable way to transfer data between reports.

3. Adding links

Our next step will be adding links to detailed reports in DSW and the overall Logi Reports, which will open with the desired filters.

For example, in our report with new participants, we will add an image with a link to our chart in DSW with a date filter.

• First, you need to insert an image into a page from reports in Logi Reports:

• In the context menu that appears, select the "Link..." field:

• Then, pick "URL" in the "Link Type" field:

• Finally, paste the link in the "Hyperlink" field and remove all unnecessary things to make it look like in the example below. Then click "OK":

I will decipher the dashboard link in DSW for you:

https://public-analytics.community.intersystems.com/dsw/index.html#/DCANALYTICS/Total%20member.dashboard?FILTERS=TARGET:*;FILTER:[DateDimension].[H1].[MonthYear].&[NOW]&widget=Total Members

Let's break it down piece by piece:

https://public-analytics.community.intersystems.com/dsw/index.html#/DCANALYTICS/Total%20member.dashboard - a path to the "Total member" dashboard in the DCANALYTICS space

?FILTERS=TARGET:* - a command to apply a filter to all objects on the dashboard (TARGET:*)

FILTER:[DateDimension].[H1].[MonthYear].&[NOW] - the actual filter. Filters data by [DateDimension] field, using [H1] hierarchy at [MonthYear] level. &[NOW] - means the current month.

&widget=Total Members - a command that opens the dashboard and focuses on the "Total Members" widget.

So, this is what this link is all about:

It opens the "total member" dashboard.

It applies a filter on the Date field, selecting the current month.

It focuses on the Total Members widget.

Thus, the report will show data on the total number of participants for the current month. The filter is automatically applied when the dashboard gets opened.

Links can be placed in the report as text directly, or you can include them as buttons or images.

In order to make it possible to transition between reports, we added appropriate links to the InterSystems Reports. For instance, we inserted an image linked to a DSW report with a date filter to the report with a graph of new participants. To do the same, follow the next steps:

• Insert an image into a Logi Reports report.
• From the context menu, select "Link".
• Paste the link you copied earlier and click OK.

Now, when you click the image, it opens a report in DSW with new member data for the current month.

Advantages of the realized solution

The described approach to drill-down organization using filters in references has the following advantages:

• Easy to implement - no programming or complex logic required
• Flexible - effortless to add or change filters and links
• User-friendly - one-button navigation while maintaining context
• Fast performance - data is already pre-filtered
• Cross-platform - the solution works in any browser

With this approach, we managed to add a convenient data drill-down capability for analysts. As a result, they can independently "dive" deep into reports with just a single mouse click as needed.

It significantly speeds up data analysis, allows faster identification of non-standard situations, and gives an understanding of the causes of deviations. Consequently, management decision-making becomes more justified.

Conclusions about Drill-Down Analysis capabilities

In addition to the benefits described in this article, Drill-Down data analysis provides a number of other powerful capabilities:

Deep drill-down: Users can drill down several levels into the data, allowing them to drill down to the tiniest details and identify even the most hidden patterns and anomalies.

Comparative Analysis: Drill-Down analysis allows you to compare data between different levels of detail or slices, which can come in handy when identifying differences and trends.

Interactivity: Users can change filtering parameters in real time and see how it affects the results, making the analysis more interactive and adaptive.

Hierarchical structure: If data has a hierarchical structure (e.g., an organizational hierarchy), Drill-Down analysis makes it easy to examine data at different levels of that hierarchy.

Multiple Measurement Support: Users can analyze multiple measurements simultaneously, allowing data to be viewed from various perspectives.

Create reports on the fly: Drill-Down analysis lets users create custom reports based on template reporting by adding or removing drill-down parameters as they please.

Manage access levels: You can manage user access to different levels of granularity, ensuring data security.

Process Automation: Some Drill-Down analysis tools allow you to automate filtering and transitions between reports, saving analysts a lot of time.

Compatible with diverse data: Drill-Down analysis can be applied to a variety of data sources, including databases, spreadsheets, and web services.

Monitoring and Alerts: Some Drill-Down Analysis systems allow you to customize data monitoring and receive alerts when notable changes occur.

All the abovementioned features make Drill-Down analysis a powerful tool for analyzing data and making informed management decisions. It lets you explore data at a deep level, uncover new insights, and ultimately improve the efficiency of business processes.