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.

 

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.

 

 

XLCubed en Français, dans Lausanne!

XLCubed is now available in French, and we’re pleased to sponsor the Swiss BI Forum in Lausanne, Switzerland on Tuesday 12th March. We look forward to meeting with customers and attendees on the day.

The addition of French brings the supported languages in XLCubed to six in total: English, German, Italian, Spanish, Portuguese and Russian. If a localised version would help in your market, we’re happy to discuss in more detail.

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.

 

 

 

 

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:

 

 

 

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.

 

 

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.