Hello,
I am trying to figure out how to get random number of records to show up in the report based on another report.
For example:
I have a summary report that tells me the count of records for each field.
I then calculate a smaller percentage of those numbers and call it random. I then need to pass this number into a detail
report, but every time I run the report, I need to get the detail report for the number that was passed from the summary report and every time it will have to be different(or random) records.
Here is my code example using movies file:

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';

TABLE FILE ibisamp/movies
SUM 
CNT.TITLE
COMPUTE 50_percent/D12.2%=50 ;
COMPUTE random_number/D12=( 50_percent * CNT.TITLE )/100 ;
BY CATEGORY
WHERE RATING EQ 'NR';
WHERE CATEGORY NE 'MYSTERY';
ON TABLE PCHOLD FORMAT XLSX OPEN
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Summary', SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $
ENDSTYLE
END

-RUN

TABLE FILE ibisamp/movies
PRINT TITLE
BY RATING NOPRINT
BY CATEGORY
WHERE RATING EQ 'NR';
WHERE CATEGORY NE 'MYSTERY';
ON TABLE SET COMPOUND BYTOC 
ON TABLE PCHOLD FORMAT XLSX CLOSE
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='', SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $
ENDSTYLE
END

-RUN

The summary tab contains Category, Title Count, percentage and random_number.
So for example for Children category I have 5 title counts. If I take 50% of number 5 and round it up to the nearest integer I get 3(2.5 rounded up).
I now need in the detail report tab for Children Category to show 3 Titles. But every time I run it I need to show RANDOM 3 titles.
And so on for all other categories. For Classic Category I need to show 5 random titles in the detail tab. And so on for each of the Rating. In this example I am limiting to one rating, but I will need to show all of the Ratings in both the summary report and each tab will be its own Rating(BYTOC).

Thank you.

This message has been edited. Last edited by: nickz,

Am I understanding correctly that you mainly want a random read from the database (movies in this case)? The more titles you have, the more random records you want?

No, not exactly.
I want to get the real count from the database, then take a smaller percentage of that count and use that number as a random sample of records.
So in my example the real count of Category Children is 5, the real count of Category Classic is 10 and the real count of Train/EX is 5. So the smaller percentage of lets say 50% would be 3,5 and 3 if rounded up to the nearest integer. So those are the random number of titles that I want to see on the detail tab. 3 random titles for children, 5 random titles for Classic and 3 random titles for Train/Ex.

Hi nickz,

Not sure if this will help, but this is how I used to pull extracts of random records.

Assign a random number to each record ... hold the records ... resort the file by the random number ... then extract however many records for your random list.

Here's a sample using the CAR file ...

-*****************************************
-**     THIS USES FUNCTION = RDUNIF     **
-*****************************************
-* CREATE HOLD FILE FROM CAR
-*
-DEFAULTH &MAX_RAND = 3;
-*
TABLE FILE CAR
PRINT
COMPUTE CNTR/I5 = CNTR + 1;
COMPUTE RAND/D12.8 WITH CAR = RDUNIF(RAND);
BY CAR
ON TABLE HOLD AS HLD1
END
-*
TABLE FILE HLD1
PRINT *
END
-*
TABLE FILE HLD1
PRINT
COMPUTE RAND_CNTR/I5 = RAND_CNTR + 1;
CAR
CNTR
BY RAND
ON TABLE HOLD AS HLD2
END
-*
TABLE FILE HLD2
PRINT *
END
-*
TABLE FILE HLD2
PRINT *
WHERE RAND_CNTR LE &MAX_RAND
END
-EXIT

Interesting puzzle. Try the following code, see if it does what you are looking for.

-DEFAULT &PCT = 50;
 TABLE FILE ibisamp/movies
      SUM 
  COMPUTE Limit/P9 = &PCT * CNT.TITLE / 100;
       BY RATING 
       BY CATEGORY 
    PRINT TITLE
  COMPUTE Random/P9.8 = RDUNIF(Random);
       BY RATING 
       BY CATEGORY
 BY TOTAL Random NOPRINT
 WHERE CATEGORY NE 'MYSTERY';
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS holdmovie
 END

 TABLE FILE holdmovie
   PRINT TITLE 
         Random NOPRINT
         Limit NOPRINT 
 COMPUTE Counter/P9 = IF LAST CATEGORY EQ CATEGORY THEN Counter + 1 ELSE 1; NOPRINT
      BY RATING NOPRINT
      BY CATEGORY
 WHERE TOTAL Counter LE Limit
 ON TABLE SET COMPOUND BYTOC 
 ON TABLE PCHOLD FORMAT XLSX
 END 

@dbeagan,
This is absolutely perfect.

Thank you so much.