Impromptu Random Result Generation

[jatmon]

Is there anyway that Impromptu can select a percentage of the results randomly.

For example, we have say 12,000 active employee's on the database, and we need to create a list to show 1% of these employee's, but the list must be randomly generated each time the report is run.

Thanks.

 

[griffindm]

Jatmon,

I've generally done this type of reporting by generating a random 'seed' number and then taking every nth number from that starting point to get the sample. Doing this depends on either a sequential number available in the database, or doing it programmatically in the selection of data into a temporary table. You could use the running count function in Impromptu to do this, but it may have performance issues.

n is determined from the sample desired divided by 100, as in i.e. 50% = 2 (every other row), 33% = 3, 25% = 4, etc.

Once you have a sequential number, use the mod function in the filter to see if the sequential value divided by n givies you an even result. Include only those rows giving this result.

It would not be too hard to do via stored procedures, but that is another level of expertise not many in the forum have.

Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com