Cognos Analytics

Expand all | Collapse all

Re: Crosstab showing percentages

  • 1.  Re: Crosstab showing percentages

    Posted Mon March 23, 2020 10:29 AM
    HI Everyone and thank you in advance for replying.

    i am using Cognos Analytics (LTS).

    i have build a report, a cross-tab, this following information is in this;

    • Date (grouped by month)
    • Category (departments in my team)
    • Measure = (the number of records completed by each team)

    I want to be able to report the activity in the measure as a percentage.

    I have tried to write the query expression but I keep getting errors, however I found and tried to apply the guidance as per the link I have supplied and I am not able to see the same options as detailed.

    I get to step 2. and the option to show values as a percentage is not there? 

    Thank you in advance

    David Willis

  • 2.  RE: Re: Crosstab showing percentages

    Posted Mon March 23, 2020 11:24 PM

    Hi David,

    The percentage operation that you're looking for is only available when you're using a dimensional source, e.g. an OLAP cube or a Dimensionally Modeled Relational source defined in a Framework Manager model.
    The UI of Reporting would look like:

    If you're using a relational source, modeled either with Framework Manager or as a Data Module. Then you can still accomplish what you need.
    Here is an example.
    Each axis contains a dimensional attribute. <Order Method En> and _year(Order Date) and there is one measure.
    Please note how it uses a nominator <Quantity> and a denominator;  total ( <Quantity> for <Order Method En> ), the latter is the total for each distinct <Order Method En>, i.e. it is a row_total.
    Apply a formatting option to the crosstab cell, I used percentage with 1 decimal

    The output looks like:

    Hopefully this helps.
    // Henk


  • 3.  RE: Re: Crosstab showing percentages

    Posted Tue March 24, 2020 07:48 AM
    Hi Henk,

    Thank you for getting back to me. i will review and apply the information as detailed.


    David Willis

  • 4.  RE: Re: Crosstab showing percentages

    Posted Thu May 21, 2020 12:36 PM
    Hi Henk,

    Referring to the solution you have provided. Is there a way that I can apply a filter in the crosstab on the column dimension without it affecting the total for this dimension, hence the percentages?

    For example, I choose to hide/exclude Sales Visit from <Order Method En> dimension without it resulting in a change in the denominator Total ( <Quantity> for <Order Method En> ). Thus the percentages for the respective <Order Method En> remaining same.

    Thank you.


    Jean Rudy

  • 5.  RE: Re: Crosstab showing percentages

    Posted Fri May 22, 2020 01:49 PM
    Hi Jean,
    If you filter out one of the members in the Order Method set, it will impact your aggregates if they are based on the local data item reference.  This differs based on whether you are using a relational vs. dimensional (OLAP) data source.  With OLAP source, this is a bit easier because you can represent your aggregate by using the parent member in the source to stack into the crosstab to represent the aggregate vs. an aggregate of details expression.  This way the parent member value will not change regardless of which detail members you show in the report.  In relational source, this is a bit more complicated.
    Hope that makes sense

    Albert Valdez