User control of dashboard layouts

Dashboard sheets were introduced with V9, primarily as a way to deliver mobile-friendly reporting with a responsive UI to auto-fit any screen size. Specific Targets which define the layout can be defined to optimise the layout for different devices and are automatically applied depending on the device type.

Another use-case which is less obvious but can also be very useful is to allow users to choose between a number of predefined layouts.

For example, on a specific report there may be just 3 or 4 slicers which are typically used, but occasionally users may need access to a much larger list of slicers to filter by. It would be a shame to clutter the report for everyone permanently with all the slicers as it makes the selection process less intuitive, and probably forces us to use only combo boxes to save space. Ideally we’d like users to be able to switch from a ‘Quick Slicer’ view to an ‘All slicer’ view.

Another example would be where users want to include additional dashboard items, or remove items to get a larger view of a data table.

These scenarios and others can be handled by giving users control over which Dashboard Target is active via a slicer.

In the example shown below the button slicer allows switching between a ‘Quick slicer’ view with the 3 primary slicers shown as list boxes, an ‘All Slicer’ view with all 9 slicers available as combo boxes, and a ‘Table View’ which maximises the space for the data table and removes the charts.


So how do I…?

Firstly, you’ll need to define the various Targets which you want the user to choose between (see here for the details).

Next you need to add a slicer allowing the user to select between Target layouts. This slicer will be based on an Excel range, and will output its selection to another cell which you specify. It’s easiest if the input range for the slicer exactly matches your Dashboard Target Names (otherwise you can use vlookups to cross-match). Of course you’ll need to enable that slicer on each of the targets to allow the users to switch views.

Finally, we can use the XL3SetProperty() formula to set the active Target for the dashboard, based on the output of the slicer we just set up. The syntax is:

XL3SetProperty(Object Type, Object Name, Property to set, value to set the property to)

The screenshot above shows the slicer and formula setup – hope it proves useful for some of you!

Icon-based Navigation and Filtering in XLCubed

Version 9 introduced an embedded icon library and XL3PictureLink, which together make the creation of icon-led navigation and filtering simple.

XL3PictureLink provides the same parameterised navigation capabilities which the XL3Link() formula has done for years, but with an added visual aspect. Users can choose from one of the thousands of icons provided, in any colour, or choose a custom image as required.

The images can then be used as an intuitive way for users to navigate to another sheet within the report, while passing a dynamic parameter to ensure the data is in-context. Alternately, where there are a small number of selection choices they can be used as visually appealing slicers.

Insert PictureLink is available from the Insert Formula tab on the XLCubed ribbon.

Click the drop-down and search for an appropriate image from the picture library – you can also specify the colour by clicking the Colour drop-down.

You then select the required image, and enter the destination cell location in the “Link to” box – this is the location where the user will be taken when they click on the image (can be the same sheet or a different sheet in the report).

You can also parameterise the image – “Value” is the content  which will be inserted into the cell specified in “Range to Set” (can be text or a cell reference).

To edit a Picturelink once it has been inserted, hold down Shift and then click the image.

Note that PictureLinks do not need to pass parameters – they can used as a simple link to another location:

Lastly, on web reports XL3PictureLink can also be used instead of the standard ‘Submit’ toolbar button.

There is more information on that here.

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.)

Auto refresh in XLCubed Pivot-views and Power BI

Version 9 of XLCubed introduced Pivot-views, which allow our powerful grids, small multiples and slicers to be driven directly from lots of different relational sources.

We also added support for Power BI as a data source, and many of our customers are now using XLCubed on Power BI data for a better in-Excel experience and slice and dice.

Many of you will already be familiar with the data refresh options in Power BI, essentially either a scheduled refresh process, or through the Enterprise Gateway.

With pivot-views we wanted to ensure that  the data refresh process is very straightforward, and to enable business users to benefit from them without worrying about scheduling data flows or getting involved with discussions about gateway configuration. Fundamentally, the data will automatically update when the report is opened or if any of the query parameters change (either by updating a slicer or if an Excel cell-based parameter value changes).

This means the report builder doesn’t have to worry about the latest view of the data as that is all handled automatically without any custom code, manual intervention or IT assistance.

Here is how it works in detail.

First insert a grid and select a relational database as the source.

We are presented with the query designer, we can pick a table and setup a parameter by entering “@productgroup”:

This gives us an option to link that parameter to a cell or directly to a slicer:

We can now design the grid we want to see:

And view the result.

Now, if I update the driving cell:

The query is re-run and the grid (and anything else driven from the data has updated to reflect the new query results):

This automatic handling  of the data refresh continues once the report is published to XLCubedWeb.  Whether accessing from Excel, a browser or the XLCubed Mobile App, the report is based on current data without the need for any additional handling or refresh processing… One less thing for the report designer to worry about, one less thing to go wrong!

Rio Olympics – Medals Treemaps

Well, the Rio 2016 games have finished and we now all need to find something else to watch on TV. As always at the Olympics there was plenty to entertain and inspire. After the London games in 2012 we blogged showing the medal distribution using Treemaps. We’ve updated that for 2016 below with the corresponding 2012 equivalent:

CBS16

CBS12

The charts are split by country, and then sport where the size of the tile represents total number of medals, and the colour saturation represents the number of Gold medals. We can see immediately that the US retains a significant lead over the other nations, and also that roughly half its medals overall were won in Swimming and Athletics. Great Britain and France have seen their relative medal positions strengthen in the four years. It’s difficult to see the breakdown for countries with smaller numbers of medals, but the interactive version can of course be drilled to additional detail and we’ll make that available in the coming weeks.

Looking at things split by Sport then by country it’s as below:

SBC16

SBC12

Athletics and swimming have the most events and hence the most medals and largest presence on the Treemap. USA dominates both categories across both London and Rio, with an even stronger grip on athletics in Rio. Elsewhere China rule the diving boards, winning 7 of  the 8 events in Rio.

Team GB again did spectacularly well in Rio, and as a British company we can allow ourselves a slight bias in our coverage (a roundabout way of saying the remaining charts are just about the British team). Firstly we’ve brought the 2012 and 2016 data for GB together into one treemap as shown below.

GBNI_1612

While the mix of sports is slightly different, and in both games the team won medals across 19 sports, the core strengths remain fairly consistent. Despite that, there are some interesting movements. Gymnastics and swimming have shown the biggest improvements between 2012 and 2016. Cycling (all cycling disciplines grouped) had the same number of medals in total, but 2 fewer gold. Having said that when you start from such a high base even being close is success – when other teams are videoing your warm up / stay warm routines it’s safe to assume you’re doing something right!

Last but not least, a column chart showing overall GB medals by discipline across the two games – if you need a binary sport by sport comparison rather than contribution to total the classics still tell it best.

GB_c_1612

 

 

 

Dynamic Tooltips / Mouseover

Tooltips can be a useful addition to reporting, often used to display additional context or information which you don’t want to have permanently visible in the main body of the report. When the user sees a value of interest they simply hover over the cell and a popup displays the additional detail. The additional information can be anything required, as long as it can be retrieved from an Excel cell (or direct from the cube). Examples could include some textual information or sales for the same period last year.

Implementing this in XLCubed reports is straightforward, albeit not immediately obvious. Tooltips are implemented using the XL3Link() formula, most commonly used for in-context navigation within a report, where the focus is moved from one sheet to another while passing a parameter. A special parameter, introduced in version 8.1, extends XL3Link() for tooltips as explained below.

The syntax for XL3Link is:

XL3Link( [LinkLocation], [FriendlyName], [LinkType], [Range1], [Value1],…, [Range13], [Value13] )

LinkLocation: where the focus would normally jump to on clicking the cell. This can be left blank if you just want a tooltip.

Friendlyname: what is displayed in the cell. This could be static text (“i”) or an Excel formula referencing other cells in the workbook as needed.

LinkType: can be left blank for Tooltips.

To specify a tooltip, set Range1 to the value “XL3Tooltip”, and Value1 to be the content you want displayed in the tooltip. Value1 can be static text or can reference other cells as required, which means you can display other values which are also dynamically retrieved from the cube based on slicer selections etc. If the report is variable in length, the formula can be added into an XLCubed grid calculation and the Value1 parameter could use a vlookup to ensure a match on the appropriate data element.

The simple example below shows the basic approach, and the result also works when published to XLCubed Web.

 

s1

The extended example below shows combining several additional data fields. The line breaks are achieved using Alt + Enter.

s2

Thanks to our colleague Norbert Engelhardt at pmOne who blogged on this point recently in German and prompted this piece, basically an English version of the original:

XLCubed Insights #4 – Mouseover Funktion in XLCubed Berichten

 

 

How to gauge data through charts – Creating Gauge Charts

A common question that comes up in support for XL Cubed is how to add charts that look like a dial, or a gauge. Something like the below:

1

 

 

 

 

 

 

These are actually very easy to make and publish to the web, plus they have the further bonus of adding something different to make your reports look more professional.

Once you have your data ready, add a new doughnut chart and configure it to show the information you want it to.

2

 

 

 

 

 

 

 

 

 

This will give you a simple doughnut chart.

 

3

 

 

 

 

 

 

 

 

Next up, pick the cell that contains the information you want to show in the middle of the doughnut chart and reference it in another cell. For example, in the below example we have the two numbers that make up our doughnut chart in cells B3 and B4. Cell E3 contains the information we want to show in the middle of the doughnut chart.

4

 

 

 

 

As you can see, the formatting is different in E3 to the other cells. This is because we have formatted the cell to show the data how we want it to appear in the chart.

Once we are at this stage, it is just a case of transferring the number to the middle of the doughnut chart. You can do this by selecting the formatted cell, in our case E3, copying it and then paste special as a ‘Linked Picture’ anywhere in the worksheet (we will move it into the chart in the next step).

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The ‘Linked Picture’ appears as a cell but it actually acts like a picture so, lastly, move the picture into the middle of the doughnut chart so it looks how you want it, then, right click on the new picture and select ‘Send to Back’

 

6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As the cell is a ‘Linked Picture’ Any changes you make to the cell you copied, formatting or data, will update the image.

7

 

8

 

Your Gauge Chart is complete! These charts also look good when published to the web.

 

Calculated fields are not available in an OLAP-based Pivot Table …

One of users biggest frustrations with cube connected PivotTables is that they can’t add calculated fields like they can with a regular PivotTable – the option is greyed out on the ribbon. Data in the Pivot Table can be referenced elsewhere in Excel, but that makes the final report largely static in shape. It means users can’t add even the simplest of calculations into a report where the number of rows or columns may vary, and can lead to BI developers being asked to add every calculation imaginable into Analysis Services.

The good news is that XLCubed Grids (described by some as PivotTables on steroids) let users add any Excel formula directly into the grid. The calculations are entered as standard Excel formulae, so users already know the syntax. All Excel formuale are supported, and the calculation is cube-aware once entered as shown in the video below.

We also let users easily create workbook level calculated members and sets, but in our experience what 90% of users actually want is to be able to add a simple calculated column into a PivotTable (or in our case a grid). They’re really not asking for much, and we’re happy to help.

We can also help where PivotTable users are experiencing performance issues, hierarchy selection restrictions, “PivotTable will overwrite” messages, etc. We keep the Excel flexibility users love, and lose the PivotTable restrictions they loathe. See our PivotTable comparison for more detail on some of the common user problems we address.

Swiss Customer Event

Last week we held a very successful XLCubed user event in the Starling Conference Centre, Geneva with our partner Evolusys. These events are a great opportunity to engage with our key customers first hand to get their thoughts and feedback and also to share ideas for future development. Thanks to all who attended, it was a productive and enjoyable day

The message which shone through was that what our customers value most about XLCubed is the flexibility. The mix of Excel itself extended with a focused BI client gives a degree of flexibility that other tools just don’t come close to.  “There is always a way” was a phrase mentioned by several of the attendees, and great to hear from our perspective.

We used part of the day as the first public preview of Version 9, and we were delighted by the feedback. Extending the reach of the product to additional data sources like SQL, Power BI and Excel was extremely popular and the slick ‘panel view’ dashboards and responsive UI really resonated. We now ‘just’ need to finish development over the summer and look forward to bringing v9 to market!

Keep watching this space for more info on v9…

DAX Performance tips– lessons from the field

XLCubed has supported a drag/drop interface for creating reports against Tabular Analysis Services since the first release of the new engine. It lets users easily create reports which run DAX queries on the cube, and we’ve often seen very good performance at customers when MDX against Tabular was a cause of long running reports.

So when we were approached at SQL Pass in Seattle by some attendees who had a SSAS Tabular performance issue we were optimistic we’d be able to help.

In this case the business wanted to retrieve thousands of rows from the cube at the transactional level, and the first approach had been to use PivotTables in Excel. To get to the lowest level they cross-joined the lowest levels of all the hierarchies on the rows section which would give the right result, but performance was terrible, with several queries taking 20 minutes or more and others not returning at all.

We hoped using an XLCubed table running DAX would be the solution and created the same report in the designer. Sadly while performance was a little better it was still far from acceptable; the model was large, and the number or columns combined with their cardinality meant that a lot of work was being done on the server.

XLCubed’s DAX generator was trying to cross-join all the values from each column, which had worked well for our other customers. But when there are a dozen columns including the transaction ID things do not go so well. DAX in itself is not a magic bullet and SSAS Tabular models can hit performance problems on low level data – we needed a new approach.

After some investigation we discussed the issue and our thinking with our friends at SQLBI and determined that instead of cross-join we wanted an option to use Summarize() instead as this only uses the rows in the database, and it can access columns related to the summarized table which were required for the report.

As the customer’s report had the transaction ID in it the result wasn’t aggregated, even though we were using summarize. But we wanted to add true transactional reporting too, using the Related() function.

Finally, SQL 2016 adds a couple of new functions, SummarizeColumns() and SelectColumns(), both of which are useful for this type of reporting, but offer better performance than the older equivalents.

The end result in XLCubed is a new option for DAX tables to allow users to set the type of report they want to run, and some internal changes so that XLCubed will automatically use the most efficient DAX function where they are available.

A beta was sent to the business users and the results were fantastic. The report which had run for several minutes now completed in a few seconds, and 20 minutes was down to 15 seconds – we had some very happy users!

The changes will be in the next release of XLCubed so that all our customers can benefit from the improvements. It’s always nice when a customer request helps improve the product for everyone.

A sample of the syntax change is included below

Before:

 

EVALUATE
FILTER (
    ADDCOLUMNS (
        KEEPFILTERS (
            CROSSJOIN ( VALUES ( 'Customer'[Education] ), VALUES ( 'Product'[Color] ) )
        ),
        "Internet Total Units", 'Internet Sales'[Internet Total Units],
        "Internet Total Sales", 'Internet Sales'[Internet Total Sales]
    ),
    NOT ISBLANK ( [Internet Total Units] )
)
ORDER BY
    'Customer'[Education],
    'Product'[Color]

After:

 

EVALUATE
FILTER (
    ADDCOLUMNS (
        KEEPFILTERS (
            SUMMARIZE ( 'Internet Sales', 'Customer'[Education], 'Product'[Color] )
        ),
        "Internet Total Units", 'Internet Sales'[Internet Total Units],
        "Internet Total Sales", 'Internet Sales'[Internet Total Sales]
    ),
    NOT ISBLANK ( [Internet Total Units] ) || NOT ISBLANK ( [Internet Total Sales] )
)
ORDER BY
    'Customer'[Education],
    'Product'[Color]

The blog of XLCubed