sum function in the pivot table does not compute sum but instead gives count

[madhuri]

Let me first explain what I am doing. I have a c# asp.net page where I am
taking some inputs from the users and displaying the output on the screen in
a pivot table. I am creating an XML document in the submit btn onClick
method and loaded this document by using VBScript into the pivot table where
I say document.pt.commandText="<%=XMLDocURL%>" where pt stands for
pivotTable.
Everything works, all the data shows up. But when I say-
document.pt.ActiveView.DataAxis.InsertTotal .AddTotal ("JanTotals", .
FieldSets("JAN").Fields(0), 1)
it gives count instead of sum. I double checked if I am sending text instead
of numbers. Its numbers. And it is not null values coming from the sql. But
in the display, I set the format as -
document.pt.ActiveView.DataAxis.FieldSets("JAN").Fields(0).NumberFormat =
"$#,###0.00;($#,###0.00);"
I commented these formats but it still doesnt work. There are no null values
if I comment the numberformat. If I have the number format on, it doesnt
display zero's, which is what I wanted that to do.
I checked the XML file generated. As it is generated using dataset, it has
RowSetSchema in it. So I am now trying it to reflect OWC pivot table schema.

I created a recordset in the code behind file now and am trying to give that
as a data source to the OWC pivot table-
objPT.DataSource=(msdatasrc.DataSource)rs.DataSource;
This line is giving me error saying "No such interface supported".
My plan was to save the recordset as an XML document whose schema is OWC
pivottable schema.
Any help on this will be appreciated. thanks so much -Madhuri.

 

[madhuri]

so found this-
The "No such interface supported" error generally occurs when we have the
previous versions of the components used. So I tried to install MDAC 2.8
(latest version). It wouldnt install on my system. I tried on other systems
with the same configuration and OS. It would install properly on theirs.
Guess my OS is corrupted or I have some other software thats not allowing
MDAC to install.
Anyway the XML document I was generating till now is from a recordset. So it
has RowSetSchema. The number columns in the XML were defaulted to varnumeric
data type. When I change the varnumeric to numeric, the pivot table displays
the sum properly. So the solution, I think for this problem, is-
get the recordset write into a xml file, then open the xml - replace
varnumeric with numeric and load onto pivot table. This will show the
aggregate functions properly.
Let me know if there is any other solution.
thanks -Madhuri.