//If you have slow queries when reporting table format data in Excel from PowerBI.com, Azure Analysis Services or Tabular SSAS, this could be just the performance boost you need.
Continue reading “Faster Excel Queries For Power BI & Azure Analysis Services”
//This week’s blog is a quick tip on Grid formatting options. Did you know that XLCubed comes with ten pre-formatted grid styles which you can easily switch between?
These are available from the Workbook Format button on the XLCubed ribbon (the option remains greyed out until an XLCubed grid is added).
//Did you know how easy it is to search for members within XLCubed?
//Most formatting in XLCubed can be handled though the formatting options available on the right-click menu, and today’s blog will cover some of the common use cases.
//Propagate across Sheets
Today’s blog will run through Propagate across Sheets – it’s an XLCubed feature that’s been around for some time but is definitely one of our hidden gems!
Propagate across Sheets is some great functionality that allows users to quickly replicate a report onto additional sheets within the same workbook or a new workbook.
//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.
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.
In addition to the member list required by the XL3LookupRW formula, the XL3DoWriteback formula requires two extra parameters:
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: FALSEC3: 1,000C4: 0.85C5: 20,132C6 =C3*C4*C5D3: =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.
To make it as easy and efficient as possible, we used:
=XL3Link(XL3Address($A$1),"Write changes",,XL3Address($B$1),TRUE)
The final workbook looked a little like this (except, of course, much larger!):
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.
//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:
| Property | Description | Value |
|---|---|---|
| “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:
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!
//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!
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.
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:
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.
//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.
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.
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.
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:
| Code | Description |
|---|---|
| %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.
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:
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:
//We’ve revisited one of our more popular guides Heatmap Tables with Excel as they can be a very effective way of presenting data on a dashboard, and have now updated it for Excel 2010…
This Heatmap Table is designed to show you the revenues and the discounts of a company over the course of one year per product group. The size of a bubble shows the revenue made in a particular month and the bubble color shows the discount rate given. The discount rate has been encoded as a range of green colors, ranging from a light green, for low discounts to a dark green for high discounts. The years and product totals are shown at the right and bottom as an integrated part of the table.
Tufte often talks about the integration of numbers, images and words; I think he’s quite right. A way to achieve this in Excel is to integrate charts into tables, so called graphical tables, a very effective means to show “More Information Per Pixel“.
The heatmap table is based on a regular Excel bubble chart. To integrate a bubble chart into a table the bubbles are positioned in a matrix that has the same row and column layout as our table.
In our case we generate a data series table with one column for the X-Series going from 1-12 for January – December and one column for our Y-Series going from 1-8 for our 8 product groups and one column for revenue.
In the sample spreadsheet we’ve setup some simple excel formula to translate data from the classic grid layout:
to the required format:
Now we can insert the bubble chart:
To ensure that the charts fit exactly into the table grid we set Min/Max for the X axis to 0.5/12.5 and for the Y axis to 0.5/8.5. Excel would calculate much larger auto scales otherwise. Also set the Major units to 1 so we can use that later to set some grid lines.
Now we remove the legend, the X and Y axis, maximize the plot area and align the chart with the Excel table. As the bubbles are initially too large we have to make them smaller. To control the bubble size go to Data Series Options and scale the bubble size to 50%:
This already makes a nice bubble table you could use to reproduce the Twitter Charts.
For the grid lines format your table headers and grid lines with light gray grid lines. Resize the plot area, remove the border and re-position the chart so that the chart and the table grid lines align.
To create the heatmap with different colored bubbles we use the fact that by default Excel does not plot data points for #NA values. For the heatmap we overlay 8 bubble series, one series per green shade, and show a revenue bubble only if the value fits into the value range that corresponds with a green shade of our color ramp, otherwise we show #NA.
We divide the range MAX(Discount)..0 into 8 groups to define the colours.
The data series columns use the following formula to test if a discount value corresponds with an interval / colour shade:
=IF(AND($E7>I$6-Step,$E7<=I$6),$D7,NA())
The formula returns the revenue, if the discount values is in the interval defined in the column header I$5.
Now create the eight data series so that the bubble size refers to the eight columns in the data table:
And use the Excel chart styles to pick a colour range – make sure you remove the border from the chart area.
And you could use the chart styles to quickly switch between different colours – or customise each series to refine the colors.
You can download a starting point for these files here: HeatmapSample.xlsx. Most of the formulae should adapt to data values that you can feed into the data sheets, including data straight from Analysis Services if using XLCubed grids or formulae.
You can see an interactive version of the Heatmap here – we added a link to some cube data, some Slicers for driving the parameters and then published to XLCubedWeb.
