RandBetween

[Prakash Bhai]

Hi All,

Is there any Randbetween function kind of thing in Cognos.


Regards

Abhi

[CognosPaul]

There is no native randbetween in Cognos, but you can use database functions for this.

First question, which database are you using? My examples will only be applicable for SQL Server 2005.

If you need a single random value for the entire list you can use

Code Select Expand

#prompt('Low','integer')# + { RAND()} * (#prompt('High','integer')#-#prompt('Low','integer')#) Note the passthrough SQL brackets.

If you need a random value for each row returned you can use the slightly more complex

Code Select Expand

#prompt('Low','integer')# + { RAND(CAST(NEWID() AS BINARY(6)))} * (#prompt('High','integer')#-#prompt('Low','integer')#) If you run it against a long list you'll notice the results aren't 100% random though.

The caveat is that it needs to be in a query being run against the database. You'll get an error if you try to run that alone in a query.

[Prakash Bhai]

Hi Paul,

Thx for the reply. Well i am using DB2 as my DB. Can i use the same functionality for DB2 also r it is only applicable for SQL....

Regards
Abhi

[CognosPaul]

I'm not familiar at all with DB2. A quick google search shows there is a RAND function.

Try using

Code Select Expand

#prompt('Low','integer')# + RAND() * (#prompt('High','integer')#-#prompt('Low','integer')#) in a data item. The logic is simple: ((random number * (High - Low) ) + Low) will act as randbetween. The hard part is just finding the correct DB2 function to return the random number.