Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  Creating Cognos report where total of one column is start for another column.

    Posted Mon March 11, 2024 10:38 AM

    Hi,

    I am trying to create a report where the total for one column becomes the start of the next column.

    Type Jan Feb Mar Apr
    Begin Bal 0 3349 5708 10534
    Tyoe A 123 561 4341 731
    Type B 83 1345 413 1354
    Type C 3143 453 72 4545
    End Bal 3349 5708 10534

    17164

    The data in the table is only for Type A,B, and C. The Begin Bal for Jan is 0. For Feb the Begin Bal is the End Bal for Jan (which is Total for the month) and so on for the next months.

    The data is coming from a relational model.

    Any ideas on how to develop thiis Cognos Reports?

    Thanks & Regards

    Sachin



    ------------------------------
    Sachin Kandalgaonkar
    ------------------------------


  • 2.  RE: Creating Cognos report where total of one column is start for another column.

    Posted Tue March 12, 2024 03:46 AM
    Edited by Jens Bäumler Tue March 12, 2024 07:09 AM

    Hi Sachin.

    At the moment I can only think of one solution for a dimensional model. Maybe we should convince IBM to better support dimensional functions in data modules as well ;-)

    start:

    total( [Menge] within set periodsToDate( [Absatz (Analyse)].[Zeitangabe].[Zeitangabe].[Zeitangabe] ; prevMember(currentMember( [Absatz (Analyse)].[Zeitangabe].[Zeitangabe] ) ) ) )

    end:

    total( [Menge] within set periodsToDate( [Absatz (Analyse)].[Zeitangabe].[Zeitangabe].[Zeitangabe] ; currentMember( [Absatz (Analyse)].[Zeitangabe].[Zeitangabe] ) ) )

    You can download the Report XML from my blog post: Kreuztabelle mit einer Start- und Endsumme der vorherigen Zeilen und Spalten (Cognos Reporting) - cognoise.de

    Here the relational model solution using running-total" :

    End: running-total( [Enrollment] )

    Begin: [End]-total( [Enrollment] for [Year] )


    ------------------------------
    Jens Bäumler
    Senior Consultant and Trainer
    Apparo Group
    ------------------------------

    Original Message


  • 3.  RE: Creating Cognos report where total of one column is start for another column.
    Best Answer

    Posted Tue March 12, 2024 07:16 AM

    Relational XML is added: Kreuztabelle mit einer Start- und Endsumme der vorherigen Zeilen und Spalten (Cognos Reporting) - cognoise.de



    ------------------------------
    Jens Bäumler
    Senior Consultant and Trainer
    Apparo Group
    ------------------------------

    Original Message


  • 4.  RE: Creating Cognos report where total of one column is start for another column.

    Posted Tue March 12, 2024 08:49 AM

    Hi Jens,

    Thank you very much. The solution for relational model worked.

    Sachin



    ------------------------------
    Sachin Kandalgaonkar
    ------------------------------

    Original Message


  • 5.  RE: Creating Cognos report where total of one column is start for another column.

    Posted Tue March 12, 2024 04:39 AM

    Are you using Framework Manager or a Data Module? This is one of those cases where building a quick DMR model will make this infinitely easier. 

    If you have to use the relational, having a limited number of types will make this a little easier. 
    First, if you don't already have it, make a separate data item for each type. In my example I'm using Order Method Type, so I'll have a bunch.

    case [C].[C_Great_outdoors_sales_data_module].[page_1].[Order_method_type] 
    when 'E-mail' then [Revenue] 
    else 0 
    end

    Setting the detail and summary aggregation total. Repeat for all types

    Next create a data item to calculate the moving total minus the current total, this gives you the beginning balance you're looking for:

    moving-total([Revenue],2) - [Revenue]

    We can extend that out to get things like variance if needed

    [Revenue] - [Beginning Revenue]

    [Variance] / [Beginning Revenue]

    The crosstab should look like this:

    And when run we see:

    The only reason this works is that the query returns a single row per month. If we had nested Order method type, it won't work anymore

    I've experimented a bit with different forms using for, but nothing works well, especially as there are some gaps in the data (e.g., Special/February).

    In general I find crosstabs work best with OLAP or dimensionally modelled data, which is one of the reasons I'm constantly pushing IBM to include some form of DMR modelling in Data Modules.



    ------------------------------
    Paul Mendelson
    ------------------------------

    Original Message


  • 6.  RE: Creating Cognos report where total of one column is start for another column.

    Posted Tue March 12, 2024 09:29 AM

    Hey Jens and Paul, 

    Doesn't the relational solution look nice and handsome for Sachin's case? ;-) 

    Paul, isn't the non-hierarchical nature of navigation paths the main reason why data modules won't be really DMR? 

    Best regards, 

    Philipp 



    ------------------------------
    Philipp Hornung
    Business Intelligence Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------

    Original Message


Related Content