//As we’ve seen in the past, XL3Link is a flexible formula that is really worth knowing about to help easily build interlinked reports. To some extent it’s like a Swiss army knife – multi-purpose! From highlighting a series on a chart to hosting a tooltip, XL3Link is a flexible formula that can help make your reporting more interactive at multiple levels.
In this post we’ll be discussing a few more novel ways to use XL3Link.
//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.
//A common scenario in business reporting is for different users to want reports to open with ‘their’ view on the data. So for a particular Store or business unit.
Many back-ends already support setting Default Members for different users and groups, for example Analysis Services allows this, but as XLCubed can connect to an increasing number of back-ends we can’t rely on this and sometimes a single report user may have different combinations of views on data (for example, a set of store/product group combinations)
Version 9.2 of XLCubed has added Workbook Aspects to help with this, allowing users to store their own slicer selections and quickly switch between those and also to set the default for when the report is loaded.
With this feature you can define the aspects at a report level, shared by all users, and also allow users to define their own private aspects.
Continue reading “Workbook Aspects – Same Report, Different View”
//In monthly reporting for the current year the most pertinent numbers for closed periods are the actuals, and for open or future months the forecast. In most cubes actual and forecast (or budget) are implemented as either different measures, or as part of a scenario hierarchy. This can lead to challenges in reporting, where putting both the period and the actual/forecast on the same axis means you will get duplicated months which show the forecast for periods when the actuals are already in as in the PivotTable below.
We would want the report to be asymmetric (i.e. different months returned for Actual and Budget). Various tools can handle that, and it’s always been possible to achieve that in XLCubed through formulae or in a grid. What’s more difficult is to make the report fully dynamic without the need to redesign it each time. Ideally we’d want the user to be able to change a slicer to select the last completed month (or pick this up directly from the cube), and have the appropriate months reported under Actual and Budget. Here’s how to achieve it in one grid…..
The approach uses a combination of:
Let’s work through this example which uses the Finance cube on the AdventureWorks DW 2008R2 database.
We’re going to use data from FY 2006 and report Actual and Budget figures across departments. We will use a slicer to select the last Actual month we want reported. For the remaining FY 2006 months we will report Budget values.
Entire year of Months in the report
There are several ways to achieve this – we added an XLCubed Excel driven slicer (XLCubed – Slicer – Excel) based on the range $AA$1:$AB$12 as shown below, containing all months in FY 2006. This lets the user pick the last month for which we want to report Actuals.
The slicer is set to output its selection into $B$1. In $C$1 the formula =VALUE(B1) converts the text output of the slicer into a number which we’ll use to calculate which months should report Actual and which Budget, see the screenshot below:
Date Table
This is an Excel range supporting the reporting logic. Row 2 contains every month in the reporting year. Row 3 assigns an incremental number to each month, 1-12.
We can then compare the value of the selected month from the slicer ($C$1) with the value for each month. Basically if the month is less than or equal to the selected last actuals month it should be reported as Actuals, otherwise as Budget. An example formula contained in B4 for Actuals would be: =IF(B3<=$C$1,B2,””) . For the Budget row the formula has the inverse logic.
At this stage we’ve used a little XLCubed, and the flexibility of Excel to match the appropriate months to Actual and Budget. We now need to add the report itself.
We’ll create our grid – initially we set it up to report Budget and Actual figures across the year, by basing the selection for Date.Fiscal on the range containing all months for the year ( $B$2:$M$2).
We then use XLCubed’s right-click menu (Keep – Exclude From Display) to exclude members. It doesn’t matter which slices are excluded at this point. To do this highlight ‘Actual’, followed by the first available month and then choose Exclude From Display. Repeat this for ‘Budget’ and the first available month. We will exclude July 2005 for Actuals and August 2005 for Budget. You will notice the red comment marker on the grid. Right-click on this cell and choose Axis – Edit, and go to the Excluded Slices tab.
The above screenshots show that we are currently excluding July 2005 from Actual as well as August 2005 from Budget.
We can then easily change the Date Fiscal selection for each scenario. For Budget we will exclude Budget values for months in cell locations $B$4:$M$4 (as we want to report Actuals for those entries) and Actuals values for the months in cell locations $B$5:$M$5 (as we want to report Budgets for those entries).
The grid report now looks like this with Actuals being reported up to November 2005 and Budgeted for the remainder of FY 2006:
We have also set Merge Repeating Cells on (in Grid Properties). You could do further formatting – for example by colour filling all Budget values and hiding the grid headers.
The end result is a one grid report giving the Actual:Budget month mix that we want with the monthly maintenance process a case of simply changing a combo box selection.
//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.
//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.
//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.
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.
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.
//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…
