Filtering a Pivot Table on the client side

[Sebastian Crewe]

Greetings,

My question concerns filtering of data within an OWC Pivot Table control
(v11) used on a Windows Forms project (VS 2005). My data source is an
Analysis Services cube (AS 2005, SP1).

We have a lot of data here, going back 5 years. My objective is to be able
to filter what is displayed within the Pivot Table. An example layout is as
follows:

Calendar Year

Region | Store [Measures].[Store Sales]

In this example, Region and Store are set up as two levels within a hierarchy.

Stores have been opened and closed over the years. What I am hoping to
achieve is for the Pivot Table to display only rows for Stores with [Store
Sales] > 0. If all years are being shown, then it is reasonable for all
Stores to show as well. But if say only 2006 has been chosen via the little
filter dropdown arrow on the Time dimension in the column axis, then I would
like to exclude those stores for which [Store Sales] are 0 (inactive during
the year on display). If, later, we want to look at Profits, then a filter
which shows Profits <> 0 would be appropriate. For various reasons, both
Store Sales and Profits are calculated measures.

I'm guessing that it is better to do this on the client side rather than
adjusting the base MDX query. The user might look at any year, so it seems
best to have all the data returned by the server, and then to do the
filtering locally. But I can't find anyway to achieve this. The latest
edition of OWC doesn't have a plFilterFunctionInclude option. The
PivotField.IncludedMembers doesn't do the filtering on the data. Actually, I
can't decide if I should be hunting within the PivotView object or the
PivotData object.

Any leads or suggestions gratefully received.

Thanks and regards,

Sebastian Crewe

 

[Alvin Bruney [MVP]]

have a look at the pivotview object and examine its filteraxis property
which returns a pivotfilteraxis object.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin

 

[Sebastian Crewe]

Thank you Alvin for your reply. I blush to admit that I am asking this
question even after reading (and working) through that book which you
shamelessly plug. Very helpful in many areas, but I may have missed
something fundamental.

My understanding is that I can't use a measure in the filter axis. The
pivotfilteraxis object only allows the InsertFieldSet method, not an
InsertTotal method. Nor, of course, can one drag a measure to the filter
axis to the pivot table in interactive mode which would seem to confirm the
point.

But it is the measure [Store Sales] that I am trying to filter on the client
side, ie no rows where [Store Sales] = 0. Is there any example that you can
point to that will show me where I am going wrong?

Many thanks

have a look at the pivotview object and examine its filteraxis property
which returns a pivotfilteraxis object.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

Greetings,

My question concerns filtering of data within an OWC Pivot Table control
(v11) used on a Windows Forms project (VS 2005). My data source is an
Analysis Services cube (AS 2005, SP1).

We have a lot of data here, going back 5 years. My objective is to be
able
to filter what is displayed within the Pivot Table. An example layout is
as
follows:

Calendar Year

Region | Store [Measures].[Store Sales]

In this example, Region and Store are set up as two levels within a
hierarchy.

Stores have been opened and closed over the years. What I am hoping to
achieve is for the Pivot Table to display only rows for Stores with [Store
Sales] > 0. If all years are being shown, then it is reasonable for all
Stores to show as well. But if say only 2006 has been chosen via the
little
filter dropdown arrow on the Time dimension in the column axis, then I
would
like to exclude those stores for which [Store Sales] are 0 (inactive
during
the year on display). If, later, we want to look at Profits, then a
filter
which shows Profits <> 0 would be appropriate. For various reasons, both
Store Sales and Profits are calculated measures.

I'm guessing that it is better to do this on the client side rather than
adjusting the base MDX query. The user might look at any year, so it
seems
best to have all the data returned by the server, and then to do the
filtering locally. But I can't find anyway to achieve this. The latest
edition of OWC doesn't have a plFilterFunctionInclude option. The
PivotField.IncludedMembers doesn't do the filtering on the data.
Actually, I
can't decide if I should be hunting within the PivotView object or the
PivotData object.

Any leads or suggestions gratefully received.

Thanks and regards,

Sebastian Crewe

 

[Alvin Bruney [MVP]]

I need to give this some thought.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

Thank you Alvin for your reply. I blush to admit that I am asking this
question even after reading (and working) through that book which you
shamelessly plug. Very helpful in many areas, but I may have missed
something fundamental.

My understanding is that I can't use a measure in the filter axis. The
pivotfilteraxis object only allows the InsertFieldSet method, not an
InsertTotal method. Nor, of course, can one drag a measure to the filter
axis to the pivot table in interactive mode which would seem to confirm
the
point.

But it is the measure [Store Sales] that I am trying to filter on the
client
side, ie no rows where [Store Sales] = 0. Is there any example that you
can
point to that will show me where I am going wrong?

Many thanks

have a look at the pivotview object and examine its filteraxis property
which returns a pivotfilteraxis object.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

Greetings,

My question concerns filtering of data within an OWC Pivot Table
control
(v11) used on a Windows Forms project (VS 2005). My data source is an
Analysis Services cube (AS 2005, SP1).

We have a lot of data here, going back 5 years. My objective is to be
able
to filter what is displayed within the Pivot Table. An example layout
is
as
follows:

Calendar Year

Region | Store [Measures].[Store Sales]

In this example, Region and Store are set up as two levels within a
hierarchy.

Stores have been opened and closed over the years. What I am hoping to
achieve is for the Pivot Table to display only rows for Stores with
[Store
Sales] > 0. If all years are being shown, then it is reasonable for
all
Stores to show as well. But if say only 2006 has been chosen via the
little
filter dropdown arrow on the Time dimension in the column axis, then I
would
like to exclude those stores for which [Store Sales] are 0 (inactive
during
the year on display). If, later, we want to look at Profits, then a
filter
which shows Profits <> 0 would be appropriate. For various reasons,
both
Store Sales and Profits are calculated measures.

I'm guessing that it is better to do this on the client side rather
than
adjusting the base MDX query. The user might look at any year, so it
seems
best to have all the data returned by the server, and then to do the
filtering locally. But I can't find anyway to achieve this. The
latest
edition of OWC doesn't have a plFilterFunctionInclude option. The
PivotField.IncludedMembers doesn't do the filtering on the data.
Actually, I
can't decide if I should be hunting within the PivotView object or the
PivotData object.

Any leads or suggestions gratefully received.

Thanks and regards,

Sebastian Crewe

 

[Alvin Bruney [MVP]]

There's two ways to do this.

Use the commandtext property to write the appropriate MDX to filter out the
unwanted data. That will require mdx query knowledge however it isn't
particularly challenging. Roughly
SELECT TIME.Whatever ON COLUMNS, PRODUCT.Whatever ON ROWS
FROM ItemsTable WHERE Year > 0or,
you can get a reference to selected fieldset (or pivotaxismember object),
then iterate the child fields. During the iteration, examine the associated
value property of each field (childmembers object) and if it is < 1 set the
Isincluded property to false so that it doesn't show up.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

Thank you Alvin for your reply. I blush to admit that I am asking this
question even after reading (and working) through that book which you
shamelessly plug. Very helpful in many areas, but I may have missed
something fundamental.

My understanding is that I can't use a measure in the filter axis. The
pivotfilteraxis object only allows the InsertFieldSet method, not an
InsertTotal method. Nor, of course, can one drag a measure to the filter
axis to the pivot table in interactive mode which would seem to confirm
the
point.

But it is the measure [Store Sales] that I am trying to filter on the
client
side, ie no rows where [Store Sales] = 0. Is there any example that you
can
point to that will show me where I am going wrong?

Many thanks

have a look at the pivotview object and examine its filteraxis property
which returns a pivotfilteraxis object.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

Greetings,

My question concerns filtering of data within an OWC Pivot Table
control
(v11) used on a Windows Forms project (VS 2005). My data source is an
Analysis Services cube (AS 2005, SP1).

We have a lot of data here, going back 5 years. My objective is to be
able
to filter what is displayed within the Pivot Table. An example layout
is
as
follows:

Calendar Year

Region | Store [Measures].[Store Sales]

In this example, Region and Store are set up as two levels within a
hierarchy.

Stores have been opened and closed over the years. What I am hoping to
achieve is for the Pivot Table to display only rows for Stores with
[Store
Sales] > 0. If all years are being shown, then it is reasonable for
all
Stores to show as well. But if say only 2006 has been chosen via the
little
filter dropdown arrow on the Time dimension in the column axis, then I
would
like to exclude those stores for which [Store Sales] are 0 (inactive
during
the year on display). If, later, we want to look at Profits, then a
filter
which shows Profits <> 0 would be appropriate. For various reasons,
both
Store Sales and Profits are calculated measures.

I'm guessing that it is better to do this on the client side rather
than
adjusting the base MDX query. The user might look at any year, so it
seems
best to have all the data returned by the server, and then to do the
filtering locally. But I can't find anyway to achieve this. The
latest
edition of OWC doesn't have a plFilterFunctionInclude option. The
PivotField.IncludedMembers doesn't do the filtering on the data.
Actually, I
can't decide if I should be hunting within the PivotView object or the
PivotData object.

Any leads or suggestions gratefully received.

Thanks and regards,

Sebastian Crewe

 

[Sebastian Crewe]

I really appreciate your steer on this and wish I was bright enough to figure
it out. Despite a lot of trying, I still find the object model very
confusing and this is a case in point.

Yes I could do an MDX query, but was hoping not to since we have some
calculated measures that take a long time to run, hence the desire to filter
on the client side. Also, we have a hidden dimension, and this stops the
CommandText property from working.

So I have struggled for a couple of hours trying to get the syntax correct
for following your second suggestion. I'm guessing that the selected
fieldset you mention will be the row axis (Region/Store in my example). I've
tried with a PivotAxisMember, a PivotFieldSet and a PivotAxisMember, looking
at the QuickWatch for each. Nowhere can I see how to reach the [Store Sales]
value. At least the following doesn't fail, but I can't figure out where to
go next. I am coding this in VB.NET so am using full type declarations.

Dim ptView As Microsoft.Office.Interop.Owc11.PivotView
Dim pfsFilter As Microsoft.Office.Interop.Owc11.PivotFieldSet
Dim ptFilterField As Microsoft.Office.Interop.Owc11.PivotField

ptView = ptOLAP.ActiveView
pfsFilter = ptView.RowAxis.FieldSets("[Dim Territory].[Region - Store]")

For Each ptFilterField In pfsFilter.Fields
'?
Next

I am hoping that the For Each loop will be iterating the child fields as you
suggested. When I look at the properties of ptFilterField via QuickWatch or
the Immediate window, all I see are the members of the dimension, their names
etc. I've looked at all the objects that return a ChildMembers property but
haven't succeeded in getting the actual measure value (Store Sales) rather
than the name of a particular member in the dimension. Any and all
clarification gratefully received.

With many thanks for your continued assistance

Sebastian

There's two ways to do this.

Use the commandtext property to write the appropriate MDX to filter out the
unwanted data. That will require mdx query knowledge however it isn't
particularly challenging. Roughly
SELECT TIME.Whatever ON COLUMNS, PRODUCT.Whatever ON ROWS
FROM ItemsTable WHERE Year > 0,
or you can get a reference to selected fieldset (or pivotaxismember object),
then iterate the child fields. During the iteration, examine the associated
value property of each field (childmembers object) and if it is < 1 set the
Isincluded property to false so that it doesn't show up.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

 

[Alvin Bruney [MVP]]

If you have a small sample project that demonstrates the problem, I can open
a support ticket for you (or you can do so for yourself if you care). That
will get a MS support person on the phone with you.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

I really appreciate your steer on this and wish I was bright enough to
figure
it out. Despite a lot of trying, I still find the object model very
confusing and this is a case in point.

Yes I could do an MDX query, but was hoping not to since we have some
calculated measures that take a long time to run, hence the desire to
filter
on the client side. Also, we have a hidden dimension, and this stops the
CommandText property from working.

So I have struggled for a couple of hours trying to get the syntax correct
for following your second suggestion. I'm guessing that the selected
fieldset you mention will be the row axis (Region/Store in my example).
I've
tried with a PivotAxisMember, a PivotFieldSet and a PivotAxisMember,
looking
at the QuickWatch for each. Nowhere can I see how to reach the [Store
Sales]
value. At least the following doesn't fail, but I can't figure out where
to
go next. I am coding this in VB.NET so am using full type declarations.

Dim ptView As Microsoft.Office.Interop.Owc11.PivotView
Dim pfsFilter As Microsoft.Office.Interop.Owc11.PivotFieldSet
Dim ptFilterField As Microsoft.Office.Interop.Owc11.PivotField

ptView = ptOLAP.ActiveView
pfsFilter = ptView.RowAxis.FieldSets("[Dim Territory].[Region - Store]")

For Each ptFilterField In pfsFilter.Fields
'?
Next

I am hoping that the For Each loop will be iterating the child fields as
you
suggested. When I look at the properties of ptFilterField via QuickWatch
or
the Immediate window, all I see are the members of the dimension, their
names
etc. I've looked at all the objects that return a ChildMembers property
but
haven't succeeded in getting the actual measure value (Store Sales) rather
than the name of a particular member in the dimension. Any and all
clarification gratefully received.

With many thanks for your continued assistance

Sebastian

There's two ways to do this.

Use the commandtext property to write the appropriate MDX to filter out
the
unwanted data. That will require mdx query knowledge however it isn't
particularly challenging. Roughly
SELECT TIME.Whatever ON COLUMNS, PRODUCT.Whatever ON ROWS
FROM ItemsTable WHERE Year > 0,
or you can get a reference to selected fieldset (or pivotaxismember
object),
then iterate the child fields. During the iteration, examine the
associated
value property of each field (childmembers object) and if it is < 1 set
the
Isincluded property to false so that it doesn't show up.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

 

[Sebastian Crewe]

Well, I'm in the UK so I suspect that I won't be able to speak to anyone on a
case that you open - many thanks for the offer.

I'll try the developer's line here, though past experience tells me that
they don't know much about this control.

Thanks for trying. I'll keep battling away when I feel revitalised. Or
else find an alternative control that is easier to use.

With best regards

Sebastian

If you have a small sample project that demonstrates the problem, I can open
a support ticket for you (or you can do so for yourself if you care). That
will get a MS support person on the phone with you.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

I really appreciate your steer on this and wish I was bright enough to
figure
it out. Despite a lot of trying, I still find the object model very
confusing and this is a case in point.

Yes I could do an MDX query, but was hoping not to since we have some
calculated measures that take a long time to run, hence the desire to
filter
on the client side. Also, we have a hidden dimension, and this stops the
CommandText property from working.

So I have struggled for a couple of hours trying to get the syntax correct
for following your second suggestion. I'm guessing that the selected
fieldset you mention will be the row axis (Region/Store in my example).
I've
tried with a PivotAxisMember, a PivotFieldSet and a PivotAxisMember,
looking
at the QuickWatch for each. Nowhere can I see how to reach the [Store
Sales]
value. At least the following doesn't fail, but I can't figure out where
to
go next. I am coding this in VB.NET so am using full type declarations.

Dim ptView As Microsoft.Office.Interop.Owc11.PivotView
Dim pfsFilter As Microsoft.Office.Interop.Owc11.PivotFieldSet
Dim ptFilterField As Microsoft.Office.Interop.Owc11.PivotField

ptView = ptOLAP.ActiveView
pfsFilter = ptView.RowAxis.FieldSets("[Dim Territory].[Region - Store]")

For Each ptFilterField In pfsFilter.Fields
'?
Next

I am hoping that the For Each loop will be iterating the child fields as
you
suggested. When I look at the properties of ptFilterField via QuickWatch
or
the Immediate window, all I see are the members of the dimension, their
names
etc. I've looked at all the objects that return a ChildMembers property
but
haven't succeeded in getting the actual measure value (Store Sales) rather
than the name of a particular member in the dimension. Any and all
clarification gratefully received.

With many thanks for your continued assistance

Sebastian

There's two ways to do this.

Use the commandtext property to write the appropriate MDX to filter out
the
unwanted data. That will require mdx query knowledge however it isn't
particularly challenging. Roughly
SELECT TIME.Whatever ON COLUMNS, PRODUCT.Whatever ON ROWS
FROM ItemsTable WHERE Year > 0,
or you can get a reference to selected fieldset (or pivotaxismember
object),
then iterate the child fields. During the iteration, examine the
associated
value property of each field (childmembers object) and if it is < 1 set
the
Isincluded property to false so that it doesn't show up.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

 

[Alvin Bruney [MVP]]

Nope, I open the ticket and attach your name and contact info to the ticket
so Microsoft calls you to resolve the problem, or you can have them email
you.

In any case if you wish to proceed, support usually wants a sample that
demonstrates the problem and clear indication of what you are trying
accomplish and why it is not working. They take over from there.
--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

Well, I'm in the UK so I suspect that I won't be able to speak to anyone
on a
case that you open - many thanks for the offer.

I'll try the developer's line here, though past experience tells me that
they don't know much about this control.

Thanks for trying. I'll keep battling away when I feel revitalised. Or
else find an alternative control that is easier to use.

With best regards

Sebastian

If you have a small sample project that demonstrates the problem, I can
open
a support ticket for you (or you can do so for yourself if you care).
That
will get a MS support person on the phone with you.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

I really appreciate your steer on this and wish I was bright enough to
figure
it out. Despite a lot of trying, I still find the object model very
confusing and this is a case in point.

Yes I could do an MDX query, but was hoping not to since we have some
calculated measures that take a long time to run, hence the desire to
filter
on the client side. Also, we have a hidden dimension, and this stops
the
CommandText property from working.

So I have struggled for a couple of hours trying to get the syntax
correct
for following your second suggestion. I'm guessing that the selected
fieldset you mention will be the row axis (Region/Store in my example).
I've
tried with a PivotAxisMember, a PivotFieldSet and a PivotAxisMember,
looking
at the QuickWatch for each. Nowhere can I see how to reach the [Store
Sales]
value. At least the following doesn't fail, but I can't figure out
where
to
go next. I am coding this in VB.NET so am using full type
declarations.

Dim ptView As Microsoft.Office.Interop.Owc11.PivotView
Dim pfsFilter As Microsoft.Office.Interop.Owc11.PivotFieldSet
Dim ptFilterField As Microsoft.Office.Interop.Owc11.PivotField

ptView = ptOLAP.ActiveView
pfsFilter = ptView.RowAxis.FieldSets("[Dim Territory].[Region -
Store]")

For Each ptFilterField In pfsFilter.Fields
'?
Next

I am hoping that the For Each loop will be iterating the child fields
as
you
suggested. When I look at the properties of ptFilterField via
QuickWatch
or
the Immediate window, all I see are the members of the dimension, their
names
etc. I've looked at all the objects that return a ChildMembers
property
but
haven't succeeded in getting the actual measure value (Store Sales)
rather
than the name of a particular member in the dimension. Any and all
clarification gratefully received.

With many thanks for your continued assistance

Sebastian

:

There's two ways to do this.

Use the commandtext property to write the appropriate MDX to filter
out
the
unwanted data. That will require mdx query knowledge however it isn't
particularly challenging. Roughly
SELECT TIME.Whatever ON COLUMNS, PRODUCT.Whatever ON ROWS
FROM ItemsTable WHERE Year > 0,
or you can get a reference to selected fieldset (or pivotaxismember
object),
then iterate the child fields. During the iteration, examine the
associated
value property of each field (childmembers object) and if it is < 1
set
the
Isincluded property to false so that it doesn't show up.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

 

[Sebastian Crewe]

Well that would be wonderful. Many thanks. I've been diverted into another
job so it may take a couple of days to create a sample with less than the 8
million rows that I have, while still showing the problem. I guess MS will
need the database, the cube and a front-end with the OWC component wired up?

Best regards

Sebastian

Nope, I open the ticket and attach your name and contact info to the ticket
so Microsoft calls you to resolve the problem, or you can have them email
you.

In any case if you wish to proceed, support usually wants a sample that
demonstrates the problem and clear indication of what you are trying
accomplish and why it is not working. They take over from there.
--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

Well, I'm in the UK so I suspect that I won't be able to speak to anyone
on a
case that you open - many thanks for the offer.

I'll try the developer's line here, though past experience tells me that
they don't know much about this control.

Thanks for trying. I'll keep battling away when I feel revitalised. Or
else find an alternative control that is easier to use.

With best regards

Sebastian

If you have a small sample project that demonstrates the problem, I can
open
a support ticket for you (or you can do so for yourself if you care).
That
will get a MS support person on the phone with you.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------


message I really appreciate your steer on this and wish I was bright enough to
figure
it out. Despite a lot of trying, I still find the object model very
confusing and this is a case in point.

Yes I could do an MDX query, but was hoping not to since we have some
calculated measures that take a long time to run, hence the desire to
filter
on the client side. Also, we have a hidden dimension, and this stops
the
CommandText property from working.

So I have struggled for a couple of hours trying to get the syntax
correct
for following your second suggestion. I'm guessing that the selected
fieldset you mention will be the row axis (Region/Store in my example).
I've
tried with a PivotAxisMember, a PivotFieldSet and a PivotAxisMember,
looking
at the QuickWatch for each. Nowhere can I see how to reach the [Store
Sales]
value. At least the following doesn't fail, but I can't figure out
where
to
go next. I am coding this in VB.NET so am using full type
declarations.

Dim ptView As Microsoft.Office.Interop.Owc11.PivotView
Dim pfsFilter As Microsoft.Office.Interop.Owc11.PivotFieldSet
Dim ptFilterField As Microsoft.Office.Interop.Owc11.PivotField

ptView = ptOLAP.ActiveView
pfsFilter = ptView.RowAxis.FieldSets("[Dim Territory].[Region -
Store]")

For Each ptFilterField In pfsFilter.Fields
'?
Next

I am hoping that the For Each loop will be iterating the child fields
as
you
suggested. When I look at the properties of ptFilterField via
QuickWatch
or
the Immediate window, all I see are the members of the dimension, their
names
etc. I've looked at all the objects that return a ChildMembers
property
but
haven't succeeded in getting the actual measure value (Store Sales)
rather
than the name of a particular member in the dimension. Any and all
clarification gratefully received.

With many thanks for your continued assistance

Sebastian

:

There's two ways to do this.

Use the commandtext property to write the appropriate MDX to filter
out
the
unwanted data. That will require mdx query knowledge however it isn't
particularly challenging. Roughly
SELECT TIME.Whatever ON COLUMNS, PRODUCT.Whatever ON ROWS
FROM ItemsTable WHERE Year > 0,
or you can get a reference to selected fieldset (or pivotaxismember
object),
then iterate the child fields. During the iteration, examine the
associated
value property of each field (childmembers object) and if it is < 1
set
the
Isincluded property to false so that it doesn't show up.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin

 

[Alvin Bruney [MVP]]

Nope, they just need a good idea of what you want to do and stuff that
you've tried so they don't repeat. BTW, it has to be framed as a "problem",
not just a "how do i do this" type thing.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

Well that would be wonderful. Many thanks. I've been diverted into
another
job so it may take a couple of days to create a sample with less than the
8
million rows that I have, while still showing the problem. I guess MS
will
need the database, the cube and a front-end with the OWC component wired
up?

Best regards

Sebastian

Nope, I open the ticket and attach your name and contact info to the
ticket
so Microsoft calls you to resolve the problem, or you can have them email
you.

In any case if you wish to proceed, support usually wants a sample that
demonstrates the problem and clear indication of what you are trying
accomplish and why it is not working. They take over from there.
--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

Well, I'm in the UK so I suspect that I won't be able to speak to
anyone
on a
case that you open - many thanks for the offer.

I'll try the developer's line here, though past experience tells me
that
they don't know much about this control.

Thanks for trying. I'll keep battling away when I feel revitalised.
Or
else find an alternative control that is easier to use.

With best regards

Sebastian

:

If you have a small sample project that demonstrates the problem, I
can
open
a support ticket for you (or you can do so for yourself if you care).
That
will get a MS support person on the phone with you.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------


message I really appreciate your steer on this and wish I was bright enough
to
figure
it out. Despite a lot of trying, I still find the object model very
confusing and this is a case in point.

Yes I could do an MDX query, but was hoping not to since we have
some
calculated measures that take a long time to run, hence the desire
to
filter
on the client side. Also, we have a hidden dimension, and this
stops
the
CommandText property from working.

So I have struggled for a couple of hours trying to get the syntax
correct
for following your second suggestion. I'm guessing that the
selected
fieldset you mention will be the row axis (Region/Store in my
example).
I've
tried with a PivotAxisMember, a PivotFieldSet and a PivotAxisMember,
looking
at the QuickWatch for each. Nowhere can I see how to reach the
[Store
Sales]
value. At least the following doesn't fail, but I can't figure out
where
to
go next. I am coding this in VB.NET so am using full type
declarations.

Dim ptView As Microsoft.Office.Interop.Owc11.PivotView
Dim pfsFilter As Microsoft.Office.Interop.Owc11.PivotFieldSet
Dim ptFilterField As Microsoft.Office.Interop.Owc11.PivotField

ptView = ptOLAP.ActiveView
pfsFilter = ptView.RowAxis.FieldSets("[Dim Territory].[Region -
Store]")

For Each ptFilterField In pfsFilter.Fields
'?
Next

I am hoping that the For Each loop will be iterating the child
fields
as
you
suggested. When I look at the properties of ptFilterField via
QuickWatch
or
the Immediate window, all I see are the members of the dimension,
their
names
etc. I've looked at all the objects that return a ChildMembers
property
but
haven't succeeded in getting the actual measure value (Store Sales)
rather
than the name of a particular member in the dimension. Any and all
clarification gratefully received.

With many thanks for your continued assistance

Sebastian

:

There's two ways to do this.

Use the commandtext property to write the appropriate MDX to filter
out
the
unwanted data. That will require mdx query knowledge however it
isn't
particularly challenging. Roughly
SELECT TIME.Whatever ON COLUMNS, PRODUCT.Whatever ON ROWS
FROM ItemsTable WHERE Year > 0,
or you can get a reference to selected fieldset (or pivotaxismember
object),
then iterate the child fields. During the iteration, examine the
associated
value property of each field (childmembers object) and if it is < 1
set
the
Isincluded property to false so that it doesn't show up.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin