Chapter 12. Module 12. External Query Reports

Table of Contents

12.1. Tests & Requirements
12.2. Begin Module 12. External Query Reports.
12.3. Obtain database query metrics and details using Status and List Views to review all outstanding queries prior to query report creation.
12.4. Run database integrity check report DF_XXkeys to export all key fields and visit dates from required plates in the database.
12.5. Use Data View to check, and if necessary correct, illegal visit dates by retrieving the problems keys identified by DF_XXkeys.
12.6. Execute DF_QCupdate to update the query database and lock all records for query report generation.
12.7. Execute DF_QCreports to create a standard, 3-part external query report for a study site.
12.8. Confirm the status of external queries following query report creation.
12.9. Execute DF_QCstatus to verify that a new query report was created by listing all reports in the QC/NEW directory.
12.10. Execute DF_QCview to display the newly created query report.
12.11. Execute DF_QCprint to print the query report from the QC/NEW directory.
12.12. Comparing several examples from the printed query report against their corresponding queries in the study database.
12.13. Execute DF_QCfax to email the query report to a valid email account.
12.14. Verify the status of the queries in the sent report.
12.15. End Module 12. External Query Reports.

12.1.  Tests & Requirements

  1. Obtain database query metrics and details using Status and List Views to review all outstanding queries prior to query report creation.

  2. Run database integrity check report DF_XXkeys to export all key fields and visit dates from required plates in the database.

  3. Use Data View to check, and if necessary correct, illegal visit dates by retrieving the problems keys identified by DF_XXkeys.

  4. Execute DF_QCupdate to update the query database and lock all records for query report generation.

  5. Execute DF_QCreports to create a standard, 3-part external query report for a study site.

  6. Confirm the status of external queries following query report creation.

  7. Execute DF_QCstatus to verify that a new query report was created by listing all reports in the QC/NEW directory.

  8. Execute DF_QCview to display the newly created query report.

  9. Execute DF_QCprint to print the query report from the QC/NEW directory.

  10. Comparing several examples from the printed query report against their corresponding queries in the study database.

  11. Execute DF_QCfax to email the query report to a valid email account.

  12. Verify the status of the queries in the sent report.

Estimate of Time Required: 40 Minutes

12.2. Begin Module 12. External Query Reports.

  1. Start DFATK as described in Module 1 (Installation & Initialization).

  2. Click 12. External Query Reports followed by Begin to begin this module.

12.3. Obtain database query metrics and details using Status and List Views to review all outstanding queries prior to query report creation.

  1. Start DFexplore, login as user data_coordinator and choose 254 Acceptance Test Study as described in Section 1.7, “How to start DFexplore.

  2. Select View > Status. Examine the Queries table to view the status and count of all queries in the database.

  3. Open the Graphics section and select the toggle button for by status. Confirm the count of Pending and Outstanding queries.

  4. Select View > List. Select the Plates & Metadata tab.

  5. Click metadata - Queries to display information about database queries.

  6. Select Select > Search to specify query search criteria. In the search dialog, check (enable) the Usage = external, click Update to update the dialog with details and a count of external queries.

  7. Click Done to close the Search dialog.

12.4. Run database integrity check report DF_XXkeys to export all key fields and visit dates from required plates in the database.

Select by Data Fields
  1. Select View > Reports to change from List View to Reports View.

  2. Double-click DFdiscover Reports then Legacy Reports list by clicking . Select the report DF_XXkeys from the list. Click Explain to view a detailed description of the report. As options do not exist for this report, click Run to execute DF_XXkeys.

  3. In Step 2, DF_XXkeys detected an illegal visit date. As indicated by the message in the reports window, a data retrieval file called VDillegal.drf was created. This file contains the plate(s) on which the illegal visit date was detected, and can be reviewed and edited in Data View. Click Close Report to close the report output window. Select View > Data to change to Data View.

12.5. Use Data View to check, and if necessary correct, illegal visit dates by retrieving the problems keys identified by DF_XXkeys.

  1. To review the data retrieval file created in Step 2, select Select > By Data Retrieval File. In the retrieval file dialog, set:

    • Mode = Validate

    • Save level = 1 - Level 1

    • Check (enable) Enable edit checks

    • Select file from = study server

  2. To locate the data retrieval file VDillegal.drf, click ... next to Select file from. In the file selection dialog, choose the file VDillegal.drf, then click OK.

  3. Click OK in the dialog. Click OK in the resulting confirmation dialog to retrieve all data records having illegal visit dates.

  4. Click anywhere in the page to make it active for data entry. Starting with the Follow-up choice field, press Tab to advance to the Visit Date. The Visit Date value has been entered as 04/17/97 (an illegal date), rather than 04/07/97 as shown on the CRF image. Enter the correct date and press Tab to exit the field.

  5. As problems no longer exist on the corrected page, the page can be saved with a status of Final. Click Final to save the page.

12.6. Execute DF_QCupdate to update the query database and lock all records for query report generation.

  1. Select Select > All Records, click OK. Select View > Reports to return to Reports View. Again choose the report name DF_XXkeys from the list and click Run. Confirm that the output no longer reports illegal visit dates.

  2. Choose DF_QCupdate from the list of Legacy Reports and click Explain to view a report description. Do not enter any options in the Specify field and click Run to update the Query database.

  3. A user requires unrestricted permissions to execute DF_QCupdate. As user data_coordinator is restricted to database Modify and Write levels of 0-4 (as set in Chapter 1, Module 1. Installation and Initialization), it is not possible to execute DF_QCupdate successfully. Click File > Exit to close DFexplore.

  4. Start DFexplore, login as user data_manager as described in Section 12.3, “Obtain database query metrics and details using Status and List Views to review all outstanding queries prior to query report creation.”.

  5. Select View > Reports.

  6. Double-click DFdiscover Reports, expand Legacy Reports by clicking . Choose DF_QCupdate from the list of reports. Click Run to execute DF_QCupdate with the correct permissions.

12.7. Execute DF_QCreports to create a standard, 3-part external query report for a study site.

  1. Choose DF_QCreports from the list of Legacy Reports and click Explain to view a report description.

  2. Do not enter any options in the Specify field so that a complete query report is generated. Click Run to execute DF_QCreports.

    [Note]Note

    DFdiscover report numbers are comprised of a site number followed by the date of report creation (as yy/mm/dd). The two are separated by a hyphen. Your Query report has been created for Site 99 and the date of creation is the date on which DF_QCreports was executed (i.e.today).

  3. To print the output, select File > Print. In the Print dialog, the Printer Name field contains the name of your printer. Click Print. Keep this printout to reference the query report number in Section 12.12, “Comparing several examples from the printed query report against their corresponding queries in the study database.”. Click Close Report to close the report output window.

12.8. Confirm the status of external queries following query report creation.

  1. Select View > Status. Click Refresh to update the database counts. Examine the Queries table to view the status and count of all queries in the database.

    [Note]Note

    The counts in the Queries table have changed because the query database has been updated by the running of the report DF_QCupdate in Section 12.6, “Execute DF_QCupdate to update the query database and lock all records for query report generation.”. If applicable, DF_QCupdate will create new queries for missing pages and overdue visits based on the study scheduling information present in the study visit map files.

  2. Select View > List.

  3. Click metadata - Queries to display information about database queries.

  4. Select Select > Search to specify a set of query search criteria. In the resulting search dialog, select (enable) Usage = External and click Update to update the dialog with details and a count of external queries.

  5. Click Done to close the dialog.

12.9. Execute DF_QCstatus to verify that a new query report was created by listing all reports in the QC/NEW directory.

  1. Select View > Reports.

  2. From the list of Legacy Reports, select DF_QCstatus and click Explain to view a report description. As a query report was created but not sent, click Run to obtain the status of all query reports currently in the study QC/NEW directory.

  3. Click Close Report to close the report output window.

12.10. Execute DF_QCview to display the newly created query report.

  1. To display the query report created for Site 099 - Hospital #99, choose DF_QCview. Click Explain to view a description of the report. As the report for Site 099 - Hospital #99 still resides in the QC/NEW directory (it has not yet been sent to the site), click Run to execute DF_QCview.

  2. Click Close Report to close the report output window.

12.11. Execute DF_QCprint to print the query report from the QC/NEW directory.

  1. Print a copy of the query report for Site 099 - Hospital #99 by choosing DF_QCprint. Click Explain to view a description of the report. Enter -u number of your query report in the Specify field. Click Run to execute DF_QCprint.

    [Note]Note

    If you do not have a printer connected to the DFdiscover server or if you are a hosted client, the query report will not be printed. Instead you can choose to print the query report displayed in the DF_QCview output window from the previous Step 1.

  2. Click Close Report to close the report output window.

12.12. Comparing several examples from the printed query report against their corresponding queries in the study database.

[Note]Note

For this test you will need to reference the query report printed in Section 12.11, “Execute DF_QCprint to print the query report from the QC/NEW directory.” to verify the information contained in the report against the study database. Three examples of queries will be verified in this test. You may choose to check the remainder of the queries yourself.

  1. In the printed query report, find the entries for patients 99002 and 99004 in the FAX/REFAX section of the report. For patient 99002, locate the entry for Form 9, AE Report 011 having multiple queries in the Intensity field. For patient 99004, locate the entry for Form Number 4, Pg 1 having the category “Missing Page”. Form 4 corresponds to Plate 4 in the database. Now locate the entry for Form Number 2, having the category “Pulse beats/minute (Missing)”. Form 2 corresponds to Plate 2 in the database.

  2. Select View > Data.

  3. Select Select > By Data Fields. In the dialog, set:

    • Mode = View.

    • Subject = 99002,99004

    • Plate = 2,4,9

    • Level = 1-7

  4. Click Done then OK to apply the record selection criteria and retrieve the applicable pages.

  5. Highlight the 9: Form 9, AE Report 011 page in the record list. Press Tab to advance through the fields until Intensity becomes the current field. Confirm that this field is blue and the query count “2” is displayed on the field widget. Examine the Query window to view the details of the queries. Verify that the information in the Query window is consistent with the corresponding printed Query report.

  6. In the record list, double-click entry for subject 99004.

  7. Press Tab to advance to the Pulse field to make it the current field. Verify that the field is blue (indicating that an outstanding query exists). Examine the Query to view the details of the query.

  8. Examine the record list for the entry for Form 4 for Subject 99004 (identified as a Missing Page on the query report).

  9. Select Select > All Records. Confirm the settings:

    • Mode = Modify

    • Save level= 1 - Level 1

    • Enable (check) edit checks

    Click OK to release the task set and display all records in the database.

12.13. Execute DF_QCfax to email the query report to a valid email account.

  1. Select View > Reports.

  2. Choose DF_QCfax and click Explain to view a description of the report. Send the query report generated in Section 12.7, “Execute DF_QCreports to create a standard, 3-part external query report for a study site.” to a valid email address. Enter -f mailto:user@localhost in the Specify field, substituting “user@localhost” with your email address as defined on your mail system. Click Run to execute DF_QCfax.

  3. Wait for several minutes, then check the recipient's email account for the arrival of the query report emailed in Step 2. Once the report has arrived, click Close Report to close the report output window.

12.14. Verify the status of the queries in the sent report.

  1. Select View > Status. Click Refresh to update the database counts. Examine the Queries table to view the status and count of all queries in the database.

    [Note]Note

    The counts in the Queries table are the same as those in Step 1 because queries have been sent to the site but not resolved.

  2. Select View > List, and click Plates & Metadata.

  3. Select File > Preferences. Click List View to expand that sub-panel and enable Label for Display coded field as. Click OK. To display information about database queries, click metadata - Queries.

  4. Select Select > Search to specify a set of query search criteria. In the resulting search dialog, select (enable) Usage = external. Click Update to update the dialog with details and a count of external queries.

  5. Click Done to close the Search dialog.

  6. Select File > Exit to close and exit DFexplore.

12.15. End Module 12. External Query Reports.

  • Return to the DFATK application. Click End to confirm successful completion of Module 12. External Query Reports.