Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  Problem with data type and aggergare function

    Posted Fri September 04, 2020 08:26 AM
    Hi all,

    I have a problem with a data element that I have to create in Framework Manager using various steps. All runs fine in Framework Manager. There is no error shown when I check the model.

    STEP1_AllDays
    running-count (count (distinct [Model View]. [WorkingDay]. [Day] for [Model View]. [Calendar]. [Date]))
    - This function counts all days from a calendar that the calendar contains. Day 1 = 1 and today = 10,000

    STEP2_WorkingDays
    running-total (([Model View]. [WorkingDay]. [WorkingDayFlag]))
    - This function only counts the days that are also working days.

    Step 3
    CASE ([Model View]. [TransportationTime]. [DeliveryMode])
    WHEN ('S')
    THEN ([Model View]. [WorkingDay]. [STEP1_AllDays])
    ELSE ([Model View]. [WorkingDay]. [STEP2_WorkingDays])
    END
    - If the DeliveryMode is 'S' (Ship) the calculation between two Timestamp should include Holidays, Weekends and so on. In all other cases the calculation should include only working days.

    If I want to use the element that I created in Step 3 in the report, I get this error message:
    RQP-DEF-0177 Error performing operation 'sqlPrepareWithOptions' Status = '- 256'.
    UDA-SQL-0213 The data type of an argument is invalid for the aggregate function.
    I have already switched off all aggregation functions in Framework Manager. I have provided the calculated elements with CAST functions (char, varchar, integer) and no matter what I do, the above error message is displayed in the report as soon as I want to use the element from Step 3.
    Does anyone have any idea why?

    Thanks in advance
    Peter


    ------------------------------
    Peter Hess
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Problem with data type and aggergare function

    Posted Fri September 04, 2020 09:51 AM
    Can you try to set Governor to not use "With clause" or in report query property set "With Clause" to No?


    ------------------------------
    Buddhi Gupta
    ------------------------------

    Original Message


  • 3.  RE: Problem with data type and aggergare function

    Posted Fri September 04, 2020 10:15 AM
    Hi all,
    I found a very strange solution.
    The database returns "nothing" for "no working day" and "001" for "working day". The very first step is to create a case-when condition in which '1' is written instead of '001' and '0' is written instead of 'nothing'. Everything is fine for the Framework Manager model.
    I changed the case-when condition to 1/0 (without apostrophes) and published the package. The report is now running fine. :-)
    Unfortunately, the Framework Manager model is now broken.
    So I work in Framework Manager with apostrophes and deliver the package without apostrophes.
    @Buddhi
    I'll test your idea next week. Many thanks.


    Peter


    ​

    ------------------------------
    Peter Hess
    ------------------------------

    Original Message


Related Content