PowerPivotPro

 
UPDATE:  Uploaded a new version of the macro file that David provided that fixed a couple of small problems.  Also, I fixed a typo (I had it as XSLM, not XLSM – thanks Dan!)

We have a guest post today from David Hager.  Like most folks who exchange email with me, David is now aware of how… intermittent… an enterprise that is.  Intermittent being the description of the replies one receives.

But he persevered.  And this is a pretty ingenious macro he has written.

So ingenious, in fact, that he is bordering on revealing a few techniques that I have been debating whether or not to unmask.  Things we do at Pivotstream that Microsoft considers unsupported.  Which, of course, is the good stuff.  David is traipsing around in some very dark corners.

The only clarification I will offer to his post, which appears below, is the following:  When David says “this procedure finds where the measures are stored in the PowerPivot workbook,” I want to make clear that what is being found is actually where PowerPivot keeps a backup copy of the measures.  The backup copy is always up to date, so it is very much reliable.

But the “real”definitions are actually stored elsewhere, in a place named Item1.data – and, like Forrest Gump, that’s all I have to say about that.

Take it away David…

Creating a Measures Table From PowerPivot Workbooks in a Folder

By David Hager

When defined name formulas are added to an Excel workbook, they can be easily accessed and viewed through the use of Excel’s Name Manager. However, when measures (formulas added to PowerPivot pivot tables and based on the DAX query language) are created, there is no way to know that they exist except for visually scanning the PowerPivot field list. An icon resembling a calculator will be to the right of each formula created in the field list. However, in order to view the DAX formula, the field must be right-clicked and “Edit Measure” selected. Then, if you wanted to store that measure somewhere for future reference, it can be copy/pasted to the desired location. This process becomes time-consuming if you have many PowerPivot workbooks that contain many measures. It would be nice if that process could be automated. To that end, the following VBA procedure for opening files in a folder and extracting the DAX measures and putting them in a table can be downloaded here.

(NOTE FROM ROB:  You MUST rename that file to .XLSM before you can open it!  WordPress does not allow upload of macro-enabled files, so I uploaded as XLSX.  And if you don’t trust macros from other people, I respect that.  Maybe we’ll upload a text only version with instructions for those of you in that camp.)

Most of the code deals with file and folder manipulation, and will not be discussed here. The core procedure is shown below.

Sub ExtractAndCopyMeasures()
Dim tText As String
Dim strStart As Long
Dim sCopy As String
Dim sCopyArray
Dim aCol As Range
Dim bCol As Range
Dim sItem

On Error Resume Next

tText = ActiveWorkbook.CustomXMLParts("http://gemini/workbookcustomization/MetadataRecovery _Information").XML

strStart = CLng(InStr(1, tText, "CREATE MEASURE"))
sCopy = Mid(tText, strStart, InStr(strStart, tText, "</") – strStart – 2)
sCopy = Replace(sCopy, " ", "")
sCopy = Replace(sCopy, Chr(10), "")
sCopy = Replace(sCopy, "&lt;", "<")
sCopy = Replace(sCopy, "&gt;", ">")
sCopyArray = Split(sCopy, ";")

For Each sItem In sCopyArray

  Set aCol = ThisWorkbook.Worksheets("MeasureTable").Range("a1048576").End(xlUp).Offset(1, 0)
  Set bCol = ThisWorkbook.Worksheets("MeasureTable").Range("b1048576").End(xlUp).Offset(1, 0)
  aCol.Value = ActiveWorkbook.FullName
  bCol.Value = Mid(sItem, InStr(1, sItem, ".") + 1)

Next

End Sub

In essence, this procedure finds where the measures are stored in the PowerPivot workbook, parses the measures and place them in a table. Now, let’s examine some key parts of the code.

tText = ActiveWorkbook.CustomXMLParts("http://gemini/workbookcustomization/MetadataRecovery _Information").XML

This line loads into a variable the XML from the custom XML part in the workbook that contains the measures.

strStart = InStr(1, tText, "CREATE MEASURE")

This is the position in the XML string where the first measure is located.

sCopy = Mid(tText, strStart, InStr(strStart, tText, "</") – strStart – 2)

This code locates and stores just the part of the XML string that contains the measures.

sCopy = Replace(sCopy, " ", "")
sCopy = Replace(sCopy, Chr(10), "")
sCopy = Replace(sCopy, "&lt;", "<")
sCopy = Replace(sCopy, "&gt;", ">")

These lines of code clean up the string so that the formulas can be properly displayed. In particular, the terms "&lt;" and "&gt;" are used in XML for “<” and “>” respectively to prevent reading errors.

sCopyArray = Split(sCopy, ";")

The Split function converts the delimited string into a variant array containing each measure as an item in the array.

For Each sItem In sCopyArray

  Set aCol = ThisWorkbook.Worksheets("MeasureTable").Range("a1048576").End(xlUp).Offset(1, 0)
  Set bCol = ThisWorkbook.Worksheets("MeasureTable").Range("b1048576").End(xlUp).Offset(1, 0)
  aCol.Value = ActiveWorkbook.FullName
  bCol.Value = Mid(sItem, InStr(1, sItem, ".") + 1)

Next

The final part of the procedure loops through the array. aCol is used to set the next empty row without using a counter. The measure is extracted from the array item by using Mid(sItem, InStr(1, sItem, ".") + 1) to start at the correct string postion.

I hope that you find this procedure useful!

Note: There may be some prompts that will have to be answered manually during the file opening process.

 
 
“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.