captionExpression usage, issues, etc

[optimist]

I'm trying to get captionExpression to work in 3.1.5. I found a bug report with patch (http://jira.pentaho.com/browse/MONDRIAN-683) but even after applying the patch I have no luck. I suspect it's because my level is hierarchical - does it work for hierarchical levels?

Specifically, I get a SQLException due to Invalid column index in mondrian.rolap.SqlMemberSource.getMemberChildren2() and I see from debugging that the caption expression isn't in the result set because it isn't in the generated sql for this level, yet childLevel.hasCaptionColumn() is true so getMemberChildren2() thinks it should be. Here's my level...

<Level name="Work Item" column="ID" table="ARTIFACT" nullParentValue="null" parentColumn="PARENT_ID" uniqueMembers="true" >
<CaptionExpression>
<SQL>
'hey'
</SQL>
</CaptionExpression>
</Level>

And the generated sql: select "ARTIFACT"."ID" as "c0" from "ARTIFACT" "ARTIFACT" where (type = 'A') and "ARTIFACT"."PARENT_ID" IS NULL group by "ARTIFACT"."ID" order by "ARTIFACT"."ID" ASC

Is there a workaround, something I'm doing wrong, or another patch that might make this work?

Thanks.

Joel

[optimist]

In case it's relevant, here's the entire dimension's definition. I notice that the Join(s) that create the hierarchy aren't in the generated sql either, but then I'm a newbie and I don't know if that's done at another step. For my purposes, if the joins aren't part of the sql, then my captionExpression won't work either since it will rely on the join - the captionExpression in the original post is just for testing. Is there something I'm doing wrong?

<Dimension name="Item" foreignKey="ID">
<Hierarchy hasAll="true" primaryKeyTable="ARTIFACT" primaryKey="ID" >
<Join leftKey="F_ID_ID" rightAlias="F_ID" rightKey="ID">
<Table name="ARTIFACT">
<SQL>
type = 'A'
</SQL>
</Table>
<Join leftKey="ID_F_ID" rightKey="ID">
<Table name="F_ID"/>
<Table name="ID_F"/>
</Join>
</Join>
<Level name="Work Item" column="ID" table="ARTIFACT" nullParentValue="null" parentColumn="PARENT_ID" uniqueMembers="true" >
<CaptionExpression>
<SQL>
'hey'
</SQL>
</CaptionExpression>
</Level>
</Hierarchy>
</Dimension>

Thanks

Joel