Crosstab with an average for the measure

[mnennig]

I am trying to produce a nested crosstab report that displays the average daily balance for an account by month, dealer and fund.  It should look like:
                                   June       July
Dealer xyz   Fund ABC        25        34
                 Fund DEF        32        19
Dealer xyz Total                57        53

The measure displayed is the average of the daily account balances.  For June it would be the sum of each day's amount (750)/30 days.  I am able to produce a list report with the correct amounts but am having trouble getting the amounts to appear properly in the crosstab, let alone subtotal.  Any suggestions would be appreciated.

[rockytopmark]

OK... so the Cells are Averaging based on the settings of the Measure.  You'll need to create a separate Total query item that will aggregate the Averages (giving you your desired total)

[mnennig]

Thank you for your feedback, but I can't even get the average amounts such as the 25 in the first row and column to appear correctly.  It is reflecting the total of the daily balances and not reflecting the average of these amounts.

[vishnug79]

Hi,

Create a Query calculation with the following....
average(currentMeasure within detail [Month])

In place of month use your own Query Item.

[mnennig]

Thank you for your suggestion.  When I use this query calculation I get the error:
Unable to build the aggregate dataItem="Account Bal". The referenced dataItem="Date Month Desc" was not found at the same nesting level

When I try using set, rather than detail to avoid the error message, the report runs but the cells of the crosstab are empty.

[CognosPaul]

If you're using a dimensional model with the time dimension set up as a hierarchy, you can try the following:

Assuming the time dimension is:
Year
Quarter
Month
Day

The columns should be: [Month]
The rows should be nested:
[Dealer] -> [Fund]
           -> [Measure] (The calculated measure should appear directly under the fund, nested with the dealer.)
The crosstab default measure should be set to [Measure]
The measure should be:
[Cube].[Measure Dimension].[Measure] / count(1 within set children(currentmember([Cube].[Dimension].[TimeHierarchy])))

If it's a relational source, it's possible but a bit more difficult. Let me know and I'll post an example using GO Sales (query)

[mnennig]

It is a relational data source.

[CognosPaul]

Using a relational source for this isn't as easy. It's possible, but it's not elegant.

There are two ways to do this, depending on what you need. You can count the days in the month with the function

Code Select Expand

count(days for month,year) The drawback of this is that (if you're using an inner join on the time dimension table) it will only return the count of days that have data. So instead of 30 days for April, you might only have 10.

The other way to do this requires modifying your time dimension table. Populate a field called "days in month". Once you have that, you can then divide the measure by that field.

Since I cannot attach anything at this time, the example xml is below. It's using the Go Sales (query) package.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (query)']/model[@name='model']</modelPath>

<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>Counting days in month (Because April only had 10 days of data, the result is 10)</staticValue></dataSource></textItem></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1">
<crosstabCorner>
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="AverageRevenue (Day Count)" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><CSS value="background-color:silver"/></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Product type" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="AverageRevenue (Day Count)" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><CSS value="background-color:gray;color:white"/></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/><style><CSS value="height:30px"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><crosstab horizontalPagination="true" name="Crosstab11" refQuery="Query1">
<crosstabCorner>
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="AverageRevenue (Days in the month)" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><CSS value="background-color:silver"/></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Product type" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="AverageRevenue (Days in the month)" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><CSS value="background-color:gray;color:white"/></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab></contents></tableCell></tableCells></tableRow></tableRows></table></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><drillBehavior drillUpDown="true" modelBasedDrillThru="true"/><queries><query name="Query1"><source><model/></source><selection><dataItem name="Month" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time dimension].[Month]</expression></dataItem><dataItem name="DayCount"><expression>count([Sales (query)].[Time dimension].[Date] for [Sales (query)].[Time dimension].[Month],[Sales (query)].[Time dimension].[Year])</expression></dataItem><dataItem name="Product line" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product line]</expression></dataItem><dataItem name="Product type" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product type]</expression></dataItem><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales].[Revenue]</expression></dataItem><dataItem name="AverageRevenue (Day Count)"><expression>[Revenue] / [DayCount]</expression></dataItem><dataItem name="Days in the month" aggregate="maximum" rollupAggregate="maximum"><expression>[Sales (query)].[Time dimension].[Days in the month]</expression></dataItem><dataItem name="AverageRevenue (Days in the month)"><expression>[Revenue] / [Days in the month]</expression></dataItem></selection><detailFilters><detailFilter><filterExpression>[Sales (query)].[Time dimension].[Year] = 2007
</filterExpression></detailFilter></detailFilters></query></queries></report>

[mnennig]

Thanks you, this did work, generally.  I did want to average the amount for only those days with balances.  What I evenually did was create an average calculation in Framework Manager and use this as the measure in the crosstab.