BusinessObjects Board

Random records

building semantic layer
#1

Hi,

We have a requirement, users want to be able to access/pull random records (like maybe 100 records at a time) in the report. How can I achieve this?

Thanks
:? :expressionless:

eye_candy :us: (BOB member since 2005-03-18)

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2
Random selection of record set
#2

Thatā€™s an odd requirement :slight_smile:

There are various ways to pull random records with SQL syntax. See this post for methods:

My first thought would be to find a place to put an order by hint of some sort, but I canā€™t recall where/how one would do that. (I donā€™t have a Universe Designer installed just yet)

You could always use ā€˜custom sqlā€™ :-1: to add it yourself to a given report, but I hate custom sql queries, especially if users need to modify the report at a later date.

Hope that points you in the right directionā€¦

JPetlev (BOB member since 2006-11-01)

#3

Canā€™t you do this under query properties, set it to return x amount of rows?

Mak 1 :uk: (BOB member since 2005-01-06)

#4

Thanks JPetlev & Mak1,

The record number can change. The user wants to see only 10 random records at a time , next time maybe 50, etc. Is it possible to do this?

Oracle 10g, BOXI R3

Thanks! :?

eye_candy :us: (BOB member since 2005-03-18)

#5

Very tricky. What database are you going against? I saw a few solutions for this in SQL Server a couple years back, but Iā€™d have to Google for them again. Iā€™d assume the process would be similar for other databases.

You could always just do what any computer does. Generate a complex polynomial, and recursively feed it numbers. Use the seconds (or microseconds) value from the sysdate as your seed value.

The tricky bit then comes from either using the random number as a filter (e.g., where MyPrimaryKey MOD MyRandomNumber = 2), or generating a set of 100 random numbers and manipulating them to use as a filter on the original table (in SQL, you would put the random numbers into a temp table, and join the temp table with the original query, but thatā€™s not possible with BOXI).

Lugh (BOB member since 2009-07-16)

#6

If on Oracle, try using DBMS_RANDOM package in a way similar to this:

SELECT   ... your columns from table A go here ...
  FROM   (  SELECT   DBMS_RANDOM.random AS random_number,
                     ... your columns from table A go here ...
              FROM   table A
          ORDER BY   1)
 WHERE   ROWNUM <= @prompt('number of random records','N',,mono,free)

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#7

Sampling is - if my memory is correct - a standard feature in 3.1.

Dave Rathbun :us: (BOB member since 2002-06-06)

#8

Ahhh so you really need TWO Features here:

  1. One feature to produce random records rather than a top #
  2. One to allow the user to specify the amount of returned records at runtimeā€¦

Which viewer do your users use? WebI (Rich or Thin), Deski, Crystal? That might narrow down what options you have available to even look at.

JPetlev (BOB member since 2006-11-01)

#9

Deski R3 :expressionless:

eye_candy :us: (BOB member since 2005-03-18)

#10

I there a way to get random 30 rows for every City for example ?

Thanks

Yoav

yohab (BOB member since 2003-12-24)

#11

Please follow Marek Chladny solution. It works.

eye_candy :us: (BOB member since 2005-03-18)

#12

Hi Marek,

Database: Oracle

     I have a similar requirement but in my case I need only 10% of the total records for a given MONTH. Any help would be appreciated.

dk1030 (BOB member since 2011-01-18)

#13

Hi,

Then change the last condition to something like this:

ROWNUM <= (SELECT Count(*)/10 FROM table A )

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#14

After posting my question I tried similar solution to what you have posted here and it worked. Thank Marek for the quick reply.

dk1030 (BOB member since 2011-01-18)

#15

just set the number of rows restriction in the query properties or you can set at the universe level too

erik.stenson :us: (BOB member since 2012-07-30)

#16

[quote:2d1797f4d9=ā€œerik.stensonā€]just set the number of rows restriction in the query properties or you can set at the universe level too
[/quote]

Thanks Erik, I tried the same (please see the attached pic) in the Universe (IDT - 4.1SP3) & in the Webi but it didnā€™t work the way I expected it to work so I went with an alternate solution of writing a SQL. The setting in Univ is not giving me 10% of the total number or records (in my case it would be 38 approx out of 386 for this month) but instead it is always giving me only 10 random records. I changed the number to 20 and its returning 20 random records.

I looked at the SQL that is generated when I used this SAMPLE setting in Univ and it looks like this (posted only part of the SQL here)

ORDER BY
dbms_random.value )
WHERE ROWNUM <= 10

Please let me know if there is anything different that I need to do.
Sample.JPG

dk1030 (BOB member since 2011-01-18)

#17

Hi,

There is no setting either on the universe side or on the report query side where you could configure the percentage of random records to be retrieved. Only a hardcoded number of records, as you already found out.

Marek Chladny :slovakia: (BOB member since 2003-11-27)