OWC11 - PivotTable - strange behavior with mdx WHERE clause

[Antoine Prevot]

Greetings,

I have a strange behavior when I try to apply where clauses in my MDX
statement.

I programatically generate an MDX request which contains some filter infos,
obviously concatenated in a crossjoin where clause. Everything works fine as
long as I crossjoin single items, ie :

Select [blabla] from [myCube]
Where {([DimA].[HierarchyA].&[ItemX])} * {([DimB].[HierarchyB].&[ItemY])}

Great, but when i tried to crossjoin multiple items (sets), all filters
disappeared from the PivotTable, ie :
Select [blabla] from [myCube]
Where {([DimA].[HierarchyA].&[ItemX])} * {([DimB].[HierarchyB].&[ItemY]),
([DimB].[HierarchyB].&[ItemZ])}

I tried without crossjoin (a simple set in the where clause), but the
problem was persistent. And everything works fine with management studio !

Did I miss something ? Is there any workaround ??

Thanks in advance,

 

[Antoine Prevot]

Ok, I guess i'm doomed with the use of sets. Some extra bugs I found with the
OWC11 PivotTable :

The followin syntax will work perfectly :
Select
{([Dim1].[H1].[Item1]), [Dim1].[H1].[Item2])} on rows,
{[Measures].[myMeasure]} on columns
From [myCube]

But the following won't :
With Set [toto] As {([Dim1].[H1].[Item1]), [Dim1].[H1].[Item2])}
Select
[toto] on rows,
{[Measures].[myMeasure]} on columns
From [myCube]

It generates an explicit error this time : the query could not be processed
.... any idea about what's happening ?

Thanks,

 

[Antoine Prevot]

So much help, i'm really moved

I finally resolved this issue programmatically.

Assuming we have a PivotTable object (pt), a dimension name (dim_name), and
an array containing the list of leaf members we want to select (sel_ary), the
code is quite simple (it's JavaScript source code but anybody should be able
to transpose it) :

//exclude all selected members
pt.ActiveView.FieldSets(dim_name).AllIncludeExclude = 2;
fields_count = pt.ActiveView.FieldSets(dim_name).Fields.count;
//select the last level index
leaf_field = pt.ActiveView.FieldSets(dim_name).Fields(fields_count - 1);
//set the list of selected members
leaf_field.IncludedMembers = sel_ary;
//move our dimension on the filter axis
pt.ActiveView.FilterAxis.InsertFieldSet(pt.ActiveView.FieldSets(dim_name));

And that's it !

This web page helped me alot
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_acprogpvt.asp

It's a lazy workaround, doesn't it ?