How’d you like that….displayed!

/ BI, Blog, Reporting, Uncategorised

Today’s blog will show you a really quick and easy way to format your grid to show different display units.

This approach is ideal for dynamic Grids where the size of the values can vary considerably based on the selected filters, or where the user has drilled down to lower levels in the data. For example, if country level numbers are in hundreds of millions, but customer level numbers are in hundreds or thousands, it can be useful to have the ability to quickly change the display units.

Using this method, you can switch quickly to using different formats.  In our example we want to give the user the option to display the measure Reseller Sales Amount as Units, Thousands or Millions.

You can see we have a slicer to the right of the grid giving the user the choice of how to display the figures.

The slicer is based on an Excel range and is not directly linked to the grid.

A couple of things to note are the ‘Update range’ and ‘Activate XL3Link’ slicer settings are checked but more of that later.

‘Enabling Update Range with selection’ means that the slicer choice is written to a cell in the workbook – in our example it’s cell $M$17 (shown in red in screenshot below) – currently Millions are selected.

The other thing to note from the screenshot above are N13:N15.  These are a series of IF statements like below which will determine how the format sheet is updated:

=IF(M13=$M$17,”*”,”NOT SELECTED”)

As M13 is not equal to M17 the Units  row will be set to ‘NOT SELECTED’.

The row containing the ‘*’ is the format which we want to be applied to the Grid.

We now need to reflect that in the format sheet. This is done by first adding three rows for the relevant hierarchy, and setting the Excel numeric format column G to units on the first row, and then thousands and millions on the other two.

We can then set the ‘Member’ cells in column E as a simple formula referencing the table shown above. For example, the Unit’s member incell E50 in the XLCubedFormats sheet is set as =Sheet1!N13.  The same process is followed for Thousands and Millions.

With the format sheet as shown above, based on the user’s slicer selection, all members will then be displayed with the predefined Millions format as ‘*’ is a wildcard and will match on all the members in the Product Categories hierarchy.

The formatting for Units and Thousands will not be applied – unless of course you have a member in your hierarchy called ‘NOT SELECTED’!

As mentioned previously, our Excel slicer is not directly linked to the grid – we need a way to tell the grid to refresh each time the slicer choice changes.

This is where XLCubed’s ActivateXL3Link and XL3RefreshObjects comes into play.

Let’s look at the XL3ActivateLink first.  As you can see from our slicer screenshot above, our slicer is set to activate the XL3Link statement in cell I7.

I7 is set as =XL3Link(XL3Address($K$7),”Set Refresh”,,XL3Address($I$6),TRUE)

As you can see from the screenshot below it sets the target cell I6 to TRUE.

Cell J6 contains the formula =XL3RefreshSheetObjects(I6, “Sheet1”, TRUE).

As I6 is set to TRUE this forces a refresh of all objects in the sheet when the slicer choice changes.

It’s that simple – so the next time your Sales Team want their sales figures displayed as units but the CEO wants to see them expressed as millions impress them with this method!

No comment? We’ve got plenty to say!

/ BI, Blog, General, Reporting, Visualization

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:

 

Using Slicers for Sheet Navigation

/ Dashboard, Excel Tricks, Reporting, UI Design

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.

Eat, Sleep, Report, Repeat!

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

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

/ BI, General, Reporting, Visualization

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

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

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

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

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

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

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

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

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

Auto refresh in XLCubed Pivot-views and Power BI

/ Analytics, Blog, Power BI

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!