Pivot Table

Jayakrishna · Jul 29, 2008

Using FrontPage You can create an OWC Pivot Table. The the tags are just XML.
My question is what is the tag for Filters.

MY code

<object classid="clsid:0002E55A-0000-0000-C000-000000000046"
id="PivotTable1" VIEWASTEXT>
<param name="XMLData" value="<xml
xmlns:x="urn:schemas-microsoft-com

ffice:excel">
<x

ivotTable>
<x:OWCVersion>11.0.0.6555 </x:OWCVersion>
<x

isplayScreenTips/>
<x:CubeProvider>msolap.2</x:CubeProvider>
<x

isplayFieldList/>
<x:FieldListTop>226</x:FieldListTop>
<x:FieldListLeft>682</x:FieldListLeft>
<x:FieldListBottom>597</x:FieldListBottom>
<x:FieldListRight>882</x:FieldListRight>
<x:CacheDetails/>
<x:ConnectionString>Provider=MSOLAP.3;Cache
Authentication=False;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog="YourAnalysisDatabase";Data
Source="YOURSERVER";Impersonation Level=Impersonate;Mode=ReadWrite;Protection
Level=Pkt Privacy;Auto Synch Period=20000;Default Isolation Mode=0;Default
MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty
Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell Value=0;SQL
Compatibility=0;Compression Level=0;Real Time Olap=False;Packet
Size=4096</x:ConnectionString>
<x

ataMember>"YourCube"</x

ataMember>
<x:Name>Microsoft Office PivotTable 11.0</x:Name>
<x

ivotField>
<x:Name>"Field"</x:Name>
<x:SourceName>[Dim ].[Field].[Field]</x:SourceName>
<x:Orientation>Column</x:Orientation>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
</x

ivotField>
<x

ivotField>
<x:Name>Field</x:Name>
<x:SourceName>[Dim ].[Field].[Field]</x:SourceName>
<x:Orientation>Row</x:Orientation>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
</x

ivotField>
<x

ivotField>
<x:Name>Per Year</x:Name>
<x:SourceName>[Dim Calendar].[Per Year].[Per Year]</x:SourceName>
<x:FilterCaption>Dim Calendar.Per Year</x:FilterCaption>
<x:CompareOrderedMembersBy>Name</x:CompareOrderedMembersBy>
</x

ivotField>
<x

ivotField>
<x:Name>Per Month</x:Name>
<x:SourceName>[Dim Calendar].[Per Month].[Per Month]</x:SourceName>
<x:FilterCaption>Dim Calendar.Per Month</x:FilterCaption>
<x:CompareOrderedMembersBy>Name</x:CompareOrderedMembersBy>
</x

ivotField>
<x

ivotField>
<x:Name>Per Quarter</x:Name>
<x:SourceName>[Dim Calendar].[Per Quarter].[Per Quarter]</x:SourceName>
<x:FilterCaption>Dim Calendar.Per Quarter</x:FilterCaption>
<x:CompareOrderedMembersBy>Name</x:CompareOrderedMembersBy>
</x

ivotField>
<x

ivotField>
<x:Name>Per Date</x:Name>
<x:SourceName>[Dim Calendar].[Per Date].[Per Date]</x:SourceName>
<x:FilterCaption>Dim Calendar.Per Date</x:FilterCaption>
<x:CompareOrderedMembersBy>Name</x:CompareOrderedMembersBy>
</x

ivotField>
<x

ivotField>
<x:Name>"Measure Data"</x:Name>
<x:SourceName>[Measures].[DATA]</x:SourceName>
<x:SourceHierarchy>[Measures].[DATA]</x:SourceHierarchy>
</x

ivotField>
<x

ivotField>
<x:Name>Data</x:Name>
<x:Orientation>Column</x:Orientation>
<x

osition>-1</x

osition>
<x

ataField/>
</x

ivotField>
<x

ivotField>
<x:Name>dATA</x:Name>
<x:TotalNumber>0</x:TotalNumber>
<x:Orientation>Data</x:Orientation>
<x

osition>1</x

osition>
<x

arentField>[Measures].[DATA]</x

arentField>
</x

ivotField>
<x

ivotData>
<x:Top>0.0</x:Top>
<x:TopOffset>0</x:TopOffset>
<x:Left>0.0</x:Left>
<x:LeftOffset>0</x:LeftOffset>
</x

ivotData>
<x

ivotView>
<x:IsNotFiltered/>
</x

ivotView>
</x

ivotTable>
</xml>">

I am trying to use the time periods as filters by using row I am able to get
row data in their, By usin column I am able to drop column data, so what is
teh tag for Filter?

I have Bought the black book for owc it has only snippets, I thought perhaps
like the microsoft press books there would be start to finish of a project to
get a grasp on how to programmatically create Pivot tables in the web. But it
instead concentrates on the Windows Form, The example for the Web is also a
Windows example.

Alvin Bruney [ASP.NET MVP] · Aug 2, 2008

Perhaps the easiest way to see the filter setting is to use Excel to start a
macro, load the pivot table with data, manipulate the filters, stop the
macro and view the generated VBA macro code. You can also save the pivot
table data as xml and then view the contents.

--

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Download OWC Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $15.00
Need a free copy of VSTS 2008 w/ MSDN Premium?
http://msmvps.com/blogs/alvin/Default.aspx
-------------------------------------------------------

Jayakrishna said:
Using FrontPage You can create an OWC Pivot Table. The the tags are just
XML.
My question is what is the tag for Filters.

MY code

<object classid="clsid:0002E55A-0000-0000-C000-000000000046"
id="PivotTable1" VIEWASTEXT>
<param name="XMLData" value="<xml
xmlns:x="urn:schemas-microsoft-comffice:excel">
<xivotTable>
<x:OWCVersion>11.0.0.6555 </x:OWCVersion>
<xisplayScreenTips/>
<x:CubeProvider>msolap.2</x:CubeProvider>
<xisplayFieldList/>
<x:FieldListTop>226</x:FieldListTop>
<x:FieldListLeft>682</x:FieldListLeft>
<x:FieldListBottom>597</x:FieldListBottom>
<x:FieldListRight>882</x:FieldListRight>
<x:CacheDetails/>
<x:ConnectionString>Provider=MSOLAP.3;Cache
Authentication=False;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog="YourAnalysisDatabase";Data
Source="YOURSERVER";Impersonation
Level=Impersonate;Mode=ReadWrite;Protection
Level=Pkt Privacy;Auto Synch Period=20000;Default Isolation Mode=0;Default
MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty
Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell
Value=0;SQL
Compatibility=0;Compression Level=0;Real Time Olap=False;Packet
Size=4096</x:ConnectionString>
<xataMember>"YourCube"</xataMember>
<x:Name>Microsoft Office PivotTable 11.0</x:Name>
<xivotField>
<x:Name>"Field"</x:Name>
<x:SourceName>[Dim ].[Field].[Field]</x:SourceName>
<x:Orientation>Column</x:Orientation>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
</xivotField>
<xivotField>
<x:Name>Field</x:Name>
<x:SourceName>[Dim ].[Field].[Field]</x:SourceName>
<x:Orientation>Row</x:Orientation>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
</xivotField>
<xivotField>
<x:Name>Per Year</x:Name>
<x:SourceName>[Dim Calendar].[Per Year].[Per Year]</x:SourceName>
<x:FilterCaption>Dim Calendar.Per Year</x:FilterCaption>
<x:CompareOrderedMembersBy>Name</x:CompareOrderedMembersBy>
</xivotField>
<xivotField>
<x:Name>Per Month</x:Name>
<x:SourceName>[Dim Calendar].[Per Month].[Per Month]</x:SourceName>
<x:FilterCaption>Dim Calendar.Per Month</x:FilterCaption>
<x:CompareOrderedMembersBy>Name</x:CompareOrderedMembersBy>
</xivotField>
<xivotField>
<x:Name>Per Quarter</x:Name>
<x:SourceName>[Dim Calendar].[Per Quarter].[Per Quarter]</x:SourceName>
<x:FilterCaption>Dim Calendar.Per Quarter</x:FilterCaption>
<x:CompareOrderedMembersBy>Name</x:CompareOrderedMembersBy>
</xivotField>
<xivotField>
<x:Name>Per Date</x:Name>
<x:SourceName>[Dim Calendar].[Per Date].[Per Date]</x:SourceName>
<x:FilterCaption>Dim Calendar.Per Date</x:FilterCaption>
<x:CompareOrderedMembersBy>Name</x:CompareOrderedMembersBy>
</xivotField>
<xivotField>
<x:Name>"Measure Data"</x:Name>
<x:SourceName>[Measures].[DATA]</x:SourceName>
<x:SourceHierarchy>[Measures].[DATA]</x:SourceHierarchy>
</xivotField>
<xivotField>
<x:Name>Data</x:Name>
<x:Orientation>Column</x:Orientation>
<xosition>-1</xosition>
<xataField/>
</xivotField>
<xivotField>
<x:Name>dATA</x:Name>
<x:TotalNumber>0</x:TotalNumber>
<x:Orientation>Data</x:Orientation>
<xosition>1</xosition>
<xarentField>[Measures].[DATA]</xarentField>
</xivotField>
<xivotData>
<x:Top>0.0</x:Top>
<x:TopOffset>0</x:TopOffset>
<x:Left>0.0</x:Left>
<x:LeftOffset>0</x:LeftOffset>
</xivotData>
<xivotView>
<x:IsNotFiltered/>
</xivotView>
</xivotTable>
</xml>">

I am trying to use the time periods as filters by using row I am able to
get
row data in their, By usin column I am able to drop column data, so what
is
teh tag for Filter?

I have Bought the black book for owc it has only snippets, I thought
perhaps
like the microsoft press books there would be start to finish of a project
to
get a grasp on how to programmatically create Pivot tables in the web. But
it
instead concentrates on the Windows Form, The example for the Web is also
a
Windows example.

Pivot Table Export to HTML dosesn't work	1	Mar 4, 2006
PivotTables with non-cube ADO Tables (OWC11)	1	Jan 9, 2006
XMLData and ChartSpace	2	Aug 13, 2003

Pivot Table

Jayakrishna

Alvin Bruney [ASP.NET MVP]

Ask a Question

Similar Threads