Cognos Analytics

 View Only
  • 1.  Running-total causing error after upgrading to 11.1.7

    Posted Wed April 14, 2021 01:48 PM
    We are moving from 11.0.11 to 11.1.7 and have run into an error in one report that we ported over.
    Through the process of elimination I have narrowed down the issue to an expression that uses running-total.
    The full expression is:
        running-total(count(distinct([C].[Site_Performance_DM_Revs_CSP033].[dat_aqu_35676].[queryid])
    for [Last Modified Substring]))

         'Last Modified Substring' = If ([C].[Site_Performance_DM_Revs_CSP033].[dat_aqu_35676].[revision] = 1)
    THEN(substring([Lastmdt],1,7))
    ELSE (Null)

         'Lastmdt' is a date

    If I remove running-total then the report runs fine.   Otherwise, I get the error:
     'Query Service internal error has occurred, please see the log for details.' 

    All the data in the report is from a single source (Hive database).

    I have tried removing running-total and adding it back in by selecting it from the list of function in 11.1.7 -  still get the error.

    ------------------------------
    Ethan Davis
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Running-total causing error after upgrading to 11.1.7

    Posted Wed April 14, 2021 03:52 PM

    You don't say what errors you observed getting generated for the report when you looked at the log files.  That might be the first place to go to.   Otherwise you would not have much more data to form an hypothesis about the cause of the problem other than what you have already.  It is possible that there's a data type mis-match in your expression. 



    ------------------------------
    IAN HENDERSON
    ------------------------------



  • 3.  RE: Running-total causing error after upgrading to 11.1.7

    IBM Champion
    Posted Wed April 14, 2021 04:27 PM
    Try to modify:
    'Last Modified Substring' = If ([C].[Site_Performance_DM_Revs_CSP033].[dat_aqu_35676].[revision] = 1)
    THEN(substring([Lastmdt],1,7))
    ELSE (Null)

    To:
    'Last Modified Substring' = 'foo'

    If this does not give an error. Then you are getting closer. You might replace null with a string.

    We hit something similar upgrading from 11.0.x to 11.17 using if-statements in filters.


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



  • 4.  RE: Running-total causing error after upgrading to 11.1.7

    Posted Thu April 15, 2021 12:52 PM
    Thank you Ralf.
    When I update 'Last Modified Substring' to substring([Lastmdt],1,7)
    then I no longer get the error. 
    However, my running total is now counting items of all 'revisions' not just where it is 1.
     
    The query has a filter for [C].[Site_Performance_DM_Revs_CSP033].[dat_aqu_35676].[revision] = 1.  (Before Auto Aggregation)
    but it seems to be ignored by the 'Last Modified Substring'
     
    Any thoughts on why the filter doesn't seem to be working?
     
    Thanks






  • 5.  RE: Running-total causing error after upgrading to 11.1.7

    Posted Thu April 15, 2021 07:32 PM
    Try by separating each calculation in its own query item.
    For example, [QryItem 1] = count(distinct([C].[Site_Performance_DM_Revs_CSP033].[dat_aqu_35676].[queryid]) for [Last Modified Substring])
    AND [QryItem 2] = running-total( [QryItem 1] )

    We faced similar issues with date calculations in extracting year using _year() function and solved it by separating the calculations in each query item.


    ------------------------------
    Muhammad Yahya
    ------------------------------