Cognos Analytics

Expand all | Collapse all

Trying to have column show summary of counts instead of individual count on multiple rows

  • 1.  Trying to have column show summary of counts instead of individual count on multiple rows

    Posted Wed June 03, 2020 02:20 PM
      |   view attached
    ​I have a report that includes several grouped columns and calculated columns to return a count of customers based on a category type for current month -1, etc.  I haven't been able to get the count to aggregate for the category types; instead the report displays the counts on multiple rows. The number is correct if I add them together, but I'd like to have a single row that includes total customer count for each category type.  I've tried changing the detail aggregation to different options (Calculated, Summary, etc) but nothing I have tried changes the output. 

    Does anyone know how I can accomplish this?

    The area highlighted in the attached screen shot show multiple rows that add up to 6 total for OTH1. What I'd like to see if a single row with the total of 6 for OTH1.












    ------------------------------
    Betina Stapleton
    ------------------------------


  • 2.  RE: Trying to have column show summary of counts instead of individual count on multiple rows

    Posted Wed June 03, 2020 03:27 PM

    Hi Betina,
    There could be a couple of reasons:
    1 - set the query property: auto-group-and-summarize to yes
    2 - does the layout element of the list have a properties collection defined that is adding an extra data item ?
    If you look at the generated Cognos SQL, is there an unexpected entry in the GROUP BY list ?
    I could have a look at the report spec, you can email it to: henk.cazemier@ca.ibm.com

    // Henk



    ------------------------------
    HENK CAZEMIER
    ------------------------------



  • 3.  RE: Trying to have column show summary of counts instead of individual count on multiple rows

    Posted Thu June 04, 2020 12:11 PM
    Hi Betina,
    I think you need to add a "for" clause to your Count() calculation to associate it with the group level, in this case the Type2 column, so count([customer] for [Type2])

    ------------------------------
    Albert Valdez
    albert@intelalytics.com
    ------------------------------



  • 4.  RE: Trying to have column show summary of counts instead of individual count on multiple rows

    Posted Thu June 04, 2020 12:42 PM
    Thanks Albert - I had already give that a try.  Was my first attempt but still shows detail rows and not totaled when using the FOR clause.  ​

    ------------------------------
    Betina Stapleton
    ------------------------------