BI, Blog, General, Reporting, Visualization

No comment? We’ve got plenty to say!

Leave a comment

Today’s blog will run through XLCubed’s commentary functionality.

At XLCubed we have seen a lot of customer interest in commentary and collaboration in the last couple of years.

We’re all familiar with the standard comments functionality in Excel where you add a comment to an Excel cell.

However, in a dynamic BI environment it can be limiting.  For example, say I add an Excel comment to cell $C$10, then the comment is tied to that specific cell.  If a user changes a filter selection, the numbers change but the comment does not and may not now be relevant.

This is where XLCubed commentary shines through.

By default, XLCubed comments are tied to a specific value and are made at a cell/datapoint level (or higher).  XLCubed comments are dynamic and tied to the data so if the data moves to a different location in the grid then so does the associated comment.  XLCubed comments are not workbook-specific and allow commentary to be viewed across reports.

So where could you use comments?

Potential use cases could be:

  • To warn of a temporary operational issue – for example, a failed data load
  • An at-source explanation of why a value is higher/lower than expected
  • A discussion thread where users can share insight or raise/answer questions in-context (that’s what our first screenshot shows)

To reassure all our users who might worry about permissions and the ability to see information that should be limited, XLCubed commentary access is controlled at a user/group level from the XLCubed control app.

Also, by default all grids have commentary disabled – you have to go into the grid property and enable comments

Comments for a particular datapoint eg Germany, Bikes, January 2015 can potentially be viewed in all reports that refer to that datapoint (as long as the destination grid is enabled to display comments).

Comments can be viewed and added both in the Excel Client and through the XLCubed Web Portal.

Commentary access is configured in the Web Admin tool below:

The options available are to enter comments, view comments, full control or none.

You also need to enable the grid property to allow comments:

Access comments by using XLCubed’s right-click menu > Comments.  Alternatively, comments are available from the XLCubed Grid ribbon.

XLCubed commentary supports highly-formatted text, which can be set directly in the dialog, or copied from Word.

You can include files attachments and images from an Excel range.  Both of these can be useful if you want to bring something in particular to the attention of other team members.

After adding your comments hovering over the cell will display the comment – just like standard Excel as below:

Selecting XLCubed > Comment will show the comments in an XLCubed dialog:

Comment Frames allow you to have a permanent on-report display of the comments rather than needing to hover over or select the specific cell.

These can be tied to a particular grid or to slicer selections.

The grid below has separate comments for France for January 2015 and June 2015 – the comment frame shows both comments at once:

This screenshot shows me all the comments held for the KPIs grid based on current slicer selections:

As you can see from the comment history, there’s been quite a discussion amongst the team regarding the Margin value for May 2012 for all PoS.

Remember, XLCubed commentary is fully-governed with strict control over who can add and who can view comments.

All in all, XLCubed commentary can help add context to and share insight on the numeric data shown in reporting. It can prevent many users investigating the same ‘known’ issues and provides a forum to help team members understand the data and decide on future steps to make better decisions.

We hope you’ve found this blog helpful.

Here’s a couple of useful links – the first is to the v9.1 commentary tutorial:

This is a link to our YouTube video commentary webinar:

 

Dashboard, Excel Tricks, Reporting, UI Design

Using Slicers for Sheet Navigation

Leave a comment

Slicers are normally used to change filter selections in a report, but a less well-known use is for report navigation, to allow users to jump to another location in the workbook.

Here I have a report with several sheets. I would like to hide all the tabs at the bottom upon publishing and just have a page navigation at the top of each sheet.

 To do this, I create a separate sheet containing the names of all the sheets that I want to be able to navigate between. Create an Excel slicer using this range that outputs the selected sheet to another cell.

I can now create an XL3Link that will link to whatever sheet is currently selected in the slicer. It uses an XL3Address that combines the outputted name with “!A1” as its link location argument.

=XL3Link(XL3Address(A8 & “!A1”), “Link”)

So now, changing the slicer and then clicking the link takes me to the selected sheet.

We would like the slicer to take us straight to the relevant sheet, without having to click the link after each selection. To achieve this, go back into editing the slicer and select ‘Activate XL3Link’, choosing the cell containing the link we just created.

Now, whenever the slicer is changed, the link is immediately activated and we are taken to the selected sheet.

I can copy this slicer into each sheet for easy navigation wherever I am in the workbook (right click on the slicer, select ‘Copy’ and then choose the location to copy to). Because each slicer outputs to the same cell, all the slicers stay in sync. I could of course also do this with a workbook slicer so it’s automatically present on each worksheet.

Analytics, BI, Blog, Charts, Dashboard, Reporting, Visualization

Eat, Sleep, Report, Repeat!

1 Comment

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!

BI, General, Reporting, Visualization

Making your (Power)Point

1 Comment

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!

Analytics, BI, Charts, Dashboard, Reporting, Sparklines, UI Design, Uncategorised, Visualization

Charting the Premier League Transfer Window

Leave a comment

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.

 

 

BI, Charts, Dashboard, Reporting, Table Design, UI Design, Visualization

User control of dashboard layouts

Leave a comment

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!

Analytics, BI, Dashboard, Excel Tricks, Reporting, Sparklines, Table Design, UI Design, Uncategorised, Uncategorized, Visualization

Icon-based Navigation and Filtering in XLCubed

Leave a comment

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.

Analytics, BI, Dashboard, Excel Tricks, Reporting, Sparklines, Table Design, UI Design, Uncategorised, Visualization

The Missing Link in Excel BI

Leave a comment

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

Analytics, Blog, Power BI

Auto refresh in XLCubed Pivot-views and Power BI

Leave a comment

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

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

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

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

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

Here is how it works in detail.

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

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

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

We can now design the grid we want to see:

And view the result.

Now, if I update the driving cell:

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

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

Uncategorized

Rio Olympics – Medals Treemaps

Leave a comment

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

CBS16

CBS12

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

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

SBC16

SBC12

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

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

GBNI_1612

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

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

GB_c_1612

 

 

 

The blog of XLCubed