How do you use Shaped Data in a PivotTable?

[JohnK]

I am trying to used Shaped Data as the data source for a pivot table.
The Shaped Data is producinging a MDX recordset which the PivotTable
Office Web Component is not recognising. I have found something about
flattening the dataset
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q279254
but this doesnot seem to be working:

Dim sConn
Dim sMPP
Dim sMDX

sMDX = "SHAPE {SELECT AssignmentUniqueID , AssignmentResourceName FROM
Assignments WHERE AssignmentRemainingWork>0}" & _
" APPEND ({ SELECT AssignmentUniqueID, AssignmentTimeActualWork ,
AssignmentTimeWork, AssignmentTimeStart FROM AssignmentTimephasedByDay
" & _
" WHERE AssignmentTimeWork>AssignmentTimeActualWork } AS chapter" & _
" RELATE AssignmentUniqueID TO AssignmentUniqueID) "

sMPP = "Master.mpp"

'Specify the connection string
sConn = "Provider=Microsoft.Project.OLEDB.11.0;Project Name=" +
sMPP
sConn = "Provider=MSDATAShape; Data " & sConn

'Get the Flattened recordset for the MDX query
Dim oConn, oRS
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")
oConn.Open sConn
oRS.Open Open sMDX, oConn, 3 'adOpenStatic=3

PivotTable1.Datasource = oRS

Can someone spot the error or suggest a way of achieving the same
effect?
Thanks in advance.
John.

 

[JohnK]

Sorry but the open line is actually:
oRS.Open sMDX, oConn, 3 'adOpenStatic=3
Best Wishes, John