Skip to content

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.

Categories: Uncategorized.

Swiss Customer Event

Last week we held a very successful XLCubed user event in the Starling Conference Centre, Geneva with our partner Evolusys. These events are a great opportunity to engage with our key customers first hand to get their thoughts and feedback and also to share ideas for future development. Thanks to all who attended, it was a productive and enjoyable day

The message which shone through was that what our customers value most about XLCubed is the flexibility. The mix of Excel itself extended with a focused BI client gives a degree of flexibility that other tools just don’t come close to.  “There is always a way” was a phrase mentioned by several of the attendees, and great to hear from our perspective.

We used part of the day as the first public preview of Version 9, and we were delighted by the feedback. Extending the reach of the product to additional data sources like SQL, Power BI and Excel was extremely popular and the slick ‘panel view’ dashboards and responsive UI really resonated. We now ‘just’ need to finish development over the summer and look forward to bringing v9 to market!

Keep watching this space for more info on v9…

Categories: Uncategorized.

DAX Performance tips– lessons from the field

XLCubed has supported a drag/drop interface for creating reports against Tabular Analysis Services since the first release of the new engine. It lets users easily create reports which run DAX queries on the cube, and we’ve often seen very good performance at customers when MDX against Tabular was a cause of long running reports.

So when we were approached at SQL Pass in Seattle by some attendees who had a SSAS Tabular performance issue we were optimistic we’d be able to help.

In this case the business wanted to retrieve thousands of rows from the cube at the transactional level, and the first approach had been to use PivotTables in Excel. To get to the lowest level they cross-joined the lowest levels of all the hierarchies on the rows section which would give the right result, but performance was terrible, with several queries taking 20 minutes or more and others not returning at all.

We hoped using an XLCubed table running DAX would be the solution and created the same report in the designer. Sadly while performance was a little better it was still far from acceptable; the model was large, and the number or columns combined with their cardinality meant that a lot of work was being done on the server.

XLCubed’s DAX generator was trying to cross-join all the values from each column, which had worked well for our other customers. But when there are a dozen columns including the transaction ID things do not go so well. DAX in itself is not a magic bullet and SSAS Tabular models can hit performance problems on low level data – we needed a new approach.

After some investigation we discussed the issue and our thinking with our friends at SQLBI and determined that instead of cross-join we wanted an option to use Summarize() instead as this only uses the rows in the database, and it can access columns related to the summarized table which were required for the report.

As the customer’s report had the transaction ID in it the result wasn’t aggregated, even though we were using summarize. But we wanted to add true transactional reporting too, using the Related() function.

Finally, SQL 2016 adds a couple of new functions, SummarizeColumns() and SelectColumns(), both of which are useful for this type of reporting, but offer better performance than the older equivalents.

The end result in XLCubed is a new option for DAX tables to allow users to set the type of report they want to run, and some internal changes so that XLCubed will automatically use the most efficient DAX function where they are available.

A beta was sent to the business users and the results were fantastic. The report which had run for several minutes now completed in a few seconds, and 20 minutes was down to 15 seconds – we had some very happy users!

The changes will be in the next release of XLCubed so that all our customers can benefit from the improvements. It’s always nice when a customer request helps improve the product for everyone.

A sample of the syntax change is included below

Before:

 

EVALUATE
FILTER (
    ADDCOLUMNS (
        KEEPFILTERS (
            CROSSJOIN ( VALUES ( 'Customer'[Education] ), VALUES ( 'Product'[Color] ) )
        ),
        "Internet Total Units", 'Internet Sales'[Internet Total Units],
        "Internet Total Sales", 'Internet Sales'[Internet Total Sales]
    ),
    NOT ISBLANK ( [Internet Total Units] )
)
ORDER BY
    'Customer'[Education],
    'Product'[Color]

After:

 

EVALUATE
FILTER (
    ADDCOLUMNS (
        KEEPFILTERS (
            SUMMARIZE ( 'Internet Sales', 'Customer'[Education], 'Product'[Color] )
        ),
        "Internet Total Units", 'Internet Sales'[Internet Total Units],
        "Internet Total Sales", 'Internet Sales'[Internet Total Sales]
    ),
    NOT ISBLANK ( [Internet Total Units] ) || NOT ISBLANK ( [Internet Total Sales] )
)
ORDER BY
    'Customer'[Education],
    'Product'[Color]

Categories: Analytics, BI.

Tags: , , ,

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.

Categories: Blog, Charts, Reporting, Uncategorized, Visualization.

Tags: , , , ,

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.

Categories: Analytics, BI, Blog, General, Reporting, Uncategorized.

Tags: , , ,

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

Categories: Blog, General, Reporting, Uncategorized.

Tags: , , , ,

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.

Categories: Blog, Reporting, Uncategorized, Visualization.

Tags: , ,

Bump Charts in XLCubed

So today’s blog is about adding Bump Charts in Excel using v8 XLCubed.

Initially a Bump Chart looks the same as a line chart – the difference is they plot the rank position rather than the actual value.

Let’s imagine that I sell a product in a marketplace with 10 other competitors. I may like to see how the rank position of my product and the competition changes over time to check if I’m gaining or losing market position. It’s a common scenario in pharma, where we have a good customer base.

You will usually want dates on the category axis so the trends are shown across time. The series then holds the items to be compared, in this case the products.

BBC1

 

 

 

 

 

 

Our example has been set up with Measures on Headers, Product Categories on Series and Date Calendar on Categories.  For more information on using Small Multiples in XLCubed please visit Small Multiple Charts.

The currently selected measure is Reseller Order Quantities (selected though the Measures slicer)

BBC2

 

 

 

 

for the eleven months prior to April 2008 (selected through the Date slicer)

 

BBC3

 

 

 

for a subset of products.

Looking at the bump chart you can see that I’ve selected Road Bikes and Mountain Bikes for easy comparison.  You can quickly see that the rank position for Road Bikes dropped quite dramatically from May 2007, picked up again in September before dropping again in November and rising in December through to February 2008.  The change for Mountain Bikes, on the other hand, was less dramatic, rising and falling slightly, steadying in February 2008 before dropping again the following month.

To create a bump chart just select Line – Bump as the Chart Type on your Small Multiple chart. The neat part is that all the rankings are worked out for you behind the scenes, without the need for lots of complex Excel gymnastics trying to work through the full result set month by month.

Categories: Blog, Charts, Dashboard, Reporting, Uncategorized, Visualization.

Tags: , , ,

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

Categories: Blog, General, Reporting, Uncategorized.

Tags: , , ,

Excel heat maps made easy!

With the recent release of version 8 we’re going to blog about a number of the new features, starting with how to create a heat map in Excel.

Here’s a fairly large table showing sales for thirty six products across twenty six US states:

 v8B1

There’s a lot of data here but it’s not giving us any helpful information as the table is too large to see any pattern or comparison.

A heat map could be a useful way to give a quick visual picture of the spread of the sales volume. Let’s add a simple heat map, new in version 8 of XLCubed.

Select the data area in the table, and then from the XLCubed ribbon select the InCell-Chart group, and heat map:

v8B2

 

As we have already selected the data area to be charted this prompt is already showing the correct cell locations.

Choose the formula destination (where the formula controlling the chart will be located), and the Chart destination (where the top left cell in the chart area will be located).

We can now define the look of the heat map in the Chart Format dialog:

v8B3

 

 

We have set the low and high colours to define a blue colour gradient.

Outlying values could potentially skew the chart so you have the option to exclude these by setting minimum and maximum values.  Select the icon to use, squares in our case, and the number of steps or bands to split the range of values into.

We have pre-arranged the Excel cell sizes to be squares, and this is the resulting heat map:

v8B4

 

You can now quickly assimilate the spread of values in a glance, and note the higher sales volumes in Maine, Michigan and Missouri for Road, Touring and Mountain Bikes.

To alter the formatting of the chart simply double click on any one of the squares in the heat map, or on the chart formula to bring up the formatting dialog.

If you are not already a user of XLCubed you can get started with an evaluation of XLCubed by going to our registration page.

Categories: Charts, General, Reporting, Uncategorized, Visualization.

Tags: , ,