[Mondrian] Excel 2007 Support

Sergey Mazin sergey.mazin at skype.net
Thu Feb 4 07:23:42 EST 2010


Hi Julian,

 

I can see that in change #13351 you added all possible properties to XMLA
response, however, we cannot add not yet supported properties. Or Excel goes
mad.

The MDX queries now look like:



SELECT NON EMPTY Hierarchize({DrilldownLevel({[Position].[All Position]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(

{DrilldownLevel({[Pay Type].[All Pay Types]})}) DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON ROWS  FROM [HR] CELL PROPERTIES VALUE, FORMAT_STRING,
LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

 

And result:

 

<Cell CellOrdinal="16">

  <Value xsi:type="xsd:double">5984.28</Value>

  <FormatString>Currency</FormatString>

</Cell>

 

LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS properties are missing.

Mondrian returns correct results but Excel ignores it because not all
properties are included.

 

We have 2 options:

1.       Remove not yet supported properties and leave only: VALUE,
FORMAT_STRING, FORMATTED_VALUE

2.       Add properties to resultset with some default values.

 

Option #1 requires minimal changes.

 

Best regards,

Sergey

 

 

<?xml version="1.0" encoding="UTF-8"?>

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <Header>

        <Session SessionId="" mustUnderstand="1"
xmlns="urn:schemas-microsoft-com:xml-analysis"/>

    </Header>

    <Body>

        <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

            <RequestType>MDSCHEMA_PROPERTIES</RequestType>

            <Restrictions>

                <RestrictionList>

                    <PROPERTY_TYPE>2</PROPERTY_TYPE>

                </RestrictionList>

            </Restrictions>

            <Properties>

                <PropertyList>

                    <LocaleIdentifier>1061</LocaleIdentifier>

                    <Content>SchemaData</Content>

                    <Format>Tabular</Format>

 
<DataSourceInfo>Provider=Mondrian;DataSource=MondrianFoodMart;</DataSourceIn
fo>

                    <Catalog>FoodMart</Catalog>

                </PropertyList>

            </Properties>

        </Discover>

    </Body>

</Envelope>

 

<?xml version="1.0" encoding="ISO-8859-1"?>

<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" >

<SOAP-ENV:Header>

<Session SessionId="" xmlns="urn:schemas-microsoft-com:xml-analysis"
/></SOAP-ENV:Header>

<SOAP-ENV:Body>

<cxmla:DiscoverResponse
xmlns:cxmla="urn:schemas-microsoft-com:xml-analysis">

  <cxmla:return>

    <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:EX="urn:s

chemas-microsoft-com:xml-analysis:exception">

      <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:s

ql="urn:schemas-microsoft-com:xml-sql"
targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset"
elementFormDefault="qualified">

        <xsd:element name="root">

          <xsd:complexType>

            <xsd:sequence>

              <xsd:element name="row" type="row" minOccurs="0"
maxOccurs="unbounded"/>

            </xsd:sequence>

          </xsd:complexType>

        </xsd:element>

        <xsd:simpleType name="uuid">

          <xsd:restriction base="xsd:string">

            <xsd:pattern
value="[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-
F]{12}"/>

          </xsd:restriction>

        </xsd:simpleType>

        <xsd:complexType name="row">

          <xsd:sequence>

            <xsd:element sql:field="CATALOG_NAME" name="CATALOG_NAME"
type="xsd:string" minOccurs="0"/>

            <xsd:element sql:field="SCHEMA_NAME" name="SCHEMA_NAME"
type="xsd:string" minOccurs="0"/>

            <xsd:element sql:field="CUBE_NAME" name="CUBE_NAME"
type="xsd:string" minOccurs="0"/>

            <xsd:element sql:field="DIMENSION_UNIQUE_NAME"
name="DIMENSION_UNIQUE_NAME" type="xsd:string" minOccurs="0"/>

            <xsd:element sql:field="HIERARCHY_UNIQUE_NAME"
name="HIERARCHY_UNIQUE_NAME" type="xsd:string" minOccurs="0"/>

            <xsd:element sql:field="LEVEL_UNIQUE_NAME"
name="LEVEL_UNIQUE_NAME" type="xsd:string" minOccurs="0"/>

            <xsd:element sql:field="MEMBER_UNIQUE_NAME"
name="MEMBER_UNIQUE_NAME" type="xsd:string" minOccurs="0"/>

            <xsd:element sql:field="PROPERTY_NAME" name="PROPERTY_NAME"
type="xsd:string"/>

            <xsd:element sql:field="PROPERTY_CAPTION"
name="PROPERTY_CAPTION" type="xsd:string"/>

            <xsd:element sql:field="PROPERTY_TYPE" name="PROPERTY_TYPE"
type="xsd:short"/>

            <xsd:element sql:field="DATA_TYPE" name="DATA_TYPE"
type="xsd:unsignedShort"/>

            <xsd:element sql:field="PROPERTY_CONTENT_TYPE"
name="PROPERTY_CONTENT_TYPE" type="xsd:short" minOccurs="0"/>

            <xsd:element sql:field="DESCRIPTION" name="DESCRIPTION"
type="xsd:string" minOccurs="0"/>

          </xsd:sequence>

        </xsd:complexType>

      </xsd:schema>

      <row>

        <PROPERTY_NAME>BACK_COLOR</PROPERTY_NAME>

        <PROPERTY_CAPTION>BACK_COLOR</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>130</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>CELL_EVALUATION_LIST</PROPERTY_NAME>

        <PROPERTY_CAPTION>CELL_EVALUATION_LIST</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>130</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>CELL_ORDINAL</PROPERTY_NAME>

        <PROPERTY_CAPTION>CELL_ORDINAL</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>19</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>FORE_COLOR</PROPERTY_NAME>

        <PROPERTY_CAPTION>FORE_COLOR</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>130</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>FONT_NAME</PROPERTY_NAME>

        <PROPERTY_CAPTION>FONT_NAME</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>130</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>FONT_SIZE</PROPERTY_NAME>

        <PROPERTY_CAPTION>FONT_SIZE</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>130</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>FONT_FLAGS</PROPERTY_NAME>

        <PROPERTY_CAPTION>FONT_FLAGS</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>19</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>FORMATTED_VALUE</PROPERTY_NAME>

        <PROPERTY_CAPTION>FORMATTED_VALUE</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>130</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>FORMAT_STRING</PROPERTY_NAME>

        <PROPERTY_CAPTION>FORMAT_STRING</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>130</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>NON_EMPTY_BEHAVIOR</PROPERTY_NAME>

        <PROPERTY_CAPTION>NON_EMPTY_BEHAVIOR</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>130</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>SOLVE_ORDER</PROPERTY_NAME>

        <PROPERTY_CAPTION>SOLVE_ORDER</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>3</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>VALUE</PROPERTY_NAME>

        <PROPERTY_CAPTION>VALUE</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>12</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>DATATYPE</PROPERTY_NAME>

        <PROPERTY_CAPTION>DATATYPE</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>130</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>LANGUAGE</PROPERTY_NAME>

        <PROPERTY_CAPTION>LANGUAGE</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>19</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>ACTION_TYPE</PROPERTY_NAME>

        <PROPERTY_CAPTION>ACTION_TYPE</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>19</DATA_TYPE>

      </row>

      <row>

        <PROPERTY_NAME>UPDATEABLE</PROPERTY_NAME>

        <PROPERTY_CAPTION>UPDATEABLE</PROPERTY_CAPTION>

        <PROPERTY_TYPE>2</PROPERTY_TYPE>

        <DATA_TYPE>19</DATA_TYPE>

      </row>

    </root>

  </cxmla:return>

</cxmla:DiscoverResponse>

</SOAP-ENV:Body>

</SOAP-ENV:Envelope>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100204/2cb05822/attachment.html 


More information about the Mondrian mailing list