Count Member in Crosstab

[markcognos]

         |  6   |    5  |  4   |     3  | 2      | 1    | <---Count the Member MOnth
        |------------------------------------------------   
        |May |Jun    |Sept|Dec   |Aug  |Nov | <-- Member MOnth   
        |------|-------|------|-------|------|------|
         |2013|2013|2012|2012|2011|2011| <-- Mmeber Year
         |------|-------|------|-------|------|------|    
         |CASE|CASE|CASE|CASE|CASE|CASE|   
Item1|        |        |        |        |        |         |
Item2|        |        |        |        |        |         |
Item3|        |        |        |        |        |         |


Hi ,

i have example above Crosstab data,How do i count member set for month,Like in the example above.?
Any Idea?

Thanks,

[CognosPaul]

You'll need to expand on what you mean by Count the Member Month. Do you mean you need a count of the number of members inside that month? Since it's in descending order, can you get away with using a report expression like: 7 - ColumnNumber() ?

[markcognos]

Hi,

I Need to Count The column of Month,See above example..i also used Crosstab..
Example a created new data item.
Then insert in the top of crosstab..

The example is in the above..
example is the 6,5,4,3,2,1 < --- For each month in crosstab in descending order starts from the right side.


Thanks,

Mac

[CognosPaul]

Based on your other question, I'm going to assume this is a relational source.

In your query, create a new data item (MonthCount) that does:

Code Select Expand

count(distinct [Month] for report)
That will return the total number of months returned in the query.

Drag a crosstab space directly below month and above year. Unlock the report, put a text item in the node, change it to data item value on the month.

Click the node the original Month node, and add the MonthCount to the properties. Change the source text from Member Caption to Report Expression. Put in the following expression into the report expression:

Code Select Expand

[Query1].[MonthCount] - ColumnNumber () + 1

So if there are 9 months in the crosstab, the first column will be 9, (9 - 1 + 1), second will be 8 and so on.

Example MDX below:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="he">
<modelPath>/content/folder[@name='Projects']/folder[@name='Quality']/folder[@name='Models']/package[@name='Quality']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Total Count" aggregate="total" rollupAggregate="total"><expression>[Presentation Layer].[Quality Assurance Processes].[Total Count]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Status" rollupAggregate="none"><expression>[Presentation Layer].[Quality Assurance Processes].[Status]</expression></dataItem><dataItem name="State" aggregate="none" rollupAggregate="none"><expression>[Presentation Layer].[Quality Assurance Processes].[STATE]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Status Count"><expression>count(distinct [Status] for report)</expression></dataItem></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 horizontalPagination="true" refQuery="Query1" name="Crosstab1">
<crosstabCorner>
<contents><textItem><dataSource><dataItemLabel refDataItem="Total Count"/></dataSource></textItem></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>



<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler><crosstabColumns><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabSpacer edgeLocation="s1" renderFactCells="true">
<contents>
<textItem>
<dataSource>
<staticValue> </staticValue>
</dataSource>
</textItem>
<textItem><dataSource><dataItemValue refDataItem="Status"/></dataSource></textItem></contents>

<style>
<defaultStyles>
<defaultStyle refStyle="xs"/>
</defaultStyles>
</style>
</crosstabSpacer></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Status" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><reportExpression>[Query1].[Status Count] - ColumnNumber () + 1</reportExpression></dataSource></textItem></contents><propertyList><propertyItem refDataItem="Status Count"/></propertyList></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="State" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><defaultMeasure refDataItem="Total Count"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab></contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-11-29T07:48:46.112Z" output="no"/></XMLAttributes></report>