Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  Complex Measures

    Posted Mon May 16, 2022 04:00 AM
    Hello IBM colleagues,

    I would like to know if you can do complex calculations like in Power BI with DAX?
    I am doing an exercise that I did in PBI in Cognos, but I can't do it because there are complex measures and I don't know how to translate them in Cognos.

    Mostly to do the (DAX): 
    - IF(HASONEVALUE
    - SUMX
    - CALCULATE
    - FILTER
    - SELECTEDVALUE
    - AVERAGEX
    - GENERATESERIES
    [...]

    When I say complex calculations, I mean this, for exemple:
    Cout_converti=
    IF(HASONEVALUE(REPORT_CURRENCY[Target Currency]),
    SUMX(VENTES,VENTES[Quantité]*VENTES[PR]*[Taux_Extract])
    + CALCULATE(SUMX(VENTES,VENTES[PR]*VENTES[Quantité]),
    FILTER(VENTES,VENTES[Devise]=[Devise_Select])))

    Have a nice week

    ------------------------------
    David GOMEZ PRIETO
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Complex Measures

    Posted Mon May 16, 2022 05:04 AM
    Hi David,

    of course IBM Cognos can do that. It depends on your datasource and studio used. In dashbaords with a relation datasource, you just have to right-click on a table or column and select "New/Calculation...". An editor window will pop up to write the expression. If it's an OLAP cube you have to right-click on a measure for a new calculation.

    In the editor window you can select many functions from the (f) button on tle left. When you select any function you'll get explanation text on the bottom right window pane.

    If you use reporting you have to go into the query and drag a new "Query calculation" into the query object list. The rest ist the same like in dashboards.


    Hope this helps,

    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------

    Original Message


  • 3.  RE: Complex Measures

    Posted Wed May 18, 2022 03:49 AM
    Good morning Robert,

    Thanks for the information, the truth is that I have used some functions, but I have not been able to make calculations as complex as with the "DAX" language of PBI.

    For example, make a calculation only if a value is selected "SELECTEDVALUE".

    I don't know if you have any site or something to document me or have clear examples, I can't find anything.

    Thank you very much

    ------------------------------
    David GOMEZ PRIETO
    ------------------------------

    Original Message


  • 4.  RE: Complex Measures

    Posted Wed May 18, 2022 06:18 AM
    Hi David,

    I don't know what SELECTEDVALUE does excatly but it sounds like a prompt macro in Cognos Analytics. Would be used in a report stating like "If a special value is selected or if no value is selected do option a or do option b".

    There's a documentation how to use macros and especially building prompts.
    https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=macros-add-query-macro

    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------

    Original Message


  • 5.  RE: Complex Measures

    Posted Wed May 18, 2022 12:39 PM
    Edited by System Admin Fri January 20, 2023 04:43 PM
    Hi David, 

    I hope you are doing well and that Robert could guide you already a little bit.

    I would like to take the opportunity to add some more information to that.

    "Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models." Source: https://docs.microsoft.com/en-us/dax/

    This is done in Cognos using functions, prompts and filters in the query generation.


    You said:
    When I say complex calculations, I mean this, for example:
    IF(HASONEVALUE(REPORT_CURRENCY[Target Currency]),
    SUMX(VENTES,VENTES[Quantité]*VENTES[PR]*[Taux_Extract])
    + CALCULATE(SUMX(VENTES,VENTES[PR]*VENTES[Quantité]),
    FILTER(VENTES,VENTES[Devise]=[Devise_Select])))

    I would translate that as follows:
    IF
        HASONEVALUE(  REPORT_CURRENCY[Target Currency])
    THEN
        SUMX(VENTES,VENTES[Quantité]*VENTES[PR]*[Taux_Extract])
        + CALCULATE(SUMX(VENTES,VENTES[PR]*VENTES[Quantité]),
    ELSE
        null

    The calculation that you reference contains SUMX (Documentation: https://docs.microsoft.com/en-us/dax/sumx-function-dax):

    • The SUMX function takes as its first argument a table, or an expression that returns a table. The second argument is a column that contains the numbers you want to sum, or an expression that evaluates to a column.


    Cognos offers a vast number of functions to calculate sums.

    Here is an example showing total() explaining for auto, for report, and so on:



    The filter part could go into the IF <condition> or could go into the FILTER Part of the query.   

    FILTER(VENTES,VENTES[Devise]=[Devise_Select])))

    The value of [Devise_Select] - looks like a Parameter that the user can select. So, that is a prompt, as Robert mentioned. 

    You could put the filter into the Query for the data adding the column to filter and ?QUESTION? ... The "?" marks a variable to be prompted to a user and use the user input for filtering.







    Apart from all the incredible data prep functions, you can add complex functions also directly in the layout ... here is how you add Layout Calculations in a Cognos Report:


    Let us know if we were able to solve your questions.


    ------------------------------
    Ralf Roeber
    https://linkedin.com/in/ralf-roeber/
    ------------------------------

    Original Message


Related Content