Excel Do, Dynamic Does

PowerPivot & DAX & Data Explorer BlogRoll - 31st March 2013

Bob Phillips — Tue, 02 Apr 2013 10:35:00 GMT

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 31st March 2013.

SQLBI
Checklist for Memory Optimizations in PowerPivot and Tabular Models - bit.ly/17aAaNr

Beyond Rational
MS BI # 85 - PowerPivot #2 - Download , Install , Connect to SQL Server and Getting Started - bit.ly/14BHhyJ

Bacon Bits
Use Data Explorer to Hack into Very Hidden Sheets - bit.ly/ZIcNqk

readwrite enterprise
Microsoft's Data Explorer: Picking Up Where Bing Leaves Off - bit.ly/16XQXDd

Matt Masson
Consuming CANSIM Datasets using Data Explorer - bit.ly/10llbL8

PowerPivotPro
Always Show Yesterday, Today, or Tomorrow’s Data - bit.ly/14KrjCL

Lightswitch For The Enterprise
Self Reporting via PowerPivot In Highly Deferred Execution Mode - bit.ly/VtkjBj

Data Explorer
Data Explorer Online Search Overview - bit.ly/10eJLfx

PowerPivotPro
New Version of Data Explorer Released - bit.ly/16il19t

Katie & Emil Newsletter
Add-in Data Explorer and Excel tutorial - conta.cc/XDYIHw

SQL blog.com
BI Beginner: Data Explorer is a must have - bit.ly/YBl7bO

Gobán Saor
M’s back – Create a PowerPivot Time Dimension using Excel’s Data Explorer - bit.ly/ZqJVmq

Gobán Saor
The Model of a very modern PowerPivot – without the add-in - bit.ly/YDgRFE

PowerPivot & DAX & Data Explorer BlogRoll - 24th March 2013

Bob Phillips — Mon, 25 Mar 2013 21:29:00 GMT

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 24th March 2013.

Romi's Stream
Data Explorer for Excel: An experiment with Sachin Tendulkar - bit.ly/1188Lrc

Kasper de Jonge PowerPivot Blog
NBA team spending and their results with Excel, PowerPivot and Data Explorer - bit.ly/10sJuJo

SQLServerCentral.com
Stairway to PowerPivot and DAX - Level 4: The DAX BLANK() Function - bit.ly/Zllw1a

BaconBits
5 Cool Things You Can Do With Data Explorer - bit.ly/Ydiorz

LessThanDot
How I used Data Explorer to create a demo - < a href="http://bit.ly/10hIYhi">bit.ly/10hIYhi

PowerPivotPro
ISFILTERED: A Better Way to Detect Totals - bit.ly/ZRvCon

Brent Greenwood's BI Blog
Pareto Chart with PowerPivot - bit.ly/14cfXXs

Gobán Saor
[Updated]Look’s like Star Schema is back on the menu! - bit.ly/YBcB8Z

Chris Webb's BI Blog
UK/US Date Format Bug in PowerPivot and SSAS Tabular - bit.ly/WMd8Jy

Romi's Stream
Data Explorer for Excel: An experiment with Sachin Tendulkar - bit.ly/1188Lrc

Machlis Musings
New Excel Data Explorer add-in bolsters data analysis - bit.ly/16Uah4g

MSSQLTips
Creating Hierarchies in PowerPivot - bit.ly/X8uAZS

PowerPivot & DAX & Data Explorer BlogRoll - 17th March 2013

Bob Phillips — Mon, 18 Mar 2013 09:24:00 GMT

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 17th March 2013.

MS Office Blog
Learn about Data Explorer Formulas - bit.ly/118wr2d

Javier Guillén Pervasive Business Intelligence
PivotStream and Mariner join forces to deliver PowerPivot solutions - bit.ly/118uzqc

Chris Webb's BI Blog
Finding Shakespeare’s Favourite Words With Data Explorer - bit.ly/133FUcz

Erik Svensen
How to use Data Explorer for Excel to extract data from Statistics Denmark - bit.ly/YKjCag

MSDN Blog
A new build of Data Explorer, and an Auto Update feature - bit.ly/WwuR7P

SSIS Junkie
M Lives! - bit.ly/10JyB8r

Gobán Saor
DAX’s back – Create DAX Measures in Excel 2013 without PowerPivot Add-in - bit.ly/XjZZn4

Kasper de Jonge PowerPivot Blog
What is eating up my memory the PowerPivot / Excel edition - bit.ly/113zmpl

Some Random Thoughts
Unpivoting Data in Data Explorer- bit.ly/118xIGy

Javier Guillén Pervasive Business Intelligence
Configuring "drill to details" behavior in PowerPivot: Part 1 - bit.ly/YlDZbE

PowerPivotPro
PowerPivot on Airplanes and NBC - bit.ly/142RIen

PowerPivot & DAX & Data Explorer BlogRoll - 10th March 2013

Bob Phillips — Sun, 10 Mar 2013 23:57:00 GMT

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 10th March 2013.

Extended Results
Calendar Analytics - bit.ly/VYvfLU

PowerPivotPro
PowerPivot Discovers a New Form of Communication in the Animal World, Makes Headlines in Science! - bit.ly/YhOPhN

Matt Masson
Using Data Explorer with Power View - bit.ly/XYGt2H

Customer Feedback SQL Server Content Samples
Data Explorer: An introduction to table relationships and the Expand operation - bit.ly/XWWtlN

PowerPivotPro
"Introducing" Data Explorer - bit.ly/Z4BSdv

Chris Webb's BI Blog
Data Explorer–Where Does The Real Work Get Done? - bit.ly/VOkkVc

Dynamics NAV Financials
PowerPivot to the People - bit.ly/YtSzzK

PowerPivotPro
A funny thing happened in Redmond… - bit.ly/12rBmfH

Gerhard Brueckl's BI Blog
Another Post about Calculating New and Returning Customers – Part 2 - bit.ly/YKRMHL

Chris Webb's BI Blog
Calling A Web Service From Data Explorer, Part 2 - bit.ly/Y9gdi1

Vignesh C
Consume csv with Data Explorer - bit.ly/14lWZcN

Matt Masson
Dynamic Lookups With Data Explorer - bit.ly/Vw6Zkg

Some Random Thoughts
Introduction to Data Explorer Preview for Excel - bit.ly/ZaWrDt

Dan English's BI Blog
Installing Data Explorer Preview & Demo with IMDB Data - bit.ly/W0vvJe

Chris Webb's BI Blog
Calling A Web Service From Data Explorer, Part 1 - bit.ly/WErC9X

PowerPivot & DAX & Data Explorer BlogRoll - 3rd March 2013

Bob Phillips — Sun, 03 Mar 2013 13:20:00 GMT

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 3rd March 2013.

Chris Webb's BI Blog
Importing Data From Multiple Log Files Using Data Explorer - bit.ly/YWPTvA

SSISJunkie
Traversing the Facebook Graph using Data Explorer - bit.ly/VWYY6J

Matt Masson
Access the Windows Azure Marketplace from Data Explorer - bit.ly/YHwRFq

Altius Consulting
Best Oscar winning Film? My first Data Explorer adventure… - bit.ly/VWYC01

Ian's SharePoint Blog
SharePoint OData and the Excel Data Explorer - bit.ly/160EaiL

Chris Webb's BI Blog
Public Preview of Data Explorer - bit.ly/XPmXlK

Microsoft
Microsoft "Data Explorer" Preview for Excel - bit.ly/Y6WY8M

PowerPivotPro
Toggling Conditional Formatting On/Off via Slicer - bit.ly/XLISNW

SQLBI - Marco Russo
Update to the PowerPivot for Excel 2013 licensing - bit.ly/XLILlB

TechCrunch
Microsoft Launches Updated Office 365 For Business, Adds ProPlus With Full Office Apps And New Small And Medium Business Versions - http://tcrn.ch/YHyaEb

MSSQLTips
Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel - bit.ly/WAQ2kD

PowerPivotPro
How to buy PowerPivot 2013, and the $30 Volume Licensing Workaround - bit.ly/ZYLrzc

MSSQLTips
Combining Data from Multiple Relational Data Sources into One Table in PowerPivot for Excel - bit.ly/13dFUFY

PowerPivot (& DAX) BlogRoll - 24th February 2013

Bob Phillips — Tue, 26 Feb 2013 09:14:00 GMT

PowerPivot & DAX activity announced on Twitter for the week ending 24th February 2013.

InfoWorld
More Office 2013 bait-and-switch revelations - bit.ly/XWHF5d

RAMBLES.IO
A Response To Where Did My PowerPivot Go? - bit.ly/W5RRep

Erik Svensen
Create a dynamic table from PowerPivot data in Excel 2013 without VBA - bit.ly/11Uwsre

SQLBI - Marco Russo
DAX Time Intelligence for 4-4-5 Calendar, ISO Calendar and other custom ones - bit.ly/UDm1nM

PowerPivotPro
Revolutionaries in Redmond! PowerPivotPro Readers Quoted in ZDNet! - bit.ly/YvMxxd

DynamicAccounting.Net
Microsoft peed on my PowerPivot - bit.ly/152Km8G

ZDNet
Can Microsoft bring BI to the masses if the Excel 2013 masses can't get BI? - zd.net/ZcFt9G

SQLBI - Marco Russo
The Right Version of Excel 2013 for using PowerPivot - bit.ly/151oieu

Gobán Saor
Look’s like Star Schema is back on the menu! - bit.ly/Y931eM

PowerPivot (& DAX) BlogRoll - 17th February 2013

Bob Phillips — Sun, 17 Feb 2013 20:55:00 GMT

PowerPivot & DAX activity announced on Twitter for the week ending 17th February 2013.

The Data Specialist
Equivalent of VLOOKUP in DAX–Part I - bit.ly/Xk0FKL

PowerPivot Pro
Hey, Who Moved My (PowerPivot 2013) Cheese? - bit.ly/11OZdWp

PowerPivot Pro
New Beginning - bit.ly/15fNsXV

PowerPivot Pro
Tomorrow is the Last Day for Chandoo Sign-Up! - bit.ly/VUlOOD

The Data Specialist
Bug in PowerPivot 2012 for Excel 2010 - bit.ly/WLhoIh

PowerPivotPro
"Announcing" the Business Analytics Conference - bit.ly/XPDerQ

BaconBits
PowerPivot and PowerView Confusion in Office 2013 - bit.ly/Vpfqws

Technitrain
[Course]Advanced DAX - bit.ly/Vpfqws

Gerhard Brueckl's BI Blog
Fiscal Periods, Tabular Models and Time-Intelligence - bit.ly/W3SiAH

SQL Chick
Checklist for Knowing Your PowerPivot Model is Complete - bit.ly/YzWEQh

PowerPivot (& DAX) BlogRoll - 10th February 2013

Bob Phillips — Tue, 12 Feb 2013 00:30:00 GMT

PowerPivot & DAX activity announced on Twitter for the week ending 10th February 2013.

PowerPivotPro
Guest Post from Ken Puls: Determine Effective Tax Rate - bit.ly/U2vdlc

PowerPivotPro
When to Use Measures vs. Calc Columns - bit.ly/VQSm8E

SQLBI
First DAX Advanced Workshop in London, May 2013 DAX Tabular SSAS - bit.ly/Y3sx4Y

Excel Hero Blog
Master Excel PowerPivot Now - bit.ly/X0vnXa

Javier Guillén Pervasive Business Intelligence
Developing child-aware KPIs in PowerPivot and BISM Tabular - bit.ly/VDtCyI

Some Random Thoughts
UNION Operation in DAX Queries - bit.ly/XTZa33

ProLogika Forums
PowerPivot Data Refresh in Excel 2013 - bit.ly/XU0wuK

PowerPivot (& DAX) BlogRoll - 3rd February 2013

Bob Phillips — Tue, 05 Feb 2013 13:11:00 GMT

PowerPivot & DAX activity announced on Twitter for the week ending 3rd February 2013.

Chandoo.org
Introduction to DAX Formulas & Measures for PowerPivot - bit.ly/126dmx9

Cindy Gross
Hurricane Sandy Mash-Up: Hive, SQL Server, PowerPivot & Power View - bit.ly/WLYQan

Chris Webb's BI Blog
Office 2013, Office 365 Editions and BI Features - bit.ly/14zvxdt

MSSQLTips
PowerPivot with Excel 2013 - bit.ly/XenvCk

PowerPivotPro
Stars and Snowflakes and Bears, Oh My! - bit.ly/WrDNdG

SQLBI
Execute a DAX Query on SSAS Tabular in Excel - bit.ly/Y7zHDP

SQLBI
Import Data from Tabular Model in Excel Using a DAX Query - bit.ly/UsB0Sx

SQLServerCentral.com
Stairway to PowerPivot and DAX - Level 2: The DAX COUNTROWS() and FILTER() Functions - bit.ly/TQbVRb

Project Botticelli
[Pay Site]New Video Tutorial by Marco: Querying with DAX - bit.ly/11FJzLY

PowerPivotPro
Mini Post #3 of 3: Are You Using Excel 2013 Yet? - bit.ly/XelcPO

PowerPivotPro
Mini Post #2 of 3: Supporting/Example Files from the Book Available for Free Download - bit.ly/11FHPCz

PowerPivotPro
Mini Post #1 of 3: Interview at SoftwareAdvice.com - bit.ly/11OzlUX

Pyramid Analytics
Pyramid Analytics announces deeper Integration with PowerPivot and SQL Server Tabular Analysis- bit.ly/WXSbcV

Technet
Tutorial: Optimize a Sample PowerPivot Model for Power View Reports - bit.ly/UssbZ1

BurningSuit
What is PowerPivot - conta.cc/VGpoMe

Salvo(z)
Currency Conversion in Tabular Model Using DAX - http://bit.ly/118W7LG

PowerPivot-info.com
PASS Business Analytics conference - Chicago, IL - April 10-12, 2013 - bit.ly/WpEGT8

Gerhard Brueckl's BI Blog
Dynamic ABC Analysis in PowerPivot using DAX - bit.ly/T3PBSv

PowerPivot BlogRoll - 27th January 2013

Bob Phillips — Tue, 29 Jan 2013 09:39:00 GMT

PowerPivot activity announced on Twitter for the week ending 27th January 2013.

Chandoo.org
Introduction to DAX Formulas & Measures for PowerPivot - bit.ly/126dmx9

Kasper de Jonge PowerPivot Blog
Filtering results to only show top x results in Power View chart - bit.ly/UzkC0n

PowerPivotPro
Modeling Viral and Marketing Growth, Part 3 of 3 - bit.ly/114srjB

Chris Webb's BI Blog
Building Relative Date Reports in PowerPivot - bit.ly/XOEXw8

Stefan Johansson
Create Time Dimension- bit.ly/126aWyJ

PowerPivotPro
Modeling Viral and Marketing Growth, Part Two - bit.ly/XOEBpl

Brent Greenwood's BI blog
A Time dimension specifically for PowerPivot & AS Tabular - bit.ly/Wut3dK

MindSharp
Demystifying PowerPivot in SharePoint and Excel -http://bit.ly/VkwlNq

PowerPivot BlogRoll - 20th January 2013

Bob Phillips — Tue, 29 Jan 2013 09:36:00 GMT

PowerPivot activity announced on Twitter for the week ending 20th January 2013.

PowerPivotPro
Modeling Viral Growth vs. Traditional Advertising in PowerPivot - bit.ly/10EOrS3

Javier Guillén Pervasive Business Intelligence
Grouping by an Aggregation in DAX - bit.ly/SeC22c

PowerPivotPro
Calendar Tables: Not Just for Formulas. Use Them on Your Pivots Too! - bit.ly/W6YrMa

Javier Guillén Pervasive Business Intelligence
Leveraging DAX query tables for staging Data Transformations - bit.ly/13Awule

Some Random Thoughts
Row Selection Using Slicers in PowerPivot - Part 1 - bit.ly/108bp2H

Chris Webb's BI Blog
A Different Approach To Last-Ever Non-Empty in DAX - bit.ly/Y82Owx

Excel Do, Dynamic Does
CUBEs For Dessert - Cube formulae with MDX - bit.ly/13uGmNf

SQLBI - Marco Russo
PowerPivot Comatibility across versions - bit.ly/UKJ9MM

SQLBI
Linkback Tables in PowerPivot for Excel 2013 -bit.ly/UBhmAi

PowerPivot BlogRoll - 13th January 2013

Bob Phillips — Sun, 13 Jan 2013 19:20:00 GMT

PowerPivot activity announced on Twitter for the week ending 13th January 2013.

The New Internal Data Model in Excel 2013 - bit.ly/VFumXvk

New Customers Per Day Generalized to “New Customers per Month,” etc. - bit.ly/RP7EeK

PowerPivot User Experience Survey - t.co/aGlhdPu7

New Customers per Day – bit.ly/VJtD7j

Getting the active products between a date range using DAX - bit.ly/VDT7AH

[Update] What is using all that memory on my Analysis server instance? - bit.ly/T51Hsc

Parameterising PowerPivot Connection Strings in Excel 2013 - bit.ly/XKBOTH

Technitrain 2013 Course Schedule: SSAS, PowerPivot, MDX, SSIS, TSQL and SQL Server Engine Training in London - bit.ly/U2mVra

Group Multiple Rows to Single Delimited Row in PowerPivot - bit.ly/XiDTlO

CUBEs For Dessert

Bob Phillips — Sun, 13 Jan 2013 19:07:00 GMT

Introduction

A few weeks ago I showed how to create a fully dynamic table using CUBE formulae against a PowerPivot model in Cooking With Cubes.This example presented data from the AdventureWorks database of sales of product categories over years, all integrated with a Fiscal Year slicer.

I recently had a similar need, but with the further challenge of presenting two levels in a hierarchy (actually, this was related tables in a snowflake schema). I needed to show all of the items in the first level, and for each of these items, all of the associated items at the second level including those items that have no sales value. The AdventureWorks database has a similar setup with products, where each sale is for a particular product, each product is of specific product sub-category, and each product sub-category is of a specific product category. Within this model, we would be showing sub-categories of Mountain Bikes and Road Bikes against the category of bikes, but not against any other category.

I will demonstrate my solution using the same AdventureWorks data. Whilst this will be similar to the previous post in showing how to manage the presentation using Excel formulae, it is extended to multiple data levels and some more complex formatting. Additionally, and the main point of this post, I show a technique to display the related values in that hierarchy, not simple with straight CUBE formulae, by adding some MDX trickery in the CUBE formulae.

Figure 1 shows the final results of all of the formulae and formatting.

Figure 1 - Final Results

Preparing With Good Ingredients

First I need to prepare the PowerPivot model so that I have have product name, product sub-category name, and product category name all on the Product table. To achieve this, I added two calculated columns

[ProductSubcategoryName]
=RELATED(ProductSubcategory[ProductSubcategoryName]
[ProductCategoryName]
=RELATED(ProductCategory[ProductCategoryName]
Formula 1 - PowerPivot Model Calculated Columns

Mixing It Up

The data is ready, so let's build the required data. I need the following:

a text definition of the cube
a set of all categories
a count if the items in the category set
a list of all of the items in the year set, from the slicer or the dimension if no slicer selections
a count if the items in the year set
a list of all of the items in the category set (in columns)
for each listed category, a set of all associated sub-category items
for each sub-category set, a count of all items in that set
a running total of the sub-category set counts
for each sub-category set, a list of items (in rows)
a reference cell for the sales measure

Taking these in the order given, showing cell(s), defined name, definition:

1. Cube Definition

This is simple text for the connection argument to the cube functions.
J1:[_model]PowerPivot Data
I am using Excel 2010 for this example, Excel 2013 would use the value ThisWorkbookdataModel.
In the previous example, I named this cell _cube, here I use _model.

2. Category Set

This is the set of all categories in the model. This is being taken from the calculated column for product category name in the Products table.
J2:[_setCategory]=CUBESET(_model,"[Product].[ProductCategoryName].Members","Categories")

3. Category Set Count

A simple count of the items in the category set, used in later dynamic formulae.
J3:[_setCategoryCount]=CUBESETCOUNT(_setCategory)-2
I subtract two from the actual count because this set contains the All member and blank, I am not interested in these.

4. Year Set

This is the set of years in the model, constrained by the slicer.
J4:[_setYear]
=IF(CUBERANKEDMEMBER(_model,Slicer_FiscalYear,1)<>"All",
CUBESET(_model,Slicer_FiscalYear,"Slicer Years"),
CUBESET(_model,"[Date].[FiscalYear].Children","Dimension Years"))
As in the previous example, to show all years with no slicer selection (not show All), I check the slicer to see the first set member is All, getting the set from the Date dimsnsion if so, otherwise get the set from the slicer.

6. Year Set Count

A simple count of the items in the year set.
J3:[_setYearCount]=CUBESETCOUNT(_setYear)

6. List of Category Set Items

A list of the items in the category set, for use in building the sub-category sets.
K2:N2:[_listCategory]=IFERROR(CUBERANKEDMEMBER(_model,$J$2,COLUMN()-COLUMN($J$2)+2),"")
The name definition uses a dynamic formula
=OFFSET(_setCategory,0,1,1,_setCategoryCount)
This is a simple dynamic OFFSET formula to build a list of the categories.

7. Sets of Subcategory Items

Sets of sub-category items, one set per category.
K3:N3:[_setsSubcategory]=IFERROR(CUBESET(_model,"EXISTS([Product].[ProductSubcategoryName].Members,"&K$2&")","Products:"&K$2),"")
This is where the MDX magic kicks in. The EXISTS function ... Returns the set of tuples of the first set specified that exist with one or more tuples of the second set specified ..., so we can use it within a CUBESET function to get all of the product subcategory names for the category above, in _listCategory.
Using this technique, instead of building a set of all subcategories, we build a set of the subcategories for a particular category; in our case the category passed to the CUBESET function from the cells in _listCategory.
I prefix the text shown with the text Products: for readability
The name definition uses a dynamic formula
=OFFSET(_setCategory,1,1,1,_setCategoryCount)

8. List of Subcategory Set Counts

A list of counts for the sub-category sets, for use in the layout.
K4:N4:[_setsSubcategoryCounts]=INDEX(CUBESETCOUNT(_setsSubcategories),COLUMN()-COLUMN(_setCategory))
The name definition uses a dynamic formula
=OFFSET(_setCategory,2,1,1,_setCategoryCount)

9. List of Subcategory Set Count Totals

A list of running totals for the sub-category set counts, for use in the layout.
K5:O5:[_setsSubcategoryTotals]=SUM($K$4:K4)-K$4+1
The name definition uses a dynamic formula
=OFFSET(_setCategory,3,1,1,_setCategoryCount+1)

10. Lists of Sub-category Sets Items

Lists of each set sub-category items, within the relevant category.
K6:N30:[_listSubcategoryies]=IFERROR(CUBERANKEDMEMBER(_model,K$3,ROW()-ROW(_setCategory)-2),"")
This is using the same technique to list the set items as in the previous blog.
The name definition uses a dynamic formula
=OFFSET(_setCategory,4,1,MAX(_setsSubcategoryCounts)-1,_setCategoryCount))
Unlike the other dynamic formulae, this one is multi-column, for the multiple categories.

11. Sales Measure

Finally, we have a cell defining the Sales measure
J7:[_sales]=CUBEMEMBER(_model,"[Measures].[Sum of SalesAmount]","Sales")
This is to save repeated definition within the value formulae.

Figure 2 and Figure 3 below show all of the helper cells populated, and the nanmes defined in the NameManager.

Figure 2 - Populated Helper Cells

Figure 3 - Defined Names

It's All About The Presentation

All that needs to be done now is to lay it out, allowing for a variable number of categories and a differing variable number of sub-categories within each category.

In my layout, I am repeating each category a number of times equal to to the number of subcategores for that category, but only showing the first instance. The years are shown as column headings, using the year slicer to filter the columns. The values are displayed referencing the category, sub-category, and year headings.

The formulae are as follows:

Category items

A7:An:=IFERROR(INDEX(_listCategory,MATCH(ROW()-ROW(_start),_setsSubcategoryTotals,1)),"")
This matches the ordinal row number within the list of categories against the running totals for sub-category set counts, so as to determine which of the categories we are listing.

Sub-category items

B7:Bn:=IFERROR(IF(A7<>A6,"",INDEX(_listSubcategories,COUNTIF($A$7:A7,A7)-1,MATCH(ROW()-ROW(_start),_setsSubcategoryTotals,1))),"")
This uses the same matching technique to determine which sub-category set to list.

Filtered Years

D6:H6:=IFERROR(CUBERANKEDMEMBER(_model,_setYear,COLUMN()-COLUMN(_start)-2),"")
Although the slicer controls the selected years, the year set in _setYear outsorts the data, the filtering is done there.

Sales Totals

C7:C50:=IFERROR(CUBEVALUE(_model,_sales,IF($A7<>$A6,$A7,$A7:$B7),Slicer_FiscalYear),"")
This determines the sum of the values for the current row, be that just a category row, or a category and sub-category row, whilst respecting the slicer selection. The IF in the formula caters for that possibility of a category row or a category and sub-category row.

Sales Values

D7:H50:=IFERROR(CUBEVALUE(_model,_sales,D$6,IF($A7<>$A6,$A7,$A7:$B7)),"")
Similar to the sales totals, but using the year heading instead of the slicer, beacuse the year heading has already take the slicewr into account.

Conditional formatting is added to the results to improve the viusual effect and readability.

Product Category Labels

The list of product categories is repeated on every row, changing at the appropriate point of course. This value is needed for the value calculation, but I feel the look is improved if the repeated items are suppressed, so I use conditional formatting to set the font colour to white on repeated items. This uses a simple formula of

A7:A50:=A7=A6

Product Category Totals

I list the product category one more time that its associated sub-categories. This is so that I can show category totals as well as category and sub-category totals. These need highlighting so that the totals stand out. To achieve this, I have made them bold with a blue font, borders above and below. The conditional formatting formula for this is

A7:H50:=AND($A7<>"",$A7<>$A6,ROW($A7)-ROW(_start)-1<=MAX(_setsSubcategoryTotals),COLUMN(A6)-COLUMN(_start)-3<=_setYearCount)

But Is It All Too Rich?

One question that may occur to anyone reading this who is skilled with pivot tables is, why bother? Couldn't the same effect be achieved by formatting a pivot table showing the product category and sub-category and years with the format set to Show Tabular Form and Repeat All Item Labels? This would give a similar look, but it would only show product categories and sub-categories that have a value, it would not show those that are not selling. Agreed, there is a pivot option to Show items with no data on rows which does address this, but the problem here is that it will show all product sub-categories under EVERY product category, my method only shows the relevant sub-categories.

In Summary

In many ways this is similar to the previous post on CUBE formulae, with an extra level of data and more complex formatting. But what I hope that it shows is that by utilising some fairly straightforward MDX the formulae can do far more complex operations, and extend the data displayed. And who knows how far this can be taken as one's MDX skills increase?

It is possible that many of the formulas could be replaced with DAX measures in the model, maybe that is a post for another time.

You can download the workbook here

Acknowledgements

Allan Folting of Microsoft first showed me this technique, I am grateful for his insights.

Chris Webb has a series of excellent blog posts on MDX For PowerPivot Users, which are worth reading if this technique interests you.

PowerPivot BlogRoll - 6th January 2013

Bob Phillips — Sun, 06 Jan 2013 17:56:00 GMT

PowerPivot activity announced on Twitter for the week ending 6th January 2013.

Time To Name This Year's Book! - bit.ly/TyZ5nX

Database Pros in Demand - bit.ly/UXN78m

[Commercial Webinar] Deep Dive into Business Intelligence in Excel 2013’s PowerPivot and Power View - bit.ly/115ls8J

Creating Linked Tables in PowerPivot for Excel - bit.ly/TyZ5nX

Excel 2013, Power View, Top 10 "long tail" and how DAX helpsbit.ly/YJTMkd

PowerPivot BlogRoll - 30th December 2012

Bob Phillips — Tue, 01 Jan 2013 11:41:00 GMT

PowerPivot activity announced on Twitter for the week ending 30th December 2012.

Templated Excel workbooks with PowerPivot and Macro’s - bit.ly/12VdWwP

Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel - bit.ly/UAX22u

PowerPivot – Importing Data from Different Data Sources into PowerPivot for Excel - bit.ly/131Qi0v

End of 2012 and news in 2013 for #PowerPivot, #ssas #tabular and BI - bit.ly/VpwWxI

Refreshing PowerPivot Data in SharePoint 2013 - bit.ly/131Q3Ti

[Video] Using PowerPivot with DataMarket Section of Windows Azure Marketplace - bit.ly/12Vd23e

PowerPivot BlogRoll - 23rd December 2012

Bob Phillips — Sun, 23 Dec 2012 14:47:00 GMT

PowerPivot activity announced on Twitter for the week ending 23rd December 2012.

Using HASONEVALUE in a DAX IF statement - bit.ly/TVMwA7

Create a memory-efficient Data Model using Excel 2013 and the PowerPivot add-in - bit.ly/ZfaRsf

Introduction to MDX for PowerPivot Users, Part 5: MDX Queries - bit.ly/12wLGQI

[Update] What is using all that memory on my Analysis server instance? - bit.ly/T51Hsc

Running Product in DAX: Calculating Portfolio Returns - bit.ly/XOobxz

The CALCULATE Function—New DAX Video Tutoria - bit.ly/XTTirq. Note that this is a subscription site.

Dynamic CUBE Formulae In Excel - bit.ly/R6Fnjs

The Ballad of Ken Puls, DAX Convert - bit.ly/RLv1ac

DAX Spicy Scale Survey - bit.ly/RbtqZC

Cooking With CUBEs

Bob Phillips — Sun, 16 Dec 2012 22:31:00 GMT

As we should all know by now, PowerPivot provides Excel with a powerful way to harness data from one or more sources, and to do further analysis on that data within familiar pivot tables.

Furthermore, because PowerPivot is creating an in-memory cube of the data, it is possible to build an analysis using CUBE formulae. I have blogged a couple of times about CUBE formulae, in Cycling Through The Fog and in Cracking The Code.

In Excel, as with any development, you want your solution to be as flexible and dynamic as possible. This blog is about building dynamic tables using CUBE formulae, but to start with the following formula shows an example of a value extracted from a PowerPivot model using CUBE functions

=CUBEVALUE("PowerPivot Data",
CUBEMEMBER("PowerPivot Data","[Measures].[Sum of SalesAmount]"),
CUBEMEMBER("PowerPivot Data", "[DimProductCategory].[EnglishProductCategoryName].&[Bikes]"),
CUBEMEMBER("PowerPivot Data","[DimDate].[FiscalYear].&[2006]"))

Equation 1

This formula gets the Sales Amount from the PowerPivot cube for the Bikes product category, for the fiscal year 2006. There will be many values at this intersection, there can be many dates in 2006 and many products within that category, all pre-aggregated in the cube; the CUBEVALUE function returns that aggregate amount

We could build the whole table of values using similar formulae. In our table we need to know what the value is related to, so we have row and column headers that identify the intersection points. We could define those headers using the CUBEMEMBER functions giving a table such as shown in Figure 1 below, which shows a table based on AdventureWorks.

Figure 1 - Table of values over year and product category

The formulas for the headings are

=CUBEMEMBER("PowerPivot Data","[DimDate].[FiscalYear].&[2006]")
=CUBEMEMBER("PowerPivot Data","[DimDate].[FiscalYear].&[2007]")
etc. for the column headings,

Equation 2

and

=CUBEMEMBER("PowerPivot Data", "[DimProductCategory].[EnglishProductCategoryname].&[Accessories]")
=CUBEMEMBER("PowerPivot Data", "[DimProductCategory].[EnglishProductCategoryname].&[Bikes]")
etc. for the row headings.

The values at the intersection points simply use these heading cells like so

=CUBEVALUE("PowerPivot Data","[Measures].[Sum of SalesAmount]",$A3,B$1)

This is equivalent to the formula given in Equation 1.

Slicing the Vegetables

Further richness is bestowed upon us because we can also link slicers to our table, giving us the sort of filtering we have with the pivot tables. For example, Figure 2 shows the same data table built using CUBE formulae with a fiscal year slicer; the data reflecting the fact that only the years 2006, 2007, and 2008 have been selected.

Figure 2 - Table of values reflecting years slicer selections

Showing the slicer selections on your report has been covered elsewhere, but it is so useful and asked so often that I thought I would also cover. I also have a couple of variations that I haven’t seen elsewhere, which are worth presenting.

The Menu

Previously, as shown in the formulae in Equation 2, we built the row and column headers using hard-coded values for the year and category fields. We need to be more dynamic in how we list these values. To show the slicer selections as in E5, F5, etc., we need a list of values from which we can choose and display the individual ordered items. The CUBESET function gives us this. The syntax for CUBESET is

CUBESET(connection. set_expression, [caption], [sort_order], [sort_by])

where connection is the cube, set_expression is the set of values required, and caption is a value to display. So, looking at cell D1 we have the formula

=CUBESET("PowerPivot Data",Slicer_FiscalYear,"Set of Years

Equation 3

which would look as shown in Figure 3 when added to cell D1 to build our set of fiscal years.

Figure 3 – Slicer years set formula

As can be seen, we use Slicer_FiscalYear as the set_expression, so the set will include all selected values in that slicer, with the caption signifying the cell contents.

So far, so good, but we still need to list those selected values. For this, we use the CUBERANKEDMEMBER function, which returns the nth, or ranked, member in a set. The syntax of this

=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])

where connection is the cube as before, set_expression is the set of values to choose from, and rank is nth value. So, to get the first member, we use

=CUBERANKEDMEMBER("PowerPivot Data",$D$1,1)

for the second

=CUBERANKEDMEMBER("PowerPivot Data",$D$1,2)

and so on.

Because there are 5 years in the PowerPivot model, and when filtered in the slicer we might be showing less than 5, we need to cater for a variable number of items. The simplest way is just to add an error wrapper around the formula,

=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$1,1),"")

=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$1,2),"")

etc.

Equation 4

Why Extra Ingredients?

We could nest the CUBESET function within the CUBERANKEDMEMBER,but that would mean that a set is evaluated 5 times. By defining the set in its own cell and referring to that cell within the CUBERANKEDMEMBER function, it is evaluated just the once. A small matter, but it makes the spreadsheet easier to maintain, and is more efficient.

Cooked To Perfection

It’s as simple as that.

But hang on a minute, have we overcooked it?

Looking at the syntax definition for these two functions, we can see that they both take set_expression as an argument. The CUBESET function is passed the slicer values as its set, and in turned is passed to the CUBERANKEDMEMBER function as its set.

As the slicer values is a set_expression, you would think that we should be able to pass the slicer values directly to CUBERANKEDMEMBER as a set and be done with. And so we can, these formulae

=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_FiscalYear,1),"")

=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_FiscalYear,2),"")

etc.

Equation 5

work equally as well as those formulae in Equation 4

Managing The Ingredients

The list of product categories can also be listed in a similar way using CUBESET and CUBERANKEDMEMBER. Here we do need CUBESET as there is no pre-defined set of values as we had with the fiscal year that we can pick up. The set will be all values for the Product Category English name in the Product Category table,

=CUBESET("PowerPivot Data",
"[DimProductCategory].[EnglishProductCategoryName].Children",
"Set of Categories")

Equation 6

As can be seen, .Children gets us all of the category values.

One thing to note is the use of the caption argument. Again, this helps to highlight the cell containing the set.

We now have formulae that can define our full table, such as

D1: the formula in Equation 3
=CUBESET("PowerPivot Data",Slicer_FiscalYear,"Set of Years")

D2: the formula in Equation 6
=CUBESET("PowerPivot Data",
"[DimProductCategory].[EnglishProductCategoryName].Children",
"Set of Categories")

E5:I5: the formulae in Equation 5
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_FiscalYear,1),"")
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_FiscalYear,2),"")
etc.,

D6:D9: formulae for the product categories
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$2,1),"")
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$2,2),"")
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$2,1),"")
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$2,2),"")

And finally, in E6:I9, the formulae for the values
=IFERROR(CUBEVALUE("PowerPivot Data","[Measures].[Sum of SalesAmount]",$D6,E$5),"")
etc., each cell reflecting the correct product category ($D6) and fiscal year (E$5).

Our table now has a full set of values, and reflects the choices made in the fiscal year slicer.

(If we wished, we could add the product categories to a slicer, and make our table dynamically reflect that.)

Ready to Serve?

Although we have been diligent in storing the evaluated sets in one place rather than nest a CUBESET function within the CUBERANKEDMEMBER function, but there are still a number of things going on here that I just don’t like:

The connection is hard-coded, multiple times
If a new year is added to the data, just copying cell I5 to J5 won’t work because the rank is hard-coded in the formula, it will need a small change
If no selection is made in the fiscal year slicer, the values shown are the total of all years, with a header value of All, as shown in Figure 4. This may be what is required in some instances, in others we may want to show each year’s values individually.

Figure 4 - Showing all years as a total with no slicer selections

These ‘difficulties’ can be overcome relatively easily.

Rather than hard-code the connection within each formula, put the connection text ‘PowerPivot data’ (without the quotes), in a cell, say D3, and assign an Excel name _cube, then use that defined name within the formulae. Note that this also makes the transition to Excel 2013 simpler, where the connection has now changed to ‘ThisWorkbookDataModel’ (again, without the quotes).

The rank is managed by using a function that returns a variable number depending upon the row or column of the cell, namely ROW([reference]) or COLUMN([reference]). You might think that you can use COLUMN(A1) in E5, and copy that across so that it updates to COLUMN(B1), COLUMN(C1), etc. Believe me, this is a very bad idea. Although everything will be fine at first, what happens if you decide to insert a column before column E? The answer is that COLUMN(A1) will update to COLUMN(B1) and whereas the first column of year table originally reflected the first selected year in the fiscal year slicer, it will now reflect the second. You might say that you would never do that, but no-one ever does until they do. For the sake of a simple change it is hardly worth risking it.

The suggested change is to use COLUMN()-COLUMN($D$5), which uses the top left cell of our table as an anchor point. Thus, a formula in cell E5 using these functions will return 1 for that calculation, and so on. If a column is inserted to the left of the table, those parts of formulae will update to COLUMN()-COLUMN($E$5), which means the formula that was in cell E5 which has now moved to cell F5 still return 1 for that calculation.

Similarly, the category list will use ROW()-ROW($D$5).

Finally, how can we show each year in the column headers and the vales for those years when no slicer selection is made, rather than showing ‘All’ and totals for all years? We already have the formula in D1 that gets the set of selected slicer years, that is

=CUBESET(_cube,Slicer_FiscalYear,"Slicer Years")

As we showed before, we can get a set of all year regardless of slicer selection with the CUBESET function and the member’s children property, as in

CUBESET(_cube,"[DimDate].[FiscalYear].Children","Dimension Years")

But how do we know when to use which? One way would be to test whether the first member of this slicer set returns All. If it does, there are no slicer selections so we show all years individually, if not we show the slicer selected years. We can check the first slicer set value with the following

IF(CUBERANKEDMEMBER(_cube,Slicer_FiscalYear,1)<>"All", …

Adding all three elements together, we have the following formula in D1 that determines what goes into the set of years that will drive the table column headings

=IF(CUBERANKEDMEMBER(_cube,Slicer_FiscalYear,1)<>"All",
CUBESET(_cube,Slicer_FiscalYear,"Slicer Years"),
CUBESET(_cube,"[DimDate].[FiscalYear].Children","Dimension Years"))

With this formula to get the years set, we can see all the years listed when no selections are made on the ribbon rather than showing all year totals, as in Figure 5.

Figure 5 - Showing all years with no slicer selections

What's For Dessert?

That’s about it. Using this technique we have a table that shows the value by year by product category, with a slicer for selecting specific years which is reflected in the years shown in the table. The years and product categories are dynamically built and so can accommodate extra years and extra categories in the source data, and the years can also handle a full slicer set without showing the values as totals for all years.

PowerPivot BlogRoll - 16th December 2012

Bob Phillips — Sun, 16 Dec 2012 20:17:00 GMT

PowerPivot in Excel 2013 - bit.ly/V1jN01

How PowerPivot can help financial reporting -bit.ly/T0o1Dj

Importing your SalesForce data directly into PowerPivot - bit.ly/T0nRMi

Set Up a VM Test Server for PowerView and PowerPivot - bit.ly/R0QY3s

Hide Calculated Items With Zero Totals In PowerPivot PivotTables - bit.ly/12tBW8W

Book Review: DAX Formulas For PowerPivot - bit.ly/12hDDXR

Creating a Spacer Column in a PowerPivot PivotTable - bit.ly/T2bMHu

Who Says The Ribbon Is Hard?

Bob Phillips — Tue, 06 Mar 2012 20:41:00 GMT

Introduction

I was recently chatting with a friend, and he was asking how you can have dynamic ribbon buttons, buttons that are available depending upon worksheet events. I knocked up a simple example, and I thought I would share it here for anyone else who might be interested. It takes a few steps, but it is remarkably easy.

The example has three buttons within a single group, on a custom tab. The first button can be hidden by changing a cell value (a data validation cell in this case), or have its visibility restored. The second does nothing, whilst the third can change the image when a certain cell is selected, from one image to another. This principle could easily be extended to have say different groups of buttons for each sheet in a workbook, hide/expose the group upon activation/deactivation of the sheet.

CustomUI Editor

If you want to look at the xml, you should download the CustomUI Editor. It is hardly what you would call a sophisticated tool, but it does enable you to create xml for the ribbon customisations.

Coding Techniques

A couple of things about my code. I do not hardcode any values in the xml, apart from the ids of the code. Instead I use callbacks for all of the properties. As an example, I could setup a button in the xml like so

which has all of the properties, except onAction, hard-coded.

I could do it this way, but I don’t, instead my xml looks like this

where all of the properties are using callbacks, which means that the value gets set within the VBA code. This is the code for the rxGetLabel callback

Ribbon Code

Public Function rxGetLabel(control As IRibbonControl, ByRef label)

   Select Case control.Id

       Case CONTROLID_TAB_DYNA:                 label = LABEL_TAB_DYNA
       Case CONTROLID_GRP_DYNA:                 label = LABEL_GRP_DYNA
       Case CONTROLID_BTN_DYNA_1:             label = LABEL_BTN_DYNA_1
       Case CONTROLID_BTN_DYNA_2:             label = LABEL_BTN_DYNA_2
       Case CONTROLID_BTN_DYNA_3:             label = LABEL_BTN_DYNA_3
   End Select
End Function

It simply tests for the control’s id, and returns the caption for the label appropriately. All of the values tested for and the values returned are stored as constants in the VBA (they could just as easily be stored in a table on a worksheet). The values of all of those constants in the above code snippet are

' CONTROLID_ constant values
Global Const CONTROLID_TAB_DYNA As String = "tabDynaRibbon"
Global Const CONTROLID_GRP_DYNA As String = "grpDynaRibbon"
Global Const CONTROLID_BTN_DYNA_1 As String = "btnDynaRibbon1"
Global Const CONTROLID_BTN_DYNA_2 As String = "btnDynaRibbon2"
Global Const CONTROLID_BTN_DYNA_3 As String = "btnDynaRibbon3"

' LABEL_ constant values
Global Const LABEL_TAB_DYNA As String = "Dyna-Ribbon"
Global Const LABEL_GRP_DYNA As String = "Dynamic Ribbon"
Global Const LABEL_BTN_DYNA_1 As String = "Button 1"
Global Const LABEL_BTN_DYNA_2 As String = "Button 2"
Global Const LABEL_BTN_DYNA_3 As String = "Button 3"

Managing Changes

Taking one of the dynamic elements, the first button that can be made non-visible, the visible value is not a constant, but a variable as it has to be manipulated by the code

' VISIBLE constant values

Global VISIBLE_BTN_DYNA_1 As Boolean

This variable is initialised as TRUE in the Workbook_Open event, and in the callback procedure, the control’s visibility property is returned as whatever is in this variable

Public Function rxGetVisible(control As IRibbonControl, ByRef Visible)

Select Casecontrol.Id

Case CONTROLID_BTN_DYNA_1: Visible = VISIBLE_BTN_DYNA_1

etc.

The visibility variable gets changed in simple worksheet event code (remember, I am using a data validation cell to drive this code).

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address

Case "$C$3"

VISIBLE_BTN_DYNA_1 = Target.Value = "Visible"

End Select

mgrxIRibbonUI.Invalidate
End Sub

As you can see, it sets the variable depending upon the cell value. Finally, it invalidates the ribbon variable so as to rebuild our ribbon customisations. The ribbon variable gets set in the ribbon onLoad callback

Public Function rxDMRibbonOnLoad(ribbon As IRibbonUI)

Set mgrxIRibbonUI = ribbon
End Function

Invalidating the ribbon variable causes the callback routines to be re-entered; this is how you change things dynamically.

Summary

Hopefully you can see how simple it is to create dynamic ribbon customisations, and how to manage the changes within your VBA code. You should also be able to see how this can be extended to other areas of dynamic interaction.

As I mentioned, I keep the CustomUI very simple, and rely on the VBA code to set all of the properties of my ribbon customisations. This is not necessary, it is a style aspect that I prefer, I like to the XML simple, reducing the amount of changes that I need to make there, and instead drive it all from VBA. I use a host of global constants for my ribbon values, but again as mentioned earlier, it would be just as simple (and maybe pro vide more manageability of the values) if all of the control properties were added as a table on a hidden worksheet, and grabbed from there on ribbon load.

My example workbook is attached .

Ribbon On The Fly Part2

Bob Phillips — Thu, 03 Mar 2011 12:49:00 GMT

Introduction

In my last blog post, I described how I thought that I could build an Excel 2007 ribbon on th fly. The technique was founded upon having a ‘worker’ addin that handled the main functionality as well as the version management and ribbon building; and a simpler Excel 2007 ribbon wrapper addin.

This approach would be similar to the myriad of table driven menu solutions around, but the ‘table’ would be a configuration file in this case, to allow the user to control the menu/ribbon.

The ‘worker’ addin would read the configuration file that held the details of the each procedure to be run, and construct the menu or ribbon on the fly. In the case of the ribbon, the customUI XML would be generated, written back to the (as yet) unopened ribbon wrapper addin, then open the ribbon wrapper addin so as to display the changed ribbon in all its pristine glory.

At that point, this was just an idea, albeit an idea that I was confident that it could be implemented. Since then I have implemented it, so it is time to share the details.

Linking the Ribbon Addin to the ‘Worker’ Addin

Having a ‘worker’ addin and a separate ribbon presentation addin does create one problem, namely how dos a button on the ribbon run a procedure in the ‘worker’ addin. This is easily resolved using Application.Run, rather than a simple call to the procedure, as this allows specifying the file as well as the procedure name.

In fact, this allows even greater flexibility when deploying in the ‘real’ world. If we release our super application, with a configuration file, the ‘worker’ addin, and a ribbon addin, a user can add items to the configuration file as long as it runs a procedure already defined in our ‘worker’ addin. But what if they want to run a completely new process, how do we provide the ability to extend the applications overall functionality? We could just open up the addin and tell the user to do add thewir code to the ‘worker’ addin, but is this a good idea? I don’t think this is good, it could break the whole application. A better way is to tell the user that they can build their code in an entirely separate project, and addin, Person.als/xlsm, or whatever they wish, and the include the full file/procedure call in the configuration file procedure column.

Format of Configuration File

It is probably a good point to describe the configuration file at this point.

I have set it up as a standard Excel workbook, in Excel 2003 format for backwards compatibility, and I have a separate tab for each (user) functional ribbon group (or sub-menu for Excel 2003).

I built this technique to service the running of a set of user reports, which are all template files and generally all run the same process, but it could be anything that you want, you would just setup the configuration data, and extract it in the configuration management code. Figure 1 - Configuration File Group shows a typical layout. If you have any other attributes that you need to capture, just insert extra columns in the first part (in my application, I was importing XML files, so I had to specify the XML table worksheet etc.).

As you can see, some of the columns are relating to the report running, and some relate to the presentation of the ribbon.

Figure 1 - Configuration File Group

I have a column for Report Type, this is so that the client can have the templates in different locations, say company reports on a central server, departmental reports on a departmental server, and the user can have their own report templates locally.

This is all defined on the Client worksheet, Figure 2 - Configuration File Client.

Figure 2 - Configuration File Client

This simply allows the user to provide the client name to appear on the ribbon/menu, a logo file, and the various template directories. Again, if you have a requirement for other client related details, add them here.

The only amendable field here is the client name, the directory values are added by clicking the browse buttons.

I have also added a button to add new groups sheets, so as to keep a consistent format.

Check the Configuration File

Writing the XML back to the ribbon addin is an expensive process, cracking open the zip file, updating the customUI.xml file and then re-writing the file, so I have added a check to ensure this is only done if and when the configuration file is updated.

This is simply achieved with some simple change event code in the configuration file, Figure 3 - Configuration File Change Code, lines 440-450 simply setup a defined name with a value of TRUE. This is checked in the ‘worker’; addin initialise code to determine whether to update the ribbon addin or not.

As you can see, there is a lot of code here for creating a simple name. In addition to this, the code also checks for a duplicate report ID, resetting if a duplicate value is added (this is to avoid compromising the ribbon).

Private mcPrevValue As Variant

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = WS_TEMPLATE Then
    
        wsClient.Activate
    End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As  Range)
      Dim mpName As  Name
      Dim mpSheet As Worksheet
      Dim mpDuplicateRepID As Boolean 
      Dim mpDuplicateGroupID As Boolean 
      Dim mpLastrow As Long
      Dim i As Long

10        On Error GoTo ws_exit
          
20        Application.EnableEvents = False
          
30        If Sh.Name = "Client" Then
          
40        ElseIf Sh.Name = "_template" Then
          
50        Else
          
60            If Target.Column = 1 Then
              
70                mpDuplicateRepID = False
80                For Each mpSheet In Sh.Parent.Worksheets
                  
90                    If mpSheet.Name <> WS_CLIENT And mpSheet.Name <> WS_TEMPLATE Then
                      
100                       mpLastrow = mpSheet.Cells(mpSheet.Rows.Count, "A").End(xlUp).Row
110                       If mpLastrow > 2 Then
                          
120                           For i = 3 To mpLastrow
                              
130                               If Sh.Name <> mpSheet.Name Or Target.Row <> i Then
                                  
140                                   If mpSheet.Cells(i, "A").Value2 = Target.Value Then
                                  
150                                       mpDuplicateRepID = True
160                                       Exit For
170                                   End If
180                               End If
190                           Next i
                              
200                           If mpDuplicateRepID Then Exit For
210                       End If
220                   End If
230               Next mpSheet
                  
240               If mpDuplicateRepID Then
250                   ShowMessage Replace(MSG_ERROR_DUPLICATE_REPORT, _
                                      "", Target.Value), vbOKOnly + vbExclamation
260                   Target.Value = mcPrevValue
270               End If
280           ElseIf Not Intersect(Sh.Range(NAME_GROUP_ID), Target) Is Nothing Then
              
290               mpDuplicateGroupID = False
300               For Each mpSheet In Sh.Parent.Worksheets
                  
310                   If Sh.Name <> WS_CLIENT And Sh.Name <> WS_TEMPLATE And Sh.Name <> mpSheet.Name Then
                      
320                       If mpSheet.Range(NAME_GROUP_ID).Value2 = Target.Value Then
                                  
330                           mpDuplicateGroupID = True
340                           Exit For
350                       End If
360                   End If
370               Next mpSheet
                  
380               If mpDuplicateGroupID Then
390                   ShowMessage Replace(MSG_ERROR_DUPLICATE_GROUP, _
                                      "", Target.Value), vbOKOnly + vbExclamation
400                   Target.Value = mcPrevValue
410               End If
420           End If
430       End If

440       Set mpName = ThisWorkbook.Names.Add(Name:="_Changed", RefersTo:="=TRUE")
450       mpName.Visible = False

ws_exit:
460       mcPrevValue = Target.Value
470       Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    mcPrevValue = Target.Value
End Sub

Figure 3 - Configuration File Change Code

Testing Excel Version

Determining whether to build a menu or a ribbon is very straight-forward, all we need to do is to check the application version, as shown in Figure 4 - Test Excel Version. There is nothing particularly trick here, but there are a few call to other procedures that will be covered in the next post, namely

AppInitialise – opens the configuration file, does some basic validation of the template paths, and then grabs all of the report details
OpenRibbonAddin – this checks if the configuration file has been changed since the last run, builds the XML, and writes it back to the ribbon addin.

There is one other procedure call, BuildMenus, which I will not go into, it is a standard commandbar menu builder.

Private Const mmModule As String = "ThisWorkbook"	

'-----------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'-----------------------------------------------------------------

    If Val(Application.Version) < 12 Then

        Call DeleteMenus
    Else

        mgInsightRibbon.Close
    End If
End Sub

'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Const mpProcedure As String = "Workbook_Open"

    On Error GoTo Workbook_Open_Error
    PushProcedureStack mpProcedure, True

    Call AppInitialise
    If Val(Application.Version) < 12 Then

        Call BuildMenus
        mgConfigWB.Close SaveChanges:=False
        Set mgConfigWB = Nothing

    Else

        Call OpenRibbonAddin
    End If

Workbook_Open_Tidy:
    PopProcedureStack

Workbook_Open_Exit:
    Application.DisplayAlerts = True
    If Not mgConfigWB Is Nothing Then mgConfigWB.Close SaveChanges:=False
    Set mgConfigWB = Nothing
    Exit Sub

Workbook_Open_Error:
    If Err.Number = AppBypassErrorNum Then Resume Workbook_Open_Tidy
    If AppErrorHandler(mmModule, mpProcedure, True) Then
        Stop
        Resume
    Else
        Resume Workbook_Open_Exit
    End If
End Sub

Figure 4 - Test Excel Version

In the next post, I will give details on the getting the configuration file details, building the ribbon XML, and using the addin.

Excel Do, Dynamic Does

PowerPivot & DAX & Data Explorer BlogRoll - 31st March 2013

PowerPivot & DAX & Data Explorer BlogRoll - 24th March 2013

PowerPivot & DAX & Data Explorer BlogRoll - 17th March 2013

PowerPivot & DAX & Data Explorer BlogRoll - 10th March 2013

PowerPivot & DAX & Data Explorer BlogRoll - 3rd March 2013

PowerPivot (& DAX) BlogRoll - 24th February 2013

PowerPivot (& DAX) BlogRoll - 17th February 2013

PowerPivot (& DAX) BlogRoll - 10th February 2013

PowerPivot (& DAX) BlogRoll - 3rd February 2013

PowerPivot BlogRoll - 27th January 2013

PowerPivot BlogRoll - 20th January 2013

PowerPivot BlogRoll - 13th January 2013

CUBEs For Dessert

Introduction

Preparing With Good Ingredients

Mixing It Up

1. Cube Definition

2. Category Set

3. Category Set Count

4. Year Set

6. Year Set Count

6. List of Category Set Items

7. Sets of Subcategory Items

8. List of Subcategory Set Counts

9. List of Subcategory Set Count Totals

10. Lists of Sub-category Sets Items

11. Sales Measure

It's All About The Presentation

But Is It All Too Rich?

In Summary

Acknowledgements

PowerPivot BlogRoll - 6th January 2013

PowerPivot BlogRoll - 30th December 2012

PowerPivot BlogRoll - 23rd December 2012

Cooking With CUBEs

Slicing the Vegetables

The Menu

Why Extra Ingredients?

Cooked To Perfection

Managing The Ingredients

Ready to Serve?

What's For Dessert?

PowerPivot BlogRoll - 16th December 2012

Who Says The Ribbon Is Hard?

Introduction

CustomUI Editor

Coding Techniques

Ribbon Code

Managing Changes

Summary

Ribbon On The Fly Part2

Introduction

Linking the Ribbon Addin to the ‘Worker’ Addin

Format of Configuration File

Check the Configuration File

Testing Excel Version

Next Post