How to only see unique values in a field

Also only to include in the report where a certain field is not blank

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

Hi
I want to suppress the duplicate department values but still want 20 random records
Hope this makes sense
Gordon

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()

Select allOpen in new window

Sort the records by the formula @RandomRecords

Use suppression on the detail section
RecordNumber > 20

mlmcc

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;
''

Select allOpen 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
    ''

Select allOpen in new window

mlmcc

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

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

Hi mimcc
Will try it
Gordon

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

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

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

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

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

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

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

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

Hi mimcc

I think for this report I need to allow duplicate departments
Don't know how though
Gordon

Just comment out or delete the code in the detail suppression

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

Excellent support