How to get memberproperty value programmatically

[VJ]

Hi,

I have a Localcube (*.cub) with a couple of dimensions. One of the dimension called "Stock" has a "member property" - "StockName" defined.

Using OWC XP pivot table user interface I can view or hide the memberproperty value displayed in tool tips and also on the report. But I couldn’t find any property or function that can be used to extract the memberproperty value programmatically.

I tried using the "PivotTable1.ActiveView.FieldSets("Stock").Member.ChildMembers(0).Field.MemberProperties(0)" but this does not expose any methods or properties to access the property value

Am I missing any other object or property to get the memberproperty value

Thanks
Vija

 

[Wei-Dong XU [MSFT]]

Hi Vijay,

From pivottable design, we can consider this issue from two angles.
* Consider PivotTable.ActiveView as accessing the "Schema" for the data and a mechanism to build a "query" for results
* Consider PivotTable.ActiveData as accessing the Results of the "query" built by PivotTable.ActiveView

When you access the PivotMemberProperty off the PivotField, you are accessing the PropertyName and setting information about that property such
as where to display it, what caption to show for the "heading", etc.

To get the "value" of a specific PivotMemberProperty, you need access the actual PivotRowMember or PivotColumnMember from ActiveData to see
exactly which property value exists for that member. The line of code below retrieves the value for the member property associated with the
PivotMember.
PivotTable.ActiveData.RowAxis.Member.ChildMembers(x).ChildMembers(x).MemberProperties(z).Value

Please feel free to let me know if you have any further questions.

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[VJ]

Hi Wei-Dong, thanks for your reply. I am still not able to get the member property value. I tried to use Pivottable1.activedata as suggested by you. But was unable to find the "value" property from PivotmemberProperties

pivottable1.ActiveData.FilterAxis.FieldSets("Stock").Member.ChildMembers(1).Field.MemberProperties(0).Nam
This displays the name of the memberproperty. I could not find the "value" property.

PivotMemberProperties sems to expose only five properties - Caption; DisplayIn; IsIncluded; Name; NumberForma

Is there any hidden; undocumented method or property that I can use

Vija

Environment
Windows 2000;VB6.0; Office XP; LocalCube

 

[Wei-Dong XU [MSFT]]

Hi Vijay,

I have researched more for you on this issue. Let's use the foodmart of analysis service for one sample.

I list my steps on how to retrieve the member properties of the "Store Name" dimension of Store from the Budget cub.
1) insert one Pivottable component in Form1
2) connect to Foodmart
3) set the cub as Budget
4) drag "Store Country", "Store state", "Store City", "Store Name" four dimension into the row axis of pivottable
5) drag and drop one button in one form
6) in the button click event, use the code below to retrieve the member properties caption
Dim x As PivotRowMember

With pt.ActiveData.RowAxis
Set x = .Member.ChildMembers(0).ChildMembers(0).ChildMembers(0).ChildMembers(0)
Debug.Print x.MemberProperties(0).Value
End With

Please test my method. If these code doesn't work in your side, I will send my project to you.

Look forward to your reply!

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[VJ]

Hello Wei-Dong

I finally got it working. Since I was not including them in the pivot report I always ended up in having "Subscript out of range" error. So when I added each memberproperty by using either the "IsIncluded" or "DisplayIn" I was able to fetch values

I added this line right before the code you provided-
PT.ActiveView.RowAxis.FieldSets(0).Fields(3).MemberProperties(0).IsIncluded = True

But there seems to be limitation, as you can not get the memberproperties value when the "Store" dimension is on the filter axis

Thanks
Vijay

 

[Wei-Dong XU [MSFT]]

Hi Vijay,

"But there seems to be limitation, as you can not get the memberproperties value when the "Store" dimension is on the filter axis."

When the dimension is placed in the filter axis. The Childmember of the fieldsets is the type PivotMember which contains no MemberProperties. At
this scenario, the dimension is used to perform the filter functionality.

If you think it is better to add the memberproperties to the filter axis, I'd recommend that you forward the recommendation to the Microsoft Wish
Program:
1. World Wide Web
a)In Internet Explorer 6, click Send Feedback on the Help menu and then click the link in the Product Suggestion section of the page that
appears.
b)In Windows XP, click Help and Support on the Start menu. Click Send your feedback to Microsoft, and then fill out the Product Suggestion page
that appears.
c)Visit the following Microsoft Web site:
http://www.microsoft.com/ms.htm
d)Click Microsoft.com Guide in the upper-right corner of the page and then click Contact Us . Click the link in the Product Suggestion section of
the page that appears.
e)Visit the following Microsoft Product Feedback Web site
http://register.microsoft.com/mswish/suggestion.asp
and then complete and submit the form.

2. E-mail - To send comments or suggestions via e-mail, use the following Microsoft Wish Program e-mail address, (e-mail address removed).

3. FAX - To send comments or suggestions via FAX, use the following Microsoft FAX number, (425) 936-7329.
NOTE : Address the FAX to the attention of the Microsoft Wish Program.

4. US Mail - To send comments or suggestions via US Mail, use the following Microsoft mailing address:
Microsoft Corporation
Attn. Microsoft Wish Program
One Microsoft Way
Redmond, WA 98052-6399

If you have any further question, don't hesitate to let me know.

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
-----------------
MORE INFORMATION
Each product suggestion is read by a member of our product feedback team, classified for easy access, and routed to the product or service team
to drive Microsoft product and/or service improvements. Because we receive an abundance of suggestions (over 69,000 suggestions a year!) we
can't guarantee that each request makes it into a final product or service. But we can tell you that each suggestion has been received and is
being reviewed by the team that is most capable of addressing it.

All product or service suggestions received become the sole property of Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.

 

[VJ]

Wei-Dong, thank you for your help and suggestions

Cheers
Vijay