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 calculated field 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

Click & Submit!

We’ve had a few queries recently where customers want to provide web reports with a number of slicer choices, and to have the report refresh just once when all selections are made, rather than the default refresh after each selection. It can be achieved in a couple of ways in XLCubed, read on for more…

The key to this approach work is the ‘Wait for Submit on Web’ option on the slicer properties, shown below on the Behaviour tab of the slicer designer:

sub1

This means when the slicer is changed it does not refresh the report straight away, and if you set this on multiple slicers users can then press the ‘submit changes’ button on the toolbar shown below after they’ve made their selections.

sub2

 

Alternatively, and to make it more obvious for web users you can have them click on some text or an image in the report itself to call the refresh, as in the examples below.

I’ve created a simple report below with five different slicers.  Note the “Refresh“ to the right, created using XL3Link().

sub3

 

The XL3Link statement is available from the Insert Formula menu on the XLCubed ribbon:

 

sub4

 

It’s most often used to move the focus to another area of the report while passing parameters to enabled linked-analysis in a multi-sheet report. However, here we can use it to call a refresh.

We can leave the “Link to” parameter blank, and also the Target and Value cells. The last parameter, LinkType calls SubmitChanges on the web, so the syntax will look like below (you will need to update the XL3Link statement to include this parameter):

=XL3Link(,”Refresh”,3)

There is more guidance on the general use of XL3Link on our Wiki at: http://www.xlcubed.com/help/XL3Link

So when we publish our report to our web server we can change the slicer choices as required but it’s only when we click the Refresh button that the report is refreshed.

sub5

 

If we’d prefer to display an image for the user to click on rather than text we can use XL3PictureLink in a similar way.  When using XL3PictureLink we can display any picture – we’ve used a generic refresh icon but it could easily be a more corporate-applicable image:

sub6

XL3PictureLInk is also available from the Insert Formula menu on the XLCubed ribbon:

sub7

Browse in the window above to locate the Picture file to insert and remember to check the Perform a Submit Changes on Web box.

There is more guidance on XL3PictureLink on our Wiki at: http://www.xlcubed.com/help/Picture_Links

This is the published report using XL3PictureLink, the user makes the required selections and clicks refresh.sub8

 

So it’s as easy as that – two ways to ensure that your users can change multiple slicers on web-published reports before calling the refresh, and without you having to direct them to the standard submit changes on web button.

Workbook slicers – all for one and one for all!

So this is our second blog on the new features of XLCubed v8 – today we’re going to run through workbook slicers.

Workbook slicers allow the user to create the slicers at the workbook level so that they can be displayed for any/all sheets.

There’s a slicer pane which can be arranged horizontally or vertically and stays in place when you navigate to another sheet.  This means that if you have a multi-sheet workbook you only need to define one set of slicers.  These can then configured to be shown or hidden for individual sheets as required.

Turn the slicer pane on by selecting Workbook slicers from the XLCubed ribbon, Slicers tab:

 

ws1

Within the slicer pane there’s an Add Slicer button – this brings up the standard design form for adding slicers.

The Edit layout button brings up the window below.  It allows you to configure the order in which slicers will appear on the pane, which sheets they will be visible on and the padding between individual slicers.  You can also set a background fill colour from here.

ws2

The screenshot above shows that the Date.Calendar slicer is available on a number of sheets.  Selecting a slicer choice on one sheet will refresh the other sheets where the slicer is also available:

ws7

Once added, you link workbook slicers to your report in the same way as embedded slicers.  You can link directly to grids and other XLCubed objects and output their selection to Excel cell locations for use by formulae.

Their positioning on the web is fixed but if you find the slicers are taking up too much screen space you can make your slicer selections and then use this icon to toggle the Slicer Pane off:

ws4

Custom drill paths for Analysis Services reporting

Any business report will answer a predefined set of questions, but it will often give rise to many additional queries and chains of thought as the user wants to explore any oddities or anomalies in more depth.

As a report designer you want to provide flexibility in your reports. You may want to create a customised drill down behaviour which you know is how the report users naturally think of the data. Similarly giving users the ability to ‘drill across’ into other hierarchies is a great way of providing chain of thought interactivity, but the sheer complexity and number of hierarchies in many corporate cubes these days means a vanilla ‘drill across’ may be problematic. In the hands of users relatively unfamiliar with the data model drill across can be a shortcut to a support ticket:

  • What is the difference between hierarchy ‘a’ & hierarchy ‘b’?
  • What is hierarchy ‘c’?
  • When I drill across into hierarchy ‘d’ it hangs?
    • that’ll be the 8 million skus in the attribute hierarchy you’ve picked…

We recently worked closely with a customer to implement a solution in XLCubed v7.6 for exactly these types of issue. Many thanks to Thomas Zeutschler at Henkel AG for the inspiration!

Flex Report extends a concept which Henkel had developed in-house to deliver report level flexibility of the drill path, while retaining control over what the user can do. Non-technical report developers can easily define the drilling behaviour for a report (for example from Country -> Promotion -> Product Category), and also to provide controlled ‘Drill Across’ options where the users have a choice of 5 or 6 meaningful levels to drill into, rather than the 200 which may exist in the cube. The difference in usability can be stark, as in the example below.

ExpandTo

The end result is report consumers with guided and controlled flexibility in data exploration. This type of reporting can be delivered from a potentially broad group of business users who have the flexibility to develop sophisticated reporting applications without the need to go back to IT each time they want to create a slightly modified drill path. We are finding that both the deliverable reports and the report development process itself resonates with many businesses. Take a look at this video for an introduction: Flex Reporting

Current and Previous month reporting made easy

We’ve all been there. Our shiny new dashboard or report pack is finished and ready to go meet its users.  We’ve presented the key information clearly, we’ve followed all the data vis guidelines on effective charting and use of colour. We like it a lot, and we’ve thought ahead and built in lots of flexibility with slicers and managed drill paths so it can already help answer some of the questions it will doubtless raise.

It’s a little disappointing then that one of the first pieces of feedback is that the senior execs don’t actually want to use the interactivity much. They want to open the dashboard and see the current month picture (or previous month), and don’t want to waste valuable seconds selecting the month in a slicer…

Joking aside, in lots of situations it’s a sensible request, and there are various different ways to handle it in an XLCubed / Analysis Services world. Often this will be for a multi-sheet report incorporating grids, formulae, and charting elements and we need a centralised point to handle the month selection – enter the XL3MemberNavigate() formula.

MemberNav

This lets you pick a hierarchy and a level, and you can specify that you want the Last member (first and previous / next are also available). It’s available in the XLCubed Insert Formula dialog. In our case we’d pick the date hierarchy, the month level and choose Last, generating a formula as below:

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”)

The issue is that at this point it has no concept of data, it will give you the last month available in the hierarchy, not the last month with data. However that’s just another parameter away, we can add dimension member pairs to force a data check, as below, where we are checking that data exists for the “Reseller Sales Amount” measure.

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”,0,”[Measures]”,”Reseller Sales Amount”)

So that will return the last month with data, and as we know in XLCubed all grids, formulae and XLCubed charts can be based off a cell. The Xl3MemberNavigate() cell becomes the driver for all time selections in the report. Job done. Or is it? What if you actually wanted the last complete month? :

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”,2,“[Measures]”,”Reseller Sales Amount”)

Adding the addional ‘2’ parameter means it will go back an additional month, hence giving you the last completed month.

In our experience this is far and away the easiest way to handle current or Previous month reporting, and we hope you find it useful if it’s new to you. For more information on XL3MemberNavigate check our wiki.

 

Rolling Forecast reporting in Excel

In monthly reporting for the current year the most pertinent numbers for closed periods are the actuals, and for open or future months the forecast. In most cubes actual and forecast (or budget) are implemented as either different measures, or as part of a scenario hierarchy. This can lead to challenges in reporting, where putting both the period and the actual/forecast on the same axis means you will get duplicated months which show the forecast for periods when the actuals are already in as in the PivotTable below.

 

 RP10

We would want the report to be asymmetric (i.e. different months returned for Actual and Budget). Various tools can handle that, and it’s always been possible to achieve that in XLCubed through formulae or in a grid. What’s more difficult is to make the report fully dynamic without the need to redesign it each time. Ideally we’d want the user to be able to change a slicer to select the last completed month (or pick this up directly from the cube), and have the appropriate months reported under Actual and Budget.  Here’s how to achieve it in one grid…..

The approach uses a combination of:

  • Excel Ranges to determine the available months, and which are for Actual and Budget
  • An XLCubed Slicer to pick the ‘Last Actuals’
  • One XLCubed grid with the ‘Exclude from display’ pointing to the relevant ranges above

Let’s work through this example which uses the Finance cube on the AdventureWorks DW 2008R2 database.

We’re going to use data from FY 2006 and report Actual and Budget figures across departments.  We will use a slicer to select the last Actual month we want reported.  For the remaining FY 2006 months we will report Budget values.

Entire year of Months in the report

There are several ways to achieve this – we added an XLCubed Excel driven slicer (XLCubed – Slicer – Excel) based on the range $AA$1:$AB$12 as shown below, containing all months in FY 2006. This lets the user pick the last month for which we want to report Actuals.

 

RP1

RP3

 

 

 

 

 

 

 

 

 

The slicer is set to output its selection into $B$1.  In $C$1 the formula =VALUE(B1) converts the text output of the slicer into a number which we’ll use to calculate which months should report Actual and which Budget, see the screenshot below:

RP2

 

 

 

 

Date Table

This is an Excel range supporting the reporting logic. Row 2 contains every month in the reporting year. Row 3 assigns an incremental number to each month, 1-12.

We can then compare the value of the selected month from the slicer ($C$1) with the value for each month. Basically if the month is less than or equal to the selected last actuals month it should be reported as Actuals, otherwise as Budget.  An example formula contained in B4 for Actuals would be: =IF(B3<=$C$1,B2,””) . For the Budget row the formula has the inverse logic.

At this stage we’ve used a little XLCubed, and the flexibility of Excel to match the appropriate months to Actual and Budget. We now need to add the report itself.

We’ll create our grid – initially we set it up to report Budget and Actual figures across the year, by basing the selection for Date.Fiscal on the range containing all months for the year ( $B$2:$M$2).

We then use XLCubed’s right-click menu (Keep – Exclude From Display) to exclude members.  It doesn’t matter which slices are excluded at this point. To do this highlight ‘Actual’, followed by the first available month and then choose Exclude From Display. Repeat this for ‘Budget’ and the first available month.  We will exclude July 2005 for Actuals and August 2005 for Budget.  You will notice the red comment marker on the grid. Right-click on this cell and choose Axis – Edit, and go to the Excluded Slices tab.

RP6 RP7

 

 

 

 

 

 

 

 

The above screenshots show that we are currently excluding July 2005 from Actual as well as August 2005 from Budget.

We can then easily change the Date Fiscal selection for each scenario.  For Budget we will exclude Budget values for months in cell locations $B$4:$M$4 (as we want to report Actuals for those entries) and Actuals values for the months in cell locations $B$5:$M$5 (as we want to report Budgets for those entries).

RP8

The grid report now looks like this with Actuals being reported up to November 2005 and Budgeted for the remainder of FY 2006:

RP5

We have also set Merge Repeating Cells on (in Grid Properties).  You could do further formatting – for example by colour filling all Budget values and hiding the grid headers.

RP9

The end result is a one grid report giving the Actual:Budget month mix that we want with the monthly maintenance process a case of simply changing a combo box selection.