Recent Posts

Pages: [1] 2 3 ... 10
Reporting / Re: If -then- else statement error
« Last post by Kiran P on 16 Dec 2017 08:36:21 pm »
Hi ,

I am trying to use a detail filter on a report where 'pVersion' is a parameter and 'abc' , 'def' are attributes of a dimension in the cube. The statement below works fine.

If(?pVersion? = 'budget') then ( [abc]=1)
else ([def] =1)

But when I add another condition to the if statement it gives me 'XQE-PLN-0267'error

If(?pVersion? = 'budget' and ?pYear? =2017) then ( [abc]=1)
else ([def] =1)

I have tried using data items for version and year instead of parameter, working around with brackets also but nothing works.

Please advice what's wrong in the 2nd if statement.
I am working with cognos Analytics and  TM1 cubes as database.

Thank You.

I am not sure why there was an issue but you can try replacing it with nested if.

If(?pVersion? = 'budget' )

        if(?pYear? =2017)
             then ( [abc]=1)
             else ([def] =1)



Sent from my ZUK Z2132 using Tapatalk

Reporting / Re: If -then- else statement error
« Last post by cognos4321 on 16 Dec 2017 11:03:45 am »
Please let me know if there is some other information needed.
I have tried replacing it with case statement also but nothing works.

TM1 / Re: "Goto" type functionality in TI process ?
« Last post by AJAYC on 16 Dec 2017 09:59:44 am »
There is no GOTO function, however I like to split this out. If you've got code of about 2000 lines, I'd keep these in another process, so have something like this:

# Process01 applies to all values of vTest_value
ExecuteProcess ( "process01".........) ;

# Process02 applies to all values except 'A' of vTest_value
IF ( vTest_value @<> 'A' );
     ExecuteProcess ( "process02".........) ;

The two processes called represent the what you wish to happen.

TM1 / Re: IF syntax question...
« Last post by AJAYC on 16 Dec 2017 09:51:59 am »
This is correct, there is no "IN" clause.

You need to test your items within the brackets individually against vTestvalue. You can do this simplistically eg.

IF (
       ( vTestvalue @='ABC' %
         vTestvalue @='XYZ' %
         vTestvalue @='MNL' %
         vTestvalue @='rrr' %
         vTestvalue @=ZZZ' )
         vType @= vTestType2 ) ;

         DimensionElementComponentAdd (sDim, vConsolidation, vTestValue, sWeight) ;


The "%" represents an "OR" function. I noted you missed a parent value, so have added "vConsolidation" in your DimensionElementComponentAdd statement.

Reporting / Cognos section fitlering all columns
« Last post by prasadloke on 16 Dec 2017 06:01:00 am »
I have a requirement to create a crosstab report which spans upto 9 pages.

My datasourcse is tm1 dimensional cube.

My rows are same across all 9 pages only difference is that my value of crosstab changes across 9 pages.

So i created a set of 9  values that is set([sales volume], [contribution] etc.)

and created a section report on the basis of that.

My columns are VERSION of actual, budget.

So it filters perfectly on the basis of set created.


I was asked to add different columns for different values in set section.

 that is volume to sales and contribution to sales which are calculated in tm1.

If I drag those elements in the crosstab, the values retrived are null since the section filters all columns including contribution to sales.

If I nest it separately.

             columns -
             contribution    |  Contribution to sales
            Actual | Budget| Actual

the values are retrived correctly.

But If I sectionwise on contribution the value for contribution to sales is retrived as null

since it is taking in the context of contribution of contribution of sales and actual.

Has anyone came across this issue?

I really don't want to create 9 pages  :'( :'(
Reporting / Nesting Union Query in Join/Select
« Last post by therese1 on 16 Dec 2017 05:43:59 am »
Hi I have set up my query as per below, I am stuck on where to put union query so I can filter it... code is ok until I add this.

Code: [Select]
select distinct

from DMS_PRC_MN a
join (
    select CTRT_CUST_CD,ORG_LOC_DEF_CD,DEST_LOC_DEF_CD, count(*) as qty
    from DMS_PRC_MN
    having count(*) > 1
union ALL
 from DWS_SP_HDR) c on a.PROP_NO=c.PROP_NO
where a.CTRT_EXP_DT>=sysdate
Many thanks for the information.
Reporting / Re: Joining Queries
« Last post by MichaelB on 15 Dec 2017 02:33:23 pm »
You possibly shouldn't need to do two queries for this. But, as far as your crazy high numbers, you obviously have duplication going on. Multiple rows of the same Sales Rep exist in the first query by month I presume. And when you join that to the second query (where you might only have one row for the entire year) your resulting joined query will multiply the Year sales figure by the number of months that exist for that Sales Rep. Twelve months in a year, so potentially how you have it set up, it could be multiplying that YTD number by 12.

So, in order to not use two queries, because it sounds like you are basically using the same data, but segmenting it differently, you can use this fancy little treat in a data item: total([Sales] for [SalesRep], [Year]). The "for" clause helps sum your totals by specific segments.
Reporting / Casting as date, or using current_date displays wrong date
« Last post by MichaelB on 15 Dec 2017 02:15:02 pm »
An issue that we've been dealing with for a while now is that when using the cognos function current_date, we get a date that is two days prior to the actual current_date. For instance today is 12/15/2017. The date that would be displayed would be 12/13/2017.

Typically, we use the SQL getdate() function which is passed to the server and the correct date is given so even though this exists, it hasn't been much of an issue.

However, today I discovered that using cast( 'sometext' as date) does the same thing. While trying to track down another bug (read: feature) I used this function like this: cast('2017-11-01' as date) and stuck it in a data item. It displayed as Oct. 30, 2017.

Any idea if this is an existing known issue, or what in the world would cause this?
Why not to create a table with one row and multiple columns. Each column represents a prompt value. The table can be loaded on demand via ETL. Cognos report will read prompts from the table and make them available to all logically connected reports (ie Apple, Samsung...).

Just my 2c.
Pages: [1] 2 3 ... 10