Retrieving existing property values for use in cookies...

[rheilman]

The code below is from a button that I've been using to investigate how to get the value back from a specific property (IncludedMembers) of a PivotTable object from the Microsoft Office Web Components.

<input type="button"
value="In Search of..."
name="InSearchOf"
onClick="debugger;
var pt=document.getElementById('PivotTable1');
var pv=pt.ActiveView;
var pa=pv.FilterAxis;
var fstcns=pa.FieldSets('[TCNs]');
var fldcontract=fstcns.Fields('[TCNs].[Cntrct]');
var aincl = new Array(1);
aincl[0] = 'A00115-a';
alert(aincl[0]);
alert(aincl.length);
fldcontract.IncludedMembers = aincl;
alert(fstcns.AllIncludeExclude);
alert(fstcns.FilterCaption);
return true">

The variables break down (mostly for my sanity and readability) the object...

pt.ActiveView.FilterAxis.FieldSets('[TCNs]').Fields('[TCNs].[Cntrct]')

In order to programmatically filter the pivot table on the page, you must supply the filtering value ('A00115-a') to the IncludedMembers property of the field (fldcontract). In addition, you have to set the AllIncludeExclude property of the fieldset (fstcns) to 2 (meaning ignore all records whose values were not listed in the IncludedMembers or ExcludedMembers properties). I have been able to make this happen programmatically.

I have also interactively, using features of the PivotTable object, changed the contract value to the one I desire, and have then looked at the state of things in the debugger.

The problem is that I cannot retrieve the state of the IncludedMembers property from the object. When I try to make reference to it, the javascript error says that it is undefined.

By its definition in the Microsoft Office Web Components documentation, it states that it is a variant or variant array of pivot members. A post from another forum suggested that the variants are VBScript values and must be converted from the variants back to an array before they can be read. Using this suggestion I tried...

var aretrieve = new Array(1)
aretrieve = VBArray(fldcontract.IncludedMembers).toarray;

The VBArray line yields "Expecting a VBArray" when you don't specify a field value by which to filter. When you do specify a field value, that statement runs fine and produces the appropriate filtering results on the page. However, when I try to alert the value of aretrieve...

alert(aretrieve[0]);
alert(aretrieve.length);

...I get "0 is null or is not an object" or "length is null or is not an object"

I want to retrieve the value of the IncludedMembers property so that I can place the value in a cookie that will cause another associated page to be filtered as the current one.

I'm new to javascript. Are there properties that you are aware of that might aid me in determining the type of the IncludedMembers property values?

Thanks!
Ray <><

"I was going to change my shirt, but I changed my mind instead." - Winnie the Pooh

 

[tsuji]

[tt]>var aretrieve = new Array(1)
>aretrieve = VBArray(fldcontract.IncludedMembers).toarray;[/tt]
The mechanism is like this.
[tt]
var x = new VBArray(fldcontract.IncludedMembers)
aretrieve=x.toArray();
[/tt]
(watch also upper/lower case.)

 

[rheilman]

tsuji,

Thanks for the clarification on the syntax of VBArray. I'm sure that my capitalization is still going to bite me.

I tried your suggestion and find that the resulting value in the aretrieve array is "undefined."

I guess I'm still in search of some documentation or function that will allow me to view the value of the .IncludedMembers property so that I may pass it along to another spreadsheet for filtering.

Thanks again!
Ray <><

"I was going to change my shirt, but I changed my mind instead." - Winnie the Pooh

 

[tsuji]

Upon spending some more time in looking at your script, I think you just have the reverse problem. This is how you do within the onclick handler (you chose to all put inline).
[tt]
> var aincl = new Array(1);
> aincl[0] = 'A00115-a';
> alert(aincl[0]);
> alert(aincl.length);
> fldcontract.IncludedMembers = aincl;
[/tt]
Do this instead.
[tt]
var x=new ActiveXObject("scripting.dictionary");
x.add(0,"A00115-a");
var aincl=x.items();
x=null;
alert(aincl.toArray()[0]);
alert(aincl.toArray().length);
fldcontract.IncludedMembers=aincl;
[/tt]

 

[rheilman]

tsuji,

I thanked you on the other post, but I will here, as well. Below is the code that worked.

<input type = "button"
value = "In Search of..."
name = "InSearchOf"
onClick = "var pt = document.getElementById('PivotTable');
var pv = pt.ActiveView;
var pa = pv.FilterAxis;
var fstcns = pa.FieldSets('[TCNs]');
var fldcontract = fstcns.Fields('[TCNs].[Gl Contract]');
if (fldcontract.IncludedMembers != undefined)
{
var x = new VBArray(fldcontract.IncludedMembers);
alert(x.dimensions());
alert(x.getItem(0).Value);
}
alert(fstcns.AllIncludeExclude);
alert(fstcns.FilterCaption);
return true">

The .dimensions property tells how many dimensions are in the VBArray.

The .getItem property returns the object representing the first (in my case) item in the VBArray object.

The .Value property of that object gives me the information which I seek.

Thanks!
Ray <><

"I was going to change my shirt, but I changed my mind instead." - Winnie the Pooh

 

[tsuji]

Thanks rheilman Ray for the feedback. I have nothing really material to add. I just want to highlight some little facets of the thing.

In your original handler, it suggested to me that you want to construct your includedmemebers property (fldcontract.IncludedMembers = aincl, in that case, that's what I meant in my 2nd post that you've a reverse problem, namely, construct a vbarray from the jscript and feed it to the said property. That's how the problem could be addressed using a quick passthrough of dictionary object.

Otherwise you working version actually address the retrieval & viewing question of the vbarray setting in the object. That's the difference and you have made use some property pertinent to vbarray. There are some other direct mimic of vbs nomination like ubound() etc. In the viewing problem, once applying .toArray() to it, you can then use the common jscript array's methods to manipulate it further.

That's about the idea behind the whole exercise as I see it.