The Missing Link in Excel BI

When viewing a high level summary report or dashboard, users often want to delve into more detail on a specific area. In some cases that may be a drill down, in many others it may be to a different view of the data or to an entirely different report. In the XLCubed example below, users can link from any one of the summary KPIs to a detail report showing product level detail for the selected KPI.

This is a fairly common requirement in reporting. In a standard Excel context, it would be easy to add a hyperlink formula to jump across to another sheet, but that’s just part of what’s needed. In this example we need to link in the context of the selected KPI, otherwise we would need a separate sheet for product detail on each KPI, far from ideal, especially in row-dynamic reports.

This type of limitation is one reason why you’ll often see workbooks with huge numbers of worksheets, which become unwieldy and horrible to maintain.

We need hyperlink functionality but also an ability to pass parameters (and of course a way for the pivot table to accept the incoming parameter…).

XLCubed makes it straightforward for non-technical users to build this type of contextual linking into reports through the XL3Link() formula.  XL3Link has arguments which determine what is displayed in the cell, where it hyperlinks to, and what cell(s) parameters are passed from and to.

Unlike Pivot tables, XLCubed Grids and formulae can reference cell content as a filter, so the data on the ‘link to’ worksheet can update as soon as a new value is passed into the driving cell, retrieving the relevant data from whichever data source is involved.

The beauty of the approach is its simplicity. It’s something which most users can get to grips with quickly, and opens up huge flexibility in joined up reporting.

Last but not least, web and mobile deployment takes a matter of seconds. The report is published to XLCubed Web and from there browser and mobile app based users have access to the same report with the same chain of thought links. The links can be to different content in the same report, to a separate report, or a url to another application or website.

 

(This piece revisits content from our blog  from several years back the missing link part 1   . The business requirement it addresses is now even more common, and still one not handled in native Excel.)

2 Replies to “The Missing Link in Excel BI”

  1. Dear XLCubed-Blog-Team,

    Maybe you need an idea to blog about:
    We prepare to run our reports with the web edition and come to the point where we need to store some user specific information like parameters or settings.
    For example:
    – Which KPI the user wants to display in a specific tile in a dashboard? (Customize Dashboards)
    – Which was the last selected parameter for a dimension?
    – Hide / unhide settings
    – ….

    A dream would be to have the built-in possibility to define user specific settings. The value could be stored as Cookie on the web or registry setting in an Excel environment or in an SQL database….
    Generic formulas would look like:
    =XL3GetUserSetting(Section, Key)
    =XL3PutUserSetting(Section, Key, Value)
    The information should be available during initialization of a report/slicer…

    Back to here and now:
    In the nearby future we think about to implement some kind of relational table like this:
    User Section Key Value
    Krampe Parameter Month 03
    Krampe Parameter PreferedKPI OpEbit
    Krampe Display ShowDetails 1

    We will have to handle select and update via an SQL connection. I think this is feasible with XLCubed.
    How would you implement such a kind of requirement with the current toolset. Use Analysis Services writeback instead?

    Best Regards,
    Sven

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.