Big question, PivotTable creation in code. !!!

[Claude Vernier]

Hi!

Here's a big one. Maybe someone else also add this requirement.

I have a big data warehouse, with a web interface to extract data.
Users have access to cubes with dimensions and members.

They can select members of dimensions to create their own queries.
Those queries are used in web services to extract data that are showed in
HTML tables.

Users also have access to pivot tables but saving and restoring the queries
are separate from the previous and are based on the XMLData of the OWC.

I need to translate my XML queries to XMLData format and backward.

The XML looks like this:

<DatasetBrowser>
<Dataset>
<Dimension Name="Country">
<Member Name="Canada" />
<Member Name="France" />
<Member Name="USA" />
</Dimension>

<Dimension Name="Time">
<Member Name="2000" />
<Member Name="2001" />
<Member Name="2002" />
</Dimension>

<Dimension>
...
</Dimension>
</Dataset>
</DatasetBrowser>


I tried to use the OWC DOM but I keep having errors about everywhere.
First, I got an XML schema of the DOM,

I create a new pivot table object providing the name of the OLAP cube, the
connection string....
Next, I keep the XMLData from that object

string sXMLData = MyClass.XMLData;


Then, I create a pivottableclass object and fill some basic properties using
the XMLData:

OWC10.PivotTable oPT = new OWC10.PivotTableClass();
oPT.XMLData = sXMLData;
oPT.AllowCustomOrdering = true;
oPT.AllowDetails = true;
oPT.AllowFiltering = true;
oPT.AllowPropertyToolbox = true;


int iThisFieldSet = 0;
System.Collections.ArrayList oList = new ArrayList();
object[] a_oDimMembers;

For each <Dimension> in <Dataset>
{
// --- For each member of each dimension, I seek the equivalent in the
cube...
if( oPT.ActiveView.FieldSets[iThisFieldSet].Name == <Dimension>.Name )
{
iLevelsToInclude = 0;

// --- Add members to a temp List
oList = new ArrayList();
For each <Member> in <Dimension>
{
if( !oList.Contains( <Member>.Name ) )
oList.Add( <Member>.Name );
}

// --- Convert List to Object[]
a_oDimMembers = oList.ToArray();

// --- Add those members to the Pivot table
oPT.ActiveView.FieldSets[iThisFieldSet].AllIncludeExclude =
OWC10.PivotFieldSetAllIncludeExcludeEnum.plAllExclude;
oPT.ActiveView.FieldSets[iThisFieldSet].Fields[0].IncludedMembers =
a_oDimMembers;
oPT.ActiveView.FieldSets[iThisFieldSet].Fields[0].set_Subtotals( 0, false );

// --- Set this dimension the filter page
oPT.ActiveView.FilterAxis.InsertFieldSet(
oPT.ActiveView.FieldSets[iThisFieldSet], iDimsOnPage++, true );

Break;
}
}

I think this code is great but I have several problems, for countries, it
seems to work.
But I have subjects such as:

-Global Population
--Europe Population
---France Population

Where any level or combinations of levels are selected by the user and I do
not know how to select them in the DOM. Or since filters are enabled in the
OLAP cubes, some members can be missing and I have no way to figure it out.

Can someone help me on that, any hints, suggestion, links, books...?
Maybe I'm not using the best objects to do this also...

Please help me... thanks in advance... sorry for my poor English.
Claude Vernier