Random selection of 300 in Business Objects

Now that was cool.

My boss said. I need you to make me a Business Objects Query. The KUL University asked us to provide a random selection of employees.

They need the selection to have the right percentage of Age groups in it.
The group should have the right amount of Men and Women in it
It should represent our departments and job functions proportionately.

You'll probably need two days to set this up.

I don't know much about statistics, but I do know that a random selection of 300 out of 9000, if the selection is truly random, I don't have to bother with the rest of that stuff, the groups will be properly represented.

So, I went ahead and made a query that gave me the Company's population.

9000 people
47% Women
53% Men

Then, I applied the following little trick -- and this is the purpose of this very article:

In Business Objects Query panel, I chose to write custom SQL and I then put this little piece of code there: at the very bottom of the SQL.

ORDER BY dbms_random.value (only for Oracle)

This will randomize the list.

Next, in the query's options, I set the option to only return 300 results.

15 minutes after my boss told me to start doing a 2-day job, I finished it.

Of course, I made the same percentage calculations and obviously, they returned the same results as the master list.

If you need to do this in other DBMS systems, the code is:



MySQL:

ORDER BY RAND()
LIMIT 1


Microsoft SQL:
ORDER BY NEWID()

Enjoy