Random Record Selection Display 2

[BStatnick]

Software: Crystal Reports Version 8.0.1.0
SQL Server 2000 Database

Situation: We have an auditing application that generates review data based on answering yes or no questions, and we use CR to determine an accuracy score based on this data. A review may contain anywhere from 1 to over 220 questions. Using the Rnd() function, I am attempting to display a random sample of the existing reviews. For instance, if an individual has 50 reviews that represent 5% of their production for a month, I would like to randomly select 30 of those, which would represent 3% of the monthly work. I have formatted the background of certain fields to change when a review contains an error, thus allowing me to quickly run several iterations of the report and track how many times reviews containing an error appear.

Here is my dilemma: The reviews with the highest number of questions are consistently appearing as the last items on the report before I suppress the results. Specifically, one review with over 220 questions is always the last item, and never appears in the suppressed results. Since this review contains an error, the fact that it does not randomly appear in the limited results defeats the whole purpose of the report. The report is based on the joining of six tables, and I have turned off the “Perform Grouping on Server” option under the Database menu. If I remove the question details from the report and only look at the Reviews, the random selection works fine, but then I cannot obtain an accuracy score.

I usually am able to find a solution without the need for posting a question, but so far I have not found anything. Thank you all for all your wisdom and advice!

Bob S.

 

[synapsevampire]

Makes sense, since you're including the questions as part of the randomization.

Try basing the RND on the Review ID.

This might mean creating a main report which only has the distinct Reviews, and a subreport which has all of the data and is linked via the review. The main report would end up suppressed, you'd basically just copy your current report in as the subreport.

That way all things are even for the randomizer.

-k

 

[IdoMillet]

See my FAQ on this: faq149-3261

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[BStatnick]

Synapsevampire,

Thanks for the prompt response. I had tried using a subreport, but found the processing time to be too slow, as there are over 37,700 reviews in the table, and the subreport was cycling through each one. I'll try again tomorrow.

IdoMillet,

I owe you a belated Thank You, since I have incorporated some of the ideas from your FAQ! This seems to be one of those cases where I can get separate pieces to work, but combining them is the challenge!

Thanks again!

Bob S.

 

[synapsevampire]

Ahhh, well then I suggest that you address this on the database side, which is always my preference anyway.

So you might create a SP or View to handle this.

SQL Server 2000 has a NEWID() function which is useful for random:

select top 5 table.reviewid from Table order by newid()

Now you can left outer the above to your recordset.

I tend to always create views rather than use Crystal's data expert as I want to promote reusability and simplify maintenance.

You can also create the above in a Crystal Add Command and then copy and paste your existing SQL in to left outer to if you can't create Views or SPs on the database.

-k

 

[7wheel]

I am new to this site so hopefully I'm doing this correctly.

I have a report that groups physicians and then displays patient identification numbers under each group.

I need to select 5 random patient numbers for each physician group for further research and verification of data.

I've tried the random function as mentioned above; but I'm getting 5 random numbers for all groups and not 5 numbers for each group.

Can you assist me in creating a formula to do that?

Thanks for any help.

 

[BStatnick]

Synapsevampire:

Thank you again for your help. I have had to put this project on the back burner for a bit,...sigh...

7Wheel:

It is recommended that you start a new thread for each specific issue. You should also provide the version number of Crystal Reports you are using, what type of database you have, and provide as much existing code as you can to assist others.

I know there is an FAQ on how to start a new thread, but I am not sure what the link is.

Bob S.

 

[lbass]

7wheel,

I think you could insert a group on physician, and then place the rnd() formula in the detail section. Then add the random formula as a sort field. Next insert a running total {#cntwingrp} where you choose count of {table.physician} (or any non-null field), evaluate for every record, reset on change of physician. Then go to the section expert->details->suppress->x+2 and enter:

{#cntwingrp} > 5

-LB

 

[7wheel]

lbass --

Kudos! This worked perfectly! Thanks for your help. We're using Crystal 10.0 as report writer for new decision support system and have much to learn.