Pivot Table ASP.NET without webservice?

[rockdale]

I googled and searched in this group but still no clue how to use Pivot
Table in ASP.NET

The only example I found is

http://msdn.microsoft.com/library/default.asp?url=/msdnmag/issues/03/10/olap/toc.asp

I downloaded its source code and it works fine.

But this example uses webService to get the meta data of Pivot Table as
XML file and return back to JavaScript function.

I am wondering is there other approach so that we can get rif of the
webservice

I am using OWC11 and ASP.net (C#)

First I have a function to build the meta data XML in my aspx.cs

private String BuildPivotTableMetaDataXML(){
String strPivotTableMetaDataXML;
String strOLAPConn =
ConfigurationSettings.AppSettings["OLAPConnectionString"];
try
{
PivotTableClass objPT = new PivotTableClass();
objPT.ConnectionString = strOLAPConn;
objPT.DataMember = "Sales";
//TODO
// define the column elements
// define the row elements
// define the members of the row elements


strPivotTableMetaDataXML = objPT.XMLData;

}
catch(Exception ex)
{
strPivotTableMetaDataXML = "<err>" + ex.Source + " - " + ex.Message
+ "</err>";
}

return strPivotTableMetaDataXML;
}



Then I tried to pass this XML string to a Function in JavaScript

function PivotTableBind(result) {
var text = result.value; // result string
// Evaluate return result
if (!result.error) {
if (text.indexOf("<err>") > -1 ) {
alert(text);
}
// Assign the XML to the PivotList XMLData value
frm1.PivotTable1.XMLData = text;
}
else {
alert("Unhandled error - " + result.errorDetail.code + " " +
result.errorDetail.string);
}
}



As you can see, those functions are similiar to the example I mention
earlier, I made some changes to not using WebService. And of course it
did not work. I believe it is because that the XML file contains some
special character (e.g back-slash).

I can put a pivot table on my aspx page and click the fields list to
define the format of pivot table and retrieve data from Analysis
service. But I can not save the format I defined. So everytime the user
comes in, the page always shows a empty pivot table.

What I want to achieve is the first time the user come to this page, I
will show him the default setting of my Pivot Table, then the user may
define his own format and load his own format later.

My question is how did you guys get Pivot Table work with ASP.net.

My understanding is first you build the meta data XML. you can build it
on the server side, but somehow you have to link this xml string to the
Pivot table object on client side (VBScript or JavaScript).

Am I right?

And I believe there are other approaches to do what I want to do.
Please help.

Thank you very much
-Rockdale

 

[Douglas Osborne]

RockDale,

If you download http://msdn.microsoft.com/msdnmag/issues/03/10/OLAP/, switch
the web.config to point to your database

eg
<appSettings>

<add key="OLAPConnectionString" value="Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=NGEPMProd;Data
Source=Nextgen1.cardionet.com" />

</appSettings>

in owc10.aspx, set the click code for Set Connection to connect to one of
your tables in your database onclick="initializePivotTable
('tmpReportPatientStatusSummary')"

and then be aware of the paths in the JS files and you should get something
working.

At first you can build a report from your table and save it to file
OLAPReport1.xml, and move from there.

It does not sink in overnight.

Doug

I googled and searched in this group but still no clue how to use Pivot
Table in ASP.NET

The only example I found is

http://msdn.microsoft.com/library/default.asp?url=/msdnmag/issues/03/10/olap/toc.asp

I downloaded its source code and it works fine.

But this example uses webService to get the meta data of Pivot Table as
XML file and return back to JavaScript function.

I am wondering is there other approach so that we can get rif of the
webservice

I am using OWC11 and ASP.net (C#)

First I have a function to build the meta data XML in my aspx.cs

private String BuildPivotTableMetaDataXML(){
String strPivotTableMetaDataXML;
String strOLAPConn =
ConfigurationSettings.AppSettings["OLAPConnectionString"];
try
{
PivotTableClass objPT = new PivotTableClass();
objPT.ConnectionString = strOLAPConn;
objPT.DataMember = "Sales";
//TODO
// define the column elements
// define the row elements
// define the members of the row elements


strPivotTableMetaDataXML = objPT.XMLData;

}
catch(Exception ex)
{
strPivotTableMetaDataXML = "<err>" + ex.Source + " - " + ex.Message
+ "</err>";
}

return strPivotTableMetaDataXML;
}



Then I tried to pass this XML string to a Function in JavaScript

function PivotTableBind(result) {
var text = result.value; // result string
// Evaluate return result
if (!result.error) {
if (text.indexOf("<err>") > -1 ) {
alert(text);
}
// Assign the XML to the PivotList XMLData value
frm1.PivotTable1.XMLData = text;
}
else {
alert("Unhandled error - " + result.errorDetail.code + " " +
result.errorDetail.string);
}
}



As you can see, those functions are similiar to the example I mention
earlier, I made some changes to not using WebService. And of course it
did not work. I believe it is because that the XML file contains some
special character (e.g back-slash).

I can put a pivot table on my aspx page and click the fields list to
define the format of pivot table and retrieve data from Analysis
service. But I can not save the format I defined. So everytime the user
comes in, the page always shows a empty pivot table.

What I want to achieve is the first time the user come to this page, I
will show him the default setting of my Pivot Table, then the user may
define his own format and load his own format later.

My question is how did you guys get Pivot Table work with ASP.net.

My understanding is first you build the meta data XML. you can build it
on the server side, but somehow you have to link this xml string to the
Pivot table object on client side (VBScript or JavaScript).

Am I right?

And I believe there are other approaches to do what I want to do.
Please help.

Thank you very much
-Rockdale

 

[rockdale]

Hi, Doug:

Thanks for your reply, but it did not answer my question.

I got the download program working what I want to do is do not use the
web service to get XMLData of Pivot table.

Sorry about my very long first post

Thank you

RockDale,

If you download http://msdn.microsoft.com/msdnmag/issues/03/10/OLAP/, switch
the web.config to point to your database

eg
<appSettings>

<add key="OLAPConnectionString" value="Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=NGEPMProd;Data
Source=Nextgen1.cardionet.com" />

</appSettings>

in owc10.aspx, set the click code for Set Connection to connect to one of
your tables in your database onclick="initializePivotTable
('tmpReportPatientStatusSummary')"

and then be aware of the paths in the JS files and you should get something
working.

At first you can build a report from your table and save it to file
OLAPReport1.xml, and move from there.

It does not sink in overnight.

Doug

I googled and searched in this group but still no clue how to use Pivot
Table in ASP.NET

The only example I found is

http://msdn.microsoft.com/library/default.asp?url=/msdnmag/issues/03/10/olap/toc.asp

I downloaded its source code and it works fine.

But this example uses webService to get the meta data of Pivot Table as
XML file and return back to JavaScript function.

I am wondering is there other approach so that we can get rif of the
webservice

I am using OWC11 and ASP.net (C#)

First I have a function to build the meta data XML in my aspx.cs

private String BuildPivotTableMetaDataXML(){
String strPivotTableMetaDataXML;
String strOLAPConn =
ConfigurationSettings.AppSettings["OLAPConnectionString"];
try
{
PivotTableClass objPT = new PivotTableClass();
objPT.ConnectionString = strOLAPConn;
objPT.DataMember = "Sales";
//TODO
// define the column elements
// define the row elements
// define the members of the row elements


strPivotTableMetaDataXML = objPT.XMLData;

}
catch(Exception ex)
{
strPivotTableMetaDataXML = "<err>" + ex.Source + " - " + ex.Message
+ "</err>";
}

return strPivotTableMetaDataXML;
}



Then I tried to pass this XML string to a Function in JavaScript

function PivotTableBind(result) {
var text = result.value; // result string
// Evaluate return result
if (!result.error) {
if (text.indexOf("<err>") > -1 ) {
alert(text);
}
// Assign the XML to the PivotList XMLData value
frm1.PivotTable1.XMLData = text;
}
else {
alert("Unhandled error - " + result.errorDetail.code + " " +
result.errorDetail.string);
}
}



As you can see, those functions are similiar to the example I mention
earlier, I made some changes to not using WebService. And of course it
did not work. I believe it is because that the XML file contains some
special character (e.g back-slash).

I can put a pivot table on my aspx page and click the fields list to
define the format of pivot table and retrieve data from Analysis
service. But I can not save the format I defined. So everytime the user
comes in, the page always shows a empty pivot table.

What I want to achieve is the first time the user come to this page, I
will show him the default setting of my Pivot Table, then the user may
define his own format and load his own format later.

My question is how did you guys get Pivot Table work with ASP.net.

My understanding is first you build the meta data XML. you can build it
on the server side, but somehow you have to link this xml string to the
Pivot table object on client side (VBScript or JavaScript).

Am I right?

And I believe there are other approaches to do what I want to do.
Please help.

Thank you very much
-Rockdale