Most formatting in XLCubed can be handled though the formatting options available on the right-click menu, and today’s blog will cover some of the common use cases.
As well as all of these, one often overlooked aspect of the Advanced member selector is the member combination mode.
This enables lots of interesting scenarios, especially as you can run more advanced selections on the results of these.
Propagate across Sheets
Today’s blog will run through Propagate across Sheets – it’s an XLCubed feature that’s been around for some time but is definitely one of our hidden gems!
Propagate across Sheets is some great functionality that allows users to quickly replicate a report onto additional sheets within the same workbook or a new workbook.
A common scenario in business reporting is for different users to want reports to open with ‘their’ view on the data. So for a particular Store or business unit.
Many back-ends already support setting Default Members for different users and groups, for example Analysis Services allows this, but as XLCubed can connect to an increasing number of back-ends we can’t rely on this and sometimes a single report user may have different combinations of views on data (for example, a set of store/product group combinations)
Version 9.2 of XLCubed has added Workbook Aspects to help with this, allowing users to store their own slicer selections and quickly switch between those and also to set the default for when the report is loaded.
With this feature you can define the aspects at a report level, shared by all users, and also allow users to define their own private aspects.
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.
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.
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.)
Tooltips can be a useful addition to reporting, often used to display additional context or information which you don’t want to have permanently visible in the main body of the report. When the user sees a value of interest they simply hover over the cell and a popup displays the additional detail. The additional information can be anything required, as long as it can be retrieved from an Excel cell (or direct from the cube). Examples could include some textual information or sales for the same period last year.
Implementing this in XLCubed reports is straightforward, albeit not immediately obvious. Tooltips are implemented using the XL3Link() formula, most commonly used for in-context navigation within a report, where the focus is moved from one sheet to another while passing a parameter. A special parameter, introduced in version 8.1, extends XL3Link() for tooltips as explained below.
The syntax for XL3Link is:
XL3Link( [LinkLocation], [FriendlyName], [LinkType], [Range1], [Value1],…, [Range13], [Value13] )
LinkLocation: where the focus would normally jump to on clicking the cell. This can be left blank if you just want a tooltip.
Friendlyname: what is displayed in the cell. This could be static text (“i”) or an Excel formula referencing other cells in the workbook as needed.
LinkType: can be left blank for Tooltips.
To specify a tooltip, set Range1 to the value “XL3Tooltip”, and Value1 to be the content you want displayed in the tooltip. Value1 can be static text or can reference other cells as required, which means you can display other values which are also dynamically retrieved from the cube based on slicer selections etc. If the report is variable in length, the formula can be added into an XLCubed grid calculation and the Value1 parameter could use a vlookup to ensure a match on the appropriate data element.
The simple example below shows the basic approach, and the result also works when published to XLCubed Web.
The extended example below shows combining several additional data fields. The line breaks are achieved using Alt + Enter.
Thanks to our colleague Norbert Engelhardt at pmOne who blogged on this point recently in German and prompted this piece, basically an English version of the original:
One of users biggest frustrations with cube connected PivotTables is that they can’t add calculated fields like they can with a regular PivotTable – the option is greyed out on the ribbon. Data in the Pivot Table can be referenced elsewhere in Excel, but that makes the final report largely static in shape. It means users can’t add even the simplest of calculations into a report where the number of rows or columns may vary, and can lead to BI developers being asked to add every calculation imaginable into Analysis Services.
The good news is that XLCubed Grids (described by some as PivotTables on steroids) let users add any Excel formula directly into the grid. The calculations are entered as standard Excel formulae, so users already know the syntax. All Excel formuale are supported, and the calculation is cube-aware once entered as shown in the video below.
We also let users easily create workbook level calculated members and sets, but in our experience what 90% of users actually want is to be able to add a simple calculated column into a PivotTable (or in our case a grid). They’re really not asking for much, and we’re happy to help.
We can also help where PivotTable users are experiencing performance issues, hierarchy selection restrictions, “PivotTable will overwrite” messages, etc. We keep the Excel flexibility users love, and lose the PivotTable restrictions they loathe. See our PivotTable comparison for more detail on some of the common user problems we address.