Excel – User Input for Custom MDX query using VBA and Pivots

I had a requirement where I wanted to do the following for a particular Power User

·         Give them the ability to put in a Date as a parameter in an Excel Workbook.

·         Once I have this date, is to the have a custom MDX query, which will then go and get the required data for the date.

o    NOTE: The reason for the custom MDX query was due to the volume of data returned I wanted to limit the data and query, thereby making the results return as quickly as possible.

·         This must then be queried and returned to Excel, where the results could then be pivoted.

·         After which the user could then create his Pivot and chart based on his date.

·         And essentially for the user to just input his date and click a button.

 

Working Example

·         I wanted the user to put in his Date (01 June 2008) which will then return all the Products and Product Lines for that date inputted.

·         The outcome will be in the same sheet once the user has clicked on the button Click to Refresh  their Pivot and Chart will then be updated.

 

NOTE:

·         VBA is new to me, so I am aware that there is no error checking in place.

·         I also do not validated that the date is in the correct format for the user input. I am going to assume that I have explained to my user what date format to use.

 

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

 

Creating the VBA Function within Excel

The first part is to hard code the VBA function to ensure that we can get our data returned correctly to our Sheet in Excel.

 

NOTE: You might have to enable the Developer Ribbon, which you can reference here: http://office.microsoft.com/en-za/excel-help/show-the-developer-tab-HA101819080.aspx

 

1.       Click on the Developer Ribbon and then click on Macros

a.       

2.       This will then open the Macro Window

3.       Where it says Macro Name, put in a name for the Macro you are going to create.

a.        As with our Example I put the following Macro Name:

b.      

c.        Then click on the Create Button

4.       This then opens the Microsoft Visual Basic for Applications Window

5.       You will now see on the left hand side under Modules, it has Module 1

a.       

6.       On the right hand side is where we will be putting in our VBA code in our AW_ProductsByDate

a.       

7.       The next that you HAVE to do is to add the Microsoft ActiveX Data Objects 2.8 Library to your VBA function otherwise it will not recognize some of the coding later.

a.        To do this click on Tools and then Reference

                                                               i.     

b.       Once this opens the References – VBAProject, you are going to have to scroll down quite a bit until you find the following:

                                                               i.     

                                                              ii.      As shown above put a tick next to Microsoft ActiveX Data Objects 2.8 Library

                                                            iii.      Then click Ok.

c.        Now if you want you can save your workbook.

d.       NOTE: when saving your workbook you have to save it as an Excel Macro-Enabled Workbook as shown below. If this is not done then your Macro will not run.

                                                               i.     

8.       Now in the section below I will explain each section, while at the end I will have the entire VBA function so that you can copy and paste it.

9.       In the first section is where we are actually just declaring our Variables to use later.

‘Declare variables

Set objMyConn = New ADODB.Connection

Set objMyCmd = New ADODB.Command

Set objMyRecordset = New ADODB.Recordset

‘Dim Input1 As String

‘Input1 = ActiveWorkbook.Sheets(“Sheet1”).Range(“A2”)

a.        What we are doing above is setting our connections.

b.       Then where we have the DIM Input1 this is where we are creating our input variable

                                                               i.      NOTE: This has been commented out and will be explained in the later steps how to get it working.

                                                              ii.      Dim Input1 As String

                                                            iii.      Then we are setting the location of our Input1 variable

ActiveWorkbook.Sheets(“Sheet1”).Range(“A2”)

1.       NOTE: The above means on our Active Workbook, on the sheet named “Sheet1” look in the column “A2”

                                                            iv.      We will only be using the input later once we have got everything configured.

10.    In the next section is where we open and create our connection to our SQL Server Analysis Services Database

‘Open Connection

objMyConn.ConnectionString = “Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog= AdventureWorksDW2012;Data Source=ServerName\OLAP;MDX Compatibility=1;Safety Options=2;Protocol Format=XML;MDX Missing Member Mode=Error;Packet Size=32767″

objMyConn.Open

a.        NOTE: In the above connection string you will have to modify the following for your environment.

b.       For Initial Catalog highlighted in RED you will have to change this to the name of your SSAS Database.

                                                               i.      In our example it was:

1.       Initial Catalog= AdventureWorksDW2012

2.       NOTE: Ensure to get the Database ID, and not the name to ensure that you can connect to the database correctly.

c.        For the Data Source, this is your Server name, and in our example it is highlighted in BLUE

                                                               i.      Data Source=ServerName\OLAP

d.       The other settings are optional settings for if data is going across a slower network and to potentially speed up queries.

11.    In the next section is where we actually put our MDX query

‘Set and Excecute SQL Command

Set objMyCmd.ActiveConnection = objMyConn

objMyCmd.CommandText = “Select ‘ {[Measures].[Internet Sales Amount]} on 0 ” & _

                        “,’ {( ” & _

                                    “[Date].[Date].&[20080601], ” & _

                                    “[Product].[Product Line].Children, ” & _

                                    “[Product].[Product].Children ” & _

                                    “)} on 1” & _

                        “from [Adventure Works]”

objMyCmd.CommandType = adCmdText

objMyCmd.Execute

a.        As you can see above the section that you need to change is after the objMyCmd.CommandText

b.       A thing to note is that you have to start your query with the double quotes

                                                               i.      “

c.        And if you are going to continue it on another line and not have it all on one like then at the end of each line you need to put the following:

                                                               i.      ” & _A

d.       With our query above you can see for now that we have hardcoded our query.

e.       NOTE: We will come back later and update it to get the details from the Input1 variable we created in step 9 above.

f.         The rest of the syntax just executes the MDX query.

12.    The next section just opens the recordset

‘Open Recordset

Set objMyRecordset.ActiveConnection = objMyConn

objMyRecordset.Open objMyCmd

13.    Now in this next section we are going to copy the data to Excel

‘Copy Data to Excel

ActiveWorkbook.Sheets(“Dataset”).Range(“A2”).CopyFromRecordset (objMyRecordset)

‘ActiveWorkbook.Sheets(“Dataset1”).Range(“DownTimeTable”).CopyFromRecordset (objMyRecordset)

a.        As you can see above what we are doing is to copy from the recordset to a sheet called “Dataset” and then insert it starting at “A2”

b.       NOTE: You will see that there is a section commented out below. We will come back and change this in further steps.

c.        What we now need to do is to create our sheet that is referenced above called “DataSet”

d.       If you click on your Excel Workbook, and the plus sign next to Sheet 1, it will create Sheet 2

                                                               i.     

e.       Right click on Sheet2 and select Rename

                                                               i.      Rename it to DataSet.

f.         Now we have created our Sheet which is referenced above.

14.    Now in the next section is where we will refresh our Power Pivot model which we will create in further steps.

‘This is to refresh the Power Pivot Model

ActiveWorkbook.RefreshAll

15.    The final part is to close the connection and end our code.

‘Close Connection

objMyConn.Close

End Sub

16.    Here is the entire code all in one block.

a.        NOTE: We will explain the Delete section in step 21 below

Sub AW_ProductsByDate()

‘Delete all data out of Table before inserting new data

With Sheet2.ListObjects(“ProductsByDate”)

        If Not .DataBodyRange Is Nothing Then

            .DataBodyRange.ClearContents

            .DataBodyRange.Delete

        End If

    End With

 

‘Declare variables

Set objMyConn = New ADODB.Connection

Set objMyCmd = New ADODB.Command

Set objMyRecordset = New ADODB.Recordset

‘Dim Input1 As String

‘Input1 = ActiveWorkbook.Sheets(“Sheet1”).Range(“A2”)

 

‘Open Connection

objMyConn.ConnectionString = “Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=AdventureWorksDW2012;Data Source=ServerName\OLAP;MDX Compatibility=1;Safety Options=2;Protocol Format=XML;MDX Missing Member Mode=Error;Packet Size=32767”

objMyConn.Open

 

‘Set and Excecute SQL Command

Set objMyCmd.ActiveConnection = objMyConn

objMyCmd.CommandText = “Select ‘ {[Measures].[Internet Sales Amount]} on 0 ” & _

                        “,’ {( ” & _

                                    “[Date].[Date].&[20080601], ” & _

                                    “[Product].[Product Line].Children, ” & _

                                    “[Product].[Product].Children ” & _

                                    “)} on 1” & _

                        “from [Adventure Works]”

objMyCmd.CommandType = adCmdText

objMyCmd.Execute

 

‘Open Recordset

Set objMyRecordset.ActiveConnection = objMyConn

objMyRecordset.Open objMyCmd

 

‘Copy Data to Excel

ActiveWorkbook.Sheets(“Dataset”).Range(“A2”).CopyFromRecordset (objMyRecordset)

‘ActiveWorkbook.Sheets(“Dataset”).Range(“ProductsByDate”).CopyFromRecordset (objMyRecordset)

 

‘This is to refresh the Power Pivot Model

ActiveWorkbook.RefreshAll

 

‘Close Connection

objMyConn.Close

End Sub

17.    Now if you click on the Run sub/UserForm as shown below your query should now run.

a.       

b.       Then on your Dataset Sheet you should see the output of your query.

c.        NOTE: If the query only returns one row, ensure that your query is not set to only show non empty results.

d.      

18.    In the next steps we are going to give our results column names and convert it to a table.

19.    So on the sheet “Dataset” at the top we put in the following column names:

a.       

b.       Then what we did is to select the entire range and clicked on Format as Table and formatted it as a table.

                                                               i.     

                                                              ii.      NOTE: Ensure to select My Table has headers

1.      

                                                            iii.      Click Ok

c.        You should now see this below:

d.      

e.       The final thing is on the top left hand side it will have Table Name, change this from Table1 to ProductsByDate

                                                               i.     

20.    Now the final step is to go back into your VBA Macro and change the following code:

‘Copy Data to Excel

‘ActiveWorkbook.Sheets(“Dataset”).Range(“A2”).CopyFromRecordset (objMyRecordset)

ActiveWorkbook.Sheets(“Dataset”).Range(“ProductsByDate”).CopyFromRecordset (objMyRecordset)

a.        You can see from above that we have now changed our dataset that is returned to go into our Table name from step 19 above.

                                                               i.      NOTE: This is so that when the number of rows changes we will always get back all the rows.

21.    What we are doing in the last part is to delete all the data in our table each time it runs.

a.        This is to ensure that there is no left over or incorrect data

b.       This will go at the top of your codeso that it will run first

‘Delete all data out of Table before inserting new data

With Sheet2.ListObjects(“ProductsByDate”)

        If Not .DataBodyRange Is Nothing Then

            .DataBodyRange.ClearContents

            .DataBodyRange.Delete

        End If

    End With

 

Taking the output from our VBA function creating our Pivots with a Chart.

In the next section we are going to take the output from our VBA function which was inserted into a table and then create our Pivot and Pivot Chart on our First sheet.

 

1.       Click on your table that you created in your sheet called “Dataset”

2.       Then click on the Insert ribbon, and then click on Pivot Table

a.       

3.       This will then open the Create Pivot Table Window, which you will configure with the following:

a.        Where it says Choose the data that you want to analyze leave the default which is shown below:

                                                               i.     

b.       Then where it says Choose where you want the PivotTable report to be placed, click on Existing Worksheet

                                                               i.      Then were it says location click on the box to select your location.

                                                              ii.      Now click on your Sheet 1, and click on Cell A6

                                                            iii.      So it will look like the following:

1.      

c.        Finally where it says choose whether you want to analyze multiple tables, click the tick box which is shown below:

                                                               i.     

                                                              ii.      NOTE: The reason that we add it to the Data Model is so that it actually puts in into Power Pivot.

1.       You can validate it, if you had to click on your Power Pivot ribbon and click on Manage you would see your data.

d.       So this is what the entire Window looks like:

                                                               i.     

e.       Then click Ok.

4.       Now we can create our Pivot and our Pivot chart.

5.       I created the following below:

a.       

b.       NOTE: While this does look quite simple it is used for our example.

c.        And the actual Pivot looks like this:

d.      

 

Creating the variable and button for the users input

Now in the final part what we are going to do is the following:

·         Define where we will get the users data from.

o    Convert this to the correct format which we need for our MDX query

·         Update our VBA code so that we can use this within our code dynamically.

·         Put in a button which will refresh everything based on the user input.

 

1.       Here we will first create our column and format it as required from the users Input

a.        So what we did is to put in some text so that the user knows what to do

b.      

c.        Next we put in a date in A2

                                                               i.      EG: 2008-06-04

                                                              ii.      NOTE: What we need to do though is to ensure that we format the A2 column as Text.

1.       The reason for this is we are going to substitute the date to the correct format which we require for our MDX query

                                                            iii.     

d.       Next in column B2 I put the following:

=SUBSTITUTE(A2;”-“;””)

                                                               i.      And you will then see that the column now has the following

                                                              ii.     

                                                            iii.      Now what I did is I moved the Chart from above to the side.

                                                            iv.      I then cut and pasted the above excel function and put it in cell D11

                                                              v.     

                                                            vi.      NOTE: The reason that I did this is so that the cell value would be hidden from the user. But we did NOT have to hide a cell.

e.       I then moved the chart and slicer back to where it belongs.

2.       Next we are going to go back into our VBA Code and update the Variable so that it will get the data from cell D11

a.        So you should still have your VBA window open.

b.       Under the Declare Variables section uncomment the two lines so that they look like the following below:

Dim Input1 As String

Input1 = ActiveWorkbook.Sheets(“Sheet2”).Range(“A2”)

c.        Now we are going to change the location of where the cell is from the above details to the following:

Input1 = ActiveWorkbook.Sheets(“Sheet1”).Range(“D11”)

d.       The next thing that we need to do is to change our MDX query from being hard coded to use the variable Input1

e.       So under the section ‘Set and Execute SQL command, go to the following line:

“[Date].[Date].&[20080604], ” & _

f.         Now we are going to change it to the following below:

“[Date].[Date].&[” & Input1 & “], ” & _

                                                               i.      What we have done above is in order to use the variable we have to encapsulate it with the following:

“& VariableName &”

c.        Now what you can do to test this is to click on the Run sub/UserForm as shown below your query should now run.

                                                              ii.     

g.        Now it should run through correctly and you should see your data updated in Sheet1.

3.       Now the final step is to add the button, which the user can press after they have put in their date.

a.        Go back into your Excel spreadsheet and onto Sheet1

b.       Click on the Developer Tab

c.        Then under the Controls section, click on Insert and then select Button

                                                               i.     

d.       Next you will need to drag your button where you want it to be.

                                                               i.      You could do this anywhere initially.

e.       After you have dragged in your button it will then open up the Assign Macro Window.

                                                               i.      From this Window select your VBA that you created.

                                                              ii.     

f.         Then click Ok.

g.        You will now see the following:

h.      

i.         Now in order to change the text on the button do the following:

                                                               i.      Right click on the Button 1 and select Edit Text

                                                              ii.     

j.         I changed it to the following:

Click to refresh data

                                                               i.      NOTE: I had to drag the button to make the text fit.

1.       Also whilst you are editing the text you could also change the font and size.

k.        I then moved it so it looked like the following:

l.        

4.       Now to finally test that it works I changed the date to:

a.        2008-06-05 and then clicked the button “Click to refresh data”

b.       Now after the data refreshed I saw my pivot change to the following:

c.       

 

Now you can save your Excel worksheet and send it to the required people

NOTE: Ensure that the users have the required access to the cube where the MDX will be running.

 

You can get a copy of the workbook here: http://1drv.ms/1dfKry4