PivotList Control - Setting the Members of a Row or Column Axis in

[Johnyc]

I developed a VB6 program that used the OWC9 PivotList Component and its
Filtermember property to save the state of the Pivotable so that it would
have the same members filtered on each axis when the user re-opened the
PivotTable.

I am trying to understand OWC11 and how to do the same thing without the
filtermember property which is discontinued. The VBA documentation lists the
Included and ExcludedMembers property which works for members that are unique
to that particular level. I have a Time dimension that has Quarters and
months for each of several years. As an example if I try to exclude all
except Quarter1 in 2005, my program sets Quarter1 of the first year in the
dimension which is the year 2000 and includes all of 2005. The kb article
q302101 tells me how to retreive filtered members but not how to set them.

I have not found a way to set the includedmembers property to a unique name
that includes the levels of the dimension. Is there a way?

 

[Wei-Dong XU [MSFT]]

Hi,

For your scenario, we can set the PivotField.ExcludedMembers and
PivotField.IncludedMembers property to exclude or include the fields. Then
set the PivotFieldSet.AllIncludeExclude property to apply the
include/exclude rule. The SDK article "AllIncludeExclude Property" provides
one sample for you. (the sdk file is located at: %programfiles%\Common
Files\Microsoft Shared\Web Components\11\1033\OWCVBA11.CHM)

Please feel free to let me know if you have any question. My pleasure to be
of any assistance.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Johnyc]

Wei-Dong,

Thanks for your reply but I have already tried the example you site, it
doesn't work for a member that is not unique to a level. As in the example
code, San Diego is unique to CA so by excluding the State CA but including
the city San Diego you get only San Diego. But in my scenario, Quarter 1 is
not unique to the year 2005 it is also contained in years 2000 to 2004. So
when I exclude years and include Quarter 1, I get Quarter 1 of 2000 the first
level with Quarter 1 in it. I need a way to specify both the year and the
quarter at the same time. That is why I was asking if there was a way to use
the unique name property to set the value i.e. [2005].[Quarter 1]. I have
tried to do this but have not been successful.
Do you have further advise?

John

 

[Wei-Dong XU [MSFT]]

Hi John,

For this scenario, when we specify the included member, we still need to
exclude other members. Please see my sample code:
'--------------------------------------------------------
Dim pview As PivotView
Dim pfield As PivotField

Set pview = pt.ActiveView
pview.FieldSets("Time").Fields("Year").IncludedMembers = Array("1997")
With pview.FieldSets("Time").Fields("Quarter")
.IncludedMembers = Array("Q1")
.ExcludedMembers = Array("Q2", "Q3", "Q4") 'excluded line
End With

pview.FieldSets("Time").AllIncludeExclude = plAllExclude
'--------------------------------------------------------

At my sample, the time fieldset has the fields year, quarter and month. We
speicfy 1997 as the include member in field Year and include Q1. If we
don't write "excluded line", all quarters will be displayed at pivottable.
Then we specify these excluded members; only [1997].[Q1] will be showed.

Please feel free to let me know if you have any question. I am more than
happy to be of any assistance.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Johnyc]

Wei-Dong,

Thank you for replying.

I modified my program to both include members I want to include and exclude
members I wanted excluded from the Quarter level in my Time dimension,
setting the AllIncludeExclude property as you outlined in your example code.
But I am still only getting the Included Quarter for the year 2000 and all of
the year 2004, not the result I want. I went further and tried to include
year 2004 and specifically exclude 2000,2001,2002 and 2003 with the same
result. I then tried your example code against the Food Mart 2000 cube, and
it worked as you specified. I think that the reason it worked is that there
is no data in year 1998 in the FoodMart cube. I believe that if you use a
cube that has data in more than one year, you will find the problem I am
having.

Please let me know if your cube has more than one year of data and your code
worked - then I'm doing something wrong. Otherwise, please let me know if
there is another solution.

Thanks,

Johnyc

 

[Johnyc]

Wei-Dong,

I did'nt mention in my previous post that to duplicate what I am
experiencing, you need to not only use a cube with more than one year of data
with Quarters but you would also need to choose a year that is not the
earliest year in the dataset. If the FoodMaart 2000 cube contained data in
1998 then if you could display the data in the first quarter of 1998 wihout
displaying the first quarter of 1997 your solution should work for me.

Thanks,
Johnyc

Wei-Dong,

Thank you for replying.

I modified my program to both include members I want to include and exclude
members I wanted excluded from the Quarter level in my Time dimension,
setting the AllIncludeExclude property as you outlined in your example code.
But I am still only getting the Included Quarter for the year 2000 and all of
the year 2004, not the result I want. I went further and tried to include
year 2004 and specifically exclude 2000,2001,2002 and 2003 with the same
result. I then tried your example code against the Food Mart 2000 cube, and
it worked as you specified. I think that the reason it worked is that there
is no data in year 1998 in the FoodMart cube. I believe that if you use a
cube that has data in more than one year, you will find the problem I am
having.

Please let me know if your cube has more than one year of data and your code
worked - then I'm doing something wrong. Otherwise, please let me know if
there is another solution.

Thanks,

Johnyc

Hi John,

For this scenario, when we specify the included member, we still need to
exclude other members. Please see my sample code:
'--------------------------------------------------------
Dim pview As PivotView
Dim pfield As PivotField

Set pview = pt.ActiveView
pview.FieldSets("Time").Fields("Year").IncludedMembers = Array("1997")
With pview.FieldSets("Time").Fields("Quarter")
.IncludedMembers = Array("Q1")
.ExcludedMembers = Array("Q2", "Q3", "Q4") 'excluded line
End With

pview.FieldSets("Time").AllIncludeExclude = plAllExclude
'--------------------------------------------------------

At my sample, the time fieldset has the fields year, quarter and month. We
speicfy 1997 as the include member in field Year and include Q1. If we
don't write "excluded line", all quarters will be displayed at pivottable.
Then we specify these excluded members; only [1997].[Q1] will be showed.

Please feel free to let me know if you have any question. I am more than
happy to be of any assistance.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Wei-Dong XU [MSFT]]

Hi Johnyc,

For this scenario, we will need to specify each excluded member. Please see
my sample code below:
'-------------------------------------------------------------------
Dim pview As PivotView
Dim pfield As PivotField

Set pview = pt.ActiveView
pview.FieldSets("Time").Fields("Year").IncludedMembers = Array("1998")


With pview.FieldSets("Time").Fields("Quarter")
.ExcludedMembers = Array("[1997].[Q1]", "[1997].[Q2]",
"[1997].[Q3]", "[1997].[Q4]", "[1998].[Q2]", "[1998].[Q3]", "[1998].[Q4]")
End With
pview.FieldSets("Time").AllIncludeExclude = plAllExclude
'-------------------------------------------------------------------

From the code above, we will need to list the exclude column from the
fieldsets. The format is: [Year].[Quarter]. This way, PIvottable will know
which column should be removed from the control UI.

Please feel free to let me know if you have any question. My pleasure to be
of any assistance.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Yan-Hong Huang[MSFT]]

Hi Johnyc,

I was reviewing the issue thread. How is everything going? If there is any
question on this issue, please feel free to post here and we will follow up.

Thanks very much.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
Register to Access MSDN Managed Newsgroups!
-http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.as
p&SD=msdn

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Johnyc]

Hi Yna-Hong,

Thanks for your response.

I have not been able to solve my issue with the provided information. I
have used the example code provided by Wei-Dong modified for my data and am
not able to set the pivottable list to the members I need. Here is the
modified code for my situation.
-----------------------------------
Dim pview As PivotView

Set pview = PivotTable1.ActiveView
pview.FieldSets("Year_Qtr_Month").Fields("Year").IncludedMembers =
Array("2005")


With pview.FieldSets("Year_Qtr_Month").Fields("Quarter")
.ExcludedMembers = Array("[2000].[Quarter 1]", "[2000].[Quarter 2]",
"[2000].[Quarter 3]", "[2000].[Quarter 4]", _
"[2001].[Quarter 1]", "[2001].[Quarter 2]",
"[2001].[Quarter 3]", "[2001].[Quarter 4]", _
"[2002].[Quarter 1]", "[2002].[Quarter 2]",
"[2002].[Quarter 3]", "[2002].[Quarter 4]", _
"[2003].[Quarter 1]", "[2003].[Quarter 2]",
"[2003].[Quarter 3]", "[2003].[Quarter 4]", _
"[2004].[Quarter 1]", "[2004].[Quarter 2]",
"[2004].[Quarter 3]", "[2004].[Quarter 4]", _
"[2005].[Quarter 2]", "[2005].[Quarter 3]",
"[2005].[Quarter 4]")
End With
pview.FieldSets("Year_Qtr_Month").AllIncludeExclude = plAllExclude
--------------------------------------------------------------------

When I run this code, I get a PivotList that displays all of 2005 not just
Quarter 1 of 2005. It looks to me as if the IncludedMembers line of code is
correctly including year 2005 but the ExcludedMembers line is not working at
all, so it is not excluding anything and therefore all of 2005 is being
displayed. I don't think the PivotList is accepting the unique name - i.e.
[2000].[Quarter 1] . I can't test the exact code provided by Wei-Dong
against the Food Mart 2000 example cube because mine doesn't have data for
year 1998 (I'm assuming he is using that cube). Something in my
Year_Qtr_Month dimension may be what is causing my code not to work . Is
there a way to get the cube Wei-Dong is using so that I can test the exact
code and see if I may have something else wrong?

Thanks,
johnyc

 

[Wei-Dong XU [MSFT]]

Hi John,

Thanks for the reply! Currently I am researching this for you. For any
result, I will update here at the first time.

Enjoy a great weekend!

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Wei-Dong XU [MSFT]]

Hi John,

Based on my research, you can also only exclude the quarters at 2005.
Please see my sample code as below:
//-----------------------------------------------------------------------
Dim pview As PivotView

Set pview = PivotTable1.ActiveView
pview.FieldSets("Year_Qtr_Month").Fields("Year").IncludedMembers =
Array("2005")


With pview.FieldSets("Year_Qtr_Month").Fields("Quarter")
.ExcludedMembers = Array("[2005].[Quarter 2]", "[2005].[Quarter
3]", "[2005].[Quarter 4]")
End With
pview.FieldSets("Year_Qtr_Month").AllIncludeExclude = plAllExclude
//-----------------------------------------------------------------------

Furthermore, for testing my previous sample code, you could build one test
machine with one new installation of SQL Analysis service and then archive
the foodmart. Then copy the archived file into your server; remove the
existing foodmart and restore that archived file. This way, the sales
dimension will contains 1997 and 1998 data.

Please feel free to let me know if any question. My pleasure to be of any
assistance.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Johnyc]

Hi Wei-Dong,

I've finally got my answer. I had to set watch on the Pivot View to find
exactly the Unique Name of the level of the dimension I wanted to work with.
When I use the following code I can do what I wanted
-------------------------------------------------------------------------------------
Dim pview As PivotView

Set pview = PivotTable1.ActiveView
pview.FieldSets("Year_Qtr_Month").Fields("Year").IncludedMembers = "2005"

pview.FieldSets("Year_Qtr_Month").Fields("Quarter").ExcludedMembers =
Array("[Year_Qtr_Month].[All Year_Qtr_Month].[2005].[Quarter 1]")

pview.FieldSets("Year_Qtr_Month").AllIncludeExclude = plAllExclud

 

[Wei-Dong XU [MSFT]]

Hi John,

You are very welcome and thanks for sharing the resolution to the
community!

Enjoy a nice day!

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.