Cognos Analytics

Expand all | Collapse all

Running-total causing error after upgrading to 11.1.7

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

    Posted 30 days ago
    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
    ------------------------------


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

    Posted 30 days ago

    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

    Posted 30 days ago
    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 29 days ago
    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 28 days ago
    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
    ------------------------------