Workbook Aspects – Same Report, Different View

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.

Workbook aspects are closely tied with Web Parameters. These are simple to setup in XLCubed and allow a cell or range of cells to be defined as parameters. In this case we need to define 2 parameters “Location” and “Product Type”:

 

Once the web parameters have been defined you can link them to directly to slicers by selecting them as the output range of the slicer:

You can setup workbook aspects to work in 2 ways, a predefined list of aspects and/or the option for the end user to store the “current” view as a new aspect. Additionally the report user can select which aspect is shown when the report first loads.

Once the report is viewed, then there are simple options to let users switch between the aspects, create personal aspects and set the default aspect.

 

It provides a very fast way for users to switch between common data selections, particularly where there are a lot of separate slicers in the overall report.

Aspects are not just limited to slicers, as long as a web parameter has been setup on a range then it can be stored in an aspect, with the use of the XL3SetProperty and XL3Link formula this gives you massive scope for allowing a high-level of report customisation.

 

No comment? We’ve got plenty to say!

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

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.

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

Dynamic Tooltips / Mouseover

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.

 

s1

The extended example below shows combining several additional data fields. The line breaks are achieved using Alt + Enter.

s2

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:

XLCubed Insights #4 – Mouseover Funktion in XLCubed Berichten

 

 

Calculated fields are not available in an OLAP-based Pivot Table …

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.

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.

Cell-Linked Decomposition view

In displaying the key and often high level information required, many reports raise lots of additional questions in terms of why a specific number is ‘too high’ or ‘too low’, or how the number is comprised. XLCubed provides lots of ways for power users to further explore and visualise this, but in this blog we’ll take a look at one of the simplest and easiest to implement within a report – a cell-linked decomposition view, or a dynamic breakout.

The scenario below is a fairly straightforward Regional Sales report which will be deployed to management through XLCubed Web. When users click on a sales number for a specific region and month we want to provide them with a ranking to show the top 10 products which were contributing to that revenue. We provide a number of ways to achieve that but this is by far the simplest:

Select any of the values returned in the Grid and then either use the ‘Breakout’ option on the Grid Ribbon, or right click and choose XLCubed – Breakout Value. You now specify where to position the resulting breakout, which hierarchy to decompose the number by, at what level, and how many ranked items to show.

BODialog

 

In this example we’ve chosen the top 10 Products at the SubCategory level. The result is shown below. The green cell in the original grid is the cell the user has selected and is ‘tracked’, so when another cell in the grid is selected the breakout updates accordingly, meaning users can easily explore the makeup of their data.

 

BO Result1

 

The breakout result can be further decomposed by running another breakout, this time on the product subcategory. In the published example below we’ve run an additional breakout showing the top ranked products in the selected subcategory.

WebBO

 

The report user can now simply click on the values which are of interest to see the associated product sales breakdown, and it’s something which can be built in minutes by the report designer.

Finding a needle in a haystack – Member Searching made easy!

Searching for specific elements of large hierarchies can be a real pain in many Analysis Services client tools, and we often hear of it as a major frustration in Pivot Tables where dialogs can be cumbersome and prone to locking up.

XLCubed has both a Quicksearch and an Advanced search in the Member Selector, but in this blog we’ll show how to link the search dynamically to an Excel cell (or a web entry cell on a published report) and to retain the search as a dynamic part of the report rather than a point in time selection.

Let’s say we are a retailer with a large product hierarchy running to tens or hundreds of thousands of products. The naming convention means groups of products can be searched by a partial match on their name, and as a report designer we’d like the users to be able to type the search in as quickly and easily as possible rather than go into a custom search dialog. Here’s how:

Below is the final result in Excel, a simple list-report where the user just types the text they want to search the hierarchy for, and matching products are shown on the rows of the report.

Search1

 

We start with a regular grid, putting Product Categories on rows, and then in the Member Selector we can either select a specific level or set of data to be searched, or go to the Advanced tab and select the whole hierarchy as shown below.

EditHierarchy

 

 

In the advanced dialog, click on the binoculars:

Binoculars

 

to add a search, and then in the dialog below you can either type a search term directly in the ‘Search Value’ or reference an Excel cell, in this case $C$3. ‘Search By’ allows you to specify exact match, begins, contains etc.

Search2

 

At this point it’s worth mentioning that while in this case we are just searching by the name of the product (MEMBER_CAPTION) we could also chose to search by any member properties which exist.

So having done that we simply type the search string into $C$3 and we get the matching products straight away – couldn’t be easier.

To make this available for web deployed reports there are two additional steps:

  • Make $C$3 available for web input. To do that right click on the cell and choose Format cells, and then on the protection tab uncheck ‘locked’.
  • Add a search or refresh hyperlink or button so that the web user can refresh the report when they’ve typed the search term. This can be handled using either XL3Link() or XL3Picturelink and the process is described in our previous blog.

The web version is shown below:

Websearch