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.

 

Excluding members in XLCubed

So today we are going to show you how you can easily exclude members from your XLCubed reports.  Here we have a simple grid which shows lowest level descendants of Promotions on rows and Geography on columns.

 

 

 

 

 

 

 

We would like to rank this report and also exclude the Promotion No Discount which is not really adding any value to the report.

So let’s edit the Promotions hierarchy and set up the exclusion of the No Discount Promotion.

 Click the Advanced tab and then the Add Member List icon:

You will see a window as below:

Now click the drop-down on the right-hand side member list and select Edit.  This will allow us to edit the member set:

 

We are going to exclude No Discount so select it and drag it across.

Next we need to choose one of the following operations to perform on our two member lists:

Add – left and right sides combined

Common – must exist on left and right side

Subtract – left side minus right side

 

We will select the subtract operator and click OK.  We will also click this icon to rank the result:

 

 

Let’s rank these Promotions based on the current measure, Reseller Sales Amount:

 

The Promotions hierarchy has now been edited to exclude No Discount and then ranked.

 

 

Our report now looks like this:

 

 

 

 

 

 

 

 

As you can see the report now excludes No Discount row and has been ranked to show the top 10 Promotions across All Geographies.

Easy pivoting of SQL queries in Excel

So today’s blog is all about pivoting SQL query data columns.

Here we have a small sample of a SQL query report that shows us actual revenue across different products over a number of quarters.  There’s nothing wrong with the data being returned but it is pretty difficult to do any comparison analysis.

So what if your task is to report back on actual revenue across the product categories over all the quarters in this report.

This would not be an easy query to write in SQL as we don’t always know which data will be returned by a query, but that’s where  XLCubed can come to our rescue!

We just right-click on the column heading which we wish to pivot – in our case cDate and then select XLCubed and Pivot (XLCubed fills in the column heading) as below:

 

The report is now displayed as with the quarters across the page as columns.

 

This format is so much easier to read and we can quickly how each of the products are contributing (or not!) to the company’s revenue across a time period.

So that’s how easy it is to pivot column data in XLCubed.

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.

 

 

Streamlining writeback with XL3DoWriteback

We were recently asked by one of our customers to help them improve their forecasting process. They had originally been using a solution developed using XLCubed Excel Edition v6.0 and our XL3LookupRW formula. The system had been working, but because of a combination of the intricacy of the data model and the slowness of the cube server when performing a writeback, the process was taking much longer than necessary.

As an example, one of the workbooks that was being used contained nearly 7,000 XL3LookupRW formulae, and another contained over 1,000. Many of these lookups could actually have been replaced by a simple Excel formula, such as a sum or a product of other values, but built as it was, the customer was having to type these values into the cells individually: a tedious, time-consuming and error-prone task.

The process before XL3DoWriteback

In the screenshot above, the price, percentage and production figures would be typed in, then a calculation made to calculate their product (in the white cells). This would then be individually copied and pasted into the corresponding cell in the revenue row.

What the customer wanted was a couple of changes to streamline the process:
* the ability to use Excel formulae in the workbook to obtain the final values – without the subsequent copying of values,
* they wanted to be able to get all the calculations lined up, then submit them all at once – this would make the poor server performance a much less important issue, since instead of having to wait to enter the next value, that period could be usefully spent doing other tasks.

What we offered was a different writeback method, which has been available in its current form since XLCubed v6.5: the XL3DoWriteback formula.

Unlike XL3LookupRW, XL3DoWriteback is geared towards the kind of batch writeback approach that the customer had envisioned. Once set up, Excel formulae can be used to do the actual work of calculating the numbers, and the XL3DoWriteback formulae remain dormant until all the values are ready, then are activated in one transaction.

If this sounds useful for you, here’s how to set it up.

The XL3DoWriteback Formula

In addition to the member list required by the XL3LookupRW formula, the XL3DoWriteback formula requires two extra parameters:

  • PerformWriteback: this parameter tells the formula whether it should be in active writeback mode, or should remain dormant
  • Value: this parameter gives the new value that should be written back to the tuple

Following these two parameters are the connection number, and the hierarchy-member pairs that will be familiar to you from the XL3Lookup and XL3LookupRW formulae.

The PerformWriteback parameter is a bit special. If it refers to a cell that contains only a boolean value of TRUE, then when it has finished sending the value, it will set that cell back to FALSE. This means that periods of writing and non-writing are very easy to define. In order to maximise the power of this, we usually point all the XL3DoWriteback formulae at a single PerformWriteback cell, which we can switch using an XL3Link formula. For example:

A1: =XL3Link(XL3Address($A$1),"Write changes",,XL3Address($B$1),TRUE)
B1: FALSE
C3: 1,000
C4: 0.85
C5: 20,132
C6 =C3*C4*C5
D3: =XL3DoWriteback($B$1,C3,1,"[Account]","[Account].[Production]",
     "[Date]","[Date].[Calendar].[January 2011]")
D4: =XL3DoWriteback($B$1,C4,1,"[Account]","[Account].[Our %age]",
     "[Date]","[Date].[Calendar].[January 2011]")
D5: =XL3DoWriteback($B$1,C5,1,"[Account]","[Account].[Price]",
     "[Date]","[Date].[Calendar].[January 2011]")
D6: =XL3DoWriteback($B$1,C6,1,"[Account]","[Account].[Forecast Revenue]",
     "[Date]","[Date].[Calendar].[January 2011]")

In this example, C3, C4 and C5 are cells containing the raw values. Since we know that the forecast revenue is a product of the production, the percentage and the price per unit, C6 is just the product over those three cells. The four XL3DoWriteback formulae in column D refer to these value cells, but because the value in cell B1 is FALSE, nothing is written back yet.

In cell A1 is a XL3Link formula that, when clicked, will change B1 to TRUE. This immediately signals the XL3DoWriteback formulae that they should gather and write back their values. Once that transaction has been sent to the cube, the XL3DoWritebacks set cell B1 back to FALSE, and the workbook is back to the ready state.

The Setup

To make it as easy and efficient as possible, we used:

  • one section for values. These were a mix of XL3Lookup formulae, typed-in values and standard Excel formulae
  • one section for XL3DoWriteback formulae. We pared away any excess XL3DoWriteback formulae, leaving only those cells that we were sure we wanted to be writeable
  • a single cell with the boolean value, set to FALSE
  • an XL3Link in a highly visible place, to switch the boolean cell. In this case, the cell containing the boolean value was B1:
=XL3Link(XL3Address($A$1),"Write changes",,XL3Address($B$1),TRUE)

The final workbook looked a little like this (except, of course, much larger!):

A section from the finished workbook

The customer would then enter all the necessary values on the left section, using whatever combination of Excel formulae, cube lookups and typed-in values he needed, without any wait between entries. A single click of the XL3Link then wrote the values back in a single batch, leaving the customer to do other jobs.

The revised model allows the user to update entries quickly and efficiently, without any ‘write’ delay. The numbers to be written back can be calculated using Excel formulae as needed based on the raw input numbers. When the input process is done and checked in Excel, everything can be committed to the cube with one button press. The end result – a happy customer, with more time to plan and analyse the budget, rather than just input it.

Further reading

XL3DoWriteback formula reference

Between and Member Searching

Our blog today takes a look at two new features available in v6.5 – Between and Member Searching.

Between

In v6 we give our users  the ability to enter a reporting range for their grid reports by allowing them to enter a  ‘From’ and a ‘To’ range on a hierarchy.

The only thing to remember is that both members have to be at the same level.

So let’s try to put together an example.  Let’s say that we would like to report all data between two dates.

So we edit the hierarchy that should include the range. The hierarchy can also be on the header area of the report as well as on rows or columns.

Under the Advanced tab we click the Clear all icon  

 

before clicking the Member Set icon

 

so that we can enter the From and the To values.

Click OK and our report will show only the members in the range specified.  It’s as simple as that!

Even better for the end-user is the option to enter an Excel range so that they just enter their values into Excel cell locations.

So we’ll run the grid report based on From and To values in $I$3 and $I$4 respectively.

Another great feature of using this option is that we can choose to leave one of the ranges empty.

So if we just enter a value in the Excel cell location for the From date and leave the To date blank the report returns all data from the From date to the latest date available in the hierarchy.

As you can see in the screenshot below we have put FY 2002 in the From range and left the To range empty – so XLCubed returns all data from FY 2002 to latest.

Conversely, leaving the From date blank and entering a value in the To date will return all data from the earliest date available to the To date.  This time XLCubed returns all data until FY 2003.

 

So that’s how you report on a range in XLCubed.

PS Don’t forget both members of your range have to be at the same level in the hierarchy.

 

Member Searching

Another great feature of  v6.5  is the new functionality that allows the user to filter a report by searching for members in a hierarchy.

Let’s show this functionality in action with a simple example.

Our report below shows a simple grid with Geography on rows and Fiscal Years on columns – we’d like to show only those members in the Geography hierarchy (at all levels) that start with B.

 

Click the Advanced tab and then select All Hierarchy Members by clicking:

Click on Member Search and the following window will be displayed:

At this point we have two options:

  • enter a value in the Search Value field – in our example we enter B
  • use Excel range to hold the value that should be used

In our example we are using the value in cell F2 to determine the filtering on our report.

We can determine the ‘search by’ criteria as below – ‘Ends with’, ‘Begins with’, ‘Exact match’ or ‘Contains’:

Additionally, we can choose the ‘Property to search by’:

  • MEMBER_CAPTION (most commonly used)
  • MEMBER_UNIQUE_NAME
  • MEMBER_KEY
  • PARENT_UNIQUE_NAME

As you can see the report now only shows those members of the Geography hierarchy that start with B regardless of their level within the hierarchy.

 

This example is based on an entire hierarchy but it is also possible to do the same for a specific set of members, for example, a level or descendants of a specific member.

We hope this short example has shown you how easy it is to use Between and Member Searching within XLCubed reports.

 

Breakout and Propagate – Oldies but Goodies

We’ve been on-site with various customers in the last few months, it’s always good to see how the product is being used, and we value customer feedback, which often feeds into our development cycle.

With two long-term customers we found that they weren’t aware of two very useful pieces of functionality which have been in the product for many years. In case there are others in the same boat…..

1) Breakout

Breakout is available on the right-click menu of any XLCubed report, grid or formula. It’s a way to understand how the number is split into elements of another hierarchy. On a right-click, you can switch to the breakdown of the number by any other hierarchy in the cube. It’s particularly useful where a reported number seems too high or too low, and needs further investigation. In the example below, we’d like to understand how the June 2004 number for the US is made up in terms of Products. We’ve chosen breakout on the right-click menu, specified the Product Model Categories hierarchy and the level to run the breakout at. The breakout result is shown on the bottom right, and we can quickly see that in June Touring bikes were contributing almost 33% of the US revenues. In this example we’ve also included a sparkline to give a feel for trend over the last 2 years (previous 23 months, plus the current month).

The breakout result is still linked to the report selection criteria, so can be used as a dynamic part of the report ongoing.

 

2) Propagate across Sheets

Propagate across sheets is a way to quickly replicate a report onto additional sheets, where just one variable is changed. Typical-use cases for this are entity-based reporting, where for example there is a standard P&L template across the business, and the user wants to quickly generate a P&L for each legal entity. You can build the report as normal, and then when done, right-click on the selected member for the hierarchy you want to propagate, and choose the elements which you want to create additional sheets for. On the new sheets, the formatting and print layout are identical, with the only change being the selected member on the propagated hierarchy.

This example shows the income statement about to be propagated for the four selected departments.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Solve order shenanigans

Today I’m going to blog about a problem we recently solved in a client’s cube, an error in the Mdx script that’s very easy to make if you aren’t careful.

We’ll run a simple example in AdventureWorks (what else?) to demonstrate the issue.

The client had already added a calculation to their cube to show year-on-year growth. The formula is:

Create Member CurrentCube.[Measures].[Delta to PrevYear] as
(
    ([Measures].[Internet Sales Amount])
    -
    ([Measures].[Internet Sales Amount],
        ParallelPeriod(
            [Date].[Calendar].[Calendar Year],
            1,
            [Date].[Calendar].CurrentMember
        )
    )
)
/
    ([Measures].[Internet Sales Amount],
        ParallelPeriod(
            [Date].[Calendar].[Calendar Year],
            1,
            [Date].[Calendar].CurrentMember
        )
    )
, Format_String = "0.00%";

(some error checking removed for clarity)

This screenshot shows a couple of simple XLCubed Grids showing the real value, and below the percentage change. I have added in an Excel calculation to show the results are as expected.

Later during the cube development, the client added a calculated member in their Product dimension, one that gives a total excluding one of the product categories.

To replicate this I’ll add a calculation for “All Ex Bikes”:

Create Member 
CurrentCube.[Product].[Product Model Categories].[All Products].[All Ex Bikes]
as
(
    ([Product].[Product Model Categories].[All Products])
    -
    ([Product].[Product Model Categories].[Category].&[1])
);

And if we run the report again we get the following.

Notice the cell I’ve highlighted. The “All Ex Bikes” calculation works fine on the normal measure, but it gives totally the wrong number for the percentage calculation. What’s going on?

The problem is that in the cell highlighted Analysis Services has two calculations to think about when working out the result.

  • Compare this year to last year
  • Get the “Grand Total”, and subtract “Bikes”

As the number returned is 1.85% we can see that Analysis Services has chosen the second option, “Grand Total” – “Bikes”.

What we really want is for the calculation to be done by getting the subtotal, and then doing the percentage change based on that.

Fortunately the fix was a simple one. Analysis Services will run the calculations in the order they are found in the Mdx Script, so to fix the issue we simply moved the new “All Ex Bikes” definition up above the percentage calculation.

Now the number returned matches our expectations.

Pass/Solve Order can be a complex topic, so you may need to be careful.

In this case the number is totally wrong, so it was easy to spot, but some bugs will be much more subtle, so watch out!

Warning: Excel can get Volatile

Excel is a great tool for dashboard/report delivery and design (it’s why we created our addin in the first place), but there is a hidden performance trap:

Offset, Now, Today, Cell, Indirect, Info and Rand

If you’ve ever used any of these formulae, you may have noticed that whenever you change a cell, or collapse/expand a data grouping, Excel recalculates. That is because these are VOLATILE formulae, as soon as you use one of these, Excel will enter a mode where everything is always recalculating, and for good reason.

Offset & Now are the formulae we see used most often. Let’s look at each of these in turn and talk about some alternate approaches to avoid this issue.

Offset

This is by far the most common of these danger formulae that we see in use. Here’s the formula definition:

=Offset(reference,rows,cols,height,width)
Returns a reference to a range that is a given number of rows and columns 
from a given reference.

We typically see these as part of a named range definition for driving chart source data – it allows the number of rows/columns driving the chart data to change automatically; a not unusual requirement when it comes to building reports (especially when a report contains some user defined filters or slicers). Here’s an example:

 

 

 

 

 

 

A very simple spreadsheet – we can type the number of months to display in the chart. In reality the number of months to display will probably be driven by the data available for the criteria selected. The screenshot already shows the issue we have –  the chart is setup to display a max of 12 months, but we only have 3 months of data available.

 

The most obvious approach is to use the Offset formula to pick the chart area to use automatically, we could create a named range such as:

 

 

 

 

 

 

Now we just change the chart data source to be the named range:

 

 

 

 

The chart is now plotting 3 months, but will automatically update to show the required number of months:

BUT we have now used a volatile formula –  although this is a simple workbook, we are now in a position where Excel is going to have to recalculate everything all the time. It’s probably a good time to look at why Excel is going to do that. Let’s have a look at very simple formula to understand how Excel recalculates things.

Consider the formula:

C1    =A1 + B1

We can see that C1 is dependent upon A1 & B1 – so whenever a value in either of these cells changes C1 will need to be recalculated to show the correct answer. Excel knows about this dependency because it maintains a dependency tree; it knows which cells need to be recalculated whenever any other cell changes. This is a very efficient way of working, if a workbook has thousands of formula, but only one values changes, and this only needs 10 of these formula to recalculate, then only 10 will be calculated.

If C1 contained:

C1    =Sum(A1:A20)

We know that C1 depends upon any of the cells A1:A20, and so does Excel. But what if C1 was:

C1    =Sum(Offset(A1,0,0,B1,1))

Which cells is C1 dependent upon? At a glance you could say A1 & B1.

 

 

 

 

 

 

but  B1 contains the number 20, so actually C1 is dependent upon A1:A20 and B1 (I’ve highlighted the additional cells that are dependent):

 

 

 

 

 

 

 

Just as we can’t see at a glance which cells C1 needs – Excel also can’t easily decide that. Therefore, Offset is volatile because, if it wasn’t then there is a danger that Excel would take so long to work out if it needs to be calculated that it might as well always calculate it.

There is an easy solution to this, INDEX. Here’s the formula definition (be careful, there are 2 ways to use Index, we want the REFERENCE one):

=Index(reference,row_num,column_num,area_num)
Returns a value of reference of the cell at the intersection of a 
particular row and column, in a given range
The big difference, compared to Offset, is that Index is going to return a single cell reference, so you need to use it as part of a range selection A1:Index(…). Here’s the same “Offset” Sum redefined as an “Index”:
C1    =SUM(A1:INDEX(A1:A20,B1,0))

The formula is simply saying the range we want starts at A1 and goes down the number of rows set in B1. The crucial difference is that the Index functions knows that A1:A20 is the maximum range we are likely to look at and therefore the dependencies are known just by looking at the formula itself:

We can now update the Named Range to use the Index function instead:

=Sheet1!$C$6:INDEX(Sheet1!$C$6:$C$17,Sheet1!$D$2,0)

 

 

Now/Today

The Now and Today functions return the current date to a cell – this is generally used so that when a report is loaded it will always show the data based on “Today”. Whilst this is not an unreasonable thing to want to do,  in reality what most people want is for the report to run for the most recent data, which could actually mean a number of things:

  • Yesterday (if the data is built in a nightly process)
  • The last working day (if the source transactional system is only used during office hours)
  • Current month etc.

The easiest solution is to let the data determine the date to use – if we use an XLCubed Grid or Query Table to retrieve the data we can simply setup a grid to retrieve the days/months where there is data:

And use the Sort option “Reverse” to display the most recent data first:

With the grid set to “Refresh on Open”  we know that A6 will always have the most recent date available in the cube and can base the rest of the report off that cell.

Incidentally, V6.2 of XLCubed introduces a new option to Slicers to automatically select the most recent date member when the report is loaded:

Ranking, Sorting and Filtering

Once we have returned cube members into a grid report we often need to exclude or change the order of the result set to provide more meaningful information. MDX (Multidimensional Expressions) language includes some very useful operators to provide filtering (FILTER), sorting (ORDER) and ranking (TOPCOUNT/BOTTOMCOUNT) of dimension members. These can be quite overwhelming even for power users of XLCubed.  So, in V6, we have introduced a new feature “Advanced Member Selections” to provide easy access to this powerful part of Microsoft Analysis Services.

Using this new functionality we can nest and combine these operations to answer complex business questions (for simpler operations you can right-click on a member in the grid and use the “Apply” menu to perform simple ranking, filters and sorting).

Filtering

So let’s go through a simple filtering example.  Say, for example, that we want to find the products at Product Key level that sold more than 25 units in 2003, Quarter 1 and show the sales figures for those subcategories during 2003 and its quarters.

  1. Start by clicking the Grid ribbon item (or the XLCubed > Design Grid menu item in Excel 2003 and below), and selecting the Internet Sales cube file
  2. Drag Calendar Period to Columns and Product to Rows. You can also drag any other hierarchies to Headers. In the example image below, Measures and Customer have been added there.

  1. Click on the Product hierarchy so that its details appear in the bottom-right panel.
  2. Drag the Product key level over to the right of the dialog. You can switch between the members view and levels view by clicking on the Show Levels icon ().
  3. Click the Advanced tab to show the advanced selection pane:

  1. Click the Members drop down and choose Filter result:


  1. Click the Calendar Period edit control in the grid to change its selection to the desired member (2003, Quarter 1):

  1. Select the This measure radio button, and select Order Quantity as the desired measure.
  2. Change the Operation to >, and type 25 in the edit field on the right:

  1. Click OK. The new filter is displayed in the advanced selections tab:

  1. Click OK again to run the Report – the Grid shows the members that fit our criteria:

 

So we can see the results, filtering by 2003 Q1, but displaying the values for All Time (or any other period we wish to use). We could have also used the Range selector:    to drive the period selecting from an Excel Range and our grid would automatically refresh whenever the driving value changes.

Ranking

Now let’s add a ranking to find the bottom 8 selling products at the Product Key level that have sold more than 25 units inQ1:

  1. Display the Product Hierarchy Editor dialog
  2. Click the Rank result icon () on the advanced selections tab to display the Edit Ranking dialog
  3. Select the Bottom radio button, and type 8 into the edit field
  4. Select 2003, Quarter 1 for the Calendar Period hierarchy in the grid below:

We now have the filter, following by the ranking:

 

Run the Grid: only the lowest 8 members are returned

 

Sorting

Now let’s sort the report on a different dimension – for example, descending order of the Q1 sales.

  1. Display the Hierarchy Editor for the Product hierarchy by double-clicking on the Product label in the Grid
  2. If it’s not already visible, select the Advanced tab
  3. Click the Sort result toolbar button ()
  4. Change the Calendar Period selection to 2003, Quarter 1:

  1. Click the Sort Descending (9-1) radio button
  2. Click OK. The new sort is displayed in the advanced selections tab
Click OK again to run the Report

 

Joining Results

It’s also possible to join different results together: combining both sets (UNION), excluding members (EXCEPT) and returning common members (INTERSECT).

So we could also add the top 10 products  along side the bottom 8 products to the grid. Begin by adding another member selection using the “Add Member List” tool-bar button:

As before, we select the list of members to rank (in this case the Product Key level) and then select the operation we want to perform, a Top 10:

There are various options to decide how to combine the lists, we’ll stick with Add:

 

 

And we get both results combined:


So the “Advanced Member Selections” feature provides lots of the power of Analysis Services in a simplified way  – to try this feature for yourself you can begin by downloading XLCubed.