How do I use DataShape for Project?

[john.kaye]

I am trying to use Data Shape to join Assignments to
AssignmentsTimePhasedByDay. My connection String is:

sMPP = "D:\Master.mpp"
sConn = "Provider=MSDATASHAPE;Extended Properties='Project Name=" +
sMPP + _
"';Persist Security Info=False;Data Source='';User ID='';Initial
Catalog=" + sMPP + _
";Data Provider=Microsoft.Project.OLEDB.11.0"

The connection works with simple select statements like:

sSQL = "SELECT AssignmentRemainingWork , AssignmentResourceName,
AssignmentStart FROM Assignments WHERE AssignmentRemainingWork>0"

sSQL = "SELECT AssignmentUniqueID , AssignmentResourceName FROM
Assignments WHERE AssignmentRemainingWork>0"

sSQL= "SELECT AssignmentUniqueID, AssignmentTimeActualWork ,
AssignmentTimeWork, AssignmentTimeStart FROM AssignmentTimephasedByDay
" & _
" WHERE AssignmentTimeWork > AssignmentTimeActualWork"

As soon as I use SHAPE, I get an error message: data provider failed
while executing a provider command e.g.

sSql = "SHAPE {SELECT TaskUniqueId FROM Tasks} APPEND ({SELECT
TaskUniqueID, ResourceUniqueID FROM Assignments} RELATE 'TaskUniqueId'
TO 'TaskUniqueId')"

The Shape commend I want to execute is:
sSQL = "SHAPE {SELECT AssignmentUniqueID , AssignmentResourceName FROM
Assignments WHERE AssignmentRemainingWork>0}" & _
" APPEND ({ SELECT AssignmentUniqueID, AssignmentTimeActualWork ,
AssignmentTimeWork, AssignmentTimeStart FROM AssignmentTimephasedByDay
" & _
" WHERE AssignmentTimeWork > AssignmentTimeActualWork } AS Details" & _

" RELATE 'AssignmentUniqueID' TO 'AssignmentUniqueID')"

Can anyone help me out here?
Thank in advance.
Best Wishes, John.

 

[john.kaye]

I have worked on this further and my problem seems to that the Shape
Data is producinging a MDX expression 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.

 

[john.kaye]

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