470,849 Members | 1,102 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,849 developers. It's quick & easy.

Web service dataset to pivot

Hi Folks
Can anyone direct me to a simple example of using a pivot table to consume a
dataset from a web service?

Using Web Service Reference Tool, Office 11. Web service comes from .NET
application

Thanks in advance!
Andrew

Jun 12 '06 #1
4 3206
Hi,
Did you find a solution, cause i have the same problem!
Best regards


*** Sent via Developersdex http://www.developersdex.com ***
Jun 19 '06 #2
HI Andrew,

could you go further in your investigation?
Best Regards

*** Sent via Developersdex http://www.developersdex.com ***
Jun 19 '06 #3
Hi, I figured it out, but in a very convoluted way.
Basically, write the contents of the dataset into a worksheet, in my case
called "Work", then construct the pivot table programatically. After all
this, clean the workarea.

I would be very pleased if someone has a shorter, more efficient way to do
this.

Code below: (Note, Office XP Web Services toolkit and Xml Parser 6 must be
installed on user machine to consume the web service and parse the Xml)

Sub Get_A21_Pivot()

On Error GoTo Errhandler

Dim objXmlDoc As New DOMDocument
Dim objNList As IXMLDOMNodeList
Dim objWS As New clsws_TNAService
Dim objTableNodes As IXMLDOMNodeList
Dim objDataNode As IXMLDOMNode
Dim RowCount As Integer
Dim ColCount As Integer
Set objNList = objWS.wsm_Get_A21_Pivot

If Not objNList Is Nothing Then
objXmlDoc.LoadXml objNList(1).XML
Set objTableNodes = objXmlDoc.getElementsByTagName("Table")
If Not objTableNodes Is Nothing Then
RowCount = objTableNodes.Length
If objTableNodes.Length > 0 Then
ColCount = objTableNodes(0).childNodes.Length
Else
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If

If objTableNodes.Length > 0 Then
ColCount = objTableNodes(0).childNodes.Length
Else
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If

Worksheets("Work").Activate
Range("A1") = "#"
Range("b1") = "Occupational Category"
Range("c1") = "Class"
Range("d1") = "Gender"
Range("e1") = "Qty"

For i = 0 To RowCount - 1
Set objDataNode = objTableNodes(i)
For j = 0 To ColCount - 1
Worksheets("Work").Cells(i + 2, j + 1) =
objDataNode.childNodes(j).Text
Next j
Next i

'Make pivot
Worksheets("Section A2").Activate

Dim PTCache As PivotCache
Dim pt As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Sheets("Work").Range("A1").CurrentRegi on.Address)

Set pt = PTCache.CreatePivotTable _
(TableDestination:="A.2.1.", TableName:="Pivot1")

With pt
.PivotFields(1).Orientation = xlRowField 'Number
.PivotFields(2).Orientation = xlRowField 'Category
.PivotFields(3).Orientation = xlColumnField 'Race
.PivotFields(4).Orientation = xlColumnField 'Gender
.PivotFields(5).Orientation = xlDataField 'Count
End With

'Remove grand totals, set the order of the columns
ActiveSheet.PivotTables("Pivot1").RowGrand = False

ActiveSheet.PivotTables("Pivot1").PivotFields(4).P ivotItems("M").Position = 1

ActiveSheet.PivotTables("Pivot1").PivotFields(4).P ivotItems("F").Position = 2

ActiveSheet.PivotTables("Pivot1").PivotFields(4).P ivotItems("Disabled").Position = 3
Call CleanWorkArea

Else
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If
End If

"Andrew van Zyl" wrote:
Hi Folks
Can anyone direct me to a simple example of using a pivot table to consume a
dataset from a web service?

Using Web Service Reference Tool, Office 11. Web service comes from .NET
application

Thanks in advance!
Andrew

Jun 19 '06 #4
Thank you for your answer!
in fact i am using office web component in IE 6 and not excel. So your
solution is not possible.
and furthermore the owc pivottable has to be create client side. i am
still looking for a solution and for me consuming a web service that
deliver xml data was an elegant solution but i can't figure out how to
connect the two parts join
best regards

*** Sent via Developersdex http://www.developersdex.com ***
Jun 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by lakshmi | last post: by
1 post views Thread by Bernard O'Flynn | last post: by
2 posts views Thread by Carl Gilbert | last post: by
1 post views Thread by kingster | last post: by
9 posts views Thread by PeteCresswell | last post: by
9 posts views Thread by jsoques | last post: by
15 posts views Thread by Joseph Geretz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.