“Prev” and “Next” in XLCubed Slicers

We’ve been asked a few times in the last couple of months if we can build a ‘Previous / Next’ selector for date hierarchies, which allows the user to quickly navigate sequentially through months or days. The answer is of course ‘yes’,  otherwise it would be a very short blog..

One of the key strengths of XLCubed is it’s tight integration with Excel, and it means that with some creative thinking the answer is very rarely  ‘no you can’t’. Here we use a combination of our slicers, the xl3membernavigate function, and standard Excel formulae to produce a very effective selector for just this scenario.

A working example of this which connects to the sample bicycle sales local cube which we  ship with the product is available here or you can view the online demo here.

There are a couple of key things to note with this approach:

1) Slicers are typically populated direct from the cube, which makes them very flexible and dynamic. However a less well known aspect is that slicers can be driven from an excel range, and in this case that’s what we’ll be doing.

2) XL3MemberNavigate(). A fairly new formula which allows you to traverse a hierarchy dynamically in a multitude of different ways. Here we just scratch the surface.

To begin with we need to prepare a range of cells in Excel to base the slicer on, in this case the months, and we also need to ensure it’s dynamic and can change with the underlying data structure.  We need to prepare a table of similar structure to the below.

Cell B2 is the selection made by the user in the slicer, which we’ll come back to. The other columns in the table show:

Description:

Logical description of what the row is

Month:

The month available for selection, determined by whatever the user chooses in the slicer, and the Xl3MemberNavigate formula (Insert Formula – Member Navigate) .

Checked Month:

Validation checks on the month to cater for when the first and last available months are selected.

Slicer Display:

what will be displayed in the slicer dialog for user selection.

The first month uses MemberNavigate to get the first available month. This is very straightforward in the MemberNavigate dialog, and will insert a formula in this syntax: XL3MemberNavigate(1,”[Time]”,”[Time].[Month]”,”FirstMember”). Last month is achieved the same way, but using ‘lastmember’.

Previous and Next are again achieved using MemberNavigate, this time the syntax will be:  XL3MemberNavigate(1,”[Time]”,SlicerData!$B$7,”Previous”).

Displayed month is simply what the user has chosen in the slicer.

 Adding the slicer:

Add a slicer from the XLCubed ribbon (or insert slicer menu in 2003). On the selection tab, choose ‘slicer range’ and select C5:D9 on the table shown above. Then set the slicer Type to be buttons. Lastly, on the settings tab, set the slicer to update cell B2 on the SlicerData sheet.

Optionally, you can also name the slicer and choose to show a title bar, as we have in this example.

On inserting the slicer, you’ll need to resize the control itself, and possibly also the size of the buttons if the data member names are long.

You should now have a slicer which enables Prev/Next selections, along with first and last.

Using the slicer in a report

The slicer isn’t currently connecting to anything, or changing filters within a report. To do that, as it’s not directly connected to a hierarchy in the same way as a standard slicer, we need to go via the excel cell which it updates. So any XLCubed grids or formulae need to reference the cell which the slicer outputs its selection to, in this case in this case SlicerData!$B$2.

In our example we’ve just connected one grid, but there can be as many as required. Our example also gives some sales and costing detail for the main product categories. We also use in-grid sparklines to give a feel for the trend, and these can be drilled or sliced and diced in the same way as a standard grid.

The working example can be downloaded here, or a similar version published to XLCubedWeb used online here.

 

 

An Excel User in a Cubed Kingdom

I’ve been using Excel for my entire professional career, most of the time in large corporations where adding a piece of software to the standard IT structure would be some kind of heresy. When I have a business need that can be solve by an out-of-the-box Excel installation that’s the path I follow (I’m also a power user of the company’s formal BI tool, so I know where to draw the line).

Over time, I’ve developed a framework that helps me to solve problems from a very specific point of view: how to minimize file size, how to minimize calculation time, how to deploy, how to update, and so on. This is the logic that you often must follow, and you tend to believe it is the best one. At that point you must start a conversation (a very fashionable word nowadays..) with someone that doesn’t share that logic.

Take OLAP cubes, for example. I never use them. I use the corporate BI tool or I create a 100% pure Excel application. But then Andreas told me about XLCubed and how you could deploy online your always-updated-file. That was a turning point because for me those are two killer features that I’ve been yearning for a long time.

I started to play with the tool, but still using the same logic. And was plain wrong. Sure I could use all my Excel background, but I needed to adjust it to a different logic. Using an OLAP cube you get a new set of functions that simplifies much of your work and you need to reevaluate some Excel functions because some of them will perform better under this new environment. You’ll leverage your Excel background to create a a new logic at a higher level.

I’m an experienced Excel user, probably like you, and this is just Excel on steroids. I’m leaving my comfort zone, one foot at a time, and I’ll document and share with you my learning curve. So come with me to the Cubed Kingdom and we’ll walk through this together.

First stop, next post: what is a cube, anyway?

Smart Dashboard Ranking Tables

Chandoo and Robert over at the PHD blog have a nice a 4 post series of posts about Creating KPI Dashboards in Microsoft Excel.

I really recommend you to read Robert’s articles. Having scrolling in your sorted table is just a really smart addition to your Excel dashboard.

Yesterdays post was about Adding One Click Sort:

Continue reading “Smart Dashboard Ranking Tables”

Hyperlink Legends to Highlight a Series

In a recent post Jon presented a way to dynamically hover over the chart legend to highlight a data series. Jon’s method is very smart, as it really shows the capabilities of the event rich Excel chart programing model.  The downside of this approach is that it requires you do code some VBA and only works with chart sheets. The chart events also work in theory for embedded charts, but you have to activate the chart, in order to make Excel handle your events, which is one click you want to avoid.

So I picked up Jon’s idea and tried to combine it with ParamLink. In  The Missing Link I introduced the (free) ParamLink Add-In. It implements a new hyperlink formula ParamLink(). When the hyperlink is followed the formula can set cell values or define names.

image

 

Continue reading “Hyperlink Legends to Highlight a Series”

The Missing Link (Part 1)

Every good discipline needs a missing link. Evolutionary biology had a missing link between humans and the ‘lower’ animals. Physics has a missing link between quantum mechanics and general relativity. The Information Visualization community discovered the Missing Link Between Information Visualization and Art.

Now we discovered the missing link in Excel Data Visualization.

As we can see in Wades winning Bank Dashboard we can greatly increase the amount of information that can be included in a dashboard by using sparklines in an overview table

image

 

Continue reading “The Missing Link (Part 1)”

Effective Management Reports? Interview with Rolf Hichert

Professor Rolf Hichert is the foremost specialist for information design for financial professionals in the German speaking world. His seminars have been attended by thousands of CFO’s, financial controllers in Germany, Austria, Switzerland and the UK. Recently my friend Martin from INTALIGN had the pleasure to interview Professor Dr. Rolf Hichert about his view on what makes an effective management report:

Professor Hichert, your extensive research claims management reports are often ineffective and largely misunderstood, mainly because they are simply never read. Yet we all continue to create these complex documents. Why is that?

Hichert: Reading management reports is enormously time-consuming, in a time when our corporate culture is particularly time-poor. Concise messages are buried, or missing altogether, phraseologies can be confusing, and notation is often not uniform. Frequently, those who do understand the reports have had prior knowledge of its contents, so they are simply reinforcing what they already know. Financial controllers in particular are frequently frustrated by what they perceive to be a lack of understanding, and interest in their reports, despite the tremendous amount of work they may have put into creating a very comprehensive document. I liken the experience to a newspaper editor, who writes a long, in-depth story and then complains about lack of interest by his readers.

What is the main objective of a management report?

Hichert: Reports need to convey a comprehensive message, otherwise they function merely as a statistic or a reference book, comparable to a telephone directory. “To report” means that the creator of the report has taken a certain position and has something of value or novel to say. This may be in the form of statements, explanations, conclusions or recommendations. So, according to this definition, many management reports are not actually reports at all, but merely an exercise in pontification.

Who should be recipients of reports?

Hichert: Structured reports are usually directed to the executive level, the managing directors, and board members. We’re all contributing to information overload however, and there is a considerable increase in the tendency to now supply these reports to middle management, and even trickle them down to all the company’s employees. Other business partners such as banks and investors also have access or are supplied with reports on a regular basis.

We hear a lot of managers complain about the volume of management reports – is that a common problem?

Hichert: Criticism about the extent and thickness of management reports probably dates back to the first ever management report itself. I come across many companies in which senior management are buried under monthly reports containing over 100 pages, an unsurmountable monthly feat to read. And then there are organizations where reports contain only 10 pages or less. The reasons for the extent of management reports are varied; if a report is targeted at a large diverse group for example, it inevitably becomes more extensive as it has to cover a wide variety of needs. In addition, volume may vary depending upon the objective of the report – wether it is to provide an overview, or to give full and complete details. I believe that the question of validity centres less around the extent of a report, and more around the structure itself – is it easy to read and does it follow clear, consistent structures?

Do you then recommend using more charts in a management report?

Hichert: We live in a visual world, where a picture is worth 1,000 words. Pictures are much quicker and clearer to describe complex facts, which might otherwise require substantial wording. It is important to note though, that we can over-use charts as well. Many management reports use charts to visualize numbers that could easily be described in two brief sentences or less. If I want to refer to an export portion of 50% for example, I can easily do this in one sentence, I really don’t need to waste space on a pie chart that depicts only two halves. Such ‘business charts’ serve primarily for ‘optical loosening up’ reports that otherwise might only contain tables and texts. Financial analysts typically complain to me that “my boss is a numbers man, he doesn’t like charts, he prefers tables.” If you look at the quality of charts produced, you can understand this notion. Typically these charts have very low ‘information density’ and are weak illustrations, with no clear message, ‘cut off’ axes, and lack a consistent concept of notation and design structures.

How important is the inclusion of strategic aspects into management reports?

Hichert: Naturally, the structure and contents of management reports should be as aligned as possible to the company’s overall targets. The now popular introduction of a Balanced Scorecard into an organization, with the objective of aligning operations with corporate strategies, offers the ideal opportunity to rethink and improve corporate reporting systems.

What are your thoughts on packaging reports  ‘decoratively’?

Hichert: We now have easy access to creative programs which render all of us amateur graphic designers. Too often, though the necessary knowledge about basic information design principles is missing. CD (Corporate Design) guidelines, that are in principle important to unify content typically  don’t address those either. From our research, we know that the simpler the structure, the easier the report is to understand. Our work shows that such graphic elements as colored backgrounds, decorative pictures, pseudo-3-D-display, shades, frames or other design facets which may be inserted without meaning, should be considered as noise. Rather than add to a report, these features ultimately reduce the quality and the message of a report. Decorations that are unnecessary additions can dilute and crowd out the message. The over-use of color is the most common source of error. Color should only be used if it has an assigned meaning. One shouldn’t for example, expect that the reader will understand the use of red and green as traffic light colors, indicating stop and go on projects, if these colors are also used in other areas of the report for purely decorative purposes.

What is your recommendation in regards to how to deal with the display of variances between actuals and targets or plans?

Hichert: Typically, the major portion of a management report should demonstrate substantial deviations between targets and current actual values. This includes different forms of deviations, such as, for example, between previous years or even more importantly, corporate numbers versus industry benchmarks. If deviations are important, then they should also be concisely represented and emphasized through colors, arrows or frames. The more important the deviation, the more the emphasis must be marked. Professional report guidelines should ensure that equally relevant deviations are equally marked and represented. And it should apply not only to charts, but to tables and texts as well. This principle should always be applied to any reports in an organization, in a consistent and uniform manner.

So, the format of reports should be standardized?

Hichert: We strongly recommend employing a consistent uniform design concept which can be easily understood and interpreted without confusion. The key sign of a quality report is its ability to convey a message and explain facts in a clear and simple manner. Its objective is not to be an object of beauty. Today, we rarely see organizations that have mapped out clear guidelines and rules for scaling, usage of color, when to use what chart types, tables or texts. But consider the road map which universally utilizes a single color scheme; a river is always blue, the scale is always on each side, and north is always at the top. Whether in Australia, or Africa, the rules of the map remain the same. With management reports, it‘s usually left to the creator whether turnover figures are shown in blue columns or green lines – rendering it difficult guesswork for the executive who is forced to interpret and understand it. To be fair, cartographers needed many hundred years to develop visualization of roads, cities and to unite standards – so the financial controllers still have some time…