Some Excel BI myths debunked: #3 – limited dashboards

#3: Limited and difficult to Maintain Dashboards

Third on our list of common criticisms of Excel focused BI, is the limitations of Excel Dashboards.

“Excel dashboards are ugly, limited, and inflexible…”

It’s possible to build a truly awful dashboard in pretty much any dashboard tool. No tool is magic, ignoring the Doctor’s Sonic Screwdriver of course, and if you make bad design choices when building a dashboard the end result can be a mess. Similarly you can build a pretty decent dashboard in most tools. So even in base Excel with no additional software you can build a dashboard which looks good, and many people do.

In native Excel there are undoubtedly some limitations around the available chart types, and the handling of dynamic charting. However you do have the benefit of very fine grain control over the layout and positioning of tables and charts. The camera object also lets you break out of the fixed column width which is sometimes seen as a limitation.

XLCubed extends the core charts available in Excel with a rich library of in-cell charts, small multiple/trellis charting, mapping and TreeMaps. It means you can deliver more in Excel visually, rather than have to leave the environment totally. Dashboards mean different things to different people, for some a dashboard can be a table with a chart, but most contain significantly more than that. The example below uses a mixture of native Excel charting and XLCubed in-cell charts.

FinanceSShot

It’s based around a sample personal finance data set, and brings a lot of information together in hopefully a visually appealing and effective way.  If you want to build a highly formatted and relatively densely populated dashboard like this, it’s going to take more than a few minutes in any tool, no matter what the marketing says. In reality you’ll most likely struggle to get the exact layout in a widget based dashboard tool as you lose some of the fine-grain control over table and chart sizing which you have in Excel.

Dashboards can be fundamentally simpler than the first example, but require more specialised chart types like the example below. In this case it’s a dashboard built in XLCubed Excel Edition and published to the Web, looking at fuel pricing for a downstream oil company (fictitious data). It’s a ranked table of data for a selected county in Florida, and is then using an extended boxplot to display the price distribution in the market, and a map to show the Revenues and Volumes geographically.

RampMap

One major issue with Excel dashboards can be the maintenance. If it’s an Excel-only dashboard, bringing in the new data, and checking all the links can be a time consuming process. In an XLCubed environment the cube is updated behind the scenes and the next time you open the report you’ll get the updated data, the ongoing burden of maintenance is largely removed.

So in summary, Excel when well used, is a very good dashboard tool, and XLCubed extends the capability further still in terms of available chart types, flexibility and maintenance.

Some Excel BI myths debunked #2: Inflexible Charting

#2: Inflexible Charting

Continuing our discussion of common criticisms of Excel focused BI, let’s take a look at charting.

“Excel charts are static, inflexible and you need to start from scratch if you want to change them.“

The flipside is that everyone knows how to use them, and in reality many charts in business reporting are in effect static – the numbers being charted change, but the chart layout and number of elements being charted stays the same.

Of course there are cases when charts can vary considerably with the data, or perhaps you would like to be able to drill into more detail on the chart, or to quickly display multiple charts split by one variable. Excel charting can’t handle those scenarios, but XLCubed caters for it through Small Multiples.  The example below depicts river water quality in different regions of England. It could be built in native Excel, but would be a painful and time consuming process. With XLCubed it’s a drag and drop process in our small multiple designer.

waterqualitysmalt

If the number of regions being reported changes, the number of charts being plotted will automatically stay in sync, and there is a direct data connection rather than having to maintain Excel ranges etc.

Sometimes with charting small is beautiful. Perhaps we just want the key numbers with a Sparkline alongside, or a bullet graph or bar chart to display actual to target. Native Excel 2010 and 2013 can handle the Sparkline, but not the ability to then drill the report and have the Sparklines extend, and there is also the issue of needing to bring the data itself into Excel before charting it.

XLCubed Grids can contain dynamic in-cell charts which build the charts as part of the query, and as such are drillable and remove the need to maintain a data range in Excel as shown below.

DrillIncell

So XLCubed brings the type of dynamic charting being described to Excel, and provides a simple web and mobile deployment option.

 

 

Current and Previous month reporting made easy

We’ve all been there. Our shiny new dashboard or report pack is finished and ready to go meet its users.  We’ve presented the key information clearly, we’ve followed all the data vis guidelines on effective charting and use of colour. We like it a lot, and we’ve thought ahead and built in lots of flexibility with slicers and managed drill paths so it can already help answer some of the questions it will doubtless raise.

It’s a little disappointing then that one of the first pieces of feedback is that the senior execs don’t actually want to use the interactivity much. They want to open the dashboard and see the current month picture (or previous month), and don’t want to waste valuable seconds selecting the month in a slicer…

Joking aside, in lots of situations it’s a sensible request, and there are various different ways to handle it in an XLCubed / Analysis Services world. Often this will be for a multi-sheet report incorporating grids, formulae, and charting elements and we need a centralised point to handle the month selection – enter the XL3MemberNavigate() formula.

MemberNav

This lets you pick a hierarchy and a level, and you can specify that you want the Last member (first and previous / next are also available). It’s available in the XLCubed Insert Formula dialog. In our case we’d pick the date hierarchy, the month level and choose Last, generating a formula as below:

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”)

The issue is that at this point it has no concept of data, it will give you the last month available in the hierarchy, not the last month with data. However that’s just another parameter away, we can add dimension member pairs to force a data check, as below, where we are checking that data exists for the “Reseller Sales Amount” measure.

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”,0,”[Measures]”,”Reseller Sales Amount”)

So that will return the last month with data, and as we know in XLCubed all grids, formulae and XLCubed charts can be based off a cell. The Xl3MemberNavigate() cell becomes the driver for all time selections in the report. Job done. Or is it? What if you actually wanted the last complete month? :

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”,2,“[Measures]”,”Reseller Sales Amount”)

Adding the addional ‘2’ parameter means it will go back an additional month, hence giving you the last completed month.

In our experience this is far and away the easiest way to handle current or Previous month reporting, and we hope you find it useful if it’s new to you. For more information on XL3MemberNavigate check our wiki.

 

Bandlines in XLCubed

In early January this year Stephen Few introduced the concept of Bandlines. He identified a useful extension to Sparklines, making use of shaded or coloured horizontal bands to provide more information on the context of the trend line itself. See Stephen’s article on Bandlines and the thinking behind them for a detail description.

The Sparklines are ideal for showing individual trends in a small amount of screen real estate, and we use them extensively in dashboards, typically in a ‘visual table’. By definition Sparklines are small, and to make the trend easily readable, they are typically scaled individually so that each Sparkline uses the whole vertical axis. This means they do not give any impression of the scale of the numbers involved across different rows. It’s possible to use a common scale, and while sometimes that works more often it means many of the rows with smaller values are excessively flattened.

Bandlines address this by introducing horizontal shaded areas depicting the lower, middle and upper quartiles and the median represented by a line. The user can determine the context of the bands. The two most common examples would be plotting recent trend in the context of a longer period, or plotting individual rows in the context of the overall set of data being displayed.

We think Bandlines add real value, so hats off once again to Stephen, and we’re pleased to say that Bandlines are now available in the current version of XLCubed (see here for more detail).

The screenshots below show two examples, displayed in two colour schemes.

 

Bandlines3

The charts depict historic margin by store. The ‘Banding across all stores’ charts show the 30-day trend for the individual store, set in the quartile context of the data for all 11 stores in the table. We can see that for the Gilroy store in row 1, while the margin has varied, it remained in the top quartile when set against all stores for almost the whole period.

The ‘Banding by store, 90 days’ charts show the individual 30 day trend, set in the context of the previous 90 days for the individual store. This helps provide much more historical context, but the line itself still focuses on the more recent trend. Stockton is probably most noteworty here as across the 30 day period it has dropped from the top quartile into the 1st quartile across the whole 90 day period.

We’d love to hear your thoughts (and also which colour scheme works best!), we will also be adding Sparkstrips in the near future so watch this space.

 

 

 

 

XLCubed as an alternative to ProClarity

With the launch of 7.1 of XLCubed Excel Edition we introduced the ability to import ProClarity Briefing Books – with support for ProClarity ending this year and many customers looking for a replacement, now is a great time for us to show you how the import works to help move users from ProClarity to an alternate solution.

Importing

Let’s start with importing from ProClarity, we’ve built a simple example briefing book based on the usual AdventureWorks sample cube, it includes a sample grid:

 

a performance map:

 

and a chart:

 

To get to the import option we load Excel and select XLCubed -> Extras -> Import -> Import ProClarity Briefing book. After selecting the file to import we are given a summary of each item that is going to be imported:

 

At this point you can control the resulting worksheet name, as well as switching the type of XLCubed object you’ll end up with. Clicking “Import” will now give us 1 sheet for each briefing page:

 

You’ll notice that the import process has created any required slicers so the report is good to go. You could now also spend a bit more time adding any extra XLCubed functionality to the report such as Incell charts or Excel calculations to leverage the power of Excel or publish to XLCubedWeb for consumption by a wider audience.

The import process is very straight forward and we have some great feedback from our customers regarding the speed and ease that they have been able to migrate users’ reports into XLCubed.

Look out for some more blogs showing other features of XLCubed that will help users transition from ProClarity!

Creating rounded corners in Excel – revisited

Today we’re revisiting one of our more popular guides, Creating rounded corners in Excel Tables, and have updated it for v7.1. When Igor Asselbergs was contemplating the value of round corners in design, he came to the conclusion that in many cases they added real value to the user experience.

The effect can be explained by the Gestalt Law of ContinuityGestalt is a set of rules based on research into perception psychology, and a very powerful tool for Excel table design. In table design this effect can help us to see the table columns as a unit.

The previous process to create rounded corners in Excel tables required quite a bit of persistence and patience. In Version 7.1, we’ve introduced a feature to enable adding rounded corners in a few seconds rather than several minutes, so while the theory is identical the implementation is much improved. Take this report showing sales KPIs, where we would like to add rounded corners to the header row in the table.

To do this we first highlight the required area:


Then we go to Extras -> Add/Edit Round Corners:


The Colours and Border thickness will be picked up from the selected cells. Select the corners to be made round (in this case the Top Left and Top Right corners):


Click OK to apply the borders

 

To edit existing corners which were created by XLCubed then you can just highlight the cell or range and Go to Extras -> Add/Edit Round Corners. The changes will be applied to the existing corners (or the corners can be removed by unselecting them).

It’s a simple addition to the product which would have saved us quite a bit of time in customer implementations over the years, and hopefully now does the same for our users.

Olympics Treemap

The 2012 London Olympics have now finished, and as a UK company we were pleased to see the games were such a success, and of course that team GB did so spectacularly well! We’re looking forward now to the Paralympics in a couple of weeks, and once the dust has settled there we’ll be shipping a new point release of XLCubed in September.

We’ll keep most of the changes under wraps for now, but one item which we are introducing is treemaps. The Olympic medal table gives us a nice opportunity to better understand the medal breakdown through the  new chart type. In XLCubed, treemaps can be produced directly from a cube or from a table held in Excel, as is the case here. The first example below shows the medals split by country and sport. The size of the rectangle depicts the total number of medals, and the colour shows the number of gold medals, the darker the colour the more gold. The numeric values list the total number of medals, then the number of golds. We can see the USA at the top, and that over half their medals came from swimming and athletics, with a bigger percentage of golds in the pool.

Any of the countries can be drilled into for a large view on their medal breakdown, not that we’re partisan of course… , but the view below is for Great Britain (GBR) where the particularly good showing by the cycling team stands out.

Taking a look at the same data split first by sport and then country, it’s easy to see the countries dominating the medals in each sport, and to delve into more detail by sport where required.


 Drilling into Athletics we can see that USA won most medals, and also most gold. Great Britain had just the 6 medals, but 4 were gold and hence the darker colour on their tile.

We’ll be making an interactive version of this available over the next few days.

 

 

A snappy fix for layout problems in Excel

Have you ever tried copying parts of one workbook to another and been restricted by column widths?  Or maybe you’re almost done with a report layout only to find that the last table you need to add has 4 columns, where there is only room for 3?  Today we’re going to show you how to use Excel’s Camera tool to get around any Excel column width limitations to achieve your dashboard goals!  Here we have an Excel heat Map on a separate sheet in our workbook.

It has been inserted into the dashboard below where the first thing to notice is the workbook’s  variable column widths, in particular columns J and K.  If we had just inserted our heat map as it was, the column widths in our dashboard would determine the width size of the heatmap.    Instead we used Excel’s camera tool to insert our heatmap sized at exactly what we wanted, regardless of the destination sheet’s column widths.

 

We follow these simple steps:

  • select  the heat map in the source sheet
  • click the Camera Tool icon
  •  navigate to the destination sheet
  • click and insert exactly where you want

The Excel Camera Tool is also a great way creating dynamic screenshots of particular groups of data.  The Camera Tool takes a picture of a selected area, and you can then paste that picture wherever you want it. It updates automatically, and because it is a picture rather than a set of links to the original cells, any formatting or data change in the source is automatically reflected in the picture.

The heat map chart source figures have been updated to show Europe’s higher sales – as you can see Europe now has the greater sales:

 

The dashboard heat map has updated automatically to reflect this value change.

 If you can’t see the Camera Tool on your Excel menu you can easily attach it to your Quick Access Toolbar by performing the following steps:

  • Click the File Tab
  • Click Options
  • Choose the Quick Access Toolbar Option
  • In the ‘Choose Command From’ dropdown, select Commands not in Ribbon
  • Find the Camera Tool from the alphabetical list of commands and add it to the Quick Access Toolbar.

Easy pivoting of SQL queries in Excel

So today’s blog is all about pivoting SQL query data columns.

Here we have a small sample of a SQL query report that shows us actual revenue across different products over a number of quarters.  There’s nothing wrong with the data being returned but it is pretty difficult to do any comparison analysis.

So what if your task is to report back on actual revenue across the product categories over all the quarters in this report.

This would not be an easy query to write in SQL as we don’t always know which data will be returned by a query, but that’s where  XLCubed can come to our rescue!

We just right-click on the column heading which we wish to pivot – in our case cDate and then select XLCubed and Pivot (XLCubed fills in the column heading) as below:

 

The report is now displayed as with the quarters across the page as columns.

 

This format is so much easier to read and we can quickly how each of the products are contributing (or not!) to the company’s revenue across a time period.

So that’s how easy it is to pivot column data in XLCubed.

Creating tree-view slicers in v7


XLCubed has always provided a tree view selector to let users chose items from different levels in a hierarchy.   Previously, however, it was only possible to do this directly from a cube-based hierarchy. With the extension of our SQL reporting capability in V7 we found a few scenarios where we wanted to create tree views from non-cube data. This can be easily achieved in Version 7 by using a slicer sourced from an Excel range.  This can then be used to drive reports sourcing data from cubes, Tabular models, or SQL as required.

You can also use this method to allow users to choose items from an amended structure of a hierarchy or a limited part of a cube hierarchy and this is what our example below shows:

As you can see we’re going with a food-based theme.  This Excel range needs to be in a specific format and so we have our list of slicer choices with the three required columns: key, value and depth.

Here are the slicer choices at the different levels of the hierarchy:

 

We’re happy with our list so from the XLCubed tab let’s select Slicer and then Excel which allows us to insert a slicer based on the data in our workbook.

 

 

At this window we need to tell the slicer where to find the data (slicer range) in our workbook and the slicer type – in our case a tree view.

 

In our example we are also giving the slicer a name ‘Food and Drink Slicer’  as well as instructing it to write the slicer selection to cell location $J$19.

The resulting slicer looks like this and the user’s choice can then be used to drive any report, ranging from cube-based grids to DAX and SQL tables.