Topic
3 replies Latest Post - ‏2013-11-15T20:49:45Z by fcl_cognos
mistryd
mistryd
176 Posts
ACCEPTED ANSWER

Pinned topic Crosstab Total Issue

‏2010-07-26T09:57:40Z |
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
Updated on 2010-07-27T23:28:47Z at 2010-07-27T23:28:47Z by SystemAdmin
  • mistryd
    mistryd
    176 Posts
    ACCEPTED ANSWER

    Re: Crosstab Total Issue

    ‏2010-07-27T15:46:42Z  in response to mistryd
    Hi,

    Can anyone plz suggest something on this issue?

    Thanx in Advance.

    Regards,
    Mistry
  • SystemAdmin
    SystemAdmin
    15496 Posts
    ACCEPTED ANSWER

    Re: Crosstab Total Issue

    ‏2010-07-27T23:28:47Z  in response to mistryd
    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.
  • fcl_cognos
    fcl_cognos
    1 Post
    ACCEPTED ANSWER

    Re: Crosstab Total Issue

    ‏2013-11-15T20:49:45Z  in response to mistryd

    Hi SystemAdmin,

     

    I am facing a problem for count distinct in crosstab using DMR. Can you please suggest some techniques in doing so.

Feed for this topic