How to only see unique values in a field

Hi
I have a report which shows 20 random records, works ok
I would like to extend this to make sure it only shows unique values in another field
The random selection is linked to the work order field I would like it only to show unique departments
Have attached the rpt file
GR-Customer-Survey-Data-4.rpt
Gordon HughesDirectorAsked:
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.

Gordon HughesDirectorAuthor Commented:
Also only to include in the report where a certain field is not blank
0
mlmccCommented:
Do you want to 20 records where the field is unique and the second field is not blank?
Field1 will have 20 different values? or do you want to suppress the duplicate values?

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi
I want to suppress the duplicate department values but still want 20 random records
Hope this makes sense
Gordon
0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

mlmccCommented:
Add the following to your filter

Not (IsNull({YourFIeld})

For random selection do the following

Add a formula to the detail section
Name -RandomNumber
WhileReadingRecords;
Rnd()

Open in new window


Sort the records by the formula @RandomRecords

Use suppression on the detail section
RecordNumber > 20

mlmcc
0
mlmccCommented:
To suppress the duplicate departments try the following

In the report header add a formula
DeclareVariables
WhilePrintingRecords;
Global StringVar Array Depts;
Global NumberVar DeptCount := 0;
''

Open in new window


Where you want to display the department use a formula
Name - CheckDepts
WhilePrintingRecords;
Global StringVar Array Depts;
Global NumberVar DeptCount;

If DeptCount = 0 then
(
    DeptCount := DeptCount + 1;
    ReDim Preserve Depts[DeptCount];
    Depts[DeptCount] := {DEPT.DEPARTMENT};
    Depts[DeptCount]
)
Else If DeptCount < 20 then
(
    If {DEPT.DEPARTMENT} IN Depts then
        ''
    Else
    (
        DeptCount := DeptCount + 1;
        ReDim Preserve Depts[DeptCount];
        Depts[DeptCount] := {DEPT.DEPARTMENT};
        Depts[DeptCount]
    )
)
Else
    ''

Open in new window


mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
Good to hear from you
A bit confussed by your support, not you it is my understanding
Not sure what i should put where!!
Gordon
0
mlmccCommented:
You said you only wanted to see unique departments.  Are you displaying the department somewhere in the details section>
If so just replace the department field with the formula

If the department isn't shown and you mean to suppress the record then you can change it to a suppression formula as

'' = {@CheckDepts}

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
Will try it
Gordon
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
OK
I added the department code to the report
Then added the CheckDepts formula to the report heading
I think the uniqueness is working but the report is showing the wrong dept against each record, is show PCF instead of CCU
Have attached the lasted rpt file
Any ideas
Gordon
GR-Customer-Survey-Data-4.rpt
0
mlmccCommented:
The CheckDepts formula should be in the detail section not the report header.  Use it instead of the Departments field

Add the Declare Variables formula to the report header

See the revised report

mlmcc
GR-Customer-Survey-Data-4-rev1.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
OK I can see why I was not getting the correct departments to the work orders changed the table link option
But as you can see from the attached doc, I am not getting unique the department, if you follow the department description you can see they are duplicated resulting in the blanks where the department code is (the field you added for me)
Am trying to get 20 random work orders with unique (not duplicated) departments

Hope this maked sense and really appreciate your help as normal
Gordon
CS-Doc.docx
0
mlmccCommented:
I tried to ask that in an earlier comment but apparently we didn't communicate.  So what you want is 1 record from 20 (unique) random departments.

I think if you use the formula as suppression on the detail section and change the Department Name field to FALSE and the '' to TRUE it will suppress appropriately.  If that doesn't work or you want help implementing let me know.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
I am lost a bit am sure I have missed some of what you say, my fault
am after 20 random work orders with only one type of department code/description
The report I get does show the random 20 work orders but it show duplicated departments
Do not know how to achieve this
Please be patient with me
Gordon
0
mlmccCommented:
Try this version.  Note how I am using suppression of the detail section

mlmcc
GR-Customer-Survey-Data-4-rev1--1-.rpt
0

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
Gordon HughesDirectorAuthor Commented:
Hi mimcc
That seems to work, but
I have 2 reports one for each site
The GR report looks ok but the Hull one is only returning 5 records not 20
Attached reports
They are the same exept the site selection

Gordon
Hull-Customer-Survey-Data3a.rpt
GR-Customer-Survey-Data3.rpt
0
mlmccCommented:
Maybe there are only 5 departments or 5 distinct departments for the time period.

Unsuppress the report footer and and add a formula to display the record count

Count({WOHISTVW.WONUM})

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

I did the last instruction
it came back with 7
Have exported the data and yes there is only 7 departments, so I need to remove the department uniqueness from this report
Not sure which elements to remove
Can you assist?
Gordon
Hull-Customer-Survey-Data3a.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

I think for this report I need to allow duplicate departments
Don't know how though
Gordon
0
mlmccCommented:
Just comment out or delete the code in the detail suppression
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
The issue at this site is that the data is not complete, so some data work required
Will close this question, appreciate you patience as normal
Gordon
0
Gordon HughesDirectorAuthor Commented:
Excellent support
0
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.