Investigating Analysis Use

Q: Is there an existing excel export template or could you create one to do the following:

If I want a list of all analytes associated with a project(s), I have to look at both the project and requirements. Or I want to select an analyte(s) and find all projects and requirements where it exists. Or list all analytes that are not currently associated with any active projects or requirements.

A: Until such a report is added in a future version of LIMS, you can investigate analysis use in your own system by adding the following SQL statement to the LIMS Data Query workbook (available in the File Library on our web site):

SELECT Project.Name AS Project, Requirement.Name AS Requirement, Analysis.Analyte FROM Analysis INNER JOIN ((Requirement INNER JOIN (Project INNER JOIN ProjectRequirement ON Project.ProjectID = ProjectRequirement.ProjectID) ON Requirement.RequirementID = ProjectRequirement.RequirementID) INNER JOIN RequirementAnalysis ON Requirement.RequirementID = RequirementAnalysis.RequirementID) ON Analysis.AnalysisID = RequirementAnalysis.AnalysisID UNION SELECT Project.Name AS Project, “” AS Requirement, Analysis.Analyte FROM Analysis INNER JOIN (Project INNER JOIN ProjectAnalysis ON Project.ProjectID = ProjectAnalysis.ProjectID) ON Analysis.AnalysisID = ProjectAnalysis.AnalysisID UNION SELECT “” AS Project, Requirement.Name AS Requirement, Analysis.Analyte FROM Analysis INNER JOIN (Requirement INNER JOIN RequirementAnalysis ON Requirement.RequirementID = RequirementAnalysis.RequirementID) ON Analysis.AnalysisID = RequirementAnalysis.AnalysisID WHERE Requirement.RequirementID Not In (SELECT RequirementID FROM ProjectRequirement) UNION SELECT “” AS Project, “” AS Requirement, Analysis.Analyte FROM Analysis WHERE Analysis.AnalysisID Not In (SELECT AnalysisID FROM ProjectAnalysis) AND Analysis.AnalysisID Not In (SELECT AnalysisID FROM RequirementAnalysis) ORDER By Project, Requirement, Analyte;

The results of the SQL statement above can be used to determine any of the following: 1. Analytes added to a project 2. Analytes added to a project via a requirement 3. Analytes in requirements where the requirements aren’t added to any projects 4. Analytes not added to any requirements or projects

The query lists project, requirement, and analyte names. Use Excel’s sorting feature to sort by either of the columns. To use the LIMS Data Query workbook, make sure LIMS is running on your workstation then open the workbook. Enter the SQL statement above on the Query worksheet then click the [Query] button and your results will be displayed on the LimsData sheet.