Author: XLCubed

Asymmetric grid reporting

XLCubed/ / Reporting & Analytics

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!

XLCubed/ / Reporting & Analytics

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.

 

XLCubed celebrates its 10th anniversary!

XLCubed/ / Customers, Case Studies & News

It’s hard to believe that it’s now 10 years since we opened for business at XLCubed, and to celebrate we are running a 10th Anniversary competition, with the prize being a shiny new iPad. As a company, we have a constant stream of ideas, but one reason we’ve been successful is that we are genuinely keen to understand how our customers use the products, and to listen to their views.

The competition is very simple, we want your suggestion for one piece of functionality you’d like to see added into the product. Maybe it’s something you’ve wanted for years, or maybe it’s just popped up. It can be anything from the smallest tweak, to a whole new area. The winner gets two prizes – the iPad, and their idea added into the product. Email your idea to myidea@xlcubed.com.

 

A snappy fix for layout problems in Excel

XLCubed/ / Dashboard Design, Grid & Report Formatting

Have you ever tried copying parts of one workbook to another and been restricted by column widths?  Or maybe you’re almost done with a report layout only to find that the last table you need to add has 4 columns, where there is only room for 3?  Today we’re going to show you how to use Excel’s Camera tool to get around any Excel column width limitations to achieve your dashboard goals!  Here we have an Excel heat Map on a separate sheet in our workbook.

It has been inserted into the dashboard below where the first thing to notice is the workbook’s  variable column widths, in particular columns J and K.  If we had just inserted our heat map as it was, the column widths in our dashboard would determine the width size of the heatmap.    Instead we used Excel’s camera tool to insert our heatmap sized at exactly what we wanted, regardless of the destination sheet’s column widths.

 

We follow these simple steps:

  • select  the heat map in the source sheet
  • click the Camera Tool icon
  •  navigate to the destination sheet
  • click and insert exactly where you want

The Excel Camera Tool is also a great way creating dynamic screenshots of particular groups of data.  The Camera Tool takes a picture of a selected area, and you can then paste that picture wherever you want it. It updates automatically, and because it is a picture rather than a set of links to the original cells, any formatting or data change in the source is automatically reflected in the picture.

The heat map chart source figures have been updated to show Europe’s higher sales – as you can see Europe now has the greater sales:

 

The dashboard heat map has updated automatically to reflect this value change.

 If you can’t see the Camera Tool on your Excel menu you can easily attach it to your Quick Access Toolbar by performing the following steps:

  • Click the File Tab
  • Click Options
  • Choose the Quick Access Toolbar Option
  • In the ‘Choose Command From’ dropdown, select Commands not in Ribbon
  • Find the Camera Tool from the alphabetical list of commands and add it to the Quick Access Toolbar.

Excluding members in XLCubed

XLCubed/ / Reporting & Analytics

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

XLCubed/ / Data, Reporting & Analytics

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/ / Reporting & Analytics


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

XLCubed/ / Grid & Report Formatting

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.

 

XLCubed V7 & SQL Server 2012

XLCubed/ / Data

SQL server 2012 has recently been released to manufacturing, and at XLCubed we’re well placed to take advantage of everything that is new in 2012.

SQL 2012 delivers Business Intelligence under the ‘BISM’ umbrella (Business Intelligence Semantic Model). BISM comes in different flavours though:

  • BISM Multi-dimensional
    • (Latest version of Analysis Services as we know it)
  • BISM Tabular
    • In-Memory Vertipaq
    • Direct Query

For client tools, BISM Multi-dimensional is largely the same as connecting to existing versions of Analysis Services, with MDX being the query language. For XLCubed we can leverage what we already have in that respect, and the transition is seamless.

BISM tabular is different though. If you choose to deploy in-memory to Vertipaq, client tools can still use MDX, and as such don’t need significant change, other than to handle the tabular rather than hierarchical data environment. However if the deployment is Direct Query (for example for real-time BI), the only available query language is DAX.

There are best use cases for the different deployment options, but it’s fair to say there is a degree of confusion in the space at the moment about the relative merits of each. We’ll try to shed some light and guidance here over the next weeks and months. As a product though, it’s important for us to support and extend the full range of 2012 BI deployment options, and to make these available and accessible to our customers. That’s exactly what we’ve done for version 7.

XLCubed v7, which releases next month, is a client for both MDX and DAX, and as such provides one consistent client interface in Excel and on the Web which can access any of the SQL 2012 deployment models for BI. We are also adding a much richer relational SQL reporting environment.

We are really pleased with some of the beta feedback we’ve had to date, and if you’d like to trial the beta version contact us at beta@xlcubed.com .

We’re looking forward to releasing the product next month, and will be previewing it at SQL Server Connections next week in Vegas.

 

Streamlining writeback with XL3DoWriteback

XLCubed/ / Reporting & Analytics

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