Sorting records while using rnd() formula

I've created a report that will be used as a worksheet for cycle counting.  It randomly selects 35 records out of a dataset of 6500+ by using the following:

Formula named @random-  int(rnd()*7000)  This is placed in the details section, far right (and I used white text so the user doesn't see it).  I added a running total that assigns each line a number called @rtotal0
I suppressed the detail section by using the formula {#rtotal0}>35
Everything works super, except that I can't sort the 'filtered' data by the alpha bin location, so they are all jumbled up.  The cycle counter will be running around the building if he tries to do the list in order.
Thought I would be tricky and force the sort by doing an 'order by' in my sql command, but strangely, that doesn't sort anything - but does blow away one of my warehouse locations and add one that normally doesn't appear in the dynamic parameter list.
Going into the sort expert and adding a second sort parameter after @random of part_location.location_id got me nowhere as well.

Any thoughts on how to get past the structure of having to do the sort on the @random formula?  I've seen some advice on the web regarding grouping by the formula and moving my data into the GF, but that doesn't appear to solve the problem, simply jacks up the data results.

Thanks in advance!
LVL 1
Tina KSystems SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
What are you trying to do with the report?

What do you have in the sort expert?

mlmcc
James0628Commented:
As I understand it:
 The reports reads all of the 6500+ records.
 You sort by the @random formula to put the records in a random order on the report.
 You use a suppression formula in the detail section to suppress everything after the first 35 records.
 Now you want to sort those 35 records by a field in the data.

 Assuming that I'm correct, I don't think you can do that.  The problem is that the records were already sorted (by @random).  You haven't "selected" 35 records out of your dataset.  You're reading all of the records, sorting them (by @random), and then suppressing the extras.  So any sort that you add to that report is going to apply to _all_ of the records, not just those 35.  You can sort by bin first and then @random, in which case your 35 records will always come from the first bins (or last bins, if the bin sort is descending).  Or you can sort by @random and then bin, which would only affect records that had the same result from @random (eg. if there were two #5 records).


 What you probably need to do is do your random sort and select your 35 records in the db, before they get to the report.  Then the report can sort those records by bin.  You mentioned a "sql command".  Maybe you could do it there (although you also mentioned a "dynamic parameter list", and I'm not sure how that figures into this).

 There may be other ways that you could handle this, but doing it in the db would probably be the most efficient.

 James

 PS:
 FWIW, assuming that the running total that you mentioned is just counting records, you could just use RecordNumber to suppress the extra records (ie. RecordNumber > 35).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
I missed that part.

Try it this way for random

If  int(rnd()*7000)  < 100 then
   50
Else
   100

In that way the first 35 records should have the same number 50.  You may want to display the actual number for a run or 2 to better tune the 100.  It may need to be larger.  Since you don't care which 35 records get chosen all you need is to ensure there are at least 35 in the first group.  You can then sort them by then bin.

mlmcc
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Tina KSystems SpecialistAuthor Commented:
@mlmcc:

SQL grabs all parts in any given location with >0 inventory.  Crystal is built using this command rather than adding the individual tables and joining in CR.  Crystal is used to generate a simple worksheet displaying 35 random locations to be counted.  Upon opening the report, the user selects which warehouse location is to be counted, the record selection then filters based on this location.  Sort expert shows ascending - @random and ascending - command.location_id, the location id isn't sorting...and that's what I'm trying to do.

@james0628:

bummer, I was really hoping I could achieve a 'sort by this, then by that' as one would do in Excel.  You've said I should do my random select and sort in SQL, which I have no idea how to accomplish.  I'll hit Google and see where that takes me...unless you happen to know?

Thanks!
Tina KSystems SpecialistAuthor Commented:
mlmcc - That was the trick - thank you so much!
Tina KSystems SpecialistAuthor Commented:
In retrospect, using the formula in Crystal really doesn't work - it's only giving me results from the first few hundred rows of bin locations, I don't make it past letter "C".  I tried a number of different variants in the if int(rnd()*7000< XX position to no avail.
However, sql has done it:

Select     Top 35 (part_location.location.id)
From       part_location
Order by   Newid()

Thought I'd share my solution :-)
James0628Commented:
In your "Order by   Newid()" clause, is Newid a random number like the one you were using in the report?

 Also, if location.id is your bin, then it seems like you're selecting the first 35 bins there, not the first 35 records.

 I'm just trying to understand your solution.

 James
Tina KSystems SpecialistAuthor Commented:
James:

Here's the whole sql statement that pulls 10 random bin locations.  Even though the code states top 10, it truly does select 10 random locations.  I have tested this quite a bit and am getting a good selection of bins from the top/middle/end of the recordset.  (our bins are arranged in alpha formation).  I'm pulling in the individual part information via a subreport linked by location_id.  
I have also created a second report with a statement to select 10 part numbers and display all bins locations where the quantity is >0 (via a subreport), so we can either cycle count based on part number or bin location based on what the inventory group decides.

SELECT        top 10 (PART_LOCATION.LOCATION_ID), PART_LOCATION.WAREHOUSE_ID, PART_LOCATION.part_ID, PART_LOCATION.QTY, PART.FABRICATED
FROM            PART_LOCATION RIGHT OUTER JOIN
                         PART ON PART_LOCATION.PART_ID = PART.ID
WHERE        (PART_LOCATION.QTY > 0)
order by   newid()
James0628Commented:
Thanks.  My main "problem" was that I didn't realize what NEWID() was.  I'd just never used it before and didn't realize it was generating a random(ish) value for each row.

 FWIW, I read that performance can be a problem if you're dealing with too much data, so you might want to keep that in mind, if the report seems too slow at some point.

 James
Tina KSystems SpecialistAuthor Commented:
It actually generates pretty quick considering how many records it's checking.  It's a big help that this whole thing generates using only two tables than the 7 table inner-outer-right- left join nightmare I'm usually working with!

Thanks for the help!
Tina KSystems SpecialistAuthor Commented:
Suggestion to accomplish random selection via SQL rather than Crystal pointed me in the right direction.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.