S
Simon
Hi,
I'm having some problems getting PivotTables to work when attached to
bog-standard tables (rather than SSAS cubes).
Here's what I can determine so far (with a really simple, 3 field,
single table source)
-> The recordset is being created fine, and does have valid data in it.
-> The pivottable is 'aware' of the table structure because when I
examine .XMLData after if added stuff to it, it contains <x
ivotField>
elements corresponding to the fields in the underlying table.
-> But the XMLData does not contain any real data - it's like it's
aware of the structure but can't see the data.
In most of the examples (against SSAS/OLAP) there is a .DataMember
property that references the source cube. What (if anything) to I put
in here for a vanilla table? That's about the only thing I can come up
with as a problem so far. Leaving it as Nothing results in a runtime
error, setting it to Book3 (the source table) doesn't seem to do much
either. I could also have missed a step in my AddPivotRow().
Perhaps some code would help. I might add that my copy of the Black
Book has been ordered, but any pointers to get me started pending its
arrival (and unless I'm being really dense it does not seem to be
offered as a download) would ease my pain. Apologies on the longish
post, but I'd rather add everything that I think is relevant at the
start.
Regards
Simon
oOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoO
Dim pt As PivotTable = New PivotTableClass
Dim conn As ADODB.Connection = New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String = "SELECT AccessLevel, LMGPIN, FunctionType
FROM book3"
With conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='C:\Documents and Settings\Simon\My Documents\Visual Studio
2005\WebSites\owc2\App_Data\db1.mdb'"
.Open()
End With
rs.Open(SQL, conn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockOptimistic)
With pt
.ConnectionString = conn.ConnectionString
.CommandText = SQL
.DataSource = rs
.DataMember = ""
With .ActiveView
.AutoLayout()
Dim Fields As String() = {"AccessLevel", "LMGPIN",
"FunctionType"}
AddPivotRows(pt, Fields)
End With
End With
TheXml = pt.XMLData
Where AddPivotRows is defined as
Private Sub AddPivotRows(ByVal PivotTable As PivotTableClass, ByVal
Fields As String())
Dim av As PivotView = PivotTable.ActiveView
For Each FieldToAdd As String In Fields
Dim fs As PivotFieldSet = av.FieldSets(FieldToAdd)
av.RowAxis.InsertFieldSet(fs)
With fs.Fields(FieldToAdd)
.IsIncluded = True
End With
Next
The resulting .XMLData is as follows
<xivotTable>
<x:OWCVersion>11.0.0.6555 </x:OWCVersion>
<x:NoDisplayAlerts/>
<xisplayScreenTips/>
<x:CubeProvider>msolap.2</x:CubeProvider>
<x:CacheDetails/>
<x:Name>Microsoft Office PivotTable 11.0</x:Name>
<x:NoColumnGrand/>
<xataAxisEmpty/>
<xivotField>
<x:Name>AccessLevel</x:Name>
<x:Orientation>Row</x:Orientation>
<x:EncodedType>adVarWChar</x:EncodedType>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
<x:Subtotal>None</x:Subtotal>
<x:Expanded/>
</xivotField>
<xivotField>
<x:Name>LMGPIN</x:Name>
<xataType>Integer</xataType>
<x:Orientation>Row</x:Orientation>
<xosition>2</xosition>
<x:EncodedType>adInteger</x:EncodedType>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
<x:Subtotal>None</x:Subtotal>
<x:Expanded/>
</xivotField>
<xivotField>
<x:Name>FunctionType</x:Name>
<x:Orientation>Row</x:Orientation>
<xosition>3</xosition>
<x:EncodedType>adVarWChar</x:EncodedType>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
<x:Expanded/>
</xivotField>
<xivotField>
<x:Name>Data</x:Name>
<x:Orientation>Column</x:Orientation>
<xosition>-1</xosition>
<xataField/>
</xivotField>
<xivotView>
<x:IsNotFiltered/>
</xivotView>
</xivotTable>
</xml>
I'm having some problems getting PivotTables to work when attached to
bog-standard tables (rather than SSAS cubes).
Here's what I can determine so far (with a really simple, 3 field,
single table source)
-> The recordset is being created fine, and does have valid data in it.
-> The pivottable is 'aware' of the table structure because when I
examine .XMLData after if added stuff to it, it contains <x
ivotField>elements corresponding to the fields in the underlying table.
-> But the XMLData does not contain any real data - it's like it's
aware of the structure but can't see the data.
In most of the examples (against SSAS/OLAP) there is a .DataMember
property that references the source cube. What (if anything) to I put
in here for a vanilla table? That's about the only thing I can come up
with as a problem so far. Leaving it as Nothing results in a runtime
error, setting it to Book3 (the source table) doesn't seem to do much
either. I could also have missed a step in my AddPivotRow().
Perhaps some code would help. I might add that my copy of the Black
Book has been ordered, but any pointers to get me started pending its
arrival (and unless I'm being really dense it does not seem to be
offered as a download) would ease my pain. Apologies on the longish
post, but I'd rather add everything that I think is relevant at the
start.
Regards
Simon
oOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoO
Dim pt As PivotTable = New PivotTableClass
Dim conn As ADODB.Connection = New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String = "SELECT AccessLevel, LMGPIN, FunctionType
FROM book3"
With conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='C:\Documents and Settings\Simon\My Documents\Visual Studio
2005\WebSites\owc2\App_Data\db1.mdb'"
.Open()
End With
rs.Open(SQL, conn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockOptimistic)
With pt
.ConnectionString = conn.ConnectionString
.CommandText = SQL
.DataSource = rs
.DataMember = ""
With .ActiveView
.AutoLayout()
Dim Fields As String() = {"AccessLevel", "LMGPIN",
"FunctionType"}
AddPivotRows(pt, Fields)
End With
End With
TheXml = pt.XMLData
Where AddPivotRows is defined as
Private Sub AddPivotRows(ByVal PivotTable As PivotTableClass, ByVal
Fields As String())
Dim av As PivotView = PivotTable.ActiveView
For Each FieldToAdd As String In Fields
Dim fs As PivotFieldSet = av.FieldSets(FieldToAdd)
av.RowAxis.InsertFieldSet(fs)
With fs.Fields(FieldToAdd)
.IsIncluded = True
End With
Next
The resulting .XMLData is as follows
<x
ivotTable><x:OWCVersion>11.0.0.6555 </x:OWCVersion>
<x:NoDisplayAlerts/>
<x
isplayScreenTips/><x:CubeProvider>msolap.2</x:CubeProvider>
<x:CacheDetails/>
<x:Name>Microsoft Office PivotTable 11.0</x:Name>
<x:NoColumnGrand/>
<x
ataAxisEmpty/><x
ivotField><x:Name>AccessLevel</x:Name>
<x:Orientation>Row</x:Orientation>
<x:EncodedType>adVarWChar</x:EncodedType>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
<x:Subtotal>None</x:Subtotal>
<x:Expanded/>
</x
ivotField><x
ivotField><x:Name>LMGPIN</x:Name>
<x
ataType>Integer</xataType><x:Orientation>Row</x:Orientation>
<x
osition>2</xosition><x:EncodedType>adInteger</x:EncodedType>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
<x:Subtotal>None</x:Subtotal>
<x:Expanded/>
</x
ivotField><x
ivotField><x:Name>FunctionType</x:Name>
<x:Orientation>Row</x:Orientation>
<x
osition>3</xosition><x:EncodedType>adVarWChar</x:EncodedType>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
<x:Expanded/>
</x
ivotField><x
ivotField><x:Name>Data</x:Name>
<x:Orientation>Column</x:Orientation>
<x
osition>-1</xosition><x
ataField/></x
ivotField><x
ivotView><x:IsNotFiltered/>
</x
ivotView></x
ivotTable></xml>