Owc introduction and attributes

Figure 3 Office Web Components (version 10)

Components	Description
PivotTable	Connect the user to an OLAP data source that supports ole db provider for OLAP services 8.0 or higher (you can also connect to an excel electronic data form and SQL Server and an access relational database ). The PivotTable control allows you to view, group, filter, and sort data.
Spreadsheet	Provides electronic data form user interfaces, including the recalculation engine and Extension function library.
Chart	Graphically displays data from a bound data source, PivotTable, or Spreadsheet control. The chart component is automatically refreshed when it is bound to the PivotTable control and the user views the data again.
Datasource	Manage communication with the backend database server. The replicttable and spreadsheet components can be used to connect to the data source separately. The datasource component is not required. This is because XML data can directly contain ole db connection strings.

Figure 4 random tTable and chart-related objects

Member	Description
PivotTable	Connect the user to an OLAP data source that supports ole db provider for OLAP services 8.0 or higher (you can also connect to an excel electronic data form and SQL Server and an access relational database ). The PivotTable control allows you to view, group, filter, and sort data.
Effectview	A specific view of the PivotTable. Used to set rows, columns, scales, and formatting in the PivotTable view.
Pivotdataaxis	Includes methods and attributes related to the data axis
Specified tresultcolumnaxis	Includes methods and attributes related to the column axis
Pivotresultrowaxis	Contains methods and attributes related to the line axis
Pivotfieldset	Field set defined in A Multidimensional Dataset
Effectfield	Fields defined in A Multidimensional Dataset
Chartspace	Connect the user to any OLAP data source that supports ole db provider for OLAP services 8.0 or higher (you can also connect to excel electronic data forms and SQL Server, access relational database ). Chartspace allows you to visually display data and bind the control to an existing javasttable or spreadsheet.
Chcharts	Chchart object set
Chchart	A single chart in chartspace, A chartspace can accommodate up to 64 charts.

Figure 5 implements tTable component's programmatic Interface

Member	Type	Description
Activeview	Attribute	It indicates the layout of an active worker tTable. This property returns a tview object.
Columnaxis	Attribute	Fields in the column axis. Returns a taxis object.
Connectionstring	Attribute	Set the connection string to connect to the analysis services server. The datasource parameter in the connectionstring attribute determines the connection protocol to be used by the owc component.
Dataaxis	Attribute	The size of the Data axis. Returns a taxis object.
Datamember	Attribute	Set the name of the data source requested by the control from analysis services. It has the same name as a multi-dimensional dataset.
Includedmembers	Attribute	Defines the data members in the effectfield. The parameter accepted by this attribute is a single member or a member array.
Isincluded	Attribute	Set the contained fields and activate effectfieldset.
Rowaxis	Attribute	The field in the row axis. The taxis object is returned.
Xmldata	Attribute	Sets or returns the XML data currently used for the PivotTable report control. Details about the report (format and data) are saved in XML data. It also includes OLAP connection details.
Addcustomgroupfield	Method	Add a custom grouping field for the specified effectfieldset.
Addmgmgroupmember	Method	Adds a custom group member to the specified effectfieldset.
Insertfieldset	Method	Insert a field set in the row or column axis.
Commandexecute	Event	Triggered after a command is executed. The chartcommandidenum and effectcommandid constants contain the list of commands supported by each owc component.
Query	Event	PivotTable is triggered when a query is activated.

Figure 6 chart component's programmatic Interface

Member	Type	Description
Datasource	Attribute	Define a data source for the chart control. When you set another control (such as javasttable or spreadsheet), you can effectively bind the chart control to other controls.
Type	Attribute	Define the chart type as defined in the chartcharttypeenum enumeration. The default type is a bar chart.

Figure 8 connecting to an OLAP data source

function initializePivotTable(strDataMember) {// This function calls the InitializePivotTableXML() Web// methodvar iCallID = service.svcOLAP.callService(onInitializePivotTableResult,'InitializePivotTableXML',strDataMember);}function onInitializePivotTableResult(result) {// This function handles the InitializePivotTableXML()// Web method resulttext = result.value; // result string// Evaluate return resultif (!result.error) {// Assign the XML to the PivotList XMLData valuefrm1.PivotTable1.XMLData = text;}else {alert("Unhandled error - " + result.errorDetail.code +" " + result.errorDetail.string);}}

Figure 9 generate xmldata for a valid tTable Control

<WebMethod()> Public Function InitializePivotTableXML(ByVal _strDataMember As String) As StringDim m_XML As StringDim strOLAPConn As String = _ConfigurationSettings.AppSettings("OLAPConnectionString")TryDim objPT As PivotTableClass = New PivotTableClassobjPT.ConnectionString = strOLAPConnobjPT.DataMember = strDataMemberm_XML = objPT.XMLDataobjPT = NothingCatch err As Exceptionm_XML = "<err>" & err.Source & " - " & err.Message & _"</err>"FinallyEnd TryReturn (m_XML)End Function

Figure 10 loadcustompivottablereport web method

<WebMethod()> Public Function LoadCustomPivotTableReport(ByVal _strCity1 As String, ByVal strCity2 As String) As StringDim m_XML As StringDim strOLAPConn As String = _ConfigurationSettings.AppSettings("OLAPConnectionString")Dim objPT As PivotTableClass = New PivotTableClassDim objPTView As PivotViewDim fldCity, fldName, fldProdFamily As PivotFieldDim fSetCustomers, fSetProduct As PivotFieldSetTryobjPT.ConnectionString = strOLAPConnobjPT.DataMember = "Sales"objPT.AllowFiltering = FalseobjPTView = objPT.ActiveViewobjPTView.TitleBar.Caption = "City Comparison of DrinkSales"' Define the column elementsobjPTView.ColumnAxis.InsertFieldSet(objPTView.FieldSets("Time"))objPTView.ColumnAxis.FieldSets("Time").Fields("Year").Expanded = True' Define the row elementsfSetCustomers = objPTView.FieldSets("Customers")objPTView.RowAxis.InsertFieldSet(fSetCustomers)fSetCustomers.Fields("Country").IsIncluded = FalsefSetCustomers.Fields("State Province").IsIncluded = FalsefSetCustomers.Fields("Name").IsIncluded = False' Define the members of the row elementsfldCity = fSetCustomers.Fields("City")fldCity.IncludedMembers = New Object() {strCity1, strCity2}' Exclude all other field row members in the fieldsetfSetProduct = objPTView.FieldSets("Product")objPTView.RowAxis.InsertFieldSet(fSetProduct)fSetProduct.Fields("Product Department").IsIncluded = FalsefSetProduct.Fields("Product Category").IsIncluded = FalsefSetProduct.Fields("Product Subcategory").IsIncluded =FalsefSetProduct.Fields("Brand Name").IsIncluded = FalsefSetProduct.Fields("Product Name").IsIncluded = FalsefldProdFamily = fSetProduct.Fields("Product Family")fldProdFamily.IncludedMembers = "Drink"' Define the measuresobjPTView.DataAxis.InsertTotal(objPTView.Totals("Store Sales"))objPTView.DataAxis.Totals("Store Sales").NumberFormat = _"Currency"' Return the XML data to the client side scriptm_XML = objPT.XMLDataobjPT = NothingCatch err As Exceptionm_XML = "<err>" & err.Source & " - " & err.Message & "</err>"FinallyEnd TryReturn (m_XML)End Function

Figure 11 load the xmldata for a custom report

function LoadSavedReport() {// Purpose:  Call Web Service method to load the saved// reportvar iCallID = service.svcOLAP.callService(onLoadSavedReportResult,'LoadSavedReport', 'OLAPReport1.xml');}function onLoadSavedReportResult(result) {// Purpose: This function handles the// wsOLAP.onLoadSavedReportResult() Web Service resultvar text = result.value; // result string// Evaluate return resultif (!result.error) {// Assign the XML to the PivotList XMLData valuefrm1.PivotTable1.XMLData = text;}}

Figure 12 JavaScript and VBScript event handler

<script language="javascript" event="Query" for="PivotTable1">{var sLog = document.Form1.Text1.value + "";document.Form1.Text1.value = "Query Event Fired. " + sLog;}</script><script language="vbscript">Sub PivotTable1_CommandExecute(Command, Succeeded)Dim ptConstantsSet ptConstants = document.Form1.PivotTable1.Constants' Check to see if the PivotTable list has been' refreshed.If Command = ptConstants.plCommandRefresh Then' Write the current data and time to the text box.document.Form1.Text1.value = vbCrLf & _"PivotTable Last Refreshed on " & Date & " at " _& Time & vbCrLf & document.Form1.Text1.valueEnd IfEnd Sub</script>

Figure 13 creating custom groups

<WebMethod()> Public Function ApplyCustomGrouping(ByVal _strReportXMLData As String) As StringDim m_xml As StringDim objPT As PivotTableClass = New PivotTableClassDim objPTView As PivotViewDim fsTime As PivotFieldSetDim fsHalfYear As PivotFieldTryobjPT.XMLData = strReportXMLDataobjPTView = objPT.ActiveView' Set a variable to the Time field set.fsTime = objPTView.FieldSets("Time")' Add a custom group field named "Group1" to the Time field' set.fsHalfYear = fsTime.AddCustomGroupField("CustomGroup1", _"CustomGroup1", "Quarter")' Add a custom field set member. This member includes all' "Q1" and "Q2" members under 1997.fsHalfYear.AddCustomGroupMember _(fsTime.Member.ChildMembers("1997").Name, _New Object() {"Q1", "Q2"}, "1stHalf")' Add another custom fieldset member to include all "Q3"' and "Q4" members under 1997.fsHalfYear.AddCustomGroupMember _(fsTime.Member.ChildMembers("1997").Name, _New Object() {"Q3", "Q4"}, "2ndHalf")' Collapse the fieldset at the custom member levelfsHalfYear.Expanded = Falsem_xml = objPT.XMLDataobjPT = NothingCatch err As Exceptionm_xml = "<err>" & err.Source & " - " & err.Message & _"</err>"FinallyEnd TryReturn (m_xml)End Function