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.

 

XLCubed V7 & SQL Server 2012

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

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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Drive Excel Chart Min/Max from Range

How do I drive the min and max values of an axis from an Excel Range? This is one of the most commonly asked questions about Excel and with each new release it always amazes me that this feature hasn’t been added to the base product.

It’s a very common scenario to come across, you are building a line chart and it’s all looking ok until Excel suddenly decides to set the min value to 0, all of the detail is lost and you have gone from a nice detailed set of lines to a mishmash of colours a few pixels high.

There are some pretty sophisticated techniques Excel is using when working out what min & max to use, but sometimes we just want to set them to a particular value (normally anything other than 0!).

Here’s a pretty simple set of numbers and the resulting chart we get from Excel (just with all the defaults).

 

 

 

 

 

 

 

 

 

 

This all looks fine, but let’s change  “C” Monday’s value to 86, now look what happens:

 

 

 

 

 

 

 

 

Excel has applied its rules and decided that 0 is a good place to start the chart from, but in this case I lose a lot of the detail and end up with all the lines grouped together.

We could, of course, change the Axis min value to something a bit more sensible, so we’ll use the Format Axis option to set a minimum value of 84:

 

 

 

 

 

 

 

 

 

 

 

 

That looks better!

 

 

 

 

 

 

 

The base numbers had been entered manually, so being able to type a fixed value into the minimum axis is fine, but what if the numbers were coming from a cube or Sql database? Wouldn’t it be really helpful to be able to drive the minimum value from a range; I can change just about every other thing about the chart but after so many years and so many different version I still can’t do this.

Luckily for me (and our customers!) we already have an Excel addin so we can simply add the functionality to do this using one of the new formulae in 6.5:

XL3SetProperty( ObjectType, ObjectName, Property, Arg1, [Arg2],…, [Arg27] )

The formula to drive the chart axis from a range is simply:

=XL3SetProperty("Chart","Chart 1","YMin",$C$1)

Other options are:

PropertyDescriptionValue
“YMin” or “YMax”Sets the limits of the Y Axis.Numeric
“Y2Min” or “Y2Max”Sets the limits of the Y2 Axis.Numeric
“XMin” or “XMax”Sets the limits of the X Axis.Numeric
“X2Min” or “X2Max”Sets the limits of the X2 Axis.Numeric

Now finally we can build reports (and publish them to the Web), confident that regardless of the data or criteria selected  we aren’t going to end up with a line chart starting at 0 and bunching all the lines together.

This formula can also be used to modify various aspects of our own grids, slicers & small multiples based on the values of excel cells. The kind of things that we and our customers wanted to achieve were things like:

  • Move  dimensions between axes
  • Change the member selection types
  • Modify various grid properties based on different formulae

Lets look how the formula works to do some of these things:

=XL3SetProperty("Grid","My Grid","HierarchiesOnColumns","[Products]","[Regions]", $a$1)

Would move the Product, Region and whichever hierarchy is in $a$1 to the columns (I could use a slicer or drop down to update $a$1 to let the user switch between various hierarchies)

=XL3SetProperty("Grid","My Grid","RemoveEmptyRows",$b$1)

Would toggle whether to display rows without data based on the value of $b$1

If there are any aspects of Excel that you think would be useful to drive from a range, please let us know!

2011 Dashboard Competition

A slight departure from the normal blogging to let everyone know about the latest developments in XLCubed and to talk about a new dashboard competition with the chance to win an iPad 2!

Dashboard Design Competition

XLCubed are sponsoring Dashboard Insight’s first dashboard design contest. The competition is based on a provided data set, and we’d encourage as many as can to enter.

We believe that XLCubed offers a class-leading dashboard development environment, with fine grained control over chart and table sizing, and we’re looking forward to seeing some great dashboards. Take a look at some of our previous winners for inspiration.

Don’t forget that this blog also contains lots of helpful information that should help you come up with a great dashboard design.

We’ll provide entrants with the sample data set in a local cube format to fully exploit the strengths of XLCubed. Entry is open to customers and non-customers alike, and your dashboard skills can win you a shiny new iPad 2. Good luck if you choose to enter.

 

XLCubed v6.5

Version 6.5 is due for release in early October. Originally scheduled as 6.2, we decided it contains so much over the current version that it deserved a bigger billing. New for 6.5 are:

 

  • iPad / iPhone app – XLCubed web reports have always worked on smartphones and tablets. However our app brings an intuitive iPad optimised user experience to report navigation and selection.

  • Mapping – Integrated point and shape based mapping in Excel and on the web.
  • Scheduling – email delivery of XLCubed web reports by pdf or Excel. Schedules can be controlled by period, or by data exception.
  • Sharepoint WebPart – customers have been using XLCubed Web reports in SharePoint for a number of years, but we now introduce a dedicated WebPart to make the process simpler and provide greater flexibility and depth of integration.
  • Away from the headline items there are a number of significant smaller enhancements which make 6.5 another big step forward for us. We’re looking forward to bringing it to market. For an early test drive, contact us along with your specific area of interest at support@xlcubed.com.

Lastly we’d like to welcome Cardinal Solutions Group to our partner program. Cardinal operate in North Carolina and Ohio and are one of a select few Microsoft Managed Partners in the U.S. East and Central Regions. We look forward to working together with new and existing customers.

Scheduling Reports with XLCubed

With version 6.2, XLCubed introduces a new feature that we’ve had enquiries about for a while now: the ability to send scheduled reports to email recipients. In this post we’ll go over the basics of how it works and why you might want it.

The Scenario

As report designers and publishers, it’s our job to share our findings with others. In some cases, we have the opportunity to present the information in person at company meetings, and in other cases, just publishing a data-driven dashboard, table or report to XLCubed Web Edition allows our audience to examine the data at their leisure.

In many cases, however, we’d like to provide our consumers with a report on a regular basis. This might be a week end sales report, or perhaps one showing a breakdown of new issues versus issues resolved during the month. By automatically sending an email containing the report, we no longer have to worry about missing that important information.

In some situations we don’t want to have a regular report, but we do want to know when some performance measure is unexpectedly high or low. Under these circumstances it can even be distracting to be updated too frequently: it would be much better to only be notified when the measure is in the unusual condition.

Both of these scenarios are catered for by XLCubed’s new scheduling feature.

Setting up the Report

As always, the report design step is carried out in Excel. For scheduled reports, there are a couple of additional considerations when designing your report.

As with normal reports, scheduled reports can use web parameters. These are enhanced for scheduled reports, allowing you to place items such as the current time, user or role on the report. This means that the report can use those variables to allow a customised view of the data.

In addition to this, when setting up the schedule you can specify one of the web parameters to be a trigger: the report will only be sent if the value in the parameter cell is TRUE.

Scheduling Basics

Since different scenarios require different schedules, XLCubed makes it easy to control exactly what is sent and when. When setting up a schedule, there will be a few things to choose.

Firstly, each schedule contains one or more reports to send. Each report can be sent as a static Excel file or as an Acrobat document (.PDF), and can be given any parameters that were defined when publishing it. In addition to arbitrary text, there are some special codes that can be inserted as parameters:

CodeDescription
%date%Inserts the current date
%time%Inserts the current time
%datetime%Inserts the current date and time
%rolename%Inserts the database role or roles that are being used
%groupname%Inserts the name of the distribution group
%email%Inserts the email address of the recipient
%username%Inserts the username of the recipient
%displayname%Inserts the display name of the recipient
%sendiftrue%Triggers sending of the report

The last of these codes is special. Instead of inserting anything into the report, XLCubed examines the parameter’s cell and sends the report only if the value is TRUE. This means that not only can you create a trigger based on your data, but you can create complex logic based on multiple criteria in Excel.

Secondly, each schedule is set to run at particular times on particular days. As you would expect, XLCubed provides a framework that allows you to fulfil a wide range of different requirements, whether you need to run your reports every day, every other week, or even only on the 29th of February.

Lastly, each schedule of course lets you choose who to send the reports to.

Report Distribution

XLCubed allows two different ways to set up the recipients for a report. The first is ideal for where the report needs to be sent to just one or two people. Just type in the email addresses and it’s ready to go.

The alternative is slightly more complex to set up, but once set up, it’s easy to make new reports with the same recipients. To use this, you set up a Distribution Group, composed of any number of people. To make it easy to set up the reports as needed, each Group is assigned a Database Role to use and a Locale to format its numbers and dates. One or more Distribution Groups are assigned to each Distribution List, so that your scheduled report can be sent to more than one group at once:

Distribution Lists and Groups schematic

To illustrate this with an example, imagine that there was a particular report that you needed to send to managers in the USA, China and Germany. Since the formatting and roles would be different for each group of managers, you would need a setup something like this:

Distribution Lists and Groups example