I created a simple crosstab against the Sales (Analysis) demo package that has a set filter on the month level, filtering on the year attribute and this is causing the report to return no data when it should return data for the months of 2011. Does anyone know why this is happening?
Here is the report
<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples_DQ']/folder[@name='Models']/package[@name='GO Sales (analysis)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemMemberSet name="Month"><setDefinition><setFilter><baseSet><setLevelMembers><dmLevel><LUN>[Sales (analysis)].[Time].[Time].[Month]</LUN><itemCaption>Month</itemCaption></dmLevel></setLevelMembers></baseSet><setFilterCriteria><stringPropertyCondition operator="matches" value="2011"><dmMemberProperty><MPUN>[Sales (analysis)].[Time].[Time].[Year].[Year]</MPUN><itemCaption>Year</itemCaption></dmMemberProperty></stringPropertyCondition></setFilterCriteria></setFilter></setDefinition><dmDimension><DUN>[Sales (analysis)].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/></XMLAttributes></dataItemMemberSet><dataItemMeasure name="Revenue"><dmMember><MUN>[Sales (analysis)].[Sales].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/></XMLAttributes></dataItemMeasure><dataItemLevelSet name="Order method type"><dmLevel><LUN>[Sales (analysis)].[Order method].[Order method].[Order method type]</LUN><itemCaption>Order method type</itemCaption></dmLevel><dmDimension><DUN>[Sales (analysis)].[Order method]</DUN><itemCaption>Order method</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Order method].[Order method]</HUN><itemCaption>Order method</itemCaption></dmHierarchy></dataItemLevelSet></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></crosstabCorner>
<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Revenue"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order method type" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab>
</contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2012-05-16T20:22:20.843Z" output="no"/></XMLAttributes><reportName>Revenue by Order Method</reportName></report>
Wouldn't you just add a calculation that returns descendants([2011],2) to deliver the months of 2011? I think that would be a far more efficient way of getting the desired results.
Cheers!
MF.
This is just a simplified example of what I'm trying to do. My real report has a crosstab and two charts, each with their own query and each one hitting a separate fact table with it's respective time dimension. I need to prompt for a year value and filter all three queries for the months of that year. I can't prompt for a year member because there are three different time dimensions. That is why I'm using set filtering. This seems like a bug to me.
Sounds like you need a prompt macro, prompting for just the year caption and constructing the rest of the MUN for each time dimension. Once you have the three members (via their MUNs) you can use the descendants() function. The first time I saw this technique was in a post by Paul, so plaudits go to him :)
MF.
Thanks! I'll try that.
I've got a draft sitting in my blog about this. One of these days I really have to finish that.
The issue with dealing with >1 cubes in a report is the structure of the MUN. The problem with cubes is that there's no guarantee that MUNs will be constructed the same way from one cube to another. So you might have one mun that's a simple pointer to the category code, and another that shows the entire path from the top level [2011].[201103].[201111].[20111111].
Easiest way to handle this would be, as Mark said, to use a prompt macro and reference the caption in each query.
descendants(filter([Cube].[Dim].[Hier].[Year],caption(currentMember([Cube].[Dim].[Hier]))=#prompt('Year','string',sq(timestampMask($current_timestamp,'yyyy')))#),[Month Level])