OWC Pivot Table - can't sum a field!

[Doug]

I've added a data field to a pivot table:
v.DataAxis.InsertFieldSet(v.FieldSets("Duration"));

I then create a total field:
var t = v.AddTotal("Duration", v.FieldSets("Duration").Fields(0),
c.plFunctionSum);
v.DataAxis.InsertTotal(t);

This causes a javascript error:
"function cannot be applied to the given field"

If I change c.plFunctionSum to c.plFunctionCount, it's all fine.
The field in question is decimal with no empty values. I've also tried
plFunctionSum with an int field, same result.

I'd be very grateful for some help. Thanks.

PS Microsoft - the documentation for OWC is pathetic. The samples for
OWC are pathetic. Get some C# samples written.

 

[Doug]

Turns out this is only a problem if the data comes from an XML file
(even though the attribute is correctly marked as decimal). If I
connect directly to the same data in a SQL table, it's fine. Another
endearing OWC quirk ...

 

[madhuri]

Doug,
Can you post how you connected to sql instead of xml. I am stuck with this.
My database is in oracle.
thanks a lot
-Madhuri.

 

[ZurcioS]

If you want to use ADO Recordset as data source for Pivot table, maybe this
trick can help you (vb syntax):

PivotTable.ActiveView.AutoLayout
If Err.Number <> 0 Then
'ignore error
End If
Set PivotTable.DataSource = MYDATARECORDSET
PivotTable.DataMember = ""
PivotTable.ActiveView.AutoLayout

you can also connect to SQL (i did it some time ago, but with MSSQL) - then
you have to provide ConnectionString and table/view name as DataSource.

Slawomir Zurek

 

[madhuri]

Slawomir,
Thank you for replying. I am using asp.net with c# as code behind language.
My code looks like this-
try
{
PivotTableClass objPT = new PivotTableClass();
ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs= new ADODB.Recordset();
string connectionString = "Provider=OraOLEDB.Oracle;" + (new Rmsdb.Db()).
GetConnectionString();
cn.Open(connectionString, null, null, 0);
rs.Open(mainSql, connectionString, ADODB.CursorTypeEnum.adOpenKeyset,
ADODB.LockTypeEnum.adLockOptimistic, -1);
try {objPT.ActiveView.AutoLayout(10000);} catch{}
objPT.DataSource=rs;
objPT.DataMember="";
objPT.ActiveView.AutoLayout(10000);
string xmlString = objPT.XMLData;
xmlStringValue.Value = xmlString;
}

It is giving error in the line- objPT.DataSource=rs;
the error is- "Cannot implicitly convert type 'ADODB.Recordset' to
'msdatasrc.DataSource'"

I tried to case it to msdatasrc.DataSource like this-
objPT.DataSource=(msdatasrc.DataSource)rs;
but this also gives me an error - "No such interface supported".

Please let me know how I can modify this to give me correct results.
thank you so much. -Madhuri

 

[ZurcioS]

Hi,
I looked over the net for some information, and found interesting newsgroup
message (this link should show You help:
http://groups.google.com/groups?q="PivotTable+FieldSets+with+C#" )
There is descritpion of problem AFTER feedind OWC pivot table with Recordset
(source written in C#) - i think the problem is that You are using
ADODB.Recordset class, not _ADODB.RecordsetClass_

From other hand, i see that in C# there is probably no need to use any
"tricky code" ;-)

Hope that helps
Slawomir Zurek

PS: Here are the few essential lines of code :

PivotTableClass pvtMSF1 = new PivotTableClass();
ADODB.RecordsetClass rsADO = new ADODB.RecordsetClass(); <----

object prm = Missing.Value;
object RecAffected = new Object();

rsADO = (ADODB.RecordsetClass) cmdADO.Execute(out
RecAffected, ref prm, opt);
pvtMSF1.DataSource = (msdatasrc.DataSource) rsADO;

 

[madhuri]

Thanks Slawomir for the quick reply.
When I am assigning the datasource of the pivot table with the result set,
it is giving me the error "No such interface supported ".

 

[ZurcioS]

Hello again Madhuri,
I made a test application - and it works excellent. Main different is that
i'm using MSSQL as data provider (but the main problem is recordset object,
not data provider). I created a simple windows form with OWC10 PivotTable
and one button - in the Click event i put following code (i changed names of
server,database and sql table because they are polish an could be obscure
for you) :

ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
conn.Open("PROVIDER=SQLOLEDB;Data Source=SERVERNAME;Initial
Catalog=DATABASENAME;Integrated Security=SSPI","","",0);
rs.Open("SELECT * FROM MYTABLE
",conn,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockUnspecified,0);
this.axPivotTable1.DataSource = (msdatasrc.DataSource) rs;
this.axPivotTable1.ActiveView.AutoLayout(100);

I hope that helps, because I have no idea what else could be the problem.
Regards
Slawomir Zurek

 

[Madhuri]

Slawomir,
thanks for the quick response and trying. I think my problem is I dont
have the right msdatasrc object.
http://support.microsoft.com/default.aspx?scid=kb;en-us;246896

I couldnt get a MDAC download for windows XP. (my system is windows
XP.) Can you please try to get a download for MDAC for windows XP.
hope that fixes the problem.
thank you so much.-Madhuri.

 

[Madhuri]

Slawomir,
I could download MDAC 2.8 (the latest version) and install on other XP
systems but not on mine. The only thing I could think of is to
re-install the XP operating system. Hopefully that fixes it.
thanks so much for helping me thru this.
-Madhuri.

 

[Prasanna Miapuram]

I was having the exact similar problem as reported by Madhuri and followed
the guidelines provided by Slawomir.

The solution worked like a champ. Thanks guys.