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.

 

 

Rolling Forecast reporting in Excel

In monthly reporting for the current year the most pertinent numbers for closed periods are the actuals, and for open or future months the forecast. In most cubes actual and forecast (or budget) are implemented as either different measures, or as part of a scenario hierarchy. This can lead to challenges in reporting, where putting both the period and the actual/forecast on the same axis means you will get duplicated months which show the forecast for periods when the actuals are already in as in the PivotTable below.

 

 RP10

We would want the report to be asymmetric (i.e. different months returned for Actual and Budget). Various tools can handle that, and it’s always been possible to achieve that in XLCubed through formulae or in a grid. What’s more difficult is to make the report fully dynamic without the need to redesign it each time. Ideally we’d want the user to be able to change a slicer to select the last completed month (or pick this up directly from the cube), and have the appropriate months reported under Actual and Budget.  Here’s how to achieve it in one grid…..

The approach uses a combination of:

  • Excel Ranges to determine the available months, and which are for Actual and Budget
  • An XLCubed Slicer to pick the ‘Last Actuals’
  • One XLCubed grid with the ‘Exclude from display’ pointing to the relevant ranges above

Let’s work through this example which uses the Finance cube on the AdventureWorks DW 2008R2 database.

We’re going to use data from FY 2006 and report Actual and Budget figures across departments.  We will use a slicer to select the last Actual month we want reported.  For the remaining FY 2006 months we will report Budget values.

Entire year of Months in the report

There are several ways to achieve this – we added an XLCubed Excel driven slicer (XLCubed – Slicer – Excel) based on the range $AA$1:$AB$12 as shown below, containing all months in FY 2006. This lets the user pick the last month for which we want to report Actuals.

 

RP1

RP3

 

 

 

 

 

 

 

 

 

The slicer is set to output its selection into $B$1.  In $C$1 the formula =VALUE(B1) converts the text output of the slicer into a number which we’ll use to calculate which months should report Actual and which Budget, see the screenshot below:

RP2

 

 

 

 

Date Table

This is an Excel range supporting the reporting logic. Row 2 contains every month in the reporting year. Row 3 assigns an incremental number to each month, 1-12.

We can then compare the value of the selected month from the slicer ($C$1) with the value for each month. Basically if the month is less than or equal to the selected last actuals month it should be reported as Actuals, otherwise as Budget.  An example formula contained in B4 for Actuals would be: =IF(B3<=$C$1,B2,””) . For the Budget row the formula has the inverse logic.

At this stage we’ve used a little XLCubed, and the flexibility of Excel to match the appropriate months to Actual and Budget. We now need to add the report itself.

We’ll create our grid – initially we set it up to report Budget and Actual figures across the year, by basing the selection for Date.Fiscal on the range containing all months for the year ( $B$2:$M$2).

We then use XLCubed’s right-click menu (Keep – Exclude From Display) to exclude members.  It doesn’t matter which slices are excluded at this point. To do this highlight ‘Actual’, followed by the first available month and then choose Exclude From Display. Repeat this for ‘Budget’ and the first available month.  We will exclude July 2005 for Actuals and August 2005 for Budget.  You will notice the red comment marker on the grid. Right-click on this cell and choose Axis – Edit, and go to the Excluded Slices tab.

RP6 RP7

 

 

 

 

 

 

 

 

The above screenshots show that we are currently excluding July 2005 from Actual as well as August 2005 from Budget.

We can then easily change the Date Fiscal selection for each scenario.  For Budget we will exclude Budget values for months in cell locations $B$4:$M$4 (as we want to report Actuals for those entries) and Actuals values for the months in cell locations $B$5:$M$5 (as we want to report Budgets for those entries).

RP8

The grid report now looks like this with Actuals being reported up to November 2005 and Budgeted for the remainder of FY 2006:

RP5

We have also set Merge Repeating Cells on (in Grid Properties).  You could do further formatting – for example by colour filling all Budget values and hiding the grid headers.

RP9

The end result is a one grid report giving the Actual:Budget month mix that we want with the monthly maintenance process a case of simply changing a combo box selection.

 

 

Excel Pareto Charts the XLCubed way!

V7.2 of XLCubed is released soon and we thought we’d take the opportunity to run through one of the new features that you’ll be seeing, Pareto Charts.

The Pareto Principle is often referred to as the 80-20 rule, that 80% of outcomes are attributable to 20% of causes. They are named after Vilfredo Pareto who lived in Italy in the 19thcentury and observed that 80% of the land was owned by 20% of the people.   Pareto charts have both bar charts and a line graph where the bars represent individual values and the line represents the cumulative total.

So how do you use Pareto Charts from XLCubed?  Very simply, within a grid you right-click on the column header to access XLCubed’s right-click menu, Grid Charts and Add Pareto Analysis.

Take this simple grid showing Reseller Sales for Product Model Categories for Canadian cities:

Right-clicking on All Products to Add Pareto Analysis brings up this window:

Click OK to return to the workbook – you will see that we have a chart showing that the top 9 cities provide some 80% of the sales.

You could also include the rolling total and percentage in your Pareto Chart.

Notice that we now also have some extra columns on the grid showing the cumulative total of all sales, the sales percentage per category and the cumulative percentage.

 

 

So that’s Pareto Charts – in a nutshell, an easy to use graphical tool which ties directly into dynamic XLCubed grids.

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.

Mind the gap!

Today’s blog is going to show you how to use XLCubed’s custom calculation functionality to create column breaks in a grid.  Imagine that you have a report that shows you Reseller Sales across Product Model Categories over a 12-month time period.

 

 

 

There’s nothing wrong with this report but don’t you think it would be nicer if there was a way to separate out each quarter block ie put in a divider column between March and April, June and July, September and October.  That would make it much easier to read and show clearly where each quarter period started and ended.

So let’s start by creating a custom calculation.  Click the highlighted icon and give your custom calculation a name – let’s call it ColBreak.  It’s connected to the Date.Calendar hierarchy.

 

Now in the Expression area enter  a blank string starting and ending with ” (double-quote).  Click OK.

To insert this into our report we now go to the Hierarchy Editor for Calendar Date – expand the All member and you will see ColBreak.

Drag this across and insert it into the report.  We will insert it after March, June and September and click OK.

 

 

The report now looks like this:

 

Now let’s format this column break so that the we don’t see ColBreak appearing as a column heading.   You need to right-click to get XLCubed’s right-click menu and then choose Format This Member.

We will choose white for the Font colour before clicking OK.

The report now looks like this with clear demarcations between each quarter:

 

 

 

Asymmetric grid reporting

A common scenario for Analysis Services reporting  is to want to present different measures for different members,  particularly in budgeting and planning. So I want a grid that shows actuals for previous month, budget for this month and forecasts for future months.

This could be achieved in the cube, by using a “Phasing” measure to switch to the different values but quite often our customers are not in control of the cube structure.

We will look at a way to achieve this from within XLCubed itself by working through an example.

So this is our initial grid – it is currently set to report both Revenue and Discount values across all quarters in 2002 – 2004.

Let’s create a couple of slicers – one for Revenue:

and a similar one for Discounts:

 

The settings tab for Discount slicer is:

You can see that this is a multi-select slicer allows which updates an Excel range with the slicer choices.

The entries in the Excel range are referred to by their ‘Unique Name’ eg for Quarter 2 2004 equates to April 2004.

The settings tab for Revenue is similar except it outputs to different cell locations:

In this example our Discount slicer choices are Quarters 2 & 3 in 2004 and our Revenue slicer choices are Quarter 4, 2004.

Now let’s set up the excluded data – remember that we when reporting Revenue rows we want to exclude the Discount slicer choices and vice versa.

Right-click on the Discount header row and then select Exclude from Display

 

You will now see a red triangle appearing in the corner for the first member of the hierarchy which has excluded data.  If you hover over this cell it displays an additional message that the rows are being restricted by members and that you should right-click to edit axis (it’s the menu option just after grid charts).

 

 

In the Axis Designer window pick the Excluded Slicers tab and click in the lower-half of the window (highlighted) – this is where we are going to define the quarters that are to be excluded on the Time hierarchy.

Select the time hierarchy and then click the box to its right (highlighted in screenshot below) and click the box next to the drop-down so that you can pick the Excel range – in our example it is cells I23 through to I28 (the Revenue slicer choices that we do not want reported as Discounts).  Clicking OK will refresh the report and only show Discounts rows for the quarters selected.

 

We now need to do the same for Revenue so right-click on the row containing the red triangle and set up the Revenue excluded slices in a similar way. Click the icon highlighted to add a new exclusion. This will be cells A23 through to A28 (the Discount slicer choices that we do not want reported as Revenue).

Click on the new exclusion row and then in lower half of screen build up the Revenue exclusion in the same way but remembering to point to the Excel range to cell locations A23 to A28.

You should end up with an Axis Designer window something like this – for Discounts exclude slices in cell locations I23 – I28; for Revenue exclude slices in cell locations A23 –A28.

 

OK to apply these changes and the report now looks like:As you can see the report shows Discounts for Quarters 2 & 3 in 2004 but only shows Revenue for Quarter 4 in 2004 and because everything has been linked to ranges driven by slicers, the user of the report can easily control the switch in measures.

 

One slicer, two reports!

So today’s blog is going to show you how easy it is in XLCubed to have a slicer driving a grid and a SQL table at the same time.  There may be occasions when some of the information you require for your report is held not in an analysis services cube but a SQL table.  So you’ve created a grid report with a slicer like below:

 

 

This is a simple report with Geography on headers and Product Model Categories on rows showing Reseller Sales Amount with the Country slicer driving the grid.  The slicer is set to update cell B9 with the slicer choice.

 

So I show this to my manager and he asks for some more detail – he wants to know what type of businesses there are in each country, their names and the number of employees.  That’s when I realise that all of this extra information is not in my cube but on a completely separate SQL table.

Not a problem for XLCubed!  I can quickly create a report that includes all this data from the SQL table.  Using the SQL option within Grids & Tables I can create a report that connects to a relational SQL data source.

Create my connection to my data source – I am selecting the AdventureWorksSDW database:

Let’s build up my SQL query – I’m using the DimReseller and DimGeography tables to return the required fields.

My SQL statement is:

Select DimReseller.BusinessType, DimReseller.ResellerName, DimReseller.NumberEmployees, DimGeography.EnglishCountryRegionName From DimReseller Inner Join DimGeography On DimGeography.GeographyKey = DimReseller.GeographyKey

This is great but it returns data for all the countries and I only want to see data for the country chosen through the slicer.  So let’s add a parameter to our SQL query.

If you look at the corner of the SQL query window you will see the parameters area – with a very helpful tip on adding a named parameter.

 

 

Let’s add the following to the end of our SQL query:

where DimGeography.EnglishCountryRegionName = @parm1

Now we can define where the Excel range is for our parameter – in our example it is cell B9.  You remember that this is the cell that the slicer has been set up to output the slicer choice.

So now when we select a country from our slicer eg United States the grid refreshes as well as the table.

 

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.