Odd BeginRange/EndRange behavior

From: summer_porch (summer_porch_at_hotmail.com)
Date: 04/14/04

  • Next message: davidsanxter_at_vf.shawcable.net: "Little Sister Giving a Handjob" 
    Date: Tue, 13 Apr 2004 20:10:31 -0400

    I've been playing with the XMLA Sample VB application to determine how
    BeginRange and EndRange work and what caching it does of results and found a
    series of odd behaviors.

    I inserted additional properties in the ExecuteQuery Function in the
    xlsXmlDataAccess.vb file to add BeginRange and EndRange properties.

            m_Properties.SetNameValue(cst_strBeginRange, "1")
            m_Properties.SetNameValue(cst_strEndRange, "40")

    The Query, below, produces approx 256 rows and 4 columns. The natural
    boundary for each row is a factor of 4.

    What I found is as follows:
       - For EndRange values of 20, 40 or 60 any BeginRange not a multiple of 4
    fails an error "The specified range rowset is not valid".

          So in this case a BeginRange of -1, 0, 4, 8, 12, .. work, but
    1,2,3.,5,6,7,.9,10, 11 all fail with the above error.

     - If the EndRange is -1 (all) then a BeginRange works (e.g. 1 to -1).

    - The actual cell ordinals returned are not what I expected.
    > Begin = 28 to End = 60 only returned CellOrdinals 27, 31
    > Begin = -1 to End = 60 returned a CellOrdinals -1 (!), plus 15, 23,
    27, 31
    > Begin = -1 to End -1 returned CellOrdinals in the 0-60 range of 0,
    3, 5, 6, 10, 14, 16, 22, 24, 26, 28, ..

    This would suggest that the cells returned for other than the complete
    CellSet (-1, -1) are incomplete. I can think of no explanation for the
    variance in the results.

    Query:

    SELECT
           NON EMPTY CROSSJOIN( DISTINCT( { [Time].[1997].CHILDREN } ),
    DISTINCT( { [Measures].[Unit Sales] } ) ) ON COLUMNS, NON EMPTY
    GENERATE( DISTINCT( TOPCOUNT( DISTINCT( { [Store].[Store Name].MEMBERS } ),
    10, ( [Measures].CURRENTMEMBER ) ) ), CROSSJOIN( { [Store].CURRENTMEMBER },
    GENERATE( DISTINCT( TOPCOUNT( DISTINCT( { [Customers].[Name].MEMBERS } ),
    10, ( [Measures].[Store Sales] ) ) ), CROSSJOIN( {
    [Customers].CURRENTMEMBER }, DISTINCT( TOPPERCENT( DISTINCT( {
    [Product].[Product Name].MEMBERS } ), 10, ( [Measures].[Store
    Cost] ) ) ) ) ) ) ) ON ROWS
    FROM [Sales]
    CELL PROPERTIES VALUE, FORMATTED_VALUE, FONT_NAME, FORE_COLOR, BACK_COLOR,
    FONT_SIZE, FONT_FLAGS

  • Next message: davidsanxter_at_vf.shawcable.net: "Little Sister Giving a Handjob"