Hi All,
Request you to plz refer the attached excel sheet for the below explanation.
I have a report which has detail section & summary section. In detail section we display the records at Sales_Type,Title & Deal level. Detail section is grouped by Sales_Type and Title . It shows the distinct count of Deal_Sys_Id for each Sales_Type & Title. In Footer Summary we show total deal count for a Sales_type & title ( highlighted in Yellow) and total deal count for a Sales_Type ( highlighted in Green). Finall we show overall total ( highlighted in Orange).
In Detail section it shows correct count for " Total for ABC = 9" as the disticnt deal_count for ABC = 9.
Finally in Grand Total it shows Deal_Count = 10 .
In Summary section ( refer Summary Output tab), we have a crosstab report to display the summary.
Refering to Current ouput crosstab : Under the Total column , It shows the Deal_Count as 10 highlighted in Gray color.
The actual output to be displayed is depicted under the Actual Requirement section:
Also I tried using the Rollup Aggregate function as "Count Distinct". Bt Cognos throws an error saying " The selected aggregation type is not supported for cross-tabs or for OLAP data sources: dataItem="REQ_DEAL_COUNT"; rollupAggregate="countDistinct". "
Can someone plz suggest some workaround for this.
Thanx in Advance.
Regards,
Mistry
Topic
-
Re: Crosstab Total Issue
2010-07-27T23:28:47Z
This is the accepted answer.
This is the accepted answer.
I suspect that you are probably using a relational data source. If that's not the case then let me know.
For a relational count distinct the problem you are having is that you have likely used the Total option to generate your summary rows in the crosstab. This does exactly what it says and will give you a total despite your measure being a count distinct. So the first thing is that you will have to go back and remove the totals.
Now there are two ways to create the count distinct. The first way is with the regular aggregate property. The second is by defining the count distinct as part of the expression. If you define it in the expression then you have to set the regular aggregate property to Calculated. In both cases, set the rollup aggregate to Calculated.
Next, you should be able to go back and use the Aggregate option to create the summary rows in your crosstab. The aggregate option should preserve the count distinct operation for the rollup. However, there are cases where this will not occur. If you are using a dimensional model and you define a custom set of members for the rows then there are difficulties with calculating the distinct count.
If you need more help then I would suggest that you create a report specification using the Cognos samples and then we can more easily discuss how you have structured the report and what you need to do to get the summary values you want.
