Cognos Analytics

Cognos Analytics

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


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only

  • 1.  Dynamic variance report - PA cube source

    Posted 03/24/22 10:48 AM

    Experts,

    I have a bit of a complex request from a client.  They would like to have a dynamic variance report that has time and the variance between periods as the columns.  They want to choose a Month view or a Quarter view.  Either view will have the current months or quarters then the prior year months or quarters then the variance between the respective periods (CY month or qtr 1 - PY month or qtr 1, CY month or qtr 2 - PY month or qtr 2).  Getting the current and prior periods is no issue, there are subsets for these and the use of the prompt macro differentiates that.  The problem is the variance columns.  We are trying to stay away from having to create a query object for every single possible combination but don't know how to dynamically determine "CY Month 1" and "PY Month 1" to then get the difference.

    Has anyone else addressed this?

    Thanks



    ------------------------------
    Dean Watts
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Dynamic variance report - PA cube source

    Posted 03/24/22 11:13 AM
    Hi Dean,

    Can you use the cousin function to get those calculations done? If I understand you correctly the report should look like this:
    In this case I would insert the current periods under "variance" + a hidden measure calculation like
    tuple ([measure],currentMember(Time Dim)) - tuple ([measure],cousin(currentMember(Time Dim);prevMember(parent(currentMember(Time Dim)))))
    Sample (If I got u right):

    Let me know if  this is what you expected.

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



  • 3.  RE: Dynamic variance report - PA cube source

    Posted 03/25/22 08:04 AM

    Robert,

    Thank you for your input on this.  Can you give me a better understanding of the "hidden measure"?  Is the calculation information you provided in the variance query object?  Are they in their own query object that is positioned under/beside the variance, current periods on the report?  Hidden by setting the rendering to 'None'.  Not familiar with this type of approach and it sounds interesting.

    Thanks,

    Dean



    ------------------------------
    Dean Watts
    ------------------------------

    Original Message


  • 4.  RE: Dynamic variance report - PA cube source

    Posted 03/25/22 10:23 AM
      |   view attached
    Hi Dean,

    attached you'll find the sample report. There's nothin hidden in the report. "Variance2 is the calculation depending on the relative position of the member to its cousin and below are the time members where we want that to be computed.

    I also have to admit that "variance" can be different things. I assumed you wanted the deviance (A - B) but you can also compute the variance but then the setup has to be changed to an aggregation function with a member set.


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

    Attachment(s)

    txt
    report_spec.txt   5 KB 1 version
    Original Message


  • 5.  RE: Dynamic variance report - PA cube source

    Posted 03/25/22 10:25 AM
    I forgot: if you want to use the sample report you need the IBM base samples "Great Outdoors DWH". Available in the installation files of Cognos Analytics.

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

    Original Message