Category Archives: Visualization

Eat, Sleep, Report, Repeat!

Repeaters are a visualisation feature introduced in v9.1.  They are effective when you want to repeat a formatted section of a report by one variable.  They can save so much time as you don’t have to go through the tedious, error-prone task of recreating the same section many times by copying and pasting manually.  Imagine the time you’d save setting the design up just once and have the repetition handled by XLCubed!

Here’s an example of a repeater:

Our repeater is based on an XLCubed grid, filtered by Geography, with a panel consisting of formulae with XLCubed In-Cell charts and PictureLinks.  Additionally, each panel is conditionally formatted, with the colouring based on ratio of 3-month and 12-month average sales.

The great thing about XLCubed Repeaters is that you can include any XLCubed or Excel content in them!

Let’s see how easy it is to design our repeater – click Visualise > Repeater on the XLCubed ribbon and in the Designer window define the hierarchy to be repeated by dragging it to the Repeater Setup area.  Similarly, you can also set up any additional filters.

Click OK and then set the content of the repeater by setting up the three key ranges:

  • Repeat range – The entire area required to generate the report for one repetition. It needs to include all the source data and formulae used in the area to be displayed – the area within the blue border below
  • Render range – the display area which will be repeated and shown in the final report – the area within red border
  • Input range – the location where the member parameter will be inserted by the repeater, the cell with a green border. This will be used as the selection criteria for XLCubed Grids or formulae within the ‘Repeat Range’.

You can move/resize the repeater, set borders and margins from the Appearance tab.

You can easily include more items in your repeater by moving them in the Render range.

Remember, you can use any Excel content – in the example below we have a standard Excel chart that we want to also include at the top of our repeater:

Drag the chart into the top of your Render Range (resize as necessary) and your chart now appears like this:

It’s as simple as that!  Set up your repeater once and no more worrying that you’ve missed vital bits of information!

For more details see the Repeaters page on our Wiki:  https://help.xlcubed.com/Repeaters

…and a YouTube video which runs through this example:

https://www.youtube.com/watch?v=d3hYF8vkz-M

For all you football fans out there, our blog on the Premier League transfer window also has an example highlighting the use of repeaters:  https://blog.xlcubed.com/2017/09/charting-the-premier-league-transfer-window/

As always we would love to hear your feedback!

Making your (Power)Point

Over the last year or so several customers have asked about the ability to export XLCubed reports directly into PowerPoint.  They were doing this manually as part of regular monthly reporting cycles, and wanted to automate what was a fairly tedious process, and to save time. We took these requests on board and are pleased to announce PowerPoint integration as a new feature in v9.1.

Enable this functionality in Workbook Options > PowerPoint settings.

Check the box to ‘Allow save to PowerPoint slideshow’ and add your slide(s).  Each slide consists of an Excel range within the report and you can change the order of your slides by dragging this icon:

You can add as many slides as required. You now simply publish the report and then from within XLCubed Web select the Download PowerPoint icon from the bank of icons on the top right of the screen:

It’s as simple as that!

A default PowerPoint template is included.  This is a standard PowerPoint file with a Master Slide to use when any new slides are created in the export.

If you have company-specific PowerPoint formats you can also set up your own PowerPoint templates, complete with Master slides, in the Repository folder on your Web server:

C:\inetpub\wwwroot\XLCubedWeb\Repository\__xlcubed__\__powerpointtemplates__

There are two types of layouts:

XLCubedNoTitle – for slides that do not require a title.  The slide must contain a ‘content’ placeholder that can be positioned and resized to the layout needed.

XLCubedWithTitle – for slides that need a title.  The slide must contain ‘content’ and ‘text’ placeholders that can be positioned and resized.

Check the Template box in Workbook options > PowerPoint settings to browse to the Repository and select the PowerPoint file to be used.

PowerPoint integration also supports Multi-Sheet web printing.  This is a really simple way to include a number of slides in your PowerPoint presentation.  For example, say we wanted to analyse and compare revenue across a number of sales regions over a period of time.

With this functionality enabled a user would be able to create separate slides showing sales revenue for the each of selected regions over each of the selected periods with the selections being made via slicers at the time the PowerPoint is created.

Multi-Sheet Web printing is enabled through Workbook Options.

There is more information on this here:

https://help.xlcubed.com/Multi-sheet_Web_Printing

PowerPoint is also available as an output option within scheduling, in addition to PDF and Excel.

To see how easy PowerPoint integration is to use, take a look at our YouTube video:

https://www.youtube.com/watch?v=IaQiBs5_R-4&t=62s

As always we would love to hear your feedback!

Charting the Premier League Transfer Window

This summer English Premier league clubs spent more than ever before on player transfers, a staggering £1.47bn in total. Some spent a lot more than others, and while PSG are making the Financial Fair Play headlines globally, the EPL clubs as a group spent more than any other league.

There are lots of ways to analyse spending, and rather than write a detailed analysis or opinion piece (as I’d doubtless end up being biased), I’ve taken the opportunity to simply present the transfer activity in a few different visualisations and readers can draw their own conclusions.

The first is a card based approach, with one ‘card’ per club. For each club we can see

  • Overall transfer activity (total revenue + total income),
  • Net transfer activity (spending – revenue)
  • A customised bullet graph showing transfer Spending : Revenue
  • Bars by player, showing incoming players (spending) in red and player sales (i.e. revenue) in blue

We’ve had some internal debate, but as the focus is on spending, actual spending is shown as positive numbers (in red) and revenue is shown as negative (blue).

I initially built the view for one club, as below, and then used a new ‘Repeater’ feature being introduced to XLCubed later this year which was a big time saver.

The repeater allowed me simply to replicate that view for the other clubs as below rather than build it 20 times. More to come on that in the next few months.

Click for a larger view. (yes, it’s a chart of two halves…)

The clubs are ordered from top left to bottom right by overall Activity. Using that approach Manchester City are top as they not only bought heavily, but also had significant sales, as did Chelsea. Perhaps surprisingly Everton are third, both due to higher spending than normal and also the sale of Romelu Lukaku for an eye-watering £76m.

Note that while this view is in many ways a Small Multiple approach, the spending axes do not have a shared scale as that makes the charts difficult to read for clubs with a smaller spend.

If we had ranked by Net spend, Manchester United would actually be top as while City and Chelsea both spent more on players, United had very little sales to offset spending.

A few other points of interest are that both the North London clubs, Arsenal and Tottenham actually had a net income over this transfer window.

The club view below is ranked by net spend, and gives an easy comparison by club.

TreeMaps can also be interesting in this context. I’ve used them here to take a look at spending by club by position, and also by age band to provide a viewpoints on where clubs have been focusing on the pitch and whether on the short or long-term.

Taking playing position first, it varies significantly across clubs. Of the 3 largest spenders Manchester City have focused most heavily on defence, Chelsea on midfield and Manchester United on Forwards (albeit on 1 expensive forward).

Club Spending by Position

Looking at age band of the players purchased, as would probably be expected the 22-25 age band is the biggest spending category for most clubs. The players are established, but their expected peak years are still to come, and their market value will likely remain high if they were sold in a few years. All Liverpool’s purchases were in this age band.

Club Spending by Age band

Clubs looking for an instant fix may also invest in slightly older players already at their peak, and the 26-29 band has the second highest level of spend.

 

The transfer window could be charted endlessly, but in the end only time will tell if the clubs have spent wisely. Although wisely is a relative term in this context of course.

 

 

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

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.

 

Report Flexibility, with Control

Sometimes we want to let report users modify the structure of a report but to govern exactly what they can and can’t do. While Grids can be restricted at a granular level to enable and disable functionality, that approach still requires some degree of product knowledge by the user.

XLCubed provides the XL3SetProperty() formula, which enables manipulation of many of the core objects such as Grids, Slicers and Small Multiples. It means report users can have simple slicer selections to change the structure of a report, what’s being displayed in a chart, or to vary the chart type. It gives flexibility within the report, but requires no product knowledge from the end user which can be crucial when delivering web reports on a widespread basis.

One common example of usage is where the hierarchy to be viewed in a grid needs to change based on the measure a user selects (depending on the structure of the cube some measures may not be applicable for all hierarchies). Typically that would need to be handled in two Grids, but we can use XL3SetProperty to bring this together, and also to give user choice on the associated Small Multiple Chart view.

The final published report is shown below:

 

S1

 

If the user selects an “Internet” measure, we show Customer Geography on rows, whereas a “Reseller” measure should show Reseller Type on rows. The same logic applies to the Small Multiple chart. In the screenshot below, the user has selected Reseller Gross Profit as the measure, and ‘Stacked Column’ as the chart type. You can see that the hierarchy on rows has been switched, as has the split within the individual charts, allowing the user to easily vary their view of the data with simple button selectors.

 

S2

 

This is implemented through the following key points:

  • A lookup table in Excel to determine what hierarchy is applicable for each measure
  • An Excel list showing the available chart types – this is used in the Chart Type slicer
    • The chart slicer outputs its selection into cell $AG$10
  • The measure slicer is linked directly to the grid and the small multiple, but also outputs its selection to an Excel cell ($A$B4)
  • A vlookup determines which hierarchy to use based on the selected measure
  • Three XL3SetProperty() formulae now control what is displayed based on user selections:
    • $AB$7 – sets the grid rows
    • $AB$8 – sets the small multiple columns
    • $AB$7 – sets the chart type

 

Formulae

 

The approach gives a deep level of access to the key XLCubed reporting objects, and enables controlled flexibility within web and mobile-delivered reports. No programming is needed, just a mid-level understanding of Excel itself, and XLCubed.

This is just one example of what the approach can achieve – it’s really limited only by imagination. See XL3SetProperty() for more detail, or contact us if you’d like the example workbook.

Click & Submit!

We’ve had a few queries recently where customers want to provide web reports with a number of slicer choices, and to have the report refresh just once when all selections are made, rather than the default refresh after each selection. It can be achieved in a couple of ways in XLCubed, read on for more…

The key to this approach work is the ‘Wait for Submit on Web’ option on the slicer properties, shown below on the Behaviour tab of the slicer designer:

sub1

This means when the slicer is changed it does not refresh the report straight away, and if you set this on multiple slicers users can then press the ‘submit changes’ button on the toolbar shown below after they’ve made their selections.

sub2

 

Alternatively, and to make it more obvious for web users you can have them click on some text or an image in the report itself to call the refresh, as in the examples below.

I’ve created a simple report below with five different slicers.  Note the “Refresh“ to the right, created using XL3Link().

sub3

 

The XL3Link statement is available from the Insert Formula menu on the XLCubed ribbon:

 

sub4

 

It’s most often used to move the focus to another area of the report while passing parameters to enabled linked-analysis in a multi-sheet report. However, here we can use it to call a refresh.

We can leave the “Link to” parameter blank, and also the Target and Value cells. The last parameter, LinkType calls SubmitChanges on the web, so the syntax will look like below (you will need to update the XL3Link statement to include this parameter):

=XL3Link(,”Refresh”,3)

There is more guidance on the general use of XL3Link on our Wiki at: http://www.xlcubed.com/help/XL3Link

So when we publish our report to our web server we can change the slicer choices as required but it’s only when we click the Refresh button that the report is refreshed.

sub5

 

If we’d prefer to display an image for the user to click on rather than text we can use XL3PictureLink in a similar way.  When using XL3PictureLink we can display any picture – we’ve used a generic refresh icon but it could easily be a more corporate-applicable image:

sub6

XL3PictureLInk is also available from the Insert Formula menu on the XLCubed ribbon:

sub7

Browse in the window above to locate the Picture file to insert and remember to check the Perform a Submit Changes on Web box.

There is more guidance on XL3PictureLink on our Wiki at: http://www.xlcubed.com/help/Picture_Links

This is the published report using XL3PictureLink, the user makes the required selections and clicks refresh.sub8

 

So it’s as easy as that – two ways to ensure that your users can change multiple slicers on web-published reports before calling the refresh, and without you having to direct them to the standard submit changes on web button.

Bump Charts in XLCubed

So today’s blog is about adding Bump Charts in Excel using v8 XLCubed.

Initially a Bump Chart looks the same as a line chart – the difference is they plot the rank position rather than the actual value.

Let’s imagine that I sell a product in a marketplace with 10 other competitors. I may like to see how the rank position of my product and the competition changes over time to check if I’m gaining or losing market position. It’s a common scenario in pharma, where we have a good customer base.

You will usually want dates on the category axis so the trends are shown across time. The series then holds the items to be compared, in this case the products.

BBC1

 

 

 

 

 

 

Our example has been set up with Measures on Headers, Product Categories on Series and Date Calendar on Categories.  For more information on using Small Multiples in XLCubed please visit Small Multiple Charts.

The currently selected measure is Reseller Order Quantities (selected though the Measures slicer)

BBC2

 

 

 

 

for the eleven months prior to April 2008 (selected through the Date slicer)

 

BBC3

 

 

 

for a subset of products.

Looking at the bump chart you can see that I’ve selected Road Bikes and Mountain Bikes for easy comparison.  You can quickly see that the rank position for Road Bikes dropped quite dramatically from May 2007, picked up again in September before dropping again in November and rising in December through to February 2008.  The change for Mountain Bikes, on the other hand, was less dramatic, rising and falling slightly, steadying in February 2008 before dropping again the following month.

To create a bump chart just select Line – Bump as the Chart Type on your Small Multiple chart. The neat part is that all the rankings are worked out for you behind the scenes, without the need for lots of complex Excel gymnastics trying to work through the full result set month by month.