GLCSS returns Lab Cal Sample Summary information (Number Scheduled, Number due...) for a variable(s).    Locate, Sampling Requirement Data places this formula in a spread report.

SYNTAX:

GLCSS(Offset/StartDate,Grouping/EndDate, Stat,VarNum1,..VarNum99)

or

GLCSS(Offset/StartDate,Grouping/EndDate, Stat,"Where Clause")

GroupOffset/StartDate: Used with the Grouping parameter to set the dates to calculate the stat for.  Sets the number of Groups (set by the Grouping Parameter) to be added to set the dates.  See examples below. If this field is a date string (i.e. "07/16/2004") or refers to a cell with a date in it, it is assumed to be the start date to calculate the stat for.

Grouping/EndDate: Used to set which dates to calculate the stat for.  If the Grouping is a date literal string (i.e. "07/16/2004") or refers to a cell with a date in it, it is assumed to be the end date to calculate the stat for. Common Groupings are:

"D" Days
“M” Monthly 
"R" Report Dates
Click here for availiable Grouping options

Stat:  The statistic/summary to calculate in quotes:

• Sampled - Samples Collected, i.e. a sample status of Received, Analyzed, or Closed.
• Received, TestsPending, or SampledNoResult - Samples collected with no result for any one variable specified, i.e. status of Received. NOTE: If the sample is set to Received, but it has a result it will NOT be counted.
• Due - Samples not yet collected/received.
• Analyzed - Number of results
• Closed - Number of closed samples
• Skipped - Number of Skipped
• Late - Samples in the Date Range that have are due (Status of 0) and are scheduled before the current date/time.

VarNum1..VarNum99: A comma separated list of variables that sets which variables to calculate from.

Where clause Instead of providing a list of variables, you can use an SQL Where clause against the VarDesc,Location table to get the list of variables.  I.E. “Units = 'mg/L'” wound return all variables with Units set to mg/L. See Examples.

EXAMPLES:

=GLCSS(1,"R","DUE","NAME = 'Free Chlorine Residual' AND LOCATION.LVL2_ID=14") - Number of samples Due (Scheduled) for variables with a name of Free Chlorine Residual and Location Lvl2_ID of 14. 

=GLCSS(1,"R","SAMPLED",41) - Number of samples that refer to variable 41 that have a status of Received, Analyzed, or Closed that are in the Report Date Range. 

Variable 41 is referred to in the Influent Sample. 

C5: Three samples (marked 1, 2, and 3 on calendar above).

C6: One sample with a status of Received (marked 3)

C7: 13 Samples are still due (Late or Due Status)

C8: One Sample with a status of Analyzed (marked 2)

C9: One Sample with a status of Closed (marked 1)

C10: One Sample with a status of Skipped (marked 5)  

C11: Two late samples.  Marked 4 and 6.  Sample 6 (200305-0003) is scheduled for 3/5/2020 at 8AM, the current time is 2:25PM therefore it is considered late. 

NOTES:

If you need to calculate these stats based on Sample information (i.e. Sample Name, Client, etc...) use SQLFIRST.  Cell C1 contains start date of report and C2 the end date.

SAMPLED: (For sample names starting with 'DIST')

=SQLFIRST(1,1,"","SELECT COUNT(*) FROM LC_SAMPLE, LC_SAMPLEDEF WHERE LC_SAMPLE.SAMPLEDEFID=LC_SAMPLEDEF.ID AND NAME LIKE 'DIST%' AND SAMPLEDTTIME>= #P1# AND SAMPLEDTTIME< #P2# AND SAMPLESTATUS IN (4,5,9)",1,1,C1,C2)

RECEIVED: (For sample names starting with 'DIST')

=SQLFIRST(1,1,"","SELECT COUNT(*) FROM LC_SAMPLE, LC_SAMPLEDEF WHERE LC_SAMPLE.SAMPLEDEFID=LC_SAMPLEDEF.ID AND NAME LIKE 'DIST%' AND SAMPLEDTTIME>= #P1# AND SAMPLEDTTIME< #P2# AND SAMPLESTATUS =4",1,1,C1,C2)

DUE: (For sample names starting with 'DIST')

=SQLFIRST(1,1,"","SELECT COUNT(*) FROM LC_SAMPLE, LC_SAMPLEDEF WHERE LC_SAMPLE.SAMPLEDEFID=LC_SAMPLEDEF.ID AND NAME LIKE 'DIST%' AND DATECOLLECTIONDUE>= #P1# AND DATECOLLECTIONDUE< #P2# AND SAMPLESTATUS =0",1,1,C1,C2)

ANALYZED: (For sample names starting with 'DIST')

=SQLFIRST(1,1,"","SELECT COUNT(*) FROM LC_SAMPLE, LC_SAMPLEDEF WHERE LC_SAMPLE.SAMPLEDEFID=LC_SAMPLEDEF.ID AND NAME LIKE 'DIST%' AND SAMPLEDTTIME>= #P1# AND SAMPLEDTTIME< #P2# AND SAMPLESTATUS =5",1,1,C1,C2)

SKIPPED: (For sample names starting with 'DIST')

=SQLFIRST(1,1,"","SELECT COUNT(*) FROM LC_SAMPLE, LC_SAMPLEDEF WHERE LC_SAMPLE.SAMPLEDEFID=LC_SAMPLEDEF.ID AND NAME LIKE 'DIST%' AND DATECOLLECTIONDUE>= #P1# AND DATECOLLECTIONDUE< #P2# AND SAMPLESTATUS =8",1,1,C1,C2)