Waterfall chart in Excel 2013 and older

Excel 2016 delivers a waterfall chart type, but what about older versions? This is a neat and nice workaround I actually learned from the business folks. The yellow values are coming from a data model or any other data source except for the Measure Bridge End, which is a SUM of the cell C11 and the range D12:D16 . The rest of the values is calculated as shown in the comments. Hopefully all the steps taken are understandable from the full-size image located here.

capture

Advertisements

Safe and predictable MDX queries for PowerPivot data loads

I have spent most of the last month digging deep and developing in PowerQuery and PowerPivot. I must say that I am impressed and there is still a lot more for me to learn. However I am not that much impressed about the documentation available online as there have been some issues I could not find an useful answer to.

Today I would like to share one quick tip related to querying OLAP database from PowerQuery or directly from PowerPivot. Imagine that you would place in the data loader section of a PowerQuery or PowerPivot a really basic MDX query looking like this:

Query 1:

SELECT
[Measures].[Internet Order Count] ON 0,
NONEMPTY([Customer].[Customer].[Customer],[Measures].[Customer Count]) ON 1
FROM
(SELECT
[Sales Territory].[Sales Territory].[Group].&[Europe] ON 0
FROM
(SELECT [Reseller].[Business Type].&[Specialty Bike Shop] ON 0
FROM
[Adventure Works])
)
WHERE [Product].[Category].&[40]

The results are:

Capture2

It could easily happen, that if you choose WHERE condition returning no rows, in this case determined by the non-existing Product Category, (or if your user would not have sufficient security rights under the OLAP role ), that the PowerPivot model could become corrupted and loose a relationship related to one of the (not)returned columns. You can rewrite the mentioned Query 1 in a much better way to ensure, that you always get the needed columns no matter how many rows are retrieved.

Query 2:

WITH
MEMBER [iCustomerName] AS [Customer].[Customer].CURRENTMEMBER.NAME
MEMBER [iCustomerKey] AS [Customer].[Customer].CURRENTMEMBER.Properties( "Key(0)" )

SELECT
{[iCustomerKey],[iCustomerName],[Measures].[Internet Order Count]} ON 0,
NONEMPTY([Customer].[Customer].[Customer],[Measures].[Customer Count]) ON 1
FROM
(SELECT
[Sales Territory].[Sales Territory].[Group].&[Europe] ON 0
FROM
(SELECT [Reseller].[Business Type].&[Specialty Bike Shop] ON 0
FROM
[Adventure Works])
)
WHERE [Product].[Category].&[40]
The results are:
Capture1
As you can see from the next picture, for Query 1 we have no Customer Name column in the model, for Query 2 we are just fine. I must admit that Customer Name is not a great example since we have the Customer Key returned anyway, but I guess you can see the point where I’m heading here. To stay on the safe side, write bullet-proof MDX queries!
 tabular1

Excel VBA for pivot table report safe delivery

As mentioned in the previous Excel VBA post, I would like to share my thoughts on safe Excel pivot tables reports delivery. The best I can recommend right now is an Excel file with an action, that on file open event hides all the rows in the pivot tables , so the user cannot see the data being pulled and saved into this sheet by someone else last time. The user has to refresh data, and thats where the provided credentials take place, right? Next thing I always do is, that I put password on the VBA code in the file. You have a tutorial for this step here. That way you ensure the user cannot see the macro VBA code and cannot do anything else then execute.


Private Sub Auto_Open()

Dim pf As PivotField
Dim PT As PivotTable
Dim WS As Worksheet
Dim aLock As Integer

On Error Resume Next

For Each WS In ActiveWorkbook.Worksheets
For Each PT In WS.PivotTables
With PT
.ManualUpdate = True
.EnableDrilldown = False
.EnableFieldList = False
.EnableFieldDialog = False
.EnableWriteback = False

For Each pf In .PivotFields
With pf
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False

End With
Next pf

End With

Next PT
Next WS

End Sub

 

Excel VBA macro for report sheet injection

Today I would like to talk a bit about Excel VBA macros. Recently I needed to come up with a macro for an Excel 2016 report sheet, that would inject this report sheet into every Excel file in the directory. This report sheet obviously contains data connections which are also being distributed to the end users. Regarding distribution purposes, this macro replaces the report sheet injected last time with the current version. ( Using this, you  can easily develop and distribute new versions of the report sheet to your users ). Sounds like some pretty useful stuff doesnt it?

I need to mention, that I am not an dedicated VBA developer, so you might find the attached code a little bit harsh, but it works perfectly. The code was also checked by the Office Code Compatibility Checker tool ( available here ). This is a pretty cool tool from Microsoft ensuring you have your VBA code compatible between MS Office versions and that classical 32/64 bit issue. You just launch this tool and it automatically rewrites and comments your code.

Now lets get to the data connections as there are definitly more options. I have been elaborating with these two: You can build .odc connection in the workbook from the .odc connection on the target machine or you can build the connection string on your own from the VBA code. I chose the second option in the final version, but I leave this up to your considerations and what better suits your environment. You can also see that there is Auto_Open VBA macro code being executed for every file injection. I will focus on this next time, you can just skip this step for now.

I think that I will do more posts on VBA macros in Excel in the future. I would also like to share my experience with the way you can deliver and secure data delivered to your Excel report users using VBA.

Sub Distribute_Sheet()
Application.DisplayAlerts = False
Dim MyFolder As String
Dim SrcWks As Worksheet
Dim Wkb As Workbook
Dim file As Variant
Dim delfile As Object
Set SrcWks = ThisWorkbook.Worksheets("_yourFileName_")
MyFolder = ThisWorkbook.Path & "\"

  file = Dir(MyFolder)
  While (file <> "")
   If InStr(file, "_yourFileName_") = 0 And InStr(file, ".xlsx") > 0 Then

      Set Wkb = Workbooks.Open(Filename:=ThisWorkbook.Path & "\" & file, ReadOnly:=False)

      Application.Run (Auto_Open)

      MsgBox ("Injecting _yourWorksheetName_ sheet with ext.data connection into: " & MyFolder & file)
        For Each delfile In Wkb.Sheets
        If delfile.Name = "_yourWorksheetName_" Then delfile.Delete
        Next delfile

        Dim strConnection As String
        Dim WBC As WorkbookConnection
        For Each WBC In ActiveWorkbook.Connections
          strConnection = WBC.Name
          If InStr(WBC.OLEDBConnection.Connection, "_yourConnOLAPName_") = 0 Then
          With ActiveWorkbook.Connections(strConnection). _
            OLEDBConnection
            .CommandText = .CommandText
            .CommandType = .CommandType
            .Connection = .Connection & ";CustomData=""_yourConnOLAPName_"""
            .RefreshOnFileOpen = True
            .SavePassword = False
            .SourceConnectionFile = ""
            .MaxDrillthroughRecords = 1
            .ServerCredentialsMethod = .ServerCredentialsMethod
            .AlwaysUseConnectionFile = False
            .RetrieveInOfficeUILang = False
          End With
          With ActiveWorkbook.Connections(strConnection)
            .Name = .Name
            .Description = ""
          End With
          ActiveWorkbook.Connections(strConnection).Refresh
          End If

         Next WBC

      SrcWks.Copy Before:=Wkb.Sheets(1)

      Wkb.ShowPivotTableFieldList = False

      Wkb.Close SaveChanges:=True

   End If
   file = Dir
 Wend

Application.DisplayAlerts = True

End Sub

Excel Fuzzy matching Add-in

Fuzzy is an adjective and it means “having shapes that do not have clear edges”.

Some time ago, I have been asked by my boss to lookup our company clients in the Panama Papers database extract, recently published on the internet. The challenge seemed pretty clear. Hundreds of thousands of company clients from the region located in CRM MSSQL database table on one side and about 1000+ clients on the Panama Papers side in a .xls file. Problem was, that there were quite many company type abbreviations i.e. Company XXX AG, Company XXX GMBH and you had only a very limited time to come up with some results.

The first thing that came across my mind was a SSIS fuzzy lookup data flow task, but this was a one-off and a in-a-hurry task, so there was no time for setting up a full size SSIS fuzzy matching project.

The second thought I had pointed me to this Excel add-in I am going to write about today.

Here is the link to the Microsoft download site where you can download this add-in.

I am pretty sure, that this is no rocket science and you will find using this tool pretty straightforward, but let me describe my personal experience.

So first, you need to install the setup.exe file and run Excel again and then you need to go into File -> Options -> Add-Ins -> Manage Excel Add-Ins and enable this Add-in. Once you have this Add-in in the tabs section, you are ready for some fuzzy matching magic.

So at this phase you need to copy these 2 datasets into two separate sheets and you also need to create table objects on the datasets. ( Insert -> Table 🙂 )

Next step is to choose the columns to be matched and the number of matched results per 1 row from the left table.

What I suggest is, to start with a small dataset and try to find the perfect Similarity Threshold between this small dataset and the set containing the results we are looking for, in this case the Panama Papers dataset. Once you are happy with the results, you can apply this setup on the whole dataset of your clients. As you will notice, when the matches are being processed, your Excel might freeze or stop responding. No worries, just let it run and keep on waiting for the results!

In my case, threshold 85 was the perfect choice. I got also some false-positive results, but at least I was sure I am not missing out some clients and deleting these results is quite quick anyway.

Please notice, that you have to setup your Windows Locale settings like language and formats and area to US, otherwise this add-in will not deliver any results and Excel will very likely crash.