Generating a Random Number for each row

[fastf00d]

I know there are a few threads around regarding this but I can't seem to find anything that works. In my report I have a list of results and as a column in that list I just want a random number to generated for each row, ideally so it can display the list in a random order and generates the list in a different order each time the report is generated too.

So far I've tried using rand() which just gives me the same random number, random(numbers,numbers) but I get an error message (stating The "Expression Engine" node "random" requires fewer operands) and using newid() but isn't a number (not necessarily a problem) but doesn't generate a different set of rows on each time the report is generated.

So in short I'm just trying to generate a different list of random results each time, does anybody have any suggestions that may help me achieve this, many thanks in advance :)

[Lynn]

I know there are a few threads around regarding this but I can't seem to find anything that works. In my report I have a list of results and as a column in that list I just want a random number to generated for each row, ideally so it can display the list in a random order and generates the list in a different order each time the report is generated too.

So far I've tried using rand() which just gives me the same random number, random(numbers,numbers) but I get an error message (stating The "Expression Engine" node "random" requires fewer operands) and using newid() but isn't a number (not necessarily a problem) but doesn't generate a different set of rows on each time the report is generated.

So in short I'm just trying to generate a different list of random results each time, does anybody have any suggestions that may help me achieve this, many thanks in advance :)

Presumably this is a relational source? The options may vary from one DBMS to another so if you mention what database you are using you might get more specific advice. I don't know if it is a standard thing or not.

I'm on DB2 and when I use rand() without any argument at all I get a different number every time I run. There is an optional integer argument allowed to see it, but it isn't necessary to get a different result in my case.

[fastf00d]

I believe it is a relational database yes and I'm using MS SQL Server (OLE DB) to provide the data. rand() does provide a random number now I can see but now I just need to get ecah row to generate a different random number.

[Lynn]

I believe it is a relational database yes and I'm using MS SQL Server (OLE DB) to provide the data. rand() does provide a random number now I can see but now I just need to get ecah row to generate a different random number.

I was getting a different number on every row and it was different every time I ran. The easy solution is to migrate off MS SQL to something else. hee hee  ;)

[fastf00d]

Well changing your database system isn't the easiest of things and is definitely not possible in this case. So any suggestions in relations to MS SQL Databases or any SQL commands that could be used (and where in Report Studio) might do the trick?

[Lynn]

Well changing your database system isn't the easiest of things and is definitely not possible in this case.

Sorry, I was making a sarcastic joke there.

So any suggestions in relations to MS SQL Databases or any SQL commands that could be used (and where in Report Studio) might do the trick?

Our Cognos samples are running on a SQL Server database so I had a play around to make up for my bad joke.

I got what I think you want by finding a way to generate a seed that is different for each row and for each run.
- Grabbing the seconds out of the date will give a different value every time you run, or so it will seem within the range of 0 to 60.
- I took the product number, cast as integer, for the varying by row aspect.
- Multiply the two together and you'll get a seed that is different per row and per run.

You will probably want to set your query to NOT use local cache in this case. I just created a list of products that sorts randomly by dragging in the product name and then creating the expression below as the column to sort on.

Code Select Expand


rand( extract ( second, getdate() ) * cast ( [Sales (query)].[Products].[Product number], integer ) )


[bdbits]

There is an oldie-but-goodie article on random number generation in SQL Server here.
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-set-based-random-numbers/

A short answer might be to include a data item like this expression, where you want the range between 0 and the last number (999 in this case) in the expression. If you want to allow negative numbers you can eliminate the abs() part of the expression. If you are particular about the "randomness" the article has more information to consider.

abs(checksum(newid())) % 999

Random number generation is actually a pretty interesting subject. Well, interesting to some of us anyway. The basic problem is that computer algorithms are inherently deterministic, so how you do you make a random number which by definition is non-deterministic? There have been quite a number of interesting approaches to the problem, most of which involve using something external as a seed value to a pseudo-random number generator. For example, the now-defunct company Silicon Graphics (SGI) used to offer a service based on a lava lamp. No really, I am serious. There is a still active service that is based on atmospheric noise at https://www.random.org/. Or just google random number generation and jump into the rabbit hole for hours of fun.  8)