Report out random records

[jeffreygreen]

I have about 10,000 +/- 400 or so records that get uploaded to my COGNOS DW a month. Then monthly, I have to audit 25 of them, at RANDOM. So what I have been doing is dumping out all 10,000 to Excel, running EzQuant (random number generator) to give me 25 random numbers from 1 -10,000. Then I go beck to Excel, and pull those lines. Way too much work, I think.

Any way of having COGNOS simply run a report every month where COGNOS counts the total records entered the month before (I would use ENTRY DATE for that criteria) and then, from that subset of data, output an Excel spreadsheet with 25 random records??? It might actually be ok for me to be prompted for the Month number . . . but it is always the previous month,

Thanks!!

 

[blom0344]

Cognos itself has no such functionality, but the database you use may have such a function. I recall that in my DB2 days a function existed that generated a random value between 0 and 1.
Such a function could be used by multiplying, rounding and using it in a seperate query , for instance joining locally with rownumber of the actual data.

So with 10000 new records and DB2 as database:

SELECT (RAND() * 10000)
FROM SYSIBM.SYSDUMMY1;

25 values:

select temp.X from
(SELECT (RAND() * 10000) as X
FROM SYSIBM.SYSDUMMY1) temp
fetch first 25 rows only;

Ties Blom

 

[jeffreygreen]

Thanks . . . I am not a programmer, but have seen my IT people do some serious stuff with SQL . . . . . not even possible with SQL?

 

[blom0344]

My example has nothing to do with programming. It is SQL that can be added as view to a database or used to define a SQL subject in a Cognos framework (which can be regarded as a Cognos view) You do not state on which RDBMS your DW resides, so it is alittle hard to be more specific..

Ties Blom

 

[jeffreygreen]

We use Oracle for our DB

 

[blom0344]

select dbms_random.value(1,10000) as X from dual;

X will be a random value between 1 and 10000..

Ties Blom