Hi all,<br><br>I am having troubles with an MDX query using the VisualTotals keyword. I must first say that the MDX query is generated by the Simba O2X excel plugin and that I can't really change it.<br><br>Basically, this is a simple query with 1 measure and 1 dimension but with a filter on that dimension. My cube is representing rentals and there is a dimension for subscriber types (1 hierarchy, 2 levels).<br>
<br>I am trying to display the number of rentals on the "Casual" subscriber type. Here is the query generated by Simba O2X:<br>QUERY A:<br>with set [XL_Row_Dim_0] as 'VisualTotals(Distinct(Hierarchize({Ascendants([Subscription].[All Subscriptions].[Casual]), Descendants([Subscription].[All Subscriptions].[Casual])})))'<br>
select NON EMPTY Hierarchize(Intersect({DrilldownLevel({[Subscription].[All Subscriptions]})}, [XL_Row_Dim_0])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS<br>from [Rentals]<br>where [Measures].[RentalsCount]<br><br>
If I execute this query in excel, I get no output (empty table) and in the MDXQueryServlet, I get:<br><pre>java.lang.IndexOutOfBoundsException: Index: 0, Size: 0<br>        at java.util.ArrayList.RangeCheck(ArrayList.java:547)<br>
        at java.util.ArrayList.get(ArrayList.java:322)<br>        at mondrian.web.servlet.MDXQueryServlet.processRequest(MDXQueryServlet.java:95)<br>        at mondrian.web.servlet.MDXQueryServlet.doGet(MDXQueryServlet.java:228)<br></pre><br>
I guess the servlet does not handle empty responses very well.<br><br>If I execute the same query without the VisualTotals, I get valid output:<br>QUERY B:<br>
with set [XL_Row_Dim_0] as
'Distinct(Hierarchize({Ascendants([Subscription].[All
Subscriptions].[Casual]), Descendants([Subscription].[All
Subscriptions].[Casual])}))'<br>
select NON EMPTY
Hierarchize(Intersect({DrilldownLevel({[Subscription].[All
Subscriptions]})}, [XL_Row_Dim_0])) DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON COLUMNS<br>
from [Rentals]<br><table class="resulttable" border="0" cellspacing="1"><tbody><tr><td class="columnheading" colspan="1" nowrap>[Subscription].[All Subscriptions]</td><td class="columnheading" colspan="1" nowrap>[Subscription].[All Subscriptions].[Casual]</td>
</tr>
<tr><td class="cell">246</td><td class="cell">71</td></tr></tbody></table><br>I did some investigation on the content of XL_Row_Dim_0:<br>QUERY C:<br>select [Measures].[RentalsCount] ON COLUMNS,<br>VisualTotals(Distinct(Hierarchize({Ascendants([Subscription].[All Subscriptions].[Casual]), Descendants([Subscription].[All Subscriptions].[Casual])}))) ON ROWS<br>
from [Rentals]<br>Results:
<table class="resulttable" border="0" cellspacing="1"><tbody><tr><td class="slicer" rowspan="1" colspan="1" nowrap> </td>
<td class="columnheading" colspan="1" nowrap>[Measures].[RentalsCount]</td></tr>
<tr><td class="rowheading" nowrap>[Subscription].[All Subscriptions]</td><td class="cell">246</td></tr><tr><td class="rowheading" nowrap>[Subscription].[All Subscriptions].[Casual]</td><td class="cell">71</td></tr><tr><td class="rowheading" nowrap>
[Subscription].[All Subscriptions].[Casual].[24h]</td><td class="cell">71</td></tr></tbody></table><br>And if I remove the VisualTotals:<br>QUERY D:<br>select [Measures].[RentalsCount] ON COLUMNS,<br>Distinct(Hierarchize({Ascendants([Subscription].[All Subscriptions].[Casual]), Descendants([Subscription].[All Subscriptions].[Casual])})) ON ROWS<br>
from [Rentals]<br><br>I get the same result:<br>Results:
<table class="resulttable" border="0" cellspacing="1"><tbody><tr><td class="slicer" rowspan="1" colspan="1" nowrap> </td>
<td class="columnheading" colspan="1" nowrap>[Measures].[RentalsCount]</td></tr>
<tr><td class="rowheading" nowrap>[Subscription].[All Subscriptions]</td><td class="cell">246</td></tr><tr><td class="rowheading" nowrap>[Subscription].[All Subscriptions].[Casual]</td><td class="cell">71</td></tr><tr><td class="rowheading" nowrap>
[Subscription].[All Subscriptions].[Casual].[24h]</td><td class="cell">71</td></tr></tbody></table><br>I don't understand why query A and B does not return the same results if query C and D output the same thing. The only difference between A and B is the content of XL_Row_Dim_0 and I assume it is the same since C and D output the same thing.<br>
<br>I have to confess that I am far from an expert in MDX query so I may be missing something obvious here.<br><br>Here is a simplified version of my schema:<br><Schema name="Bixi"><br> <Cube name="Rentals" caption="Rentals" cache="true" enabled="true"><br>
<Table name="RentalFact"><br> </Table><br> <Dimension type="StandardDimension" foreignKey="subscriptionType_id" name="Subscription" caption="Subscription"><br>
<Hierarchy hasAll="true" primaryKey="id"><br> <Table name="SubscriptionTypeDim"><br> </Table><br> <Level name="UserType" column="userType_en_CA" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="User Type"><br>
</Level><br> <Level name="SubscriptionType" column="name_en_CA" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="Subscription"><br>
</Level><br> </Hierarchy><br> </Dimension><br> <Measure name="RentalsCount" column="id" datatype="Numeric" aggregator="count" caption="Nb Rentals" visible="true"><br>
</Measure><br> </Cube><br></Schema><br><br>I can provide test data if needed (sql script for the database).<br><br>I understand that it is not easy to debug such problem without the actual setup but any help will be greatly appreciated.<br>
<br>BTW, mondrian is a great product and help us a LOT in the analysis of our data.<br><br>Thank you!<br><br>Manuel Darveau<br>