Custom code sample: count unique values

Sometimes you may need to generate or calculate a value that is not available from the Statistics or Columns in the Retrieval Settings for the report. One method you can use to calculate values is by using Custom Code.

In this example, we will create a report where the Retrieval Settings include the Column for "Lot". This means that the lot number from each retrieved data record will be provided to the report. Then we will use custom code to count the number of unique Lot numbers that were retrieved for the report, and we will show this number (the count) on the report.

 

Contents  [Hide]

  

Getting Started

To begin:

  1. Create a new report – either an SPC report or a DMS report.

  2. On the Part Number tab, select one standard or process. (You can later edit the report for multiple standards/processes if desired.)

  3. On the Columns tab, choose the traceability field for which you want to count the unique values (such as Lot):



    You can also choose any other data columns that you want to provide to the report.

  4. Click Design Report to proceed to the design window for this report.

    If you expand the GainSeeker\DataDetail dataset, you should see all of the data columns you chose on the Columns tab – including the traceability field for which you want to count the unique values.

Adding the custom code

  1. Click the  button and then paste the following text into the upper part of your screen:

public function SumParts(values as System.Collections.ArrayList) as double

dim i as integer

dim totTrace as integer

dim psTrace as string, psTemp as string

totTrace = 0

for i = 0 to values.Count-1

psTemp = values(i) & "|"

if instr(psTrace, psTemp) = 0 then

psTrace = psTrace & psTemp

totTrace = totTrace + 1

end if

next i

return totTrace

end function

Your screen will now look something like this:

Using the custom code on your report

To use this custom code on your report:

  1. Click the button.

  2. From the GainSeeker\DataDetail dataset, drag the Lot column and drop it on the body of your report:





    You may also want to drag the textbox wider, as we'll have to add more to call the custom code.

  3. Because the SumParts code that we pasted expects us to send it an array of traceability values, we can convert the default value for this textbox from the first value to the array of values. We do this by using the ToArray method, as shown below:

  4. Now to send this array to the SumParts code, we enclose this entire ToArray(...) command in parentheses and add code.SumParts to the front, like so:

Viewing the report and validating the result

Now we need to save the report, view the results, and make sure the results match the data.

  1. To save the report, click the button. Alternately, you may click the File menu and then click Save Report.

  2. To view the report, click the button or click the File menu and then click View Report.

    Your report will probably contain only a number – one that reflects the number of unique values from the traceability column you specified.

  3. If you want to check this number, you can do one of the following:

  1. Use the SPC Charts and Reports module to create a control chart for the same data you retrieved on the report.

  2. Right-click the chart and choose Group Data, then group and sort the data by the traceability field for which you are counting unique values.

  3. Count the data groups, or else right-click the grouped chart and then choose Control Limit Legend.

    Either way, the number of groups you find here should match the number reported by your custom code.

  1. Use the DMS Charts and Reports module to draw a Pareto chart for the same data you retrieved on the report, with the Pareto bars sorted by the traceability field for which you are counting unique values.

  2. Right-click the chart and then click Display Options. Select the Show bars with no Defects check box, and then click OK.

  3. Count the Pareto bars. This should match the number reported by your custom code.

Other variations on this code

You could modify this code to use it with data point values or specification limits. In that case, you would need to convert the string value into a double using cdbl(values[i]) if you want to treat the data set as numbers.

For some operations, it will be easier to use the built-in statistics, e.g. Sum values, because the GainSeeker statistics automatically exclude data values that are not set. If you perform the sum yourself in custom code, you will need to verify that values[i] contains a valid number before trying to convert it to a double using cdbl (missing data values are blank/empty strings). Note that when the upper and lower specification limits are not set, their value is "NONE".