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.



Formatting Tables in v7

We’ve had some great feedback from our Newsletter announcing the release of v7.  A number of users have asked how we created the example in the Newsletter:

So,  today we’re going to show you how to achieve some of the formatting that is now available when using SQL tables in v7.

Let’s create a SQL table from Grids & Tables tab.  You’ll see the Create connection window:

Click Connect and you will see the databases you have access to….we’ll create our query based on Bicycle Sales and the fctData view.


Our SQL query returns the following data which is great but clearly is not that easy to read.

Let’s format this table.  We’ll get rid of any borders currently set on the workbook by going to the format sheet and using Format Cells on the default cell format cell as below:


Back to the table, right-click and refresh table.


Now for the actual formatting of the table. Let’s format entries in the first column cPOS. Right-click on Car and Bike Stores, right-click and select Format Column and let’s set the font to be bold, size 12 with a double border on both top and bottom.

Now the second column cProduct. Again right-click Format Column and set the top border to be double, bottom border thick and the font italic.


We now go to Properties tab and on Appearance tab set Sections as below:

Check the box ‘Use columns as sections’, the column count is 2 in our example and Display style is set as ‘Sections in separate rows’.

We’ll also hide the first row of the workbook showing the table column headings.
The report now looks like the screenshot below which is much easier to read.