P
Pedro Mendoza
Hi,
While trying to implement a custom drillthrough, I had found an strange
behaviour in the way the PivotTable report the structure of the custom
groups level's members
Basically, if you query the structure of a custom group level's members
directly from a cell's corresponding row (or column) member is not the
same as querying it via its FieldSet
Using the attached code (OWC10's PivotTable against a localhost
FoodMart 2000 database), is possible to reproduce this strange
behaviour
The following steps should be follow:
1) Drop any measure in the data area
2) Drop any dimension (e.g. Promotions) in the row area
3) Create a custom group including some dimension members
4) Collapse the dimension so the only level displayed is the
just-created custom level
5) Double click on a data area cell corresponding to any of the members
in the just-created custom level
6) A popup will appear showing the UniqueName (computer generated) of
the selected member in the row area and the numbers of children members
this member has
Up to this point everything is okay, but now ...
7) Move the dimension to the page (filter) area
8) Filter the dimension excluding a member at the just-created custom
level
9) Repeat step 5) and the same popup as in 6) will appear but now the
numbers of children members reported will be 0
And that's the problem, is something wrong with the way I am accesing
the members (via their corresponding Field's FieldSet) or it is in fact
a bug ?
Many thanks in advance and sorry for the long post,
Pedro
--- start html code ---
<html>
<body>
<script language="jscript">
function VB2JS(arrayVB) {
return VBArray(arrayVB).toArray();
}
function getFullMember(member) {
var result = "", l;
// if total, upper member
if (member.IsTotal)
member = member.ParentMember;
// if group, union of lower members
if (member.CustomGroupType != 1) {
alert(member.UniqueName + " has " + member.ChildMembers.Count +
" children");
for (l = 0; l <= member.ChildMembers.Count - 1; l++) {
result += getFullMember(member.ChildMembers(l));
}
} else {
result = member.UniqueName + "|";
}
return result;
}
function getFieldSetFilter(fieldset) {
var j, k;
// abort if no filter
if (fieldset.AllIncludeExclude == 0)
return;
for (j = 0; j <= fieldset.Fields.Count - 1; j++) {
if (fieldset.Fields(j).IncludedMembers != null) {
includedMembers = VB2JS(fieldset.Fields(j).IncludedMembers);
for (k = 0; k <= includedMembers.length - 1; k++) {
getFullMember(includedMembers[k]);
}
}
if (fieldset.Fields(j).ExcludedMembers != null) {
excludedMembers = VB2JS(fieldset.Fields(j).ExcludedMembers);
for (k = 0; k <= excludedMembers.length - 1; k++) {
getFullMember(excludedMembers[k]);
}
}
}
}
</script>
<script language="jscript" for="PivotTable1" event="DblClick()">
table = window.document.getElementById("PivotTable1");
selection = table.selection;
if ((selection != null) && (table.SelectionType ==
"PivotAggregates")) {
cell = selection.Item(0).Cell;
// rows
row = cell.RowMember;
if (row.Field != null) {
getFullMember(row);
}
// pages
for (i = 0; i <= table.ActiveView.FilterAxis.FieldSets.Count - 1;
i++) {
fieldset = table.ActiveView.FilterAxis.FieldSets(i);
getFieldSetFilter(fieldset);
}
}
</script>
<br>
<form name="form1" action="generateSQL.asp" method="post">
<div align=center x
ublishsource="Excel">
<object id="PivotTable1"
classid="CLSID:0002E552-0000-0000-C000-000000000046">
<param name="XMLData" value="<!--[if gte mso 9]><xml
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:html="http://www.w3.org/TR/REC-html40">
<WorksheetOptions
xmlns="urn:schemas-microsoft-comffice:excel">
<DefaultColWidth>10</DefaultColWidth>
<Zoom>0</Zoom>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>3</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectContents>False</ProtectContents>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<PivotTable
xmlns="urn:schemas-microsoft-comffice:excel">
<PTSource>
<DataMember>Sales</DataMember>
<CacheIndex>1</CacheIndex>
<VersionLastRefresh>2</VersionLastRefresh>
<RefreshName>pedro.mendoza</RefreshName>
<RefreshDate>2005-04-26T19:11:56</RefreshDate>
<RefreshDateCopy>2005-04-26T19:11:56</RefreshDateCopy>
<NoSaveData/>
<CubeSource/>
<QuerySource>
<QueryType>OLEDB</QueryType>
<CommandText>Sales</CommandText>
<CommandType>Cube</CommandType>
<Maintain/>
<Connection>Provider=MSOLAP.2;Persist Security Info=True;Data
Source=localhost;Initial Catalog=FoodMart 2000;Client Cache
Size=25;Auto Synch Period=10000;Large Level
Threshold=10</Connection>
<RobustConnect>0</RobustConnect>
<VersionLastEdit>2</VersionLastEdit>
<VersionLastRefresh>2</VersionLastRefresh>
</QuerySource>
</PTSource>
<Name>Table</Name>
<DataMember>Sales</DataMember>
<ImmediateItemsOnDrop/>
<ShowPageMultipleItemLabel/>
<VisualTotals/>
<Location>$A$1:$E$6</Location>
<VersionLastUpdate>2</VersionLastUpdate>
<DefaultVersion>1</DefaultVersion>
</PivotTable>
</xml><![endif]-->">
</object>
</div>
<br>
</form>
</body>
</html>
--- end of html code ---
While trying to implement a custom drillthrough, I had found an strange
behaviour in the way the PivotTable report the structure of the custom
groups level's members
Basically, if you query the structure of a custom group level's members
directly from a cell's corresponding row (or column) member is not the
same as querying it via its FieldSet
Using the attached code (OWC10's PivotTable against a localhost
FoodMart 2000 database), is possible to reproduce this strange
behaviour
The following steps should be follow:
1) Drop any measure in the data area
2) Drop any dimension (e.g. Promotions) in the row area
3) Create a custom group including some dimension members
4) Collapse the dimension so the only level displayed is the
just-created custom level
5) Double click on a data area cell corresponding to any of the members
in the just-created custom level
6) A popup will appear showing the UniqueName (computer generated) of
the selected member in the row area and the numbers of children members
this member has
Up to this point everything is okay, but now ...
7) Move the dimension to the page (filter) area
8) Filter the dimension excluding a member at the just-created custom
level
9) Repeat step 5) and the same popup as in 6) will appear but now the
numbers of children members reported will be 0
And that's the problem, is something wrong with the way I am accesing
the members (via their corresponding Field's FieldSet) or it is in fact
a bug ?
Many thanks in advance and sorry for the long post,
Pedro
--- start html code ---
<html>
<body>
<script language="jscript">
function VB2JS(arrayVB) {
return VBArray(arrayVB).toArray();
}
function getFullMember(member) {
var result = "", l;
// if total, upper member
if (member.IsTotal)
member = member.ParentMember;
// if group, union of lower members
if (member.CustomGroupType != 1) {
alert(member.UniqueName + " has " + member.ChildMembers.Count +
" children");
for (l = 0; l <= member.ChildMembers.Count - 1; l++) {
result += getFullMember(member.ChildMembers(l));
}
} else {
result = member.UniqueName + "|";
}
return result;
}
function getFieldSetFilter(fieldset) {
var j, k;
// abort if no filter
if (fieldset.AllIncludeExclude == 0)
return;
for (j = 0; j <= fieldset.Fields.Count - 1; j++) {
if (fieldset.Fields(j).IncludedMembers != null) {
includedMembers = VB2JS(fieldset.Fields(j).IncludedMembers);
for (k = 0; k <= includedMembers.length - 1; k++) {
getFullMember(includedMembers[k]);
}
}
if (fieldset.Fields(j).ExcludedMembers != null) {
excludedMembers = VB2JS(fieldset.Fields(j).ExcludedMembers);
for (k = 0; k <= excludedMembers.length - 1; k++) {
getFullMember(excludedMembers[k]);
}
}
}
}
</script>
<script language="jscript" for="PivotTable1" event="DblClick()">
table = window.document.getElementById("PivotTable1");
selection = table.selection;
if ((selection != null) && (table.SelectionType ==
"PivotAggregates")) {
cell = selection.Item(0).Cell;
// rows
row = cell.RowMember;
if (row.Field != null) {
getFullMember(row);
}
// pages
for (i = 0; i <= table.ActiveView.FilterAxis.FieldSets.Count - 1;
i++) {
fieldset = table.ActiveView.FilterAxis.FieldSets(i);
getFieldSetFilter(fieldset);
}
}
</script>
<br>
<form name="form1" action="generateSQL.asp" method="post">
<div align=center x
ublishsource="Excel"><object id="PivotTable1"
classid="CLSID:0002E552-0000-0000-C000-000000000046">
<param name="XMLData" value="<!--[if gte mso 9]><xml
xmlns
="urn:schemas-microsoft-comfficeffice"xmlns:x="urn:schemas-microsoft-com
ffice:excel"xmlns:html="http://www.w3.org/TR/REC-html40">
<WorksheetOptions
xmlns="urn:schemas-microsoft-com
ffice:excel"><DefaultColWidth>10</DefaultColWidth>
<Zoom>0</Zoom>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>3</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectContents>False</ProtectContents>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<PivotTable
xmlns="urn:schemas-microsoft-com
ffice:excel"><PTSource>
<DataMember>Sales</DataMember>
<CacheIndex>1</CacheIndex>
<VersionLastRefresh>2</VersionLastRefresh>
<RefreshName>pedro.mendoza</RefreshName>
<RefreshDate>2005-04-26T19:11:56</RefreshDate>
<RefreshDateCopy>2005-04-26T19:11:56</RefreshDateCopy>
<NoSaveData/>
<CubeSource/>
<QuerySource>
<QueryType>OLEDB</QueryType>
<CommandText>Sales</CommandText>
<CommandType>Cube</CommandType>
<Maintain/>
<Connection>Provider=MSOLAP.2;Persist Security Info=True;Data
Source=localhost;Initial Catalog=FoodMart 2000;Client Cache
Size=25;Auto Synch Period=10000;Large Level
Threshold=10</Connection>
<RobustConnect>0</RobustConnect>
<VersionLastEdit>2</VersionLastEdit>
<VersionLastRefresh>2</VersionLastRefresh>
</QuerySource>
</PTSource>
<Name>Table</Name>
<DataMember>Sales</DataMember>
<ImmediateItemsOnDrop/>
<ShowPageMultipleItemLabel/>
<VisualTotals/>
<Location>$A$1:$E$6</Location>
<VersionLastUpdate>2</VersionLastUpdate>
<DefaultVersion>1</DefaultVersion>
</PivotTable>
</xml><![endif]-->">
</object>
</div>
<br>
</form>
</body>
</html>
--- end of html code ---