5 Interactive Chart Techniques Come Together

October 2, 2014

by Matt Allington

Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from powerpivotpro.com, from searching the Web, as well as some of my own ideas.  While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated.  I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user.  There is a link to this workbook at the end of this post.

The techniques I have used are:

  1. Disconnected slicers used to create interactive chart series
  2. Cube formulae and standard Excel to make an interactive chart title

I love these 2 tips I learnt from Rob – so user friendly.  However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.

  1. Cube formulae and standard Excel to make an interactive legend
  2. VBA and “link to source” for interactive axis formatting
  3. Excel VBA to change which Axis the series appears on.

I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below.  I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.

Now let me call out the key techniques I have used to make this workbook rock.
Read the rest of this entry »


A Neat Trick/Macro for More Readable Pivots

August 5, 2014

 
OK, you’ve been a good Power Pivot author and given your measures clearly descriptive names.

Your punishment is spending all day looking at pivots like this:

Your Pivot is Too Wide for the Screen

Hey, Where’s the Rest of My Information?
(Hint:  It’s in “Scrollsville.”)

Nice and Cleanly Readable Pivot

MUCH Better:  Last Two Measures Completely Visible, With Space to Spare!
(Assuming Vertical Space Isn’t a Problem, Of Course)

A Trick I “Harvested” From a Client

Awhile back I was working with a gentleman named Tom Phelan who repeatedly used a series of click mouse clicks to achieve the sort of layout pictured above.  After seeing him do that about ten times I asked him to slow down so I could see what the clicks were.

Read the rest of this entry »


Adding tables to a Power Pivot model from VBA (in Excel 2013)

July 17, 2014

Guest Post by Dany Hoter

After I published a post about manipulating relationships, Rob suggested that I take a step back and cover the entire scope of what’s possible with the object model.

Can you build a model from scratch? Can you add a new table to an existing table? Can you add calculated columns? What about calculated measures? , Can you change a connection for an existing table in the model?

The short answer to these questions is Yes, Yes, No, No, Yes

The longer version is the rest of this post. Everything in this post is NOT possible in Excel 2010 – this stuff works in 2013 only.

The object model consists of the following elements:

clip_image001

The only property that I found useful in this list is ModelRelationships collection which I used extensively in the previous post.

The ModelTables collection looks promising as it contains ModelTableColumns and could be the way to introduce new tables, new columns or even measures into the model. Unfortunately all these collections are read-only and cannot be used for adding to the model.

So how is still possible to add new tables or even to start a model from scratch?

It all has to do with the method add2 of the Connections collection.

Read the rest of this entry »


Manipulating Relationships in VBA (in 2013)

June 10, 2014

Guest Post From Dany Hoter

Intro from Rob:  Ah, the international man of mystery returns!  My first instincts when I think of Dany Hoter, other than “one of the most fabulous humans I have ever known,” generally can be summarized as “MDX and Cube Formulas Monster.”

But he’s far from a one-trick pony.  Generally speaking, he has a level of tenacity and patience rarely encountered outside of laboratory conditions.  Couple that with an insatiable drive for The Right Thing, and you get some crazy results.

Today is one such CRAZY example.  Simultaneously, he shows us how to compensate for a drillthrough bug, AND delivers a working example of relationship manipulation via VBA macros.

THIS IS AN ADVANCED TOPIC POST.  Feel free to skip this one.  This is the deep end of the pool and even I don’t swim in these particular waters yet.

Note of course that this technique is 2013 only, and will not work with Power Pivot in 2010.

Take it away, Dany…

A Drillthrough Bug with Inactive Relationships

I started this VBA project after one of our partners wrote to me about a customer complaint regarding inactive relationships in Power Pivot:

image

The Sales Table has TWO Relationships to Calendar – One is Based on OrderDate (Active), and the Other is Based on ShipDate (Inactive – Dashed Line)

 

Read the rest of this entry »


Custom Toooltips in Dashboards!

November 12, 2013

 
Power Pivot Dashboard Tooltips/Comments

Custom “On Hover” Tooltips on Each Cell in the Dashboard!
(The yellow dot and distortion around mouse pointer are GIF side effects and do NOT appear in Excel)

Question from PowerPivotPro School!

Got a great question the other day from Oscar, a student in PowerPivotPro School

[OSCAR]:  “Is it possible to have a tooltip in powerpivot which shows additional information based on the cells selected (or mouse roll over). the info to be displayed comes in from a table created with cube fuctions on the same data source. So the coordinates of the highlighted cells would be inputs for the cube formula and result displayed in a tool tip dynamically.”

My first thought was “no, not possible.”  Then ten seconds later, a guerilla-style hack came to mind.  And then, my reply:

[ROB]:  “Oscar you are a very, VERY bad man. I am now obsessed with this problem. There goes my Sunday.”

The Trick:  Hyperlinks to Nowhere!

Read the rest of this entry »


Display User’s Slicer Selections: A Macro to Automatically Create the Formulas

October 2, 2012

 
Slicer Selections Displayed via Formulas

Note the “Readout” that Displays the User’s Slicer Selections:
Now Do This for Every Pivot in Your Workbook With One Click!

A Common Trick, Now Automated

This is something we do all the time at Pivotstream – we write formulas that capture user slicer selections and then display those selections back to them.

We do this via hidden report filters:

image

(Yes, we could also do this with cube formulas, but we started out (literally years ago now) using this approach and we’ve just kinda stayed with it.  I’m not sure cube formulas would be better, but they might be.)

Why is the Readout Useful?

Why do we do this?  Well, for one thing, the Download Snapshot feature on the server does NOT download the slicers – you get a big blank white space where the slicers were, which isn’t terribly helpful.  It leaves you wondering what you had selected.

It’s also useful when there are slicers on other sheets impacting your current sheet.  And even on a single sheet, it’s often nice to have a compact readout of your selections without having to scroll (or even scan with your eyes) to see what selections you have made.

Read the rest of this entry »


Combine Multiple Worksheets/Workbooks into a Single PowerPivot Table

September 18, 2012

One of those simple but indispensable tricks

Back to a “real” post now after all the book stuff, but it’s going to be a short one while I get back on my feet.

Let’s say you have multiple worksheets (or workbooks) that all contain the same sort of data:

image  image  image

Multiple Worksheets (or Workbooks), All Contain The Same Type of Data

You Want to Combine ALL of Them Into a Single PowerPivot Table

These worksheets all come to you separately, but really you just want them as one big table.

Naturally, if it’s a small number of sheets, and each sheet isn’t massive, you can just copy paste them all into one table in Excel, then copy/paste into PowerPivot, or link the table into PowerPivot, or export as CSV so you can import it.

And you could also use Paste Append to directly paste into PowerPivot.

But if the combined data set exceeds 1 million rows, you won’t be able to combine the sheets into one – you will exceed the worksheet row limit.  And a data set of that size is not something you can paste into PowerPivot directly with Paste Append – pasting large data sets into PowerPivot takes forever, if it completes at all.

Here’s what I do when I find myself in this position:

Read the rest of this entry »


Implementing a Dynamic Top X via slicers in Excel 2013 using DAX queries and Excel Macros

July 26, 2012

image

Our First Post on Excel 2013 Beta!

Guest post by…  Kasper de Jonge!

Notes from Rob:  yes, THAT Kasper de Jonge.  We haven’t seen him around here much, ever since he took over the Rob Collie Chair at Microsoft.  (As it happens, “de Jonge” loosely translated from Dutch means “of missing in action from this blog.”  Seriously.  You can look it up.)

1) Excel 2013 public preview (aka beta) is out, which means that now we’re not only playing around with PowerPivot V2 and Power View V1, but now we have another new set of toys to take for a spin.  I am literally running out of computers – I’m now running five in my office.  Kasper is here to talk about Excel 2013.

2) I’ve been blessed with a number of great guest posts in a row, and there’s already one more queued up from Colin.  This has given me time to seclude myself in the workshop and work up something truly frightening in nature that I will spring on you sometime next week.  But in the meantime, I hand the microphone to an old friend.

Back to Kasper…

Inspired by all the great blog posts on doing a Dynamic Top X reports on PowerPivotPro I decided to try solving it using Excel 2013. As you might have heard Excel 2013 Preview has been released this week, check this blog post to read more about it.

The trick that I am going to use is based on my other blog post that I created earlier: Implementing histograms in Excel 2013 using DAX query tables and PowerPivot. The beginning is the same so I reuse parts of that blog post in this blog.

Read the rest of this entry »


MiniPost 2 of 2: Changing Slicer Fonts With Macros

July 12, 2012

 
In Tuesday’s post, I showed how WingDings and other symbolic fonts can be used on slicers for an interesting effect.

But there was a lot of manual work involved with changing slicer style settings in order to accomplish that – eight repetitive steps.

I’m back today to share a macro that makes that process much more painless.

I’d Like to Thank the Academy…  of Macro Recording

I’ve said it before and I’ll say it forever – the ability to record macros in Excel is the single greatest reference on how to write macros of your own.  And I leaned heavily on that recording feature to write what I’m sharing here.

So even if you’re not a macro person, I recommend trying out macro recording.  There’s some “how to” info at the end of a post from March.

Read the rest of this entry »


Auto-Modify Every Pivot in a Workbook

March 6, 2012

 
image

It’s *ALMOST* That Easy Smile

It’s been awhile since I’ve talked about Macros (also known as VBA).  I think it’s overdue.

Macros are nothing short of amazing.  We couldn’t live without them at Pivotstream.

Wait Rob, Macros Don’t Run on the Server!

That’s right, they don’t.  And everything we do at Pivotstream eventually lands on the server.  So why do we use macros?

Simple:  we use macros to more efficiently create and modify our workbooks.  Macros are a “design time” tool for us, not a “run time” tool.

And they have saved us probably decades of work.  I’m not exaggerating.

An Example:  The “Change Every Pivot” Macro

Here’s a pretty simple macro that runs through every PivotTable on every visible worksheet and does whatever you want to each pivot:

Sub ModifyAllPivots()
    Dim Pivot As PivotTable
    Dim Sheet As Worksheet
   
    For Each Sheet In ActiveWorkbook.Worksheets
        If Sheet.Visible = xlSheetVisible Then
            For Each Pivot In Sheet.PivotTables
                AutoPadPivot Sheet.Name, Pivot.Name, 10
                GrandTotalsBottomOnly Sheet.Name, Pivot.Name

            Next
        End If
    Next

End Sub

I emphasized the “payload” of the macro – for each pivot the macro finds, it “pads” the columns of the pivot to be wide enough, and sets the pivot to display grand totals only on the bottom of the pivot (and never on the right).

Those two lines are macros that I also wrote, and I will include them below.

Auto Pad Pivot Columns Macro

At Pivotstream, we tend to ALWAYS turn off the “auto-fit columns on update” setting on our pivots:

Making Pivots NOT Change Column Widths On Slicer Clicks Etc

Making Pivots NOT Change Column Widths On Slicer Clicks Etc

This yields a much more pleasant “application-like” result – clicking a slicer never results in things jumping around.  I highly recommend it.

But this DOES lead to a problem.  If your numbers suddenly grow by a digit in the future, you can get something like this:

One of the Drawbacks of Turning off Autofit Column Width

One of the Drawbacks of Turning off Autofit Column Width

To account for this, we have a macro that runs through every pivot in the workbook and “pads” the column width by a percentage:

Sub AutoPadPivot(sSheet As String, sPivot As String, iPct As Integer)
    Dim oPivot As PivotTable
    Dim oSheet As Worksheet
    Dim r As Range
    Dim rCurr As Range
    Dim iCol As Integer
  
    Set oSheet = ActiveWorkbook.Worksheets(sSheet)
    Set oPivot = oSheet.PivotTables(sPivot)
   
    Set r = oPivot.DataBodyRange.Rows(1)
    For Each rCurr In r.Cells
        iCol = rCurr.Column
       
        oSheet.Columns(iCol).EntireColumn.AutoFit
        oSheet.Columns(iCol).ColumnWidth = oSheet.Columns(iCol).ColumnWidth * (1 + (iPct / 100))
    Next
   
   
End Sub

At the beginning of this post, you saw an example where I called this macro to pad each column by 10 percent.  But I can pad by 5, 15, whatever I want.  And you can easily imagine a version that pads by an absolute amount rather than a percentage.

Grand Totals Bottom Only Macro

This one is more self-explanatory:

Sub GrandTotalsBottomOnly(sSheet As String, sPivot As String)
    Dim oPivot As PivotTable
    Dim oSheet As Worksheet
   
    Set oSheet = ActiveWorkbook.Worksheets(sSheet)
    Set oPivot = oSheet.PivotTables(sPivot)
   
    oSheet.PivotTables(sPivot).RowGrand = False
End Sub

Want one more?  OK.  You talked me into it.  We have dozens, many of which are quite ambitious.  Simpler macros make for better blog posts though, so…

Create Page Filter For Each Slicer On Pivot Macro

Sub CreatePageFilterForEachSlicerOnPivot(sSheet As String, sPivot As String)
    Dim oSlicer As Slicer
    Dim oSlicerCache As SlicerCache
    Dim sField As String
    Dim oPivot As PivotTable
    Dim oSheet As Worksheet
   
    Set oSheet = ActiveWorkbook.Sheets(sSheet)
    Set oPivot = oSheet.PivotTables(sPivot)
   
    For Each oSlicer In oPivot.Slicers
        sField = oSlicer.SlicerCache.SourceName
        oPivot.CubeFields(sField).Orientation = xlPageField
    Next
   
End Sub

Why would I want to create a page filter for very slicer on the pivot?  Well, primarily so you can “harvest” the slicer selections in formulas.  But there are other reasons you might do this as well, which I will likely cover in the future.

Learn to Record Macros Folks!

What’s that, you say?  You never need to pad pivots, switch their grand total settings, or add page filters for every slicer?  Not satisfied eh?  Well, you can make your own!

If you have never recorded a macro, seriously, it’s SOOOO easy.  You should try it. 

First you will need to enable the Developer ribbon by going to File|Options|Customize the Ribbon.

Once you’ve done that, here’s how you get started:

Going Into Macro Recording Mode

Going Into Macro Recording Mode
(Note That I Named the Macro Based On What I Am Going to Do Next)

Now I do something to the pivot.  In this case, I switch my Pivot to one of the “Medium Green” Styles:

image

Changing My Pivot to a Different Style
(While the Macro Recorder Watches My Every Move)

Now I can stop recording:

Stop Recording the Macro

Stop Recording the Macro

Inspecting Your Freshly-Recorded Macro

Inspecting Your Freshly-Recorded Macro

Just click the Macros button, select your macro, and then click Edit

And here it is:

Sub ChangeToGreenStyle()

‘ ChangeToGreenStyle Macro


    ActiveSheet.PivotTables(“PivotTable3″).TableStyle2 = “PivotStyleMedium4″
End Sub

There’s really only one line in the macro that DOES anything, so I highlighted it.

Note that it’s “tied” to a pivot named “PivotTable3.”  Not all of your pivots will be named that of course.  And it only works on the ACTIVE sheet.  So it won’t work if you try to loop through using the ModifyAllPivots macro.

So, you can modify it to look like the other macros I showed above (GrandTotalsBottomOnly, etc).  Change the macro to be:

Sub ChangeToGreenStyle (sSheet As String, sPivot As String)

   Dim oPivot As PivotTable
   Dim oSheet As Worksheet

   Set oSheet = ActiveWorkbook.Worksheets(sSheet)
   Set oPivot = oSheet.PivotTables(sPivot)


   oSheet.PivotTables(sPivot).TableStyle2 = “PivotStyleMedium4″

End Sub

The stuff in grey is just copied from the other macros.  The red is a replacement (also copied from the other macros) for the part the recorder set to Active Sheet and PivotTable3.

That macro can now be called from within ModifyEveryPivot just like the others were:


      For Each Pivot In Sheet.PivotTables
         AutoPadPivot Sheet.Name, Pivot.Name, 10
         GrandTotalsBottomOnly Sheet.Name, Pivot.Name

         ChangeToGreenStyle Sheet.Name, Pivot.Name

      Next

Off you go Smile


PowerPivot VBA Macro Mania!

August 23, 2010

 
Macros Are Assembly Line Approach for Spreadsheets 
“I approve of your spreadsheet methodology.”
-Henry Ford

I’ve got some Excel VBA macros that you may find useful in your work on PowerPivot.  Been saving these up for awhile now, and I think there are now enough to warrant a post.

I’ll post them all below with a description of each, but since WordPress seems to prevent copy/paste by default, I will also place a text file here containing all of them, so you can download all at once.

These get the job done in workmanlike fashion.  Feel free to point out how much better I could have made them :)  I know, I could have turned off visual display updates to make them faster, for instance, but if I had to make these things pretty before sharing, I never would have shared :)

So hopefully, you will find them useful, if as nothing more than a starting point.

Without further delay, I give you, the macros…

“Make the same change to all PivotTables in the Workbook” Macro

Useful When:  You have lots of Pivots and they all need the same change made

Be careful of:  Well, as with all macros, you should save your work before running

Sub ModifyAllPivotTables()
    Dim Pivot As PivotTable
    Dim Sheet As Worksheet
    For Each Sheet In ActiveWorkbook.Worksheets
        For Each Pivot In Sheet.PivotTables

            ‘YOUR CODE HERE 
            ‘EXAMPLE:  Add Field “b” to Rows:
            ‘Pivot.AddDataField Pivot.PivotFields(“b”), “Sum of b”, xlSum
            ‘With Pivot.PivotFields(“b”)
            ‘     .Orientation = xlRowField
            ‘     .Position = 3
            ‘End With

        Next
    Next
End Sub

Remove slicer parent controls (rectangles around slicers in the sheet)

Useful when:  You want to get rid of that “Unsupported Features” warning in Excel Services
Also useful when:  You end up with lots of slicer parent controls stacked on top of each other, like we do…  ‘cuz we copy/paste sheets too much

Be careful of:  This macro nukes all Rectangle art shapes in the workbook :)
Also be careful of:  The PowerPivot addin puts the parent controls back whenever you select a pivot

Sub RemoveAllRectangles()
    Dim oSheet As Worksheet
    Dim oShape As Shape
    For Each oSheet In ActiveWorkbook.Sheets
        For Each oShape In oSheet.Shapes
            If Left(oShape.Name, 9) = “Rectangle” Then
                oShape.Delete
            End If
        Next oShape
    Next oSheet
End Sub

Connect all Slicers on a given sheet to a specific pivot on any sheet

Useful when:  You want to create an All Slicers sheet, that contains nothing but slicers that are intended to affect pivots on other sheets.  I like to use the PowerPivot addin to create the slicers because it sizes them correctly, then getting rid of the pivot, leaving just a slicer sheet.  But then you have to connect all those slicers to the pivot(s) on other sheet(s), eek.  This macro is a huge help.

Be careful of:  What could possibly go wrong with this one?  Heh heh.

Sub ConnectSlicers()

   Dim oSlicer As Slicer
   Dim oSlicerCache As SlicerCache

   For Each oSlicerCache In ActiveWorkbook.SlicerCaches
       For Each oSlicer In oSlicerCache.Slicers
           If oSlicer.Shape.BottomRightCell.Worksheet.Name = “YOUR SHEET NAME HERE” Then
               oSlicer.SlicerCache.PivotTables.AddPivotTable (Sheets(“ANOTHER OR SAME
                       SHEET”).PivotTables(“DESIREDPIVOT”))
           End If
       Next
   Next

End Sub

“Disable slicer cross filtering” macro

Useful when:  you have a lot of pivots in a workbook that all have very similar slicer fields in use, and you suddenly realize that cross-filtering is not required on a bunch of those fields and it is just slowing down performance.

Be careful of:  If you have slicer fields with similar names, this macro’s use of InStr (essentially, a “Contains” operator) may cause you problems.

Sub DisableCrossFilter()

Dim oSlicer As Slicer
Dim oSlicerCache As SlicerCache
Dim sName As String
Dim bClearCrossFilter As Boolean

‘ASSUMES 3 FIELDS YOU WANT TO DISABLE X-FILTER FOR, CHANGE 3 TO OTHER NUMBER AS NEEDED!
Dim aTestString(1 To 3) As String
Dim iStringCount As Integer
iStringCount = 3

Dim i As Integer

‘YOUR FIELD NAMES HERE
aTestString(1) = “Buyer”
aTestString(2) = “Period”
aTestString(3) = “Store”
For Each oSlicerCache In ActiveWorkbook.SlicerCaches
    For Each oSlicer In oSlicerCache.Slicers
        bClearCrossFilter = False
        For i = 1 To iStringCount
            If InStr(oSlicer.Name, aTestString(i)) > 0 Then
                bClearCrossFilter = True
            End If
        Next i
        If bClearCrossFilter = True Then oSlicer.SlicerCacheLevel.CrossFilterType = xlSlicerNoCrossFilter
    Next
Next
End Sub

Enable slicer cross filtering

Useful when:  you over-use the macro above

Just replace the FOR loop body of the macro above with:

For Each oSlicerCache In ActiveWorkbook.SlicerCaches
    For Each oSlicer In oSlicerCache.Slicers
        bSetCrossFilter = False
        For i = 1 To iStringCount
            If InStr(oSlicer.Name, aTestString(i)) > 0 Then
                bSetCrossFilter = True
            End If
        Next i
        If bSetCrossFilter = True Then oSlicer.SlicerCacheLevel.CrossFilterType = 
            xlSlicerCrossFilterShowItemsWithDataAtTop
    Next
Next

Hide all gridlines and headers

Useful when:  you want to make your report sheets look more professional

Sub HideGridAndHeaders()

Dim sSheet As Worksheet

For Each sSheet In ActiveWorkbook.Worksheets
    sSheet.Activate
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.DisplayHeadings = False
Next

End Sub

Link to text file with all macros

Here’s the link again in case you don’t want to scroll all the way back up.

Got a macro of your own?

Post it in comments or email it to me.  If I get enough I’ll do a “Part Two – Macros From the Community” post.