Similar to Brio

[Abhi1]

Hi Everyone,
In Brio we have a function called Prior(). Is there any function which acts in same manner in Cognos.

Regards,
Ram.

[MFGF]

My crystal ball is not functioning today.   
Can you give us at least a tiny clue about what the function does?

MF.

[PaulM]

I loved Brio, especially when you have lots of tracks and bridges and trains. Ahhh, youth.

The prior function would return the value in the previous row of the selected column.


ID | A | B
01 | 1 |
02 | 2 | 1
03 | 3 | 2

and so on.

A running function might work for numeric values, but it's not so useful for text. Can you share what you're trying to achieve?

[Abhi1]

Thanks Paul and MFGF.
Well I have a piece of code which was actually developed in BRIO using Prior() function.

if (Emp_Stat_Cd == 'PT' ) { 24}
 if ( Emp_Stat_Cd == 'RG'  || Emp_Stat_Cd == 'TE') { 40}
if (Prior ( Unique_Number )  == Unique_Number) {0}

this functionality , I need to develop in cognos.

Regards,

Ram.

[MFGF]

Thanks Paul and MFGF.
Well I have a piece of code which was actually developed in BRIO using Prior() function.

if (Emp_Stat_Cd == 'PT' ) { 24}
 if ( Emp_Stat_Cd == 'RG'  || Emp_Stat_Cd == 'TE') { 40}
if (Prior ( Unique_Number )  == Unique_Number) {0}

this functionality , I need to develop in cognos.

Regards,

Ram.

Sorry - other than Paul (who has a brain the size of a planet!  ), we don't know much about Brio on these forums.  I can see the code you have posted above, but you haven't explained what it is supposed to do.  We might be able to help if you can tell us what it is you want to do in your report.

MF.

[PaulM]

Brain the size of a planet, and they ask me to play with BRIO Toys...


It's been a few years since I've worked with BRIO. So I might be reading it wrong, but try something like:

case [ns].[Emp_Stat_Cd]
   when 'PT' then 24
   when 'RG' then 40
   when 'TE' then 40
   else
      case
         when moving-total ([ns].[Unique_Number],2) - [ns].[Unique_Number] = [ns].[Unique_Number]
            then 0
            else 1234
      end
end

[Abhi1]

Hi Paul,
I have implemented the code as per your guidelines like this
case [ns].[EMP_STAT_CD]
   when 'PT' then 24
   when 'RG' then 40
   when 'TE' then 40
else
case
  when moving-total ([ns].[UNIQUE_NUMBER],2) - [ns].[UNIQUE_NUMBER] = [ns].[UNIQUE_NUMBER]  then 0
            else ([ns].[UNIQUE_NUMBER])
      end
     end
But It is throwing an error saying, Incompatible Case Statement.

[PaulM]

A couple things.

1. Did you copy and paste directly? the [ns] should be replaced with your namespace, (drag the field directly in).

2. Is UNIQUE_NUMBER a numeric field? If not then this method won't work. Look at this post.

If it's neither of those issues, can you give more details on the error thrown?

[Abhi1]

Hi Paul,
1)I have not copied the code as it is, In the Forum I have mentioned with ns.
2)UNIQUE_NUMBER Is not a numeric field,It is a string which is like this (107624Ireland2010-01-01)
Regards,
Ram

[PaulM]

The error was caused by the UNIQUE_NUMBER being a string. In my example I suggested using the moving-total function, which would only work for numeric types.

If you're using Oracle, you can try writing a pure SQL statement in order to use the Lag function. Otherwise you'll have to find another way to handle this, can you think of another way of trying to achieve your goal?

[Abhi1]

Hi Paul,
It is a string and we use DB2 Db. And Earlier I have used Lag and Lead()functions to achieve this..

Regards,
Ram

[georgia]

Hello,

I know it is been a while but Can you pls tell me if you have found a solution?? I am looking for the same.. In Brio I use the next function to flag if one line is different then the other one..

[Abhi1]

Hello Georgia,

I couldn't find any logic for that... till now

Regards,

Ram

[georgia]

  Thanks anyway