Customer Segmentation By Revenue Contribution

We recently had a customer asking us for some assistance with a business challenge.

He had been asked to provide a report analysing their customer base by % contribution to company revenue. But that wasn’t all! He also had to produce an additional report that tracked the customers ‘lost’ by the company Year on Year by looking at sales activity.

This approach is also often used when looking at profitability, and many businesses like to classify their customers in this way:

  • group ‘a’ of key customers make up 40% of our company revenue and group ‘b’ make up the next 30%
  • which customers have we ‘lost’ or have become inactive over a period

This is where XLCubed’s capability and flexibility comes into play – we have an advanced selection mode for handling exactly this type of scenario.

Continue reading “Customer Segmentation By Revenue Contribution”

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.

Continue reading “Workbook Aspects – Same Report, Different View”

How’d you like that….displayed!

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.

Continue reading “How’d you like that….displayed!”

User control of dashboard layouts

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!

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

Asymmetric grid reporting

A common scenario for Analysis Services reporting  is to want to present different measures for different members,  particularly in budgeting and planning. So I want a grid that shows actuals for previous month, budget for this month and forecasts for future months.

This could be achieved in the cube, by using a “Phasing” measure to switch to the different values but quite often our customers are not in control of the cube structure.

We will look at a way to achieve this from within XLCubed itself by working through an example.

So this is our initial grid – it is currently set to report both Revenue and Discount values across all quarters in 2002 – 2004.

Let’s create a couple of slicers – one for Revenue:

and a similar one for Discounts:

 

The settings tab for Discount slicer is:

You can see that this is a multi-select slicer allows which updates an Excel range with the slicer choices.

The entries in the Excel range are referred to by their ‘Unique Name’ eg for Quarter 2 2004 equates to April 2004.

The settings tab for Revenue is similar except it outputs to different cell locations:

In this example our Discount slicer choices are Quarters 2 & 3 in 2004 and our Revenue slicer choices are Quarter 4, 2004.

Now let’s set up the excluded data – remember that we when reporting Revenue rows we want to exclude the Discount slicer choices and vice versa.

Right-click on the Discount header row and then select Exclude from Display

 

You will now see a red triangle appearing in the corner for the first member of the hierarchy which has excluded data.  If you hover over this cell it displays an additional message that the rows are being restricted by members and that you should right-click to edit axis (it’s the menu option just after grid charts).

 

 

In the Axis Designer window pick the Excluded Slicers tab and click in the lower-half of the window (highlighted) – this is where we are going to define the quarters that are to be excluded on the Time hierarchy.

Select the time hierarchy and then click the box to its right (highlighted in screenshot below) and click the box next to the drop-down so that you can pick the Excel range – in our example it is cells I23 through to I28 (the Revenue slicer choices that we do not want reported as Discounts).  Clicking OK will refresh the report and only show Discounts rows for the quarters selected.

 

We now need to do the same for Revenue so right-click on the row containing the red triangle and set up the Revenue excluded slices in a similar way. Click the icon highlighted to add a new exclusion. This will be cells A23 through to A28 (the Discount slicer choices that we do not want reported as Revenue).

Click on the new exclusion row and then in lower half of screen build up the Revenue exclusion in the same way but remembering to point to the Excel range to cell locations A23 to A28.

You should end up with an Axis Designer window something like this – for Discounts exclude slices in cell locations I23 – I28; for Revenue exclude slices in cell locations A23 –A28.

 

OK to apply these changes and the report now looks like:As you can see the report shows Discounts for Quarters 2 & 3 in 2004 but only shows Revenue for Quarter 4 in 2004 and because everything has been linked to ranges driven by slicers, the user of the report can easily control the switch in measures.

 

One slicer, two reports!

So today’s blog is going to show you how easy it is in XLCubed to have a slicer driving a grid and a SQL table at the same time.  There may be occasions when some of the information you require for your report is held not in an analysis services cube but a SQL table.  So you’ve created a grid report with a slicer like below:

 

 

This is a simple report with Geography on headers and Product Model Categories on rows showing Reseller Sales Amount with the Country slicer driving the grid.  The slicer is set to update cell B9 with the slicer choice.

 

So I show this to my manager and he asks for some more detail – he wants to know what type of businesses there are in each country, their names and the number of employees.  That’s when I realise that all of this extra information is not in my cube but on a completely separate SQL table.

Not a problem for XLCubed!  I can quickly create a report that includes all this data from the SQL table.  Using the SQL option within Grids & Tables I can create a report that connects to a relational SQL data source.

Create my connection to my data source – I am selecting the AdventureWorksSDW database:

Let’s build up my SQL query – I’m using the DimReseller and DimGeography tables to return the required fields.

My SQL statement is:

Select DimReseller.BusinessType, DimReseller.ResellerName, DimReseller.NumberEmployees, DimGeography.EnglishCountryRegionName From DimReseller Inner Join DimGeography On DimGeography.GeographyKey = DimReseller.GeographyKey

This is great but it returns data for all the countries and I only want to see data for the country chosen through the slicer.  So let’s add a parameter to our SQL query.

If you look at the corner of the SQL query window you will see the parameters area – with a very helpful tip on adding a named parameter.

 

 

Let’s add the following to the end of our SQL query:

where DimGeography.EnglishCountryRegionName = @parm1

Now we can define where the Excel range is for our parameter – in our example it is cell B9.  You remember that this is the cell that the slicer has been set up to output the slicer choice.

So now when we select a country from our slicer eg United States the grid refreshes as well as the table.

 

Creating tree-view slicers in v7


XLCubed has always provided a tree view selector to let users chose items from different levels in a hierarchy.   Previously, however, it was only possible to do this directly from a cube-based hierarchy. With the extension of our SQL reporting capability in V7 we found a few scenarios where we wanted to create tree views from non-cube data. This can be easily achieved in Version 7 by using a slicer sourced from an Excel range.  This can then be used to drive reports sourcing data from cubes, Tabular models, or SQL as required.

You can also use this method to allow users to choose items from an amended structure of a hierarchy or a limited part of a cube hierarchy and this is what our example below shows:

As you can see we’re going with a food-based theme.  This Excel range needs to be in a specific format and so we have our list of slicer choices with the three required columns: key, value and depth.

Here are the slicer choices at the different levels of the hierarchy:

 

We’re happy with our list so from the XLCubed tab let’s select Slicer and then Excel which allows us to insert a slicer based on the data in our workbook.

 

 

At this window we need to tell the slicer where to find the data (slicer range) in our workbook and the slicer type – in our case a tree view.

 

In our example we are also giving the slicer a name ‘Food and Drink Slicer’  as well as instructing it to write the slicer selection to cell location $J$19.

The resulting slicer looks like this and the user’s choice can then be used to drive any report, ranging from cube-based grids to DAX and SQL tables.

 

 

“Prev” and “Next” in XLCubed Slicers

We’ve been asked a few times in the last couple of months if we can build a ‘Previous / Next’ selector for date hierarchies, which allows the user to quickly navigate sequentially through months or days. The answer is of course ‘yes’,  otherwise it would be a very short blog..

One of the key strengths of XLCubed is it’s tight integration with Excel, and it means that with some creative thinking the answer is very rarely  ‘no you can’t’. Here we use a combination of our slicers, the xl3membernavigate function, and standard Excel formulae to produce a very effective selector for just this scenario.

A working example of this which connects to the sample bicycle sales local cube which we  ship with the product is available here or you can view the online demo here.

There are a couple of key things to note with this approach:

1) Slicers are typically populated direct from the cube, which makes them very flexible and dynamic. However a less well known aspect is that slicers can be driven from an excel range, and in this case that’s what we’ll be doing.

2) XL3MemberNavigate(). A fairly new formula which allows you to traverse a hierarchy dynamically in a multitude of different ways. Here we just scratch the surface.

To begin with we need to prepare a range of cells in Excel to base the slicer on, in this case the months, and we also need to ensure it’s dynamic and can change with the underlying data structure.  We need to prepare a table of similar structure to the below.

Cell B2 is the selection made by the user in the slicer, which we’ll come back to. The other columns in the table show:

Description:

Logical description of what the row is

Month:

The month available for selection, determined by whatever the user chooses in the slicer, and the Xl3MemberNavigate formula (Insert Formula – Member Navigate) .

Checked Month:

Validation checks on the month to cater for when the first and last available months are selected.

Slicer Display:

what will be displayed in the slicer dialog for user selection.

The first month uses MemberNavigate to get the first available month. This is very straightforward in the MemberNavigate dialog, and will insert a formula in this syntax: XL3MemberNavigate(1,”[Time]”,”[Time].[Month]”,”FirstMember”). Last month is achieved the same way, but using ‘lastmember’.

Previous and Next are again achieved using MemberNavigate, this time the syntax will be:  XL3MemberNavigate(1,”[Time]”,SlicerData!$B$7,”Previous”).

Displayed month is simply what the user has chosen in the slicer.

 Adding the slicer:

Add a slicer from the XLCubed ribbon (or insert slicer menu in 2003). On the selection tab, choose ‘slicer range’ and select C5:D9 on the table shown above. Then set the slicer Type to be buttons. Lastly, on the settings tab, set the slicer to update cell B2 on the SlicerData sheet.

Optionally, you can also name the slicer and choose to show a title bar, as we have in this example.

On inserting the slicer, you’ll need to resize the control itself, and possibly also the size of the buttons if the data member names are long.

You should now have a slicer which enables Prev/Next selections, along with first and last.

Using the slicer in a report

The slicer isn’t currently connecting to anything, or changing filters within a report. To do that, as it’s not directly connected to a hierarchy in the same way as a standard slicer, we need to go via the excel cell which it updates. So any XLCubed grids or formulae need to reference the cell which the slicer outputs its selection to, in this case in this case SlicerData!$B$2.

In our example we’ve just connected one grid, but there can be as many as required. Our example also gives some sales and costing detail for the main product categories. We also use in-grid sparklines to give a feel for the trend, and these can be drilled or sliced and diced in the same way as a standard grid.

The working example can be downloaded here, or a similar version published to XLCubedWeb used online here.