//Many would believe that Excel is a static environment and not suited for making responsive mobile reports. XLCubed users know better. With XLCubed’s Dashboard Designer, it is possible to create dashboards that are not only interactive and data-connected, but also responsive to fit any device.
Continue reading “Dashboard Designs: Responsive Navigation Bars”
//One of the biggest improvements in 9.2 is undoubtedly in the area of interactive charting. We’ve hugely extended the capabilities of Small Multiples through a new charting engine which brings rich interactive Visual Analytics to Excel (and web, and mobile…).
The ‘Small Multiple’ concept of many charts with a shared axis is very powerful, but in some cases users just need a single interactive chart and 9.2 caters for both scenarios. We have added zoom controls, sliders and a play axis to help users quickly focus in on and further explore specific areas of interest within the chart.
//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.
//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.
//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.
//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!
//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.
//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. Continue reading “Charting the Premier League Transfer Window”
//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!
//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.
