Random Name Selection

[ReformedCoog]

Hello everyone!

First post! I'm fairly new to Cognos. We conduct random drug screens in my organization and have a report built with set positions who are to be included in the random selection. Right now we export the full list to excel, run some random number generator formulas to get a list of individuals to test. We only pull 20 names from the total list every month. More inefficient than it should be.

Is there a way to accomplish this task in Cognos? I've searched through various topics and others outside of this board, but I cannot seem to find what I'm trying to accomplish. I basically need the report to review all the eligible names and pull 20 random selections when ran. We are on Cognos 10.2.1 and pull from a SQL database.

On a side note, does anyone recommend any web resources/books that would help someone 'self-learn' Cognos Reporting?

[BigChris]

Hi,

On the face of it, that sounds quite simple, but the more you think about it the harder it becomes. You can use the RAND function from the SQL functions, but making that link up to a series of records in your report is more problematic. I'd be tempted to see what the function brings back (I haven't had time to try it myself), and then compare it to a calculated running-total(1) field. I haven't worked out if you'd still need to export the report...you could try playing around with filters either before or after auto-aggregation.

[ReformedCoog]

Applying the rand() function to a query calculation data item returns the same number for every item in the column. Not sure what or how to compare to a calculated running total. The goal is to export the report.

At the end of a day a manager will just run the report and export to save/print.

[AnalyticsWithJay]

You're on the right track.

1. Create a data item with a random function
2. Set the pre-sort property on the data item to Descending.
3. Add a filter such as Row Number <= 20 (there are many ways of doing this, including Rank(), running-count, etc).

What we're essentially doing is assigning random numbers, sorting them in descending order, and grabbing the top 20 records. You might want to set Use Local Cache to 'No' for a query of this nature.

[ReformedCoog]

Thanks CognoidJay.

I was able to complete the below steps (including turning off the local cache). Thanks for listing those. The issue appears to be with the rand() function or maybe SQL. All the individuals in the list have the same random number, none of them are unique. I can rerun the report and the random number generated is unique, but it applies the same number to all the individuals again. Thoughts on this why it is doing this?

[AnalyticsWithJay]

Hmm... Could you set the aggregation and rollup aggregation properties on the data item to 'None'? I wonder if you're seeing a summary value.

If you go to the query and go Run -> View Tabular Data, do you see the same number for all?

Jay

[ReformedCoog]

I set the aggregation and rollup to 'none' and the results were the same. Query-Run-View Tabular Data, all the numbers are still the same.

[AnalyticsWithJay]

Could you simplify the query to 1 data item and the other data item containing the random function, and generate the SQL for that query? I would like to look at the generated Native SQL.

Is your database MS SQL Server?