Excel 2007 Pivot - Use VBA to Extract data from Sql Server Cube 1

[BxWill]

Having difficulty and would appreciate some insight...

Objective is to use Excel vba to create pivot table that is based on a Sql Server 2005 cube.

Upon running the vba code, I receive a "subscript is out of range error" and the follwing part of the code is highlighted

Workbooks("Book2").Connections.Add "SSAS_PROD EduSales Cube EduSales", "", _
"OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=SSAS_PROD;Initial Catalog=EduSales Cube" _
, Array("EduSales"), 1

Would like to save the code as part of a Excel macro-enabled workbook and then going forward just open the workbook and run the code.

did try to save as part of the personal workbook but was not allowed due to a error with something like "personal workbook must remain open..."

What modifications are needed so that I can run the code using the Macro-enabled workbook and be prompted for a filename and then have the Excel workbook stored at C:\Analysis?

Thanks in advance for some insight.

Sub mcrEduSalesCube()
'
' mcrEduSalesCube Macro
' Create EduSales Cube
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    Workbooks("Book2").Connections.Add "SSAS_PROD EduSales Cube EduSales", "", _
        "OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=SSAS_PROD;Initial Catalog=EduSales Cube" _
        , Array("EduSales"), 1
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("SSAS_PROD EduSales Cube EduSales"), Version:= _
        xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R1C1", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
    Cells(1, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").CubeFields("[Measures].[Paid Amt]")
    With ActiveSheet.PivotTables("PivotTable1").CubeFields("[Date].[Month]")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").CubeFields( _
        "[Product].[Market Desc]")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Product].[Market Desc].[Market Desc]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Product].[Market Desc].[Market Desc]").CurrentPageName = _
        "[Product].[Market Desc].&[Wash - Seattle]"
    With ActiveSheet.PivotTables("PivotTable1").CubeFields( _
        "[Service Category].[Svc Desc]")
        .Orientation = xlPageField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Service Category].[Svc Desc].[Svc Desc]"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Service Category].[Svc Desc].[Svc Desc]"). _
        CurrentPageName = _
        "[Service Category].[Svc Desc].&[Day-Southeast]"
    With ActiveSheet.PivotTables("PivotTable1").CubeFields( _
        "[Service Category].[Detail COS Desc]")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").CubeFields( _
        "[Product].[Product Desc]")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Date].[Month].[Month]"). _
        VisibleItemsList = Array("[Date].[Month].&[201001]", "[Date].[Month].&[201002]", _
        "[Date].[Month].&[201003]", "[Date].[Month].&[201004]")
    ActiveWorkbook.ShowPivotTableFieldList = False
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "PaidAmt"
    Range("A6").Select
    ChDir "C:\Analysis"
    ActiveWorkbook.SaveAs Filename:= _
        "C:Analysis\CurrentReview.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Application.Goto Reference:="mcrEduSalesCube"
    ActiveWindow.SmallScroll Down:=-12
End Sub

 

[xlbo]

Do you want to create a pivot table every time or just have it refresh based on a predefined pivot table?
your code is in error as this line:

Workbooks("Book2").Connections.Add "SSAS_PROD EduSales Cube EduSales", "", _
        "OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=SSAS_PROD;Initial Catalog=EduSales Cube" _
        , Array("EduSales"), 1

should be more like

Workbooks("Book2").Connections.Add "SSAS_PROD EduSales Cube EduSales", "", "OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=SSAS_PROD;Initial Catalog=EduSales Cube EduSales"

To save a workbook based on a prompt:

Dim wbName as string

wbName = inputbox("Please enter the name for the workbook")

if wbName <> "" then

thisworkbook.saveas "C:\Analysis\" & wbName,xlWorkbookDefault)

emd if

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BxWill]

Will make the code modifications.

Basically, I plan to use the the macro-enabled Excel workbook to create 10 different Excel workbooks that each contain a different pivot table. There is a possibility that additional pivots will be needed in the next few weeks...

Did initially consider creating 10 pivots - 1 pivot on 10 separate worksheets within the same Excel 2007 workbook.

If I was to reconsider the option of creating multiple pivots within the same Excel workbook - for example, 5 pivots within 2 Excel workbooks, how would the code change?

Once I create all of the pivot tables, the plan is to refresh on a semi-monthly basis.

Is the logic sound? How would you perform differently?

 

[SkipVought]

Are the 10 pivots each from different sources and/or different fields?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

If the pivot tables won't change bar refreshing data, I would suggest setting them all up 1st as you would need to do is refresh the info which is far simpler and less prone to error

If you choose to do that, there is not much difference in the code to use between 1 pivot per workbook or 10 in a workbook or anything in between - all you would do is loop through either a list of worksheets or workbooks or worksheets within workbooks and execute the refreshtable method of the pivottable

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BxWill]

Incorporated the suggestions into the code and worksheet is populated.

The source of the pivots are various fields within the sql server cube.

The Service Category is basically the only field that will change.

Desire to set up a separate worksheet per Service Category.

For example, the Service Category will be similar to
"Day-South", "Day-West", "Day-North", "Day-North2", etc.

Fields such as the months will be the same for all of the pivots.

For example, the block of code like the following will be the same for all of the pivots.

 VisibleItemsList = Array("[Date].[Month].&[201001]", "[Date].[Month].&[201002]", _        "[Date].[Month].&[201003]", "[Date].[Month].&[201004]")

Is it possible to have a "rolling month feature" whereby every month, an additional month is "automatically" added to the query?

For example, the query above is run during April 2010 and on May 1,2010, "[Date].[Month].&[201005]" should "automatically"
be added to the code, on June 1,2010, "[Date].[Month].&[201006]" should automatically be added.

Currently, I have to manually modify the dates every month...

 

[SkipVought]

Is it possible to have a "rolling month feature" whereby every month, an additional month is "automatically" added to the query?

Your posted code indicates that you are importing ALL the data from your cube and THEN filtering the data in the PT.

If you were to set a criteria in your query, to have a where clause criteria to only return data from a date range based on the current date, then your PT date range would adjust 'automatically.' THAT is the simplest way IMHO.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Where would I place a "WHERE" clause?

Upon accessing a blank MS Excel worksheet, I place the cursor at cell A1, then "Data/External Data" then connect to the cube and create pivot.

 

[SkipVought]

I've never connected to a cube. Are there no choices in the Data/External Data wizard for selecting any criteria?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

BxWill, depending on the type of connection, you should be able to edit the query and change the command type. If you right click on your query table and go to Edit Query it should either come up in MS Query or a popup called "Edit OLE DB Query"
Either way you should be able to use SQL to change the query parameters.

The VBA to change the command text or connection string or anything like that will depends on the connection type. Easiest way to figure out how to change it is the old standby of recording a macro of a manual change.

I do something similar myself where I have a bunch of .sql files in a folder that I load into excel, and change the query parameters to that of the query file. Once you get it all set up it's quite handy.

 

[SkipVought]

Here's some code that I use for Excel 2007, AND assuming that you have only ONE ListObject on your sheet...

With Activesheet.ListObjects(1).QueryTable
   debug.print .commandtext
end with

That prints the SQL to the Immediate Window.

Modify the code and then assign the CommandText object, for instance...

dim sSQL as string

sSQL = sSQL & "Select * "
sSQL = sSQL & "From [YourTable] "
sSQL = sSQL & "Where [YourDateField] <= Date + 90

With Activesheet.ListObjects(1).QueryTable
   .commandtext = sSQL
   .refresh false
end with

where Date is the CURRENT DATE value in your database (could be SYSDATE or Date or CurrentDate ???)


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}