Welcome to Discern Explorer^® 1! This course is designed to introduce you to the Discern Explorer language. The course contains several demonstrations to assist you in becoming familiar with the various concepts around creating Discern Explorer queries. You also will have the opportunity to apply the knowledge you have learned by creating practice queries in an environment of your choosing.

Course Objectives

At the end of this course, participants will be able to:

· Describe the three main components of the Cerner Millennium system.

· Describe how data is organized in tables.

· Describe how the four common transactions (admissions, orders, results, and discharges) are handled by the system.

· Describe how data is retrieved from the database.

· List required elements of the basic SELECT statement.

· Describe the three methods to be used in identifying fields.

· Use the Data Dictionary tools to identify tables and fields.

· Describe the three display options for field formats.

· Format a field.

· Describe how the system stores data as code values.

· Use the CNVTAGE, CONCAT, TRIM, and EVALUATE functions.

· Use indexes to create qualifications.

· Use date and time qualifications with different operators.

· Build a query that includes a join and a qualification.

· Qualify on coded fields while joining tables.

· Create sorts and sub sorts.

· Build a prompt.

· Build a report using Visual Explorer.

Getting Started

You will need access to a Cerner Millennium environment to complete the following practice activities. It is recommended that the environment have at least the Cerner STANDARD database installed.

Clients:

Clients should use a client Build, Certification, or Training environment to do the assigned practice activities. Use caution if you must work in a production environment. Set the maximum number of records returned at a low number to avoid large queries that might slow the system.

For further questions, email cliented@cerner.com. Global clients should email, uklearningservices@cerner.com.

Cerner Associates:

Cerner Associates can use the internal Play Environments Cerner Millennium which is maintained by Cerner's GRID team, or other domains in which access is supplied.

Note that the steps in this document follow the 2012 code release.

You can print this participant guide or toggle between the document and the environment.

This participant guide includes the practice activities for the Discern Explorer course, focusing on the Visual Explorer application. The steps are written to correlate with the 2012 Cerner Millennium release.

Performance Check

The goal of this course is to teach the Discern Explorer programming language. The Performance Check will evaluate your understanding of the Discern Explorer language. In the assessment, you will be asked to evaluate syntax examples and select the correct option; therefore, pay special attention to the format of the syntax as you create your queries throughout this course.

Return to the Table of Contents and click on the topic Discern Explorer Foundations.

Building Queries

Running an Ad Hoc Query

In this practice activity, you will build an ad hoc query using the three required elements of the SELECT command. You will use the Discern Visual Developer (DVDev) application. Using DVDev, you will create an ad hoc query using two methods: manually typing the commands and using Query Builder.

1. Open Discern Visual Developer. You can use any environment to which you have access. If you currently have an environment installed on your computer, double-click DiscernVisualDeveloper.exe from the network or the local install directory. The local install directory commonly is located under Program Files > Cerner (C:\Program Files\Cerner). Use the log on, password and domain name appropriate to that environment.

Cerner associates can use the internal Play environment, which is a domain maintained by Cerner's GRID team. Complete the following steps to access the Play environment. Note: Cerner clients will not be able to access the Play environment.

a) Click PLAY Environment Access. Note the username and password.

b) Click CONNECT to connect to the Play Environment through virtual desktop.

e) Double-click the "Cerner_Shortcuts" folder on the desktop.

f) Double-click DiscernVisualDeveloper.exe.

g) Log on with the username and the password as listed in the Play Environments page.

h) The Play environment is refreshed every Friday evening which means all files created throughout the week are deleted. For any files you want to save for future should be saved to your local device or to a network share, either by copy/paste or using the Export and providing the path to a Cerner network drive.

Cerner Clients must be granted access to a non-production domain that is internal to the client's organization in order to complete the necessary activities. Work with your internal system administrator to get access to an internal client domain. Note: Cerner clients will not be able to access the Play environment as it is a domain internal to Cerner.

2. You are prompted for a username and password. Usernames and passwords are specific to each user and are the basis for Cerner security. Enter your username and password, and click OK.

3. You want to run a simple query to get the PERSON_ID for the first 100 people in the system. First, start a new file. From the File menu, select New.

4. Make sure that Blank is selected in the File Type list, and click OK.

5. Type the following SELECT statement in the (Code Editor) window. The asterisk, or wildcard, is used to tell the system to bring back all columns from the table.

SELECT * FROM PERSON WITH MAXREC = 100

Note: It does not matter whether you type in uppercase, lowercase, or a combination of the two.

6. To run the query, from the Build menu, select Run Ad Hoc Query. The following screen is a sample of results received from the query. Your results should have the same column headings, however, not the exact same data. Notice that the output of your query commands display in a spreadsheet like grid and returned only 100 rows. By using the With Maxrec=100 control option, you told the system to return only 100 rows.

7. Review the results. When you finish, close the Output window by clicking either Close icon as shown below.

8. Next, you will build an ad hoc query using a second method and allow the application to build the query for you. First, comment out the query you created by placing a semi-colon (;) in front of the SELECT command. When placing a comment, the editor changes the text color to give you a visual indication that the commands are commented and not meant to be executed.

9. Click at the end of the query and press ENTER two times. From the Tools menu, select Query Builder.

10. The Discern Query Builder opens.

The Query Builder is used to create and modify queries. The TABLES tab is used to select the tables that contain the data needed for your query. You will have the opportunity to learn all of the different aspects of each tab as you move through this course. For now, follow the directions to create a simple query. In the Tables list, double-click PERSON to select that table. You can also select the table from the Tables list, and click Select.

11. Notice that an alias of P is automatically assigned to the PERSON table. An alias defines an abbreviation for a table. Aliases are assigned in the FROM clause immediately following the table name. By using the alias assigned to the table, you no longer need to refer to the whole table name when referring to a field from that table. For example, instead of PERSON.PERSON_ID, you can refer to the field by the alias, P.PERSON_ID. Click the FIELDS tab. To extract all fields from the table into the query, click Select All Fields.

12. Click Yes to confirm that you want to select all fields. All of the available fields for the PERSON table are placed in the Selected Fields section.

13. The Query Builder has created the query. Notice that the alias is placed in front of each field in the Selected Fields list. When you have fields from multiple tables, the alias helps you to quickly identify to which table the field belongs. To view the syntax for the query, click the VIEW QUERY tab and scroll through the code. The SELECT command and all of the fields listed were created when you selected all fields on the FIELDS tab. Notice the syntax for listing the fields requires that a comma be placed between each of the fields being displayed. The FROM PERSON P statement was created by selecting the PERSON table in the TABLES tab. Run the query by clicking Run Query.

14. You are prompted whether you want to set the maximum number of records returned to 100.

It is a good idea to limit the amount of data returned to avoid large queries that might slow system performance. Click Yes.

15. Review the output. The output should be the same data returned from your original query. When you are finished reviewing the output, close the Output window.

16. Click Close to close the Query Builder.

17. The query created by the Query Builder is written to the source file. Scroll to the very bottom of the query and examine the WITH clause. You need to add the MAXREC control option to specify that you only want 100 rows returned. At the end of the WITH clause type a comma(,) MAXREC = 100.

Your WITH clause should look similar to:

WITH NOCOUNTER, SEPARATOR=" ", FORMAT, MAXREC = 100

18. Execute the ad hoc query from the source file by selecting Run Ad Hoc Query from the Build menu.

19. Close the Output window. Remove the semi-colon from the front of the first SELECT command. Now you have two ad hoc queries in the source file.

20. You can execute either query. Place the focus of your cursor on the word SELECT from the first query. To execute the query, press CTRL+ Q (or select Ad Hoc Query in the File).

21. Close the Output window and place the focus of your cursor on the word SELECT from the second query. To execute the query, press CTRL + Q (or select Ad Hoc Query in the File).

22. Close the Output window.

23. Close the source file that contains the queries by clicking the Close Window icon in the upper-right corner.

24. You will receive a message asking you if you want to save.

Since this is an ad hoc query that you do not need to save, click No.

The queries were not saved and not available for you to run later. These queries were simple and not time consuming to create, however, if it is helpful, you can place commonly used queries in a file that can be saved and accessed at a later time. For example, if you use the same query for troubleshooting or validating information, and know you will run it frequently, you can save the queries in a file.

Continue to the next practice activity, Creating a Command File, to create and save a file that contains queries.

Creating a Command File

A command file is file that can store queries and commands that you may need to access and execute for simple troubleshooting or auditing purposes. If you find that you are retyping the same commands or queries frequently, place the commands in a file to access and execute when needed.

1 In Discern Visual Developer, from the File menu, select New. The New dialog box opens.

2. Select Blank and click OK.

3. To save the file, from the File menu, select Save. The Save as Host Source dialog box opens.

4 In the Logical/Path box enter CCLUSERDIR:. The CCLUSERDIR directory is the default directory in Discern Explorer where all files are placed unless you specify otherwise.

5. In the File Name box, enter 1_<your initials>_CMDFILE.PRG, such as, 1_CCL_CMDFILE.PRG.

Note: The Node value is <default>. On a UNIX system, a *.prg is saved on ALL nodes. All other extensions, for example .ccl, are saved on only one node.

6. Click OK.

7. Create a simple query that returns the highest PERSON_ID from the PERSON table. Your query should be similar to the following:

SELECT MAX(PERSON_ID) FROM PERSON

8. Create another query that returns the total number of rows on the PERSON table. Your query should be similar to the following:

Select count(*) from person

9. Click anywhere inside the first query and from the Build menu, select Run Ad Hoc Query or Ctrl+Q.

The result of the query is displayed. The output of this query returns one row with one value that displays the highest PERSON_ID on the PERSON table. The following is an example of the output. The PERSON_ID returned by your query will most likely be different.

10. Close the Output window. Click anywhere inside the second query. From the Build menu, select Run Ad Hoc Query or press CTRL+Q. The output of this query returns one row with one value that displays the total number of rows on the PERSON table. The following is an example of the output. The number returned by your query will most likely be different.

11. Close the Output window.

Now that the query is in a file, you can open it, edit it, and execute the commands as you need.

12. Close the file by selecting Close from the File menu or by clicking the X in the upper-right corner. If prompted, save the program.

13. Say you decide you want to add a query to return the PERSON_ID and the NAME_FULL_FORMATTED. First, you need to open the file. Click the Open toolbar button .

14. In the Open Host Source dialog box, enter CCLUSERDIR: in the Logical/Path box.

15. Enter the file name, 1_<your initials>_CMDFILE.PRG, in the File Name box.

16. Deselect the Read Only option so you can edit the file, and click OK.

Your query is displayed in the code editor section.

17. You want to add a query to your source file so that you have a query that returns the PERSON_ID and the Name_Full_Formatted from the PERSON table. In the code editor, enter the following query as the third query in your source file as shown in the following display:

SELECT PERSON_ID, NAME_FULL_FORMATTED

FROM PERSON

WITH MAXREC=100

18. Click anywhere in the third query and execute the query.

19. Review the output. Note that only two columns are displayed, PERSON_ID and NAME_FULL_FORMATTED. The items used in the SELECT list are used as the title for each column. The data you see will be different due to different domains; however, the format should be similar.

When you are finished, close the Output window.

20. Close the source code file.

21. Click Yes to save your updates.

You have completed the activities for the Building Queries topic. Return back to the Table of Contents of the WBT and click on the topic Creating an Executable Program.

Creating an Executable Program

In this practice activity, create an executable program in Visual Explorer.

1. Open Visual Explorer (VisualExplorer.exe).

2. Visual Explorer opens to the Report Writer grid that we can use to visually lay out items on a report. However, we must first build a query and will learn about the Report Writer later in this course.

Notice the file name in the title bar and the report tab. When you create a new program in Visual Explorer, a file is automatically created with your user name as the title. You should create a new file name.

3. In preparation for creating a new file and object, make sure the object name you want to use isn’t already in use. Plan to name the object 1_<your_initials>_PERSON_ALLFIELDS. Begin by running CCLPROT to make sure the object name is unique. From the Tools menu, select Execute Program, and then CCLPROT. The Discern Prompt: CCLPROT window opens similar to the following:

a) In the Output to File/Printer/MINE box, keep the default value of MINE.

b) In The Object Type box, keep the default value of Program. The Object Type box narrows the search to a specific type of object. Most of the time, you will leave the default value of Program, however searching for other object types is sometimes helpful.

c) In the Object Name box, enter 1_<your initials>_PERSON_ALL_FIELDS and click Execute (or click anywhere outside of current box). When you click Execute, a program checks the object library for the existence of the name you entered. If a match is found, you can continue to populate the next prompt.

If a match is not found, the Object Name box turns red. This is indicates that the object is not found and the name is unique and is available to use.

d) The Include Source Name, Yes or No is used to indicate if you want the source code location included in the report. Since your object has not yet been created, you do not need to use this parameter. Click CANCEL to exit the Discern Prompt: CCLPROT dialog box.

Note: In this course, you are given a naming convention where the file name starts with 1_. Cerner recommends that clients name all custom objects beginning with a number. For example: 123_PERSON_ALLFIELDS. This naming convention prevents accidental overwriting of object names. If client custom programs all begin with a number, and Cerner production programs all begin with a letter, the possibility of a new Cerner program overwriting an existing custom program or a custom program overwriting an existing Cerner program is eliminated. Using CCLPROT to check for an existing object name, prevents the client from overwriting an existing Cerner program but does not prevent a future Cerner program from overwriting a client custom program.

4. Once you have a unique name, save your file. From the File menu, select Save. The Save As dialog box opens.

5. By default, files are usually saved in the CCLUSER folder on the PC or network, but you can change this if necessary. Visual Explorer automatically attaches the .vcl extension to your file name. Enter the unique name you selected, such as 1_<your initials>_PERSON_ALLFIELDS, in the File Name box, and click Save. Be sure to keep track of your file names as many are opened and edited in later exercises.

Note: If you are a Cerner associate using the Play environment, be certain to save your files to the CCLUSER folder and not to the desktop. To find CCLUSER, From the File menu, select Save As and the Save As dialog box opens.

6. The file name displays in the title bar. At this point, it is a good idea to include comments to be saved with the file. Comments provide vital information about the intended purpose of the program. This is especially helpful for other people who need to access and modify the program.

From the Tools menu, select Comments. The Report Comments box opens.

7. Recommended comments include the program's purpose, owner's name, creation date, and any update information. Add comments similar to the following and click OK.

Name: CCL Smith

Date created: 12/22/2014

Purpose of Report: A query that displays all fields from the PERSON table

8. When you put information in the Comments section, Visual Explorer automatically places /* */ around the line of text to indicate to Discern Explorer that the comments are text and not Discern Explorer commands. To see the comments in the source code from the Report menu, select View Program. The View Explorer Program box opens.

9. Review the source code. You can also comment lines of text by placing a semicolon (;) or an exclamation (!) in front the line you want commented. The following lines of code are comments only:

; Name: CCL Smith

! Date created: 12/22/2014

Anything to right of the symbol is ignored when the file is included. Click Close.

10. You are ready to create the query. Click the Query Builder toolbar button to open the Discern Query Builder window.

11. When using the Query Builder, you create and modify queries using these tabs. Use the TABLES tab to select the tables that contain the data needed for your query. Tables are grouped by category in the far left section of the window. The center section lists the tables for the selected category. The far right section contains the selected table or tables.

12. Since you want the PERSON table, double-click it to select it. The PERSON table and the Alias P display in the Selected Tables.

13. Select the fields for your query. Click the FIELDS tab.

14. In the Fields tab, you select the fields you want to include in your query. Because you are querying all fields in the PERSON table, click Select All Fields.

15. Click Yes to the Confirm message: Do you want to select all fields from table PERSON?

16. All PERSON table fields display in the Selected Fields list. To remove a single field, select it and click Remove . Clicking Remove All removes all selected fields. Click Run Query to execute the query.

17. The prompt displays with a default value of MINE.

You want to see your output on your screen, so accept the default value of MINE and click Execute.

18. The output is displayed in the Output window. The column headings in your output will be the same as the following; however, your data will be different.

19. Click the View Program tab. Notice that Visual Explorer automatically creates an executable program by adding the DROP and CREATE PROGRAM commands. It also defaults the SELECT INTO $OUTDEV:

20. Close the Output window.

21. Click Close to close the Query Builder.

22. Save the existing file by clicking the Save icon .

23. Since you are finished with this query for now, close the file. From the File menu, select Close.

You have completed the activities for the Creating an Executable Program topic. Return back to the Table of Contents of the WBT and click on the topic, Creating a File with Visual Explorer.

Identifying Tables and Fields

Using the Data Dictionary

Visual Explorer provides you with several tools to help you identify the tables and fields needed for queries.

In this practice activity, use the various tools contained within Visual Explorer to collect information about people associated with the hospital. For example, name, identification number, birth date, the last time an individual’s record was updated, and whether the record is in an active status.

Using Query Builder

1. Open Visual Explorer, and create a new file by clicking the New toolbar button.

2. Before you build this query, name the object and save the file. You decide to use the object name 1_<your initials>_PERSON_DOB. First, make sure the object name is unique and available to use. From the Tools menu, select Execute Program and then CCLPROT. The Discern Prompt: CCLPROT window opens.

a) In the Output to File/Printer/MINE box, keep the default value of MINE.

b) In the Object Type box, keep the default value of Program.. The Object Type box narrows the search to a specific type of object.

c) In the Object Name box, enter 1_<your initials >_PERSON_DOB, and click Execute. If CCLPROT finds a match, a report displays information about that object. If this is the case, select a new name and run CCLPROT again until you find a unique name.

When you have found an object name that is not already in use, the Object Name box fills in with the color red.

d) The Include Source Name option of Yes or No, is used to indicate if you want the source code location included in the report. Since our object has not yet been created, we do not need to use this parameter. Click CANCEL to exit the Discern Prompt: CCLPROT dialog box.

3. Once you have a unique name, save your file. From the File menu, select Save As. The Save As box opens.

4. Confirm that you have successfully named the new file by looking at the name in the title bar. Click the Query Builder toolbar button to begin creating the query.

The first Data Dictionary tool is the CATEGORIES list. The various folders contain logical groups of tables.

The COMMON category contains a list of commonly used tables and is selected by default.

5. Click the Favorites category. This category is most likely empty and remains so until you decide which tables to add to this folder. Within the TABLES section of the Categories pane, Right-click and select Customize or click Customize at the bottom of the Categories pane. The Customize Favorites dialog box opens. Select PERSON from the Available Tables and click Add to add your frequently used tables to the Favorites folder. Click OK to close the Customize Favorites window.

6. Click the Inline category. This category is most likely empty and remains empty until an inline table is created. This category allows for the creation of inline tables, which are temporary result sets that can helpful to use in qualifications.

7. Click the Millennium category. This category contains all tables in Cerner Millennium.

8. Click the Dictionary category. This category contains a list of system, or reference tables used by Cerner Millennium.

9. Click the RDBMS category. This category contains a list of system or reference tables used by the relational database management system (RDBMS). A relational database management lets you create, update, and administer a relational database. Commonly-used RDBMS products are Oracle, IBM's DB2 and Microsoft's SQL Server.

10. Click the All category. This category is a full list of all tables in the entire system.

11. Click the plus sign next Applications to expand the Applications category. This category contains groupings by solutions. Click the RadNet folder under the Applications folder. All tables associated with the Radiology solution are listed. If you need to write a Radiology report, you might research the tables listed in this section.

12. Find the table called MAMMO_FIND_DETAIL by typing in MAMMO in the Table Filter.

Using the Table Filter helps you to get to specific tables in the list quickly. You can also scroll through the list to look at all of the tables for that category.

13. Right-click the MAMMO_FIND_DETAIL table and select Properties. Read the DESCRIPTION for the table, then click Close. You can use the Properties to access glossary information about tables to help you research what kind of data resides in that table. Delete the word MAMMO in the Table Filter.

14. Double-click Applications folder or select the minus sign to close the folder.

15. Click the Domains category. This category displays sub categories from a broad spectrum of the data model. Click the PERSON category and note all of the tables that are a part of the PERSON data model. Person information and all the tables that relate to the Person data model span multiple solutions and cannot be placed in any one Application or solution list.

16. Click the Record Structure folder. This category is most likely empty until you create a record structure. Record Structures are in memory structures for temporarily storing data.

17. Click the MILLENNIUM folder, and enter PERS in the Tables Filter box. Notice that tables starting with PERS appear in the list.

18. Right-click PERSON and select Properties. The Table window provides a brief description of the owner and the purpose of a table. Click Close.

19. Double-click the PERSON table to select it. When you select this table, the Query Builder automatically builds the FROM clause for the SELECT command that looks similar to:

FROM PERSON P

20. Click the VIEW QUERY tab. Identify the FROM clause that has been added.

21. Click the FIELDS tab.

22. The Glossary provides a description of each field in the selected table, in this case, the PERSON table. Click Glossary.

23. In addition to the field description, the data type and, if applicable, the Code Set number are displayed. Scroll down to view more of the glossary. The Glossary is a valuable resource in determining the fields needed to display in your queries and reports.

24. Click Close to return to the FIELDS tab window.

25. Select the fields for the query by double-clicking the following fields in the FIELDS list to include them in your results.

· ACTIVE_IND

· BIRTH_DT_TM

· NAME_FULL_FORMATTED

· PERSON_ID

26. The order of the fields in the selection list determines the sequence that the columns are displayed in the output. You need to move the PERSON_ID and NAME_FULL_FORMATTED fields so they are listed first. Select each of the fields from the Selected Fields list, and click Move Up.

27. When you select items for display from the Fields list, the fields are placed under the SELECT command and separated by commas. Click the VIEW QUERY tab and review the structure of the query that is being created by the Query Builder. Validate that the fields are separated by commas.

28. Click Run Query. The Discern Prompt window opens. Keep the default value MINE and click Execute. The Output window opens.

29. Review your query results. Click the View Program tab to see the source code for your query.

30. The View Program tab shows the Discern Explorer source code that was created as you built this query. Close the Output window.

31. Click Close to exit the Discern Query Builder, save the file and continue to the next step.

Using CCLGLOS

32. You have used the Fields Glossary in the Query Builder to help you identify the fields you need. CCLGLOS is a second method available to find fields. From the Tools menu, select Execute Program and then CCLGLOS. The Discern Prompt: CCLGLOS window opens.

33. The Output To box has the default value MINE. The Data Model Object Type box has the default value Table. If not, select Table from the selection list. In the Object Name box, enter PERSON, in all uppercase.

34. Click Execute. The Report Output window opens with the data displayed in a report format. The report shows all of the fields in that table, its data type and a description about that field. The information displayed in this report is the same information as when you access the Properties for a field.

35. Review the format of the report and close the Output window.

36. Save and close the file.

You have completed the activities for Using the Data Dictionary. Return to the Table of Contents of the WBT and click on the topic Using Data Types.

Using Data Types

Fields or columns are grouped into three basic data types: numbers, characters, or dates. In this practice activity, review the list of sample data and identify its data type. Use one of the data type options listed above the sample data. Check your answers using the key in Appendix A.

Data Types

I2 F8 C5 DQ8 VC100

Sample Data

Fill in the blank with the appropriate data type from above.

________ 10

________ CBC

________ Call Dr. Jones when the CBC results are back

________ 12345.00

________ 12/10/03

You have completed the activity for Using Data Types. Return to the Table of Contents of the WBT and click on the topic, Using Field Names.

Using Field Names

In this practice activity, fill in the blank with the field tag that would best identify the description of the field. Examples of field tabs are ID, IND and Key. Check your answers using the key in Appendix B.

A field that stores the unique primary identifier of the PERSON table is most likely PERSON______________.

A field that indicates when an order was placed would most likely be ORIG_ORDER______________

A field that indicates whether a row is active would most likely be named ACTIVE___________.

A field that stores a person's last name in uppercase characters with all spaces and punctuation removed would most likely be named NAME_LAST_____________.

A field that stores one of several numbers that correspond to other data would most likely be named ORDERABLE_TYPE_____________.

You have completed the activity for Using Field names. Return to the Table of Contents of the WBT and click on the topic, Working With Code Values.

Working with Code Values

Create a query that shows personnel names, their positions, and the beginning effective date. The system manager wants the display, code value, and CDF meaning for the position code to display.

In this practice activity, you will create a query that displays the code value and text associated to a code value by creating expressions that use different UAR routines to get the Meaning, Display and Description for the POSITION_CD field.

1. From Visual Explorer, create a new file by clicking the New toolbar button .

2. You want to create a new prompt program by creating a new source code file named 1_<your initials>_PRSNL_INFO.vcl. Execute CCLPROT to ensure you do not overwrite an existing object with the same object name. Refer to Creating an Executable Program for details on running the CCLPROT.

3. To name and save your file, click the Save toolbar button. Enter 1_<your initials>_ PRSNL_INFO in the File Name box, and click Save. A file is created with a .VCL file extension with the name you provided.

4. Open the Query Builder.

5. The PRSNL table contains information about personnel. In the COMMON Category, double-click the PRSNL table from the Tables section.

6. Click the FIELDS tab and select the following fields. Arrange them in the order listed.

· NAME_LAST

· NAME_FIRST

· BEG_EFFECTIVE_DT_TM

POSITION_CD

7. Place your pointer over the last field in the list. The Selected Fields shows an expression that uses the UAR_GET_CODE_DISPLAY(). The POSITION_CD is stored in the database as a number known as a code value. The textual value associated with the code values are unfamiliar to most users and difficult to memorize. The Query Builder automatically uses a User Access Routine (UAR) to convert the code value which is a number into a textual value for your output display. Run your query to see how this field appears in your output.

The following is an example of how the output may look. Instead of seeing a number for the POSITION_CD, a textual value is displayed:

When selecting any field that ends in _CD using the Query Builder, it will automatically build an expression that uses the UAR_GET_CODE_DISPLAY routine.

8. Close the output and follow the next steps to add the code value and the CDF meaning.

9. In the Fields tab, click Code Values under the Selected Fields list. The Code Value Displays dialog box opens. The Display field option is selected by default.

10. In the Code Value Displays dialog box, select the following options and click OK to apply the fields to the existing query:

Code_Value Field
CDF_Meaning Field
Apply to existing code value fields

11. Notice the additional fields in the Selected Fields list. Click Run Query. From the Discern Prompt window, click Execute.

12. Identify the expressions automatically created by the Query Builder that use the UAR’s. The Query Builder automatically assumes that you would rather see the textual value, and creates an expression that uses the UAR.

13. Close the Output window.

14. Close the Discern Query Builder window and save your file.

You have completed the activity for Working with Code Values. Return to the Table of Contents of the WBT and click on the topic, Formatting Fields.

Formatting Fields

Using Field Formats

Your site's system manager is auditing the interface feeds for orders. You are asked to create a report that lists the mnemonics of the ordered procedures, the unique order identification number, whether comments are associated with each order, and the order date.

In this practice activity, define the order of the fields in the report and format a date.

1. You want to create a new prompt program by creating a new source code file named 1_<your initials>_ORDER_AUDIT.vcl. First, execute CCLPROT to ensure you do not overwrite an existing object with the same name. Refer to Creating an Executable Program for more details on running CCLPROT.

2. Name and save your file. Click the Save toolbar button, enter 1_<your initials>_ORDER_AUDIT in the File Name box, and click Save.

3. Click the Query Builder toolbar button to begin building your query.

4. You think you need the ORDERS table, but decide to check the glossary first. Right-click the ORDERS table in the Common category, and click Properties.

5. Review the information in the properties, and close the window.

6. Double-click the ORDERS table to select it.

7. Click the FIELDS tab to select the fields for your query.

8. Access the Glossary to review the fields for the ORDERS table. From reviewing the descriptions, identify the following fields:

The order identification
A field that displays the orderable name
A field that indicates if the order has a comment
A field that displays when the order was placed

Close the Glossary.

9. Select the following fields and arrange them in the order listed. Take note of each field's data type and length as you select them from the FIELDS list.

ORDER_MNEMONIC
ORDER_ID
ORDER_COMMENT_IND
ORIG_ORDER_DT_TM

10. Run the query.

11. In the result set, notice the format of the O.ORIG_ORDER_DT_TM field contains a two-digit year.

This is the default format applied to your date when the field is selected. Your site’s policy recommends that dates display using a four-digit year. Close the Output window and follow the next steps to change the format.

12. To change the format of the order date, right-click the O.ORIG_ORDER_DT_TM field in the Selected Fields list and select Field Format.

Note: The example shows the date in MM/DD/YY format for @SHORTDATE format option. The internationalized @SHORTDATE format option is the actual format of the date that will flex based on locale. For example, at your location the format might be DD/MM/YY or DD.MM.YY.

13. The Format Field dialog box enables you to change the way data displays in your output. The Name column provides a description of the format and the Format column displays an example of the format. Scroll through the list and select @SHORTDATE4YR format and click Insert.

14. Click the View Query tab and notice that the formatting option is placed to the right of the field.

15. Execute the query and verify the date now displays the format of MM/DD/YYYY.

16. Close the Output window and the Query Builder.

17. Save your file.

You have completed the activity for Using Field Formats. Return to the Table of Contents of the WBT and click on the topic, Modifying Field Formats.

Modifying Field Formats

The system manager liked the ORDER_AUDIT query, however has requested that you change the formatting of two fields. He would like to remove the decimal on the Order_ID and display nine digits, padding the id with zeros. This accommodates for when the Order_ID number increases and displays a fixed length number on the results. He also wants you to include the time with the date.

In this practice activity, format fields to meet the report requirements.

1. The 1_<your initials>_ORDER_AUDIT query should already be open. If it is not, click the Open toolbar button, select the file from the list and then click Open. Open the Query Builder to begin the requested modifications.

2. Click the Fields tab.

3. First, change the format of the Order_ID by right-clicking O.Order_ID in the Selected Fields list and then select Field Format.

4. Use the Format Field window to change the display of the order identification numbers in your output. Notice the formatting options are different for this field because it is a numeric field. Select the fourth format option from the list (####).

5. You need the Order_ID to display nine digits, so you need to modify the format. After the last pound (#) sign but before the quote, enter an additional five pound signs to make a total of nine. Click Insert when you finish.

6. Notice that the new format, "#########", has been added to the end of the O.Order_ID field in the Selected Fields list. It is always a good idea to check your output as you go along. Therefore, run your query now to make sure everything is correct.

7. The output looks good; however, the system manager specifically requested that the output show nine digits padded with zeros. Close the Output window so you can add the padding with zeros.

8. Right-click the O.Order_ID field and select Field Format to make the modification.

9. After the display template (9 fill characters), enter a semi-colon and then a P and then a 0. Your format should look like the following: "#########;P0". Click Insert when you are finished.

10. Before you run the query again, change the format of the Order Date to include a time. Right-click O.ORIG_ORDER_DT_TM and select Field Format.

11. Notice that the format you selected earlier displays. You do not see a format in the list that meets your needs, so you need to manually enter the format. The text in the Field Format box is already selected, so enter "mm/dd/yyyy hh:mm;;d". Note that you must include the quotes. Click Insert to add the new format.

12. Run your query to check your results.

13. The Order_ID column displays nine digits padded with zeros and the date and time should display with the order date. Close the Output window and the Discern Query Builder window.

14. Save your file by clicking the Save toolbar button or from the File menu, select Save.

You have completed the activity for Modifying Field Formats. Return to the Table of Contents of the WBT and click on the topic, Creating Expressions.

Creating Expressions

Building an Expression

Build a query that includes person information, including their age and date of birth. Since a person's age is not stored in the database, you need to create an expression that calculates it at run time.

In this practice activity, create an age expression.

1. You want to create a new prompt program by creating a file named 1_<your initials>_PERSON_AGE.vcl. First, execute CCLPROT to ensure you do not overwrite an existing object.

2. The query uses many of the same data elements as the query in the source code file 1_<your initials>_PERSON_DOB query you created earlier. Rather than start a new query from scratch, you can open that file and save it with the new name. To open an existing file, click the Open toolbar button, select the file, and click Open.

3. Save the file with the new name. From the File menu, select Save As. Enter 1_<your initials>_PERSON_AGE in the File Name box, and click Save.

4. Open the Query Builder to modify the query.

5. First you need to add gender, which is stored as a coded field. Go to the FIELDS tab and select SEX_CD to add it to the list of selected fields.

6. You would like a four-digit year to display for the birthday and decide the MM/DD/YYYY format works best for you. Right-click P.BIRTH_DT_TM in the Selected Fields list and select Field Format. Select @SHORTDATE4YR, and click Insert.

7. Click the View Query tab and review the syntax for the display option for the date and the expression created by the Query Builder for the gender.

8. Run your query and verify that the BIRTH_DT_TM displays a four-digit year.

9. Once you have reviewed the output, close the window to continue modifying your query.

10. You need to display a person’s age in the output. Because age is not stored in the system, you must create an expression that calculates the person's age based on his or her date of birth. From the FIELDS tab, click Add Expression to open the Add Expression dialog box.

11. Notice the Function Builder option under the Functions list is checked by default. The Function Builder helps to build an expression with the parameters required for the function you selected. Find and double-click the CNVTAGE function to open the Build Function:CNVTAGE dialog box.

The CNVTAGE function can be used to calculate a person’s age by comparing the birth date and time to the current date and time.

12. First, you should assign the expression a name. If you do not assign a name, the Query Builder assigns one for you. In the Expression Alias box, enter AGE.

13. The required parameter for the CNVTAGE function is the birth date and time. Place your cursor in the box for the Date of birth parameter and double-click P.BIRTH_DT_TM from the Available Fields list to select it as the parameter.

The other parameters are optional and help to flex the way the CNVTAGE function calculates an age. For example, you could calculate a person’s age at the point of registration.

To read about the optional parameters, refer to the Discern Explorer Help pages:

https://wiki.ucern.com/display/public/1101discernHP/CNVTAGE+using+Discern+Explorer

14. Click OK. The expression, AGE=CNVTAGE(P.BIRTH_DT_TM), is written to the Add Expression dialog box in the lower-right section of the window. The Function Builder created the expression for you. If you did not want to use the Function Builder, you could have manually entered the expression in this box.

15. Click OK. The Age expression is displayed in the Selected Fields list.

16. Run your query and verify that the output has an AGE column with ages displaying. When you finish, close the Output window.

17. Save your file and close it.

Continue with the next practice activity.

Using Concat and Trim Functions

An audit is being performed on the personnel records. Create a query that contains a list of all staff names, their positions, and the date and time each record was entered in the system.

In this practice activity, combine first and last names into one column by using the CONCAT and TRIM functions.

1. Instead of beginning a new query, you decide to modify an existing query. Open the 1_<your initials>_PRSNL_INFO file you created previously.

2. Execute the program and review the output. A person’s last and first name are displayed in different columns. The requested format is to have the first name and then the last name together in one cell of the output. You need to create an expression that combines, or concatenates, the first and last name fields into one.

3. Open the Query Builder and click the FIELDS tab.

4. Click Add Expression.

5. Discern Explorer has a function that combines character strings into a single value. Ensure that the Function Builder option is selected, and double-click the CONCAT function in the Functions list.

6. Enter Name in the Expression Alias box.

7. Double-click Name_First from the Available Fields list to add it as the first parameter.

8. Click in the second box in the Parameters section and double-click NAME_LAST to add it as the second parameter.

9. Click OK to finish creating the NAME expression.

10. The NAME expression syntax is displayed in the pane in the lower right side of the dialog box. It looks like:

NAME = CONCAT(PR.NAME_FIRST, PR.NAME_LAST).

Click OK to add the expression to the Selected Fields list.

11. You no longer need the individual name fields now that you have the name expression. Remove the two extraneous fields. In the Selected Fields list, remove NAME_FIRST and NAME_LAST.

12. You want to make Name the first column in your report. Select the NAME expression and move it to the top of the list by using the up arrow. You can also select the NAME expression and drag it to the top of the list.

13. After you make some preliminary edits, it is always a good idea to check your work to see if the query you built is correct thus far. Run the query.

14. Why are you only seeing the first name? The last name is hidden because there is a large amount of white space after the first name in the expression. When Discern Explorer concatenates fields, it allows for the total length of each field. Since the Name_First field is a VC200 data type, you are seeing the first name and then the rest of the spaces that make up the field. The last name is there, just 200 bytes over. You want to eliminate that space for this query. Close the Output window to make additional edits.

15. From the Selected Fields list, select the Name Expression, and click Edit Expression.

16. The TRIM function can be used to remove trailing spaces from a character value. Let’s use the TRIM function on the NAME_FIRST column. However, you do not want to use the Function Wizard. Deselect the Function Builder option.

17. Click between the open parenthesis and NAME_FIRST in the expression, and double-click the TRIM function to add it to the expression.

18. Notice the opening parenthesis of the TRIM function is inserted before NAME_FIRST. Add a closing parenthesis between NAME_FIRST and the comma. It will look like the following example:

NAME = CONCAT(TRIM( PR.NAME_FIRST), PR.NAME_LAST)

19. Click OK and run the query to see how the TRIM function is applied.

20. The report displays with first and last names concatenated, but now there is no space between the first and last names. Close the Output window so you can edit the expression again.

21. Select the NAME expression and click Edit Expression.

22. You need to add a space between the first and last names. Click just before NAME_LAST and enter a quotation mark, a space, a quotation mark, and a comma. The expression should look like the following:

NAME = CONCAT(TRIM( PR.NAME_FIRST)," ",PR.NAME_LAST)

23. Click OK to save the changes.

24. Run the query. The first and last name should be displayed in the NAME column with a space between them. Close the Output window.

25. Save and close your file.

Continue with the next practice activity.

Using Evaluate Function

In the previous activity, you used functions to create an expression that held the age value and also the name in the desired format. Functions, such as CNVTAGE and CONCAT, are available to assist in creating expressions. Functions are basically mini programs that accept parameters and return a value.

The Human Resources department has two messages they need to distribute through the email system-- one for physicians, the other for non-physicians. Create a query for them that lists the names of all staff and their email addresses and then differentiate between physicians and non-physicians. In this practice activity, use the evaluate function to define the display value for a field.

1. You want to create a new prompt program by creating a new file named 1_<your initials>_PHYS_LIST.vcl. Execute CCLPROT to ensure you do not overwrite an existing object.

2. You can use the 1_<your initials>_PRSNL_INFO file as a good starting point. Open the 1_<your initials>_PRSNL_INFO.vcl file you created earlier and save it as 1_<your initials>_PHYS_LIST.vcl, or another unique name.

3. Now start building your query. Open the Query Builder and go to the FIELDS tab.

4. Find the field that indicates whether the person is a physician. Did you find PYSICIAN_IND? Double-click the PHYSICIAN_IND field to add it to the Selected Fields list

5. Run the query and review the output.

6. Looking at the Physician_IND column, you realize that a value of yes or no would be more meaningful than a one or zero. Follow the next steps to create an expression that returns these types of values. Close the Output window.

7. This time you will use the EVALUATE function. The EVALUATE function returns an expression based on a condition. Click Add Expression to begin adding a new expression.

8. From the Functions list, double-click the EVALUATE function.

9. Enter Phys_Flag in the Expression Alias box.

10. You want to establish that a value of one means YES and a value of zero means NO. From the Available Fields list, double-click the P.PHYSICIAN_IND field to add it to the first parameters box.

11. In the Value to Compare to the Case Expression: box, enter 1 as the first value for the system to check.

12. In the Return Value if the Above Case Value = Case Expr: box, enter the character string "Yes". Be sure to enter the quotation marks. When a value of 1 is encountered in the PR.Physician_IND field, the system returns the character string Yes.

13. In the Value to Compare to the CASE Expression: box, enter 0.

14. In the Return Value if the above CASE Value = CASE expr: box, enter "No". Be sure to include the quotation marks. When a value of 0 is encountered in the PR.Physician_IND field, the system returns the character string No.

15. Click OK to add the expression.

16. Review the expression that the Function Builder built for you. Remember that you can manually enter the syntax for the expression if you want. Click OK to continue.

17. Run the query to see the changes that were made to the output.

18. Review the data in the expression column to confirm that each value of 1 correlates to the character string of Yes and that each value of 0 correlates to the character string No.

19. Close the Output window.

20. The query is ready for Human Resources. Save and close the file.

You have completed the activities for Creating Expressions. Return to the Table of Contents of the WBT and click on the topic, Working with Qualifications.

Working with Qualifications

Creating a Qualification

As you saw in the Understanding Qualifications demonstration, qualifications help to limit the data returned in your queries. The WHERE clause is used to specify which rows should be returned. There are five types of qualifications available. A single qualification is used to make one condition and each record must qualify on that condition.

Note: In the following practice activities, you will add qualifications to limit the amount of data returned. If you are using an environment that does not contain much data, your queries might not return any data. In those cases you need to expand or change the qualifications until you return data. For example, if the query limits data to a particular month, you might need to try another month or several months until you return data.

In this practice activity, you will create a query that has a single qualification. You will create a report that provides a list of encounters along with the date and time of registration and discharge. You want to qualify the query to include only active encounters.

1. You want to create a new prompt program by creating a new file named 1_<your initials>_DISCH_RPT.vcl. Execute CCLPROT to ensure you do not overwrite an existing object.

2. Save the file with the new name 1_<your initials>_DISCH_RPT.vcl, or another unique name.

3. Open the Query Builder to begin your query.

4. Since this query is to analyze encounter information, select the ENCOUNTER table and click the FIELDS tab.

5. Add the following fields and arrange them in the order listed:

Encntr_ID
Reg_DT_TM
Disch_DT_TM
Active_IND

6. Change the format of the date and time fields. Right-click REG_DT_TM and select Field Format.

7. Select @MEDIUMDATETIME and then click Insert.

8. Change the format for the DISCH_DT_TM to @MEDIUMDATETIME.

9. Run the query and review the output. Note that a value of 1 in the Active_IND column indicates that the encounter is active. Close the Output window.

10. You want to extract the encounters that have a value of one in the Active_IND field so your output lists only active encounters. Go to the QUALIFICATIONS tab to place restrictions on the query.

11. The QUALIFICATIONS tab is where we build the syntax needed to limit the list to active encounters only. Click WHERE to begin creating your syntax.

12. Double-click ACTIVE_IND in the Fields list.

13. Select the equals (=) sign from the operators list located to the right of the WHERE button.

14. Enter 1 at the end of the statement. Because you are qualifying on a numeric field, the value is entered without quotation marks. Your qualification should look like the following.

Note that you can also manually type the syntax in this box.

15. Run your query to verify the output.

16. Your output should have only rows with a value of 1 in the Active_IND column. Close the Output window.

17. Save your query and close the file.

You have completed the activities for Creating a Qualification. Return to the Table of Contents of the WBT and click on the topic, Working with Indexes.

Using Indexes

You want to create a query that shows all personnel names beginning with the letter A.

In this practice activity, create qualifications using indexed field to improve the performance of the query.

1. You want to create a new prompt program by creating a new source code file named 1_<your initials>_PRSNL_NAME.vcl. Execute CCLPROT to ensure you do not overwrite an existing object.

2. You can use a program you have already created as a starting point for the new program. Open the file named 1_<your initials>_PRSNL_INFO.VCL and using Save As, save the file with the new file name of 1_<your initials>_PRSNL_NAME.VCL.

3. Open the Query Builder and go to the FIELDS tab to begin modifying the query.

4. Add the NAME_LAST_KEY to the Selected Fields list and move the field to the top of list.

5. Run the query to review the output. Since there are not any qualifications yet, this query displays all personnel up to the number of rows applied by the MAXREC control option. Close the Output window.

6. You would like to limit the query to show only persons with a last name starting with A. Adding qualifications that use an index field is an effective way to improve the efficiency of the query and limits the data being returned for more meaningful output. Before the qualification is added, you should research to see if there is an index field that can be used in the qualification. Go to the FIELDS tab. In the Fields list there are yellow and red keys by the left side of some of the fields. The yellow key indicates that the field is used in one or many non-unique indexes. The red key indicates that the field is used as the unique index for that table.

7. Click the yellow key to the left of the NAME_LAST_KEY. A drop-down list displays the fields that make up that index.

The NAME_LAST_KEY is listed first in this composite index. If you add a qualification using this field, the chances of accessing this index is increased. Close the list.

8. If a field is used in multiple indexes, the drop-down list allows you to look through the different indexes where that field is used. Click the key that is directly to the left of NAME_FIRST_KEY_A_NLS. A drop down list appears that displays the fields that make up that index. Click on the right arrow to see how the field is used in the next index. Close the list.

9. Let’s review another way to view indexes. Click the Show Indexes option located under the Selected Tables list.

10. Click the QUALIFICATIONS tab. View the list of indexed fields by selecting the Show Indexes option located under the Table list. This view of the indexes displays each index and the fields that make up the index, similar to the Show Index view.

11. Notice there is an index that lists NAME_LAST_KEY as the first field for first index listed. Since the query qualifies on the person’s last name, the NAME_LAST_KEY can be used to do an index read on the PRSNL table.

12. Go to the QUALIFICATIONS tab so you can limit the data the query returns.

13. Qualifications start with a Where clause. Click the WHERE button to begin.

14. In this case, you will qualify on the NAME_LAST_KEY field. This is the preferred field to use in qualifying your query because it is an indexed field.

15. Double-click the NAME_LAST_KEY field.

16. Now select the equals sign (=) from the operators list located to the right of the WHERE button.

17. Since you want only last names starting with A, enter "A" after the equals sign. The system reads character strings literally, so if you enter a single letter, the system looks for only that letter. Use a wildcard (the asterisk) to find all instances where the last name starts with an A. You must also include quotes around the character string. Your qualification should look like the following example:

WHERE P.NAME_LAST_KEY = "A*"

18. Run the query to see what it looks like.

19. The query should have returned only personnel whose last names begin with the letter A. Close the output.

20. It is important to remember to add quotes around the character string. Try removing the quotes and then running the query to see what happens.

21. Click No. Add the quotes back and run the query. Verify the output contains only personnel whose last names begin with the letter A. Close the Output window.

22. Save and close your file.

You have completed the activity for Using Indexes. Return to the Table of Contents of the WBT and click on the topic, Using Date/Time Qualifications.

Using Date/Time Qualifications

In the last demonstration, you reviewed information about the CNVTDATETIME function and how it can be used to translate dates and times in different formats into one value that can be understood by the database.

In this practice activity, select a pre-defined date and time format that uses the CNVTDATETIME function when qualifying on a date.

1. The admitting director needs encounter information for all patients discharged in March of the previous year. You decide you can modify an existing file. Open the file named 1_<your initials>_DISCH_RPT.VCL.

2. Open the Query Builder and go to the FIELDS tab.

3. The admitting director wants the report to show the reason for the visit. In the Fields list, locate the REASON_FOR_VISIT field and add that field to the Selected Fields list.

4. Your query needs to find encounters discharged in a specific date range, which requires a qualification. Before adding the qualification, look at the indexes for the table and determine if there is an index field that can be used for qualifying on discharged patients. What index field did you find?

5. Click the QUALIFICATIONS tab and add to your qualification by typing AND or select AND from the operators list located to the right of the WHERE button.

6. Double-click DISCH_DT_TM to add it to the qualification.

7. Select the Greater Than or Equal To (>=) symbol from the operator list.

8. The Query Builder provides several sample conversion options to facilitate writing your qualification.

Select 31-DEC-2014 23:59:59.00 from the Date/Time Conversion Options list.

9. Notice that Query Builder automatically inserts the CNVTDATETIME function for you. Adjust the date and time to March 1^st of the previous year using the same format of DD-MMM-YYYY and the time to be at the beginning of the day. For example:

AND E. DISCH_DT_TM >= CNVTDATETIME("01-MAR-2014 00:00:00.00")

10. It is helpful to break your syntax into several rows to make it easier to read. Press ENTER to start a new line.

11. Add the AND operator to begin building the second half of your qualification.

12. Double-click DISCH_DT_TM to add it to the qualification.

13. Select the Less Than or Equal To (<=) operator.

14. Select the "31-DEC-2014 23:59:59.00" example from the Date/Time Conversion Options list again.

15. Adjust the date to March 31^st of the previous year using the same format of DD-MMM-YYYY. The time values already reflect the end of the day so they will stay the same. For example:

AND E.DISCH_DT_TM <= CNVTDATETIME("31-MAR-2014 23:59:59:00")

Your qualification should now look similar to the following:

WHERE E.ACTIVE_IND = 1

AND E.DISCH_DT_TM >= CNVTDATETIME("01-MAR-2014 00:00:00.00")

AND E.DISCH_DT_TM <= CNVTDATETIME("31-MAR-2014 23:59:59:00")

16. Your query should return only discharges that occurred between March 1st , starting at the beginning of the day, and March 31st, ending at the end of the day. Run the query to validate the data.

17. Review your output. The query should show only discharges that occurred in March of the previous year. If your does not return any data, your environment might not contain data for that time frame. You might need to adjust the month or year.

18. Close the Output window and the Query Builder.

Continue with the next practice activity.

Using Between in Qualifications

In the previous activity you specified a date range using the operators >= and <=. The Between operator is another way to specify a range that requires less syntax than the >= and <= operators, and is a commonly used technique. In this practice activity, use the Between operator to create a qualification for a date range.

1. The Admitting Director wants a report that shows only January and February admissions for the previous year. You want to create a new prompt program by creating a new source code file named 1_<your initials>_ADM_RPT.vcl. Execute CCLPROT to ensure you will not overwrite an existing object with the same name as1_<your initials>_ADM_RPT.

2. Use the source code file named 1_<your initials>_DISCH_RPT file as a starting point. Open that file and save it with the new name.

3. Open the Query Builder.

4. Go to the QUALIFICATIONS tab.

5. You need to insert a new qualification using the BETWEEN operator and use another date and time option. Instead of deleting the already existing qualifications, comment the lines for future reference by placing a semicolon as the first character on each of the lines in the existing qualification. Whenever a semicolon is detected, the system ignores everything to the right of it and treats it as a comment. The following shows an example of commented lines:

;WHERE E.ACTIVE_IND = 1

;AND E.DISCH_DT_TM >= CNVTDATETIME("01-MAR-2014 00:00:00:00")

;AND E.DISCH_DT_TM <= CNVTDATETIME("31-MAR-2014 23:59:59:00")

6. Below the commented out code, add the following qualification:

WHERE E.REG_DT_TM

7. Since you want data for a two month time span, select the BETWEEN operator.

8. This time select the CNVTDATE(123114),0 example from the Date/Time Conversion Options list. The zero represents the time value for the beginning of that day. Notice again that Query Builder automatically inserts the CNVTDATETIME function for you.

9. Change the date from 123114 to January 1^st of current year in MMDDYY. If the year is 2015, then the date should be 010114.

10 On the next row, select the AND operator.

11. Select the CNVTDATE(123114),235959 example from the Date/Time Conversion Options list. The number 235959 is representing the last moment in time for that day.

12. Change this date from 123114 to February 28^th of the previous year in MMDDYY such as 022814.

The qualification should now look similar to the following:

WHERE E.REG_DT_TM BETWEEN CNVTDATETIME(CNVTDATE(010114),0)

AND CNVTDATETIME(CNVTDATE(022814),235959)

13. Run the query to verify the output. Only admissions for January and February of the previous year should display. If you do not return any rows, you might need to change the qualification to different months or a different year.

14. Save your query and close the file.

You have completed the activities for Using Date/Time Qualifications and Using Between in Qualifications. Return to the Table of Contents of the WBT and click on the topic, Multiple Qualifications.

Creating Multiple Qualifications

As you saw in the previous demonstration, you must be careful about writing syntax with multiple qualifications. Evaluate what data you want returned and then add parentheses to group the qualifications appropriately.

In this practice activity, create a query with multiple qualifications. The results should display order information for orders placed in January of the previous year for two specific orders.

1. You want to create a new prompt program by creating a new file named 1_<your initials>_JAN_ORDERS.vcl. Execute CCLPROT to ensure you do not overwrite an existing object with the same object name.

2. Once you have selected a unique name, save your file.

3. Open the Query Builder to begin creating the query.

4. The data to be included in the report are orders. From the TABLES tab, select the ORDERS table, and then go to the FIELDS tab.

5. Select the following fields and arrange them in the order listed.

Order_Mnemonic
Order_Status_CD
Orig_Order_DT_TM

6. Run the query.

7. Select two procedures listed in the ORDER_MNEMONIC column and write down their mnemonics. Be sure to note any capitalization. This course uses the Comprehensive Metabolic Panel (CMP) and BUN procedures as examples. Close the Output window.

8. Click the QUALIFICATIONS tab. Add the start of a qualification to limit the output to only the two procedures by typing:

WHERE O.ORDER_MNEMONIC =

9. After the equals sign, enter the order mnemonic of the first order, such as "CMP". You are qualifying on a character field, so enclose the text in quotes.

10. Add the OR operator and then the field, O.ORDER_MNEMONIC. Make the O.Order_Mneumonic equal to the second order mnemonic in quotes. Your qualification should look similar to the following:

WHERE O.ORDER_MNEMONIC = "CMP" OR O.ORDER_MNEMONIC = "BUN"

11. Run the query and validate that the query returns the two orders you defined in the qualification. Close the Output window.

12. Add the AND operator as a start for adding a date and time qualification.

13. You want to add a qualification to only include procedures ordered within a specific month in the previous year. Double-click O.Orig_Order_DT_TM.

14. Since you want to qualify on a range of dates, select the BETWEEN operator.

15. Select "31-DEC-2014 23:59:59:00" from the Date/Time Conversion Options list.

16. Change the date and time to January 1^st of the previous year, such as "01-JAN-2014 00:00:00.00".

17. Begin another new line to make this qualification easier to read. Press ENTER.

18. Select the AND operator, and select "31-DEC-2014 23:59:59:00" from the Date/Time Conversion Options list.

19. Change the date and time to January 31^st of the previous year, such as "31-JAN-2014 23:59:59.00". Your qualification should look similar to the following:

WHERE O.ORDER_MNEMONIC = "CMP" OR O.ORDER_MNEMONIC = "BUN"

AND O.ORIG_ORDER_DT_TM BETWEEN CNVTDATETIME("01-JAN-2014 00:00:00.00")

AND CNVTDATETIME("31-JAN-2014 23:59:59:.00")

20. Run the query to check the output. Again, you might need to adjust the date qualification if your output does not return any data.

21. Look carefully at the data. The output is not what is expected. The first procedure in the qualification displays and the dates are outside of the expected range or the second procedure displays within the date range. To prevent this, you must group your qualification into logical groups using parentheses to guide the processing. Close the Output window to modify your syntax.

22. Because you want to find the two orders, and then apply the date range qualification, you must insert parentheses around the order mnemonic qualification. Add open parentheses before the first O.ORDER_MNEMONIC and close parentheses after the name of the second order.

23. Parentheses should also be placed before and after the date/time qualification. This allows the date and time range qualification to be applied to the subset of ordered procedures. Add open parentheses before O.Orig_Order_DT_TM and close parentheses at the end of the syntax. Your qualification should look similar to the following:

WHERE (O.ORDER_MNEMONIC = "CMP" OR O.ORDER_MNEMONIC = "BUN" )

AND (O.ORIG_ORDER_DT_TM BETWEEN CNVTDATETIME("01-JAN-2014 00:00:00.00")

AND CNVTDATETIME("31-JAN-2014 23:59:59:.00") )

24. Run the query and verify your output. Again, you might need to adjust the date in your qualification if your output does not return any data.

25. Run the query and verify your output. You should see both of the procedures, such as the CMP and

BUN, and the Order date should be limited to January of the previous year. Review the data.

26. Close the Output window.

27. Close the Query Builder.

28. Save and close your file.

You have completed the activity for Creating Multiple Qualifications. Return to the Table of Contents of the WBT and click on the topic, Understanding Joins.

Joining Tables

Creating a Join

In this practice activity, you will build a query that uses two tables to return order and person information in the output. The output should return a list of persons who had a particular procedure ordered in a specific month the previous year.

1. Begin by opening the file 1_<your_initials>_ORDER_AUDIT.vcl you previously created.

2. Run the query so that you can view the order mnemonics in your system.

3. Write down the order mnemonic for one of the procedures. The procedure name CMP will be used as an example in this activity. Close the Output window.

4. You can use this file as a starting point for a new prompt program. First, you want to name your new file 1_<your_initials>_SINGLE_ORD.vcl. Execute CCLPROT to ensure you do not overwrite an existing object with the same object name.

5. Use Save As to save the file with the new name, and open the Query Builder.

6. The ORDERS table is already selected. You also want a list of patient names in the report, so from the TABLES tab, add the PERSON table.

7. Notice the alias that has been assigned to each table in the Selected Tables list. Go to the FIELDS tab.

8. Both tables now display in the Selected Tables list and the first table, ORDERS, is selected. The Fields list displays only the fields for the selected table, in this case ORDERS. You need to add the ORDER_STATUS_CD field. Scroll down and double-click this field.

9. From the PERSON table, add the NAME_FULL_FORMATTED and the PERSON_ID field. Move these fields to the top of the Selected Fields list.

10. Now that there are two tables referenced in the query, the tables must be linked using a common field. Study the indexes for the ORDERS and PERSON table and identify the common fields between the two tables. What common fields did you find?

_______________________________________________________________________________

11. Did you find: UPDT_DT_TM and PERSON_ID? Do not use the UPDT_DT_TM field to link the tables because the date and time represent when that specific row was updated, however, it doesn’t represent any correlation between the PERSON and ORDERS tables. That leaves PERSON_ID. The PERSON_ID is the primary or unique key on the PERSON table. The PERSON_ID resides on the ORDERS table and helps to identify who that order belongs to. This field is an index on the ORDERS table. Since a person can have multiple orders, they can have multiple rows on the ORDERS table identified by the PERSON_ID, which is considered a foreign key.

12. Go to the QUALIFICATIONS tab to begin linking the ORDERS table to the PERSON table.

Note: Running a query where the tables have not been linked is extremely resource intensive and should never be executed. If you query tables without linking them, the system does not know which order to pair with which patient, so every record in the ORDERS table is returned and erroneously matched with every row from the PERSON table causing a cartesian product and a waste of system resources. Using the common field to link the tables establishes the correct match between the tables.

The PLAN/JOIN syntax establishes a link between the tables being read. You create the syntax for linking tables in the qualification section. The PLAN is used to identify the table that you intend to be read first. The table you intend to read first should be the table that returns the fewest amounts of rows using the most restrictive index.

13. Start to build the syntax by clicking PLAN.

14. Identify the alias for the PERSON table and enter the alias to the right of the word PLAN. For this example the alias is P. Click JOIN to begin the next piece of the syntax.

15. Identify the alias for the ORDERS table and enter O after the JOIN statement.

16. Click WHERE to begin adding the linking statement.

17. Select P.PERSON_ID, enter the equals sign (=) and then select O.PERSON_ID. Note that you also could reverse this portion of the qualification and add O.PERSON_ID = P.PERSON_ID instead. Your qualification will look similar to the following syntax:

PLAN P

JOIN O WHERE P.PERSON_ID = O.PERSON_ID

18. You need to restrict the query to show only the orders for the procedure you previously wrote down. Select the AND operator from the list and then add O.ORDER_MNEMONIC.

19. Select the equals (=) operator, and enter the name of the procedure, such as "CMP", (including the quotes) to limit the query to the specified order. The value in quotes is case sensitive and must be typed exactly how it is in the database. Your syntax should look similar to the following. (You probably have another procedure in place of CMP.)

PLAN P

JOIN O WHERE P.PERSON_ID = O.PERSON_ID

AND O.ORDER_MNEMONIC= "CMP"

20. Run the query and validate that the output displays rows for only the specific order that you selected in your qualification. The results show persons that have a matching row on the orders table. For each PERSON_ID that has a match on the ORDERS table, the results bring back a row for each order, duplicating the person information.

21. Write down the month and year of when one of the orders was placed and close the Output window.

22. You need to limit the data to include the specific order placed within a certain month and year. On a new row, add the AND operator and the O.ORIG_ORDER_DT_TM field.

23. Add the BETWEEN operator and select the CNVTDATE( 123114 ), 0 date and time conversion option.

24. Add the AND operator, and select the CNVTDATE( 123114 ), 235959 date and time conversion option.

25. Modify the dates so the query only returns results for a specific month and year that you previously identified. The following is an example showing a date range qualification:

PLAN P

JOIN O WHERE P.PERSON_ID = O.PERSON_ID

AND O.ORDER_MNEMONIC= "CMP"

AND O.ORIG_ORDER_DT_TM BETWEEN CNVTDATETIME(CNVTDATE(010114), 0)

AND CNVTDATETIME(CNVTDATE(013114), 235959)

26. Run the query.

27. Review the results. Your query should show a list of patients with the selected procedure, such as CMP, ordered within the month specified in the qualification.

PERSON_ID	NAME_FULL_FORMATTED	ORDER_MNEMONIC	ORDER_ID	ORDER_COMMENT_IND	ORIG_ORDER_DT_TM
677925.00	Taylor, Marcus	CMP	12211383.00	0	01/23/2014
677926.00	Lane, Michael	CMP	12211387.00	0	01/23/2014
677928.00	Levine, Edward	CMP	12211391.00	0	01/25/2014
677930.00	Holland,Bo	CMP	12211392.00	0	01/26/2014

Note that if you were to create the same query using a single WHERE clause, the qualification would be:

WHERE P.PERSON_ID = O.PERSON_ID

AND O.ORDER_MNEMONIC = "CMP"

AND O.ORIG_ORDER_DT_TM BETWEEN CNVTDATETIME(CNVTDATE(010114),0 )

AND CNVTDATETIME(CNVTDATE(013114), 235959 )

28. Close the Output window and the Query Builder, and save your file.

You have completed the activity for Creating a Join. Return to the Table of Contents of the WBT and click on the topic, Complex Joins.

Joining Three Tables

The hospital administrator needs a census of all patients currently registered in your system.

1. You want to create a new prompt program by creating a new file named 1_<your initials>_ Census_Rpt.vcl. Execute CCLPROT to ensure you do not overwrite an existing object with the same object name. Once you have a unique name, save the file.

2. Open the Query Builder.

3. The data you need for this query is located on three tables, but you should add only the first two to test your Join before adding a third table. Select the ENCOUNTER and ENCNTR_DOMAIN tables first. The ENCNTR_DOMAIN table captures current census information. Since this table is not listed in the COMMON folder, enter ENCNTR_DOMAIN in the Table Filter box and then select the MILLENNIUM category. Select the table and then clear out the text in the Table Filter.

4. Click the FIELDS tab.

5. Select the following fields from the ENCOUNTER table, and arrange them in the order listed:

LOC_NURSE_UNIT_CD
LOC_ROOM_CD
LOC_BED_CD
ENCNTR_TYPE_CLASS_CD
REG_DT_TM.
ENCNTR_ID

6. Select the ENCNTR_DOMAIN_TYPE field from the ENCNTR_DOMAIN table.

7. Review the indexes on both tables to find the common field to use for linking. What common field did you find?

_____________________________________________________________________________________

8. Click the QUALIFICATIONS tab to begin creating the Join.

9. You should start the qualification using the table that has the least amount of rows on the table or the table that you have the best index read. The ENCNTR_DOMAIN table typically has fewer rows than the ENCOUNTER table as this table stores current encounter information. Click the PLAN button and enter the alias for ENCNTR_DOMAIN table.

10. You need to join to the ENCOUNTER table. Click JOIN and enter the alias.

11. Click WHERE to begin adding the linking statement.

12. The ENCNTR_ID field is common to both tables. To the right of WHERE add ENCNTR_ID = ENCNTR_ID. Make sure to put the proper alias in front of the field names. The linking statement should be similar to the following:

WHERE E. ENCNTR_ID = ED.ENCNTR_ID

13. Run the query to make sure no syntax errors were introduced and that your output displays encounters that have a matching row on the tables. The output might be more readable if all of the rows for a specific nursing unit were grouped together. Follow the next steps to sort the data by nursing unit. Close the Output window.

14. Select the PERSON table and then go to the FIELDS tab.

15. Select the NAME_FULL_FORMATTED and PERSON_ID fields from the PERSON table.

16. Review the indexes to find the common field between the PERSON and ENCOUNTER to use for linking. What common field did you find?

___________________________________________________________________________

17. Go to the QUALIFICATIONS tab to join the PERSON table.

18. Insert JOIN on the next line and then the alias for the PERSON table.

19. Click WHERE to start the linking statement.

20. The PERSON_ID is the common link. Add PERSON_ID = PERSON_ID. Make sure to put the proper alias in front of the field names. Your qualification should look similar to the following:

PLAN ED

JOIN E WHERE E.ENCNTR_ID = ED.ENCNTR_ID

JOIN P WHERE P.PERSON_ID = E.PERSON_ID

21. This qualification joins all three tables. Run the query and validate that for each encounter in the output, their name and ID is also displayed. Click the View Program tab and review the syntax of the entire query.

22. When you are finished, close the Output window, the Query Builder, and save the file.

Remember, the same qualification to join tables can be written using single WHERE syntax:

WHERE E.ENCNTR_ID = ED.ENCNTR_ID

AND P.PERSON_ID = E.PERSON_ID

You have completed the activity for Joining Three Tables. Return to the Table of Contents of the WBT and click on the topic, More Join Logic.

Creating a Non-Linear Join

In this practice activity, join three tables. Create a non-linear join path by joining the PERSON and ORDERS table and then join the PERSON table to the ADDRESS table.

1. You want to create a new prompt program by creating a new file named 1_<your initials>_ ADDRESS_RPT.prg. Execute CCLPROT to ensure you do not overwrite an existing object with the same object name as 1_<your initials>_ ADDRESS_RPT. Once you have a unique name, save the file, and open Query Builder.

2. It is a good idea to first join two tables and verify the output before you add a third table. From the TABLES tab, select the PERSON and ORDERS tables.

3. From the FIELDS tab, select the following fields:

PERSON_ID
NAME_FULL_FORMATTED
ORDER_ID
ORDER_MNEMONIC

4. Review the indexes to find the common field between the PERSON and ORDERS table to use for linking. What common field did you find?

_________________________________________________________________________________

5. Select the QUALIFICATIONS tab to add the qualification for linking tables.

6. Use PLAN and JOIN syntax to join the ORDERS table to the PERSON table whenever the PERSON_ID of the ORDERS table is the same as the PERSON_ID of the PERSON table. Your qualification should look similar to the following:

PLAN P

JOIN O WHERE O.PERSON_ID = P.PERSON_ID

7. Add a qualification on the PERSON table to eliminate any rows if the PERSON_ID is 0.0. Your PLAN statement should look like the following:

PLAN P WHERE P.PERSON_ID != 0.0

8. Run the query to make sure there are no errors and the output displays people and their orders.

9. Close the Output window, return to the TABLES tab and add the ADDRESS table. To find the ADDRESS table, use the Table Filter, type ADDRESS and click the MILLENNIUM folder.

10. Add the following fields from the ADDRESS table:

Street_Addr
State
Zipcode
Parent_Entity_ID
Parent_Entity_Name

11. As the addresses belong to a person and not an order, you will join the ADDRESS table to the PERSON table to get the addresses. Review the indexes to find the common field between the PERSON and ORDERS table to use for linking. What common field did you find?

______________________________________________________________________________

12. Select the QUALIFICATIONS tab and join the ADDRESS table to the PERSON table. Check your qualification against the following example. Your qualification should look similar the following:

PLAN P

JOIN O WHERE O.PERSON_ID = P.PERSON_ID

JOIN A WHERE P.PERSON_ID = A.PARENT_ENTITY_ID

13. Run the query and review the results.

14. The results could potentially show more than one address per person. That address could potentially belong to a business that has the same PARENT_ENTITY_ID as the PERSON_ID. To ensure that you are only returning addresses for a person and not a business, you need to distinguish what table should be used to reference back to the ADDRESS table. Add the following to the qualification:

AND A.PARENT_ENTITY_NAME = "PERSON"

15. Your qualification should now look like the following:

PLAN P

JOIN O WHERE O.PERSON_ID = P.PERSON_ID

JOIN A WHERE P.PERSON_ID = A.PARENT_ENTITY_ID

AND A.PARENT_ENTITY_NAME = "PERSON"

16. Run the query and review the results. Your query should return people, their orders and addresses.

17. When you are finished, close the Output window and the Query Builder.

18. Save and close the file.

You have completed the activity for Creating a non-Linear Join. Return to the Table of Contents of the WBT and click on the topic, Qualifying on Coded Fields.

Qualifying on Coded Fields

Making the Qualification Portable - Qualify on Males

The prolog is the section of the program where you place commands you want to execute before the main SELECT command. A common command used in the prolog section is to set global variables that are used throughout the program. The EPILOG section is the section of the program where you place commands you want to execute after the main SELECT statement is executed.

In this practice activity, look up code set and code value information to use in commands placed in the prolog section that makes your code portable to any environment.

1. As part of a census, you will create a program that lists the names and ages of all males currently in your system. You want to create a new prompt program by creating a new file named 1_<your initials>_PERSON_MALES.vcl. Execute CCLPROT to ensure you do not overwrite an existing object with the same object name.

2. Once you have a unique name, open the previously created file, 1_<your initials>_PERSON_AGE, and use Save As to save it with the new name.

3. Open the Query Builder and click the TABLES tab and select the PERSON table.

4. From the FIELDS tab locate the SEX_CD field and note the data type

5. Because code_values are assigned during the build process, the actual code_value number can vary between domains and environments. If you are building a qualification and reference a specific code_value, that qualification may only work as you expect for that domain. When that program moves to a new domain, you would need to check what the code_value is for that domain and change the qualification to reference the proper value. Instead, you should find out the code_value for that domain before the query is executed. In this activity, we want to query only for males. Because gender is stored as a coded field, you need to find the code value for this specific domain before the main query runs. First you need to know the code set used to store the gender values. Right-click SEX_CD, select Properties and note the code set number.

6. The code set for SEX_CD is 57. Remember that the code set is defined by Cerner and does not vary between domains or facilities. You need to find the CDF Meaning value for males. Select SEX_CD and click Lookup.

7. The CDF Meanings are Cerner-defined and give code values universal and consistent meaning across domains and facilities. Record the textual value from the CDF_MEANING column.

8. Close the Output window and the Field Properties window.

9. Follow the next steps to create the necessary statements to capture the code value for male in the specified domain that executes before your main query command. The prolog section is a place where you can place these commands and have them executed before the main query. From the Tools menu, select Prolog/Epilog Commands.

10. You need to define the global variable. Think of the global variable as a holding field. In the Prolog section of the window, enter

DECLARE MALE_VAR = F8

This statement establishes MALE_VAR as the global variable with an F8 data type. You chose the F8 data type because that is the same data type as the P.SEX_CD field.

11. The global variable, MALE_VAR, is going to be used to store the code value for Male. The recommended way to populate this variable is by using a UAR (User Access Routine). On the next line enter:

SET MALE_VAR = UAR_GET_CODE_BY ("MEANING",57,"MALE")

Your Query / Report Additional Commands window should look similar to following:

12. Click OK when you finish.

13. Now that you have established a global variable to capture the specified code value, you can use MALE_VAR to qualify on the SEX_CD field in your query. Enter the following qualification:

WHERE P.SEX_CD = MALE_VAR

14. Run the query to check the output. The query should have returned only males. Close the Output window and close the Query Builder.

15. Save your query.

16. The Add Code Value Builder can also be used to populate the variable. The builder automatically adds the UAR_GET_CODE_BY() routine for you. First, access the Prolog/Epilog section from the Tools menu and comment out the DECLARE and SET commands by placing a semi-colon (;) in front of the commands.

17. Click Add and select Add Code Value Lookup. The Add Code Values dialog box opens.

18. Click the Code Set Wizard . The Code Set Wizard dialog box opens.

19. Click the ellipsis button . The Available Code Sets window displays.

20. In the Code Set box, enter 57, and click OK. The Code Set Wizard opens and displays all of the code_values for that code_set.

21. In the Select Code Value box, select MALE, and click Add.

22. The Add Code Values dialog box opens again with the grid populated.

Click OK.

You should now see the commands to create and populate the global variable written to the Prolog Commands section that looks similar to the following:

DECLARE MALE_VAR = F8 WITH CONSTANT(UAR_GET_CODE_BY("MEANING",57,"MALE")),PROTECT

The builder populates the global variable using a different technique, but accomplishes the same thing of populating the global variable. The CONSTANT declare option forces the variable to not allow the value of the variable to change throughout the life of the program. The PROTECT scoping option ensures that a variable in a parent process with the same variable name cannot override the value in the current process.

23. Execute the program and validate that the query only return males. Close the Output window.

24. Click Save, and from the File menu, select Close.

Continue with the next practice activity.

Making a Qualification Portable – Qualify on an Order

In the previous exercise, you created and populated a global variable that stored the Code_Value for males. You then used that global variable in the qualification to limit the result set to only display males. Next you will get more experience making your programs portable by creating a report that qualifies on a specific order.

In this practice activity, you will look up code set and code value information to be used to populate a variable which then can be used in a qualification. Using this technique will ensure your program is portable to any environment.

1. You will create a query that shows only a specific order. This activity will use the procedure name Blood Urea Nitrogen (BUN) as an example. First, execute CCLPROT to verify that the object name, 1_<your initials>_ORDER_RPT, is unique.

2. You decide to modify an existing query rather than start one from the beginning. Open the 1_<your initials>_ORDER_AUDIT.vcl and save it with the new name, 1_<your initials>_ORDER_RPT, or another unique name.

3. Open the Query Builder. From the FIELDS tab, locate CATALOG_CD and note the data type.

You want to limit the result set to only show a specific order. For this example, we want to only see the Blood Urea Nitrogen orders. The order procedure names are stored in the ORDER_MNEMONIC field and also on the CATALOG_CD field. To limit the results to that specific order, a qualification is needed. But first, look at the Fields list and compare the ORDER_MNEMONIC to the CATALOG_CD to decide which one is best to use in the qualification. What difference did you notice?

Using indexes for qualification is one of the most important things you can do to ensure the query runs as efficiently as possible. The CATALOG_CD is an indexed field and would be more efficient to use in a qualification. To complete the qualification, we need to know the code_value that represents Blood Urea Nitrogen.

4. Right-click on CATALOG_CD to access the Properties. Write down the code_set:

Code_Set:________________________________________________________________________

5. Click Lookup.

6. Find Blood Urea Nitrogen and note the capitalization of the description. Also write down the Code_value for the orderable.

Description:_________________________________________________________________

Code_value:_________________________________________________________________

Close the Output window and the Properties.

7. From the QUALIFICATIONS tab, add a qualification to only display orders for Blood Urea Nitrogen. Your qualification should look similar to the following; however, your code_value will most likely be a different number then the one shown here:

WHERE O.CATALOG_CD = 2921234.00

8. Run the query and validate that you are only seeing data for the specific orderable. Close the output window. If your database does not have any orders for BUN, select a different order name and change the code value so that you have rows returned in your output.

Since the code_value can change in different domains, it is recommended to populate a global variable with the code_value used for this specific domain before the SELECT command is executed. Then the qualification can reference the global variable instead of the hard coded number.

9. From the Tools menu, select Prolog/Epilog Commands.

10. Define and populate a global variable called BUN_VAR by either using DECLARE and SET or by using the Add Code Value builder.

The commands to populate the global variable should look similar to one of the following:

declare bun_var = F8

set bun_var = uar_get_code_by("displaykey",200,"BLOODUREANITROGEN")

declare bun_var = f8 with Constant(uar_get_code_by("displaykey",200,"BLOODUREANITROGEN"))

,protect

11. Click OK to exit the Prolog Commands window.

12. You need to modify the qualification to use your global variable, for example:

WHERE O.CATALOG_CD = BUN_VAR

13. Run the query.

14. Only the procedure you added to the qualification should have been returned. Close the Output window.

15. Close the Query Builder and save your query.

16. Close the file.

Continue to the next practice activity.

Qualify on Coded Fields, Joining Two Tables

Your need to send a schedule of events to all persons listed in the system. This schedule is to be sent to their homes, so you need to build a query that lists the names and home addresses of all people in the system.

1. You want to create a new prompt program by creating a new file named 1_<your initials>_PERSON_MAIL .vcl. Execute CCLPROT to ensure you do not overwrite an existing object.

2. Open the Query Builder.

3. Since you need person and address information, select the PERSON and ADDRESS tables.

4. Select NAME_FULL_FORMATTED from the PERSON table.

5. Select the following fields from the ADDRESS table and arrange them in the order listed:

Street_Addr
City
State
Zipcode
Address_Type_CD
Parent_Entity_Name

6. Now add the Join. Read the PERSON table first, and join the ADDRESS table. Be sure you join them using the ID fields from the list of indexes. Create the Join and run the query to check the output. If you need help, refer to the following example.

Your qualification should look like the following:

PLAN P

JOIN A WHERE A.PARENT_ENTITY_ID=P.PERSON_ID

7. Run your query to check the output. Look at the results specifically in the PARENT_ENTITY_NAME column. Notice that some organization addresses are included in your list. Remember, this is because the ADDRESS table stores both person and organization addresses. The value in the PARENT_ENTITY_NAME field indicates the table of the related data. You need to write the qualification so that it includes only person addresses because you want the schedule of events to be sent to people, not businesses. Review the values in the PARENT_ENTITY_NAME field and write down the value that you need for the qualification. Close the Output window and then modify the qualification so that the output includes only person addresses. If you need help, refer to the following example.

Your qualification should look like the following:

PLAN P

JOIN A WHERE A.PARENT_ENTITY_ID = P.PERSON_ID

AND A.PARENT_ENTITY_NAME = "PERSON"

8. Run the query again to check the output and validate that only addresses returned are for a person. Look at the results in the ADDRESS_TYPE_CD column.

9. The ADDRESS_TYPE_CD field is showing both business and home addresses for a person. You need to modify the qualification one more time so that only home addresses are displayed. Close the Output window to change the qualification.

10. Since ADDRESS_TYPE_CD is a coded field, the process of limiting the addresses is similar to the process you used in a previous query where you limited the query to only males. First, find the code set number, note one of the textual values for the code set and what column the text is coming from. Then, create and populate a variable and add a qualification. Give it a try, and if you need help, refer to the following steps:

a) From the FIELDS tab, right-click on ADDRESS_TYPE_CD and access Properties

b) Document the code set number

c) Click Lookup

d) Document the textual value for Home and the column it is listed under

e) Close the Output window

f) From the Tools menu, select Prolog/Epilog Commands

g) Enter:

Declare home_ind = f8

Set home_ind = uar_get_code_by("MEANING",212,"HOME")

h) Click OK to exit the Prolog Command window.

i) Add the following to the qualification:

AND A.ADDRESS_TYPE_CD = HOME_IND

11. Run the query and validate that only home addresses are returned in the output.

12. Close the output window. Save the file.

13. Continue with the next practice activity.

Qualify on Coded Fields, Joining Three Tables

For your final query in this section, you need a report that lists medical services and the patients name and encounter information for services used in the previous year. The data will come from three tables: PERSON, ENCOUNTER and ENCNTR_ALIAS.

1. You want to create a new prompt program by creating a new file named 1_<your initials>_MED_SERVICE.vcl. Execute CCLPROT to ensure you do not overwrite an existing object with the same object name.

2. Open the Query Builder and select the PERSON and ENCOUNTER tables.

3. From the FIELDS tab, select the following fields and arrange them in the order listed.

E.MED_Service_CD
P.Name_Full_Formatted
E.ENCNTR_ID
E.Reg_DT_TM
E.Disch_DT_TM
E.Reason_For_Visit

4. From the QUALIFICATIONS tab, use the PLAN to read the ENCOUNTER table first and add a qualification to only select rows where the person was registered last year.

5. Use the JOIN to link the PERSON table to the ENCOUNTER table.

Your qualification should look similar to the following:

PLAN E WHERE E.REG_DT_TM BETWEEN CNVTDATETIME("01-JAN-2014 00:00:00.00")

AND CNVTDATETIME("31-DEC-2014 23:59:59.00")

JOIN P WHERE P.PERSON_ID = E.PERSON_ID

6. Run your query and validate that you are getting encounter information and the person’s name.

7. Close the Output window

8. Next, add the ENCNTR_ALIAS table and the following fields:

· EA.Alias

· EA.Encntr_Alias_Type_CD

9. Use the JOIN to join the ENCNTR_ALIAS table to the ENCOUNTER table.

Your qualification should be similar to the following:

PLAN E WHERE E.REG_DT_TM BETWEEN CNVTDATETIME("01-JAN-2014 00:00:00.00")

AND CNVTDATETIME("31-DEC-2014 23:59:59.00")

JOIN P WHERE P.PERSON_ID = E.PERSON_ID

JOIN EA WHERE EA.ENCNTR_ID = E.ENCNTR_ID

10. Run the query and validate that the results display aliases for an encounter. Look at the ENCNTR_ALIAS_TYPE_DISP column in your query output. Notice that you have a couple of different types of aliases listed. For this query, you want to only display medical record numbers (MRNs). Close the Output window.

11. Create and populate a global variable called MRN_VAR that has the code_value that represents MRN. Refer to the following list if you need assistance populating the variable:

a) From the FIELDS tab, right-click the ENCNTR_ALIAS_TYPE_CD from the ENCNTR_ALIAS table and choose Properties.

b) Write down the code set number listed.

c) Click Lookup and document the textual value for MRN for one of the code set display options.

d) Close the Output window and the Field Properties window.

h) From the Tools menu, select Prolog/Epilog Commands.

i) Enter the following:

DECLARE MRN_VAR = f8

SET MRN_VAR = uar_get_code_by("meaning", 319,"MRN")

12. Add a qualification that references the global variable MRN_VAR to only return MRN’s.

13. Run the program and validate that the output displays only the alias of MRN’s for an encounter.

14. Close the Output window.

15. Save your file.

16. From the Report menu, select View Program. Your program should be similar to the following:

; Application file: C:\CCLUser\1_ccl_medical_service.VCL

; Discern Explorer report file: ccluserdir:1_ccl_medical_service.prg

; Generated by VisualExplorer on 1/24/2015 3:51 PM USER= CERNER

DROP PROGRAM 1_ccl_medical_service:DBA GO

CREATE PROGRAM 1_ccl_medical_service:DBA

PROMPT "Output to File/Printer/MINE" = MINE

WITH OUTDEV

; Request HNAM sign-on when executed from CCL on host

IF (VALIDATE(IsOdbc, 0) = 0) EXECUTE CCLSECLOGIN ENDIF

if (validate(_SEPARATOR) = 0)

SET _SEPARATOR=^ ^ ; applies to query execution from VisualExplorer or other apps

endif

declare mrn_var = f8

set mrn_var = uar_get_code_by("MEANING",319 , "MRN")

SET MaxSecs = 0

IF (VALIDATE(IsOdbc, 0) = 1) SET MaxSecs = 15 ENDIF

SELECT INTO $OUTDEV

E_MED_SERVICE_DISP = UAR_GET_CODE_DISPLAY(E.MED_SERVICE_CD)

, P.NAME_FULL_FORMATTED

, E.ENCNTR_ID

, E.REG_DT_TM

, E.DISCH_DT_TM

, E.REASON_FOR_VISIT

, EA.ALIAS

, EA_ENCNTR_ALIAS_TYPE_DISP = UAR_GET_CODE_DISPLAY(EA.ENCNTR_ALIAS_TYPE_CD)

FROM

PERSON P

, ENCOUNTER E

, ENCNTR_ALIAS EA

PLAN E WHERE E.REG_DT_TM BETWEEN CNVTDATETIME("01-JAN-2014 00:00:00.00")

AND CNVTDATETIME("31-DEC-2014 23:59:59.00")

JOIN P WHERE P.PERSON_ID = E.PERSON_ID

JOIN EA WHERE EA.ENCNTR_ID = E.ENCNTR_ID

AND EA.ENCNTR_ALIAS_TYPE_CD = MRN_VAR

WITH MAXREC = 100, NOHEADING, FORMAT= VARIABLE, TIME= VALUE( MaxSecs )

END

17. Click Close. Keep this file open to use in the next exercise.

You have completed the activities for Working with Coded Fields. Return to the Table of Contents of the WBT and click on the topic, Sorting.

Sorting

Sorting and sub-sorting enables you to define the order and organize the output. Sorting combines information into logical groups, which is important to improve the readability of the output of queries and reports.

You will add an ORDER BY to two programs that you previously created to increase the readability and usefulness of the output.

Creating Sorts Project 1

You recently completed a query that displays encounters and the medical service. You can improve the display of the output by displaying all encounters for a specific medical service together. Within the specific medical group, you can display the person’s names in ascending order.

1. If it is not already open, open the file 1_<your_initials>_MED_SERVICE.VCL.

2. Open the Query Builder, and click the SORT tab.

3. The SORT tab allows you to add the ORDER BY clause to your query so that you can sort the data in ascending or descending order. The Fields list displays all fields and expressions selected for display; however, you can select any field from the table for sorting. Double-click the expression created for MED_SERVICE_CD.

The field you selected is now listed in the box on the right side of the window. The first field in the list is the primary sort. You can sub-sort up to ten levels. Notice that the Asc option is selected by default.

4. Click the VIEW QUERY tab and review the syntax for the ORDER BY. This command is optional, but when used, it must be place after the FROM and any qualifications.

ORDER BY

E_MED_SERVICE_DISP

5. Run the query. Validate that medical services are grouped together in ascending order, so a medical service that starts with "A" should be listed first. The following is an example showing the sort order of the medical service:

Close the Output window.

6. From the SORT tab, select DESC for the E_MED_SERVICE_DISP. Click the VIEW QUERY tab and notice the DESC is placed to the right of the expression in the ORDER BY clause like the following:

ORDER BY

E_MED_SERVICE_DISP DESC

7. Run the program to see how the data is rearranged. Now the medical service is listed in the reverse order.

8. Within each grouping of Medical Service, display people in alphabetical order. From the SORT tab, select NAME_FULL_FORMATTED. Click the VIEW QUERY tab to see the syntax used to sub-sort.

9. Run the program to see how the data is rearranged. For each medical service, the names of the people within that group should be listed in alphabetical order.

10. Close the Output window. Save and close the file. This file will be used again in the section titled Building A Report.

Continue to the next practice activity

Creating Sorts Project 2

You previously created a query that provided census information for current patients. You need to

modify the query to sort the patients by location.

11. Open the 1_<your initials>_CENSUS_RPT.VCL file you previously created.

12. Open the Query Builder and open the SORT tab.

13. Double-click E_LOC_NURSE_UNIT_DISP to create a primary sort by nursing unit in ascending order.

14. Run the query to view the output. The output should display nursing units in alphabetical order. If you have nursing units that start with a number, those are listed before the units that start with a letter. Below is an example of the nursing units sorted in ascending order.

Close the Output window and return to the SORT tab.

15. Next, within each nursing unit, you want to sub sort by room and then by bed. Double-click E_LOC_ROOM_DISP, and E_LOC_BED_DISP.

16. Run the query and review the output. Within a nursing unit, the rooms should be displayed in ascending order. If there is more than one bed in a room, the beds will be displayed in ascending order. The following is example data showing the primary and sub sorts:

17. Close the Output window.

18. Click the View Query tab. Review the ORDER BY syntax that was added. The ORDER BY follows the qualification and subsequent sort items are separated with a comma.

ORDER BY

E_LOC_NURSE_UNIT_DISP

, E_LOC_ROOM_DISP

, E_LOC_BED_DISP

19. Save your file, but keep it open for use in the next exercise.

You have completed the activities for Sorting. Return to the Table of Contents of the WBT and click on the topic, Setting Controls.

Working with Control Options

In your last practice activity, you used ORDER BY to sort by Nursing Unit as your primary sort and two ascending sub-sorts by Room and Bed. As you have seen, sorting enables you to organize the data in your output. You can also place limits or controls on your report, such as setting various print options, placing delimiters between selected columns, and modifying other aspect of your report.

Setting Control Options

Control Options enable you to place controls on your query, such as the number of records read and maximum processing time. Both of these options are useful when you are in the testing mode of your query.

In this practice activity, set a maximum number of records to be returned in the query and specify an amount of time allotted to run the query.

1 Open the 1_<your initials>_CENSUS_RPT.VCLused in your last task. Open the Query Builder and go to the CONTROL OPTIONS tab.

2. The number in the Max Records box indicates how many records are to be processed and controls the MAXREC control option in the source code. Using Visual Explorer, the default number of records to return is 100. This is helpful to limit the number of returns when testing a new query. When you complete the development of query, you should clear out the value so that all records are processed. Change this value from 100 to 10 and run your query.

3. The number of rows in your output should now be 10. Click the View Program tab and scroll to the bottom of the file and review the syntax for the WITH clause. It should be similar to the following:

WITH MAXREC = 10, format, separator = " ", TIME= VALUE( MaxSecs ), SKIPREPORT= 1

Close the Output window.

4. Change the value to 1000. Change the Stop execution after from 15 to 30. The Stop execution after option passes a number in the unit of seconds to TIME control option. Using Visual Explorer, you can increase the time to execute a process for up to a maximum of 300 seconds or 5 minutes. If the program takes longer than that to run, it will need to be executed from an application that does not have a timeout setting like Explorer Menu.

5. Run the program. If your program takes more time than what you have allotted to execute, you will receive a message indicating that time has run out:

When you click OK, results may display, however, the results could be an incomplete data set.

Click the View Program tab and scroll to the bottom to view the WITH clause.

WITH MAXREC = 10, format, separator = " ", TIME= VALUE( MaxSecs ),

SKIPREPORT= 1

The number you supply in the Stop execution after populates the variable MaxSecs.

The FORMAT control option makes the data readable for display.

The SEPARATOR = " " control option places a space delimiter between the fields so that the grid component used to display the data separates the data properly to each cell.

The SKIPREPORT = 1 control option tells the system to execute the query commands and ignore any Reportwriter commands. In this query, there are not yet any Reportwriter commands.

6. Return to the course to continue.

Creating a Prompt

Discern Prompt Builder is an interactive component available to use in Visual Explorer containing a set of controls you can use to build and maintain prompt controls that interact with the end user at run time.

In this practice activity, build a prompt based on last name.

1 Find and open the 1_<your initials>_PRSNL_NAME.prg.

2 From the Tools menu, select Prompt Builder.

3. Click Add to add a new prompt.

4. The available tabs display. Add the text that displays to the end user in the prompt. Type Enter a person’s last name: in the *Prompt Display box.

5. Double-click in the Prompt Name box and enter LNAME. The General tab should look like the following example:

6. When you finish, click the Text Properties tab.

7. If the user does not enter anything in the prompt, you want the query to look for last names with any value, so we want the default value to be the wildcard. Enter the asterisk (*) in the Default Value box. You also want the characters to always be uppercase. Select the Upper option in the Character Case section. The Text Properties tab should look like the following example.

8. Click Save to save the prompt you just created.

9. The last time you used this file, you searched for all names that began with the letter A. In this case, you want names returned that the user enters at the prompt. You need to reference the prompt in the query qualification. Open the Query Builder and go to the QUALIFICATIONS tab.

10. You want the prompt to use the P.NAME_LAST_KEY to qualify, so you need to change the current qualification value from A* to reference the prompt. Change "A*" to $LNAME without quotation marks.

11. Run the query.

12. The prompt should open with the wildcard defaulted in the Enter a person’s last name box. Keep the wildcard and run the query.

13. The output should return all names, not just those beginning with the letter A. Close the Output window.

14. Run the program again, but this time limit the output to only those people who have a last name beginning with the letter S. Run the program, enter S* in the Enter a person’s last name prompt, and click Execute.

15. This time the query should have only returned last names starting with S. Write down the last name of a person that was returned in your output. Close the Output window.

16. Run the prompt program again, but this time, enter the last name that you wrote down in the previous step in the prompt. Notice that the system defaults all the letters to uppercase.

17. The output should now show only those people with the last name that you entered in the prompt. Close the Output window.

18. Save and close your file.

Note: The functionality for creating prompts within Discern Explorer is very robust. This practice activity covered only one basic concept. You might optionally review the Use Prompt Builder tutorial gain a better understanding of prompt functionality. You can click this link or find the tutorial at: https://wiki.ucern.com/display/reference/Discern+Explorer+Reference+Pages

Once you get to the Discern Explorer Reference Pages, access the Use Discern Prompt Builder listed under the Use category.

You have completed the activity for Creating Prompts. Return to the Table of Contents of the WBT and click on the topic, Building Reports.

Building a Report

When your query is complete and validated for accuracy, you can take the raw data that we have been displaying in the spreadsheet like grid, and organize it into a report style format. This processing is done using the Reportwriter section. The Reportwriter commands are a part of the SELECT command and follow the query commands. When a SELECT command has Reportwriter commands, the query commands are executed first so that the data can be retrieved from the database and returned back to Discern Explorer. Then the Reportwriter commands are executed for further processing.

It is good practice to be sure your query runs correctly and that the information you retrieve is the information you want before you begin building your report. Following this process, any problems you encounter while using Reportwriter should be narrowed to the Reportwriter logic itself and not the query. The steps will guide you through the five primary sections of the Reportwriter.

Defining the Head Report

The Head Report section processes only one time and is a good place to display items that should only appear once at the beginning of a report, such as a report title, report date and time, or program name.

In this practice activity, add the necessary commands to create a report header by placing the name of the report in the Head Report section.

1. Open the file that has the query that retrieves encounters associated to a specific medical service named, 1_<your initials>_MED_SERVICE.VCL .

2. Visual Explorer opens to the Reportwriter grid which you can use once you have a query. First you want to add a title to your report. Click Add/Edit report text button and click near the centerline of the Head Report section. Enter Medical Service Report in the text box and press ENTER.

3. Center the text on the grid by clicking the text box and dragging it to the appropriate location. You can also move the text box by using the arrows on your keyboard.

4. You decide the report name should be in all uppercase. Double-click the header to open the Edit Text dialog box.

5. Note that you have the option to change the font and color of the text. For this exercise, do not change the font to bold as this changes the appearance of the code. Change the header to all capital letters, and click OK to close the Edit Text dialog box.

6. Visual Explorer automatically created the code that places the label you just placed on the report. To see the generated code, from the Reports menu, select View Program. Scroll down to the end of the code until you see Head Report in the text. Notice the Row and Col commands that are used to place the item on the report. Click Close.

7. Run the report to see the items added for the Head report. Do this by selecting Run Report from the Report menu or by clicking the Run Report button .

8. Review the output. Since you have only added display commands to display the title, the title will be the only item displayed on the report. Close the Output window.

9. Save the file, but keep it open for the next practice activity.

Continue with the next practice activity.

Head Page

The Head Page is a section that processes one time for each page that is created and is a good place to display items that you want displayed at the top of every page.

In this practice activity, add the necessary commands to display the report date and column headers to the report.

1. If it is not already open, open the file 1_<your initials>_MED_SERVICE.VCL.

2. You want the run date of the report to display at the top of the report. Click the text edit button .

3. Click the left side under the Head Page section.

4. Enter REPORT DATE: in the text box, and press ENTER.

5. If you need to reposition the header, select it and drag it into position.

6. You need to display the current date each time the report runs. Click the Add Discern Explorer commands to report button, and click to the right of Report Date header that you just created. The Edit Discern Explorer Commands dialog box opens.

7. From the Variables box, double-click CURDATE. Notice that CURDATE has been added to the Enter Expression box at the bottom of the dialog box.

8. Make sure that the Treat As Printable Expression option is selected, and click OK.

9. If necessary, reposition the CURDATE to follow the Report Date header. Your Reportwriter grid should look similar to the following example:

10. Under the report date, you want five columns to appear at the top of each physical page and identify what data is displayed below it. Add each column header separately using the button and place the items in the order listed side by side: Medical Service, Full Name, Medical Record Number, Admission Date, and Discharge Date.

Your Head Page section should look similar to the following example:

11. Run the report to see the items added to the Head Page. Do this by selecting Run Report from the Report menu or by clicking the Run Report toolbar button .

12. Review the output. The report should now show the report title, report date and column headings. Click the View Program tab. Scroll down past the query and find the HEAD PAGE section and review the commands used to display the items. Close the Output window.

13. Save the file, but keep it open for the next practice activity.

Continue with the next practice activity.

Detail

In the previous two practice activities, you created your report title and defined column headers. The DETAIL section processes for each row returned in the query. Because this section processes multiple times, you can access each row and write the data from the query to the report.

In this practice activity, add the necessary commands to display the data from the query to the proper column in the report.

1. If it is not already open, open the file 1_<your initials>_MED_SERVICE.VCL.

2. Any fields from the tables in your query or any select expressions you create can be displayed on the report. The Place Items button is used to select the field or expression that you want in the report. First you need to add the expression to the report so the various medical services display under that column header. Click the Place Items button . The Place Report Items dialog box opens and displays all of the items you selected in the select list of the query.

3. Drag the E_MED_SERVICE_DISP = UAR_GET_CODE_DISPLAY( E.MED_SERVICE_CD) from the Place Report Items to the DETAIL section of the Reportwriter grid. Close the Place Report Items.

4. Drag the E_MED_SERVICE_DISP expression under the Medical Service column in the Detail section.

5. Notice the fill characters following the expression name. These represent the number of characters the field can include in its display. For this report, medical service does not require that many characters so you need to edit the display width. Double-click the expression E_MED_SERVICE_DISP to open the Edit Report Field dialog box. Change the Display Width to 18 and click OK. From the Report menu, click View Program. Scroll past the query and find the DETAIL section. Review the syntax created by changing the width from 40 to 18.

A local variable is created that uses the SUBSTRING () to shorten the length of the field. Then the variable is referenced to write the value on the report. Close View Program and continue adding items to display.

6. Click Place Items . Drag the following items to the report, one at a time and place them anywhere in the DETAIL section. You can rearrange them after you close the Place Report Items box: P.NAME_FULL_FORMATTED, EA.ALIAS, E.REG_DT_TM and E.DISCH_DT_TM . Close the Place Items dialog box when you are finished.

7. You need to edit the display width for a couple more fields. Double-click P.NAME_FULL_FORMATTED to open the Edit Report Field dialog box. Change the display width to 25 and click OK.

8. Double-click EA.ALIAS and change the display width to 15. Click OK.

9. Drag each of these detail headings under their appropriate column header. Your report should look similar to the following:

10. Double-click the Detail section header

11. The Section Properties dialog box allows you to define how many blank rows display between the data for the selected section. Since you double-clicked on the Detail header, changing these numbers would define the number of rows that would display between the data returned from the query. When the data is double-spaced, the syntax shows Row + 2. Since you only want the data single-spaced, you do not need to make any changes. Click OK.

12. Run the report to see the items added to the DETAIL section. Do this by selecting Run Report from the Report menu or by clicking .

13. Review the output. The data from the query should now display in the output. Click the View Program tab and scroll down past the query and find the DETAIL section. Review the syntax used to display the items in the detail section. Close the Output window.

14. Save your file, but keep it open for the next practice activity.

Continue with the next practice activity.

Foot Page and Foot Report

The Foot Page is a section is a place where items can be displayed on each physical page. This may be where you want to display items like the page number. It is recommended to have a matching FOOT for every HEAD. Since there is a HEAD PAGE section, it is a good idea to match that HEAD to its corresponding FOOT. Visual Explorer’s Reportwriter grid keeps this rule in mind for you.

The Foot Report is a section that processes one time only after all of the other sections have been processed. This is a good place to display items that you want to display one time at the end of the report such as an End of Report tag. Since there is a HEAD REPORT section, it is a good idea to match that HEAD to its corresponding FOOT.

In this practice activity, add a footer to display the page number for each page. Also add text to the end of the report indicating the end of the report.

1. If it is not already open, open the file 1_<your initials>_MED_SERVICE.VCL.

2. First you need to add a label for the page numbers. Click the text edit button and click just to the right of the centerline in the Foot Page section.

3 Enter the text PAGE: in the text box and press ENTER. Reposition as needed.

4. To add the page number, you can reference a reserved variable called CURPAGE that keeps track of the page number for you. This variable keeps track of how many pages have been processed and can be used to display the information on the report. Click and click to the right of the PAGE label.

5. From the list of variables, double-click CURPAGE. The variable CURPAGE appears in the Any Expression box in the Parameters section. To the right of CURPAGE enter a space and then "##".

6. Validate that the Treat as Printable Expression option is selected, and click OK. Your Foot Page section will look similar to the following:

7. The variable CURPAGE is an I4 data type which could potentially be up to 10 digits. When referenced on the report the display will contain enough space for all digits even when displaying the number 1 for the first page. You end up with a display that looks like the following:

To control the formatting of CURPAGE on the report, the Display_Template portion of the Display Options can be used. Using "##", will specify to only display two digits. Using the display option, the variable CURPAGE can display up to number 99 and only use up two spaces on the report.

To review the Display_Template information, refer to the Discern Explorer Wiki Help Pages:

https://wiki.ucern.com/display/public/1101discernHP/Display+Template+using+Discern+Explorer , and when referenced on the report will hold the space for all digits even when displaying 1 for the first page.

8. Run the report and view the output. Scroll down and validate that the page number is displaying. Click the View Program tab and scroll down the FOOT PAGE section to view the syntax created to display the items in this section. Also look at the DETAIL section and notice the extra commands added.

Adding items to a FOOT PAGE section can be complicated because you need to make sure you leave enough room at the bottom the page to write the items you want from the FOOT PAGE section. You need to add logic to any section that may display items at the bottom of page to break to the next page if there is not enough room to write that item and the items in the FOOT PAGE section. The good news is that Visual Explorer added the necessary commands for you.

Directly after the DETAIL heading, the following syntax was added to your file :

if ((ROW + 2) >= maxrow) break endif

This logic checks to see if it is near the end of the physical page. If it is within two rows, instead of writing the data, it issues the BREAK command. This forces the FOOT PAGE to process, write its data, and then return to the DETAIL section to continue writing.

Directly after the FOOT PAGE heading, the following syntax was added to your file:

ROW maxrow - 1

COL 78 "PAGE:"

COL + 2 curpage "##"

This logic will back up one row from where it currently is and specifies the row to write the items.

9. Lastly, you want to add an End of Report message at the bottom of the last page. Click and click near the center of the Foot Report section.

10. Enter the text END OF REPORT and press ENTER. Reposition as needed.

Your report should now look similar to the following:

11. Run the report to see what it looks like so far. Click the Run Report toolbar button and click Execute.

12. The formatting or your report should look similar to the following:

13. Notice the report title and report date at the top of each page. Scroll down to view additional pages. Notice the page number is at the bottom of each page.

14. Click the View Program tab in the Output window. Scroll down past the query until you find the Foot Page and Foot Report section and review the syntax used to display the page number and the End of Report text. The syntax contains the specific row and column positions for the various headings and expressions. Visual Explorer does a good job at predicting the proper row and columns to place items on a report. Using Discern Visual Developer to create a report, you would manually have to figure out the exact row or column position which typically requires cycles of trial and error for every item on the report.

15. Close the Report Output and save your work. Keep the file open for the next practice activity.

Continue with the next practice activity.

Report Layout

Because there can be multiple people in each medical service, the medical service is name repeated.

You can improve the readability of the report by displaying the unique medical service only once. This can be done because this data is logically grouped by medical service using the ORDER BY. Using the ORDERY BY allows extra sort sections available to use in the Reportwriter section to help with processing and in this case to display the unique medical service once.

In this practice activity, group sections and add a total count of patients for each medical service.

1. If it is not already open, open the file 1_<your initials>_MED_SERVICE.VCL.

2. Sort sections enable you to create additional sections on the grid to help group your data. Because these sections are based on the sort logic (ORDER BY), you must have a sort clause in your query to use the group section functionality. This query contains a primary sort on Medical Service, which groups all patients with the same medical service together in the output. In the report you ran in the last practice activity, the medical service was printed for each row of data in the report. You can use a sort section to print each medical service only once. To add a group section, from the Reports menu, select Sections to open the Report Selections dialog box.

3. Each item selected in the SORT tab when building the query is listed as a possible sort header in the Report Selections window. Select the option for the Head E_MED_SERVICE_DISP. Notice that the Foot option is selected automatically when you select the Head section.

4. Click OK. You should now see the Head E MED SERVICE DISP and Foot E MED SERVICE DISP sections added to the report grid wrapped above and below the DETAIL section.

5. The purpose of the HEAD E MED SERVICE DISP section is to process one time for each unique medical service at the beginning of each group. Select MEDICAL SERVICE in the Head Page section and drag it to the lower-left portion of the Head MED SERVICE DISP section.

6. Double-click the MEDICAL SERVICE header and add a colon to the end of the title. Click OK to close the Edit Text dialog box.

7. You need to move the expression so that it is processed at the beginning of each group along with the column header. Select E_MED_SERVICE_DISP from the DETAIL section and drag it to the right of the MEDICAL SERVICE heading in the Head MED_SERVICE_DISP section. Your report should now look similar to the following:

8. Now run your report to view the new sections. Click and click Execute.

Your report should now display the medical service one time similar to the following format:

9. Close the Output window.

10. The Foot MED_SERVICE_DISP section is used to process items at the end of the logical grouping. Various calculations based on the group are commonly placed in this section. Statistics such as total or sub counts, the sum of multiple values, the average or mean of multiple values, and the standard deviation can be calculated and captured in the Foot sort sections or Foot Report section. In this case, you want to know how many people are associated with each medical service group. First create a heading for the count. Click and click the upper-left area of the Foot MED SERVICE DISP section. Enter MEDICAL SERVICE TOTAL: in the text box, and press ENTER. Reposition as needed.

11. Add an expression to capture the number of patients associated with each service. Click and click to the right of the MEDICAL SERVICE TOTAL heading.

12. From the list of functions, double-click the COUNT function.

13. In the Expression Alias box, enter TOT_PATIENTS.

14. From the list of available fields, double-click E.ENCNTR_ID, and click OK.

15. Click OK to close the Edit Discern Explorer Commands dialog box. The Foot E_MED_SERVICE_DISP will look similar to the following:

16. Run the report to make sure the totals are displaying. Click the and click Execute. Validate that the totals are displayed at the end of each medical services. The following is an example of data:

17. Close Output window. You are finished building the report.

18. From the Report menu, select Report Layout. Notice the option to define the report orientation as portrait or landscape. Close the Report Layout window.

19. Close the report and save your work.

20. Close the file.

You have completed the activities for Building Reports. Return to the Table of Contents of the WBT and click on the topic, Using the Help File.

Discern Explorer Help

Accessing Help

Since Discern Explorer is a robust language there is always much more to learn about language. The more queries you create, the more functions and techniques you want to utilize. Help is always available when you need it!

Discern Explorer Help, as well as all of Cerner’s solutions Help Pages, are uCern Wiki pages dedicated to giving you the information you need to use the solution.

Discern Explorer Help is organized so there is a main Home Page and many topics broken down in to different categories, such as specific application or language information.

Follow the directions to learn ways of accessing Discern Explorer Main Help.

1. From DVDev choose Discern Explorer Help from the Help menu.

If you do not have internet access or the uCern Wiki is undergoing maintenance, select Click Here if you cannot view help in the right window. This brings up an offline version of the Discern Explorer Help files.

2. You can also access Discern Explorer Help directly without logging in to the application. Click the link below and log in using your Cerner log in credentials:

https://wiki.ucern.com/display/1101discernHP/Discern+Explorer+Main+Help

To have easy access to the Discern Explorer Main Help, create a favorite, bookmark or short-cut to the page.

The Discern Explorer Main page you are viewing should be similar to the following:

Discern Explorer Help is organized in a hierarchical structure. The Discern Explorer Help Home Page is divided into several broad topics, which each contain individual topic pages.

Searching in the uCern Wiki

There are several ways to find topics in the Help. When you click a topic, from the Main page, that topic is displayed. If you know exactly what you are searching for, you can use the Search Wiki bar to help you get to the information you are looking for. Once you are in the topic, you can search for a specific word or phrase.

When you use the Search Wiki control, you are actually searching ALL wiki pages. It is helpful to know a few tips to limit your search to the Discern Explorer Help pages.

1. Find the Search Wiki control located on the top right of the Wiki page:

2. In the Search Wiki bar, type CNVTDATETIME and wait a moment to see a list populate similar to the following:

You will see several choices, some in different languages, some topics from different solutions.

If you are English speaking, it is helpful to limit your search to English. Many Discern Explorer topics are followed by the text using Discern Explorer. Entering the word using after your topic limits the list to English choices. If the topic you are searching for is common to other areas, the category listed below the topic as Discern Help Pages lists only topics from the Discern Help Pages.

In some versions of the Wiki there is an option, Only in Discern Explorer Help, which will further limit the list of topics to that space.

3. Add the word using to the right of CNVTDATETIME in the Search Wiki bar and select the first topic. The help page for CNVTDATETIME topic is displayed.

The CNVTDATETIME function is used in date time qualifications. This might be a page you visit frequently until the syntax becomes easy to remember.

4. Navigate back to the Main Home page by either using the back button or using the following link:

https://wiki.ucern.com/display/1101discernHP/Discern+Explorer+Main+Help

Another helpful feature in the Wiki help pages is the ability to easily locate a topic in a long document by using the Find functionality.

5. In the Search Wiki type SELECT using and wait for the list. Find and select the topic SELECT using Discern Explorer.

This is a comprehensive section covering all parts of the SELECT command. This section covers many things you learned in this course and many more topics. You might want to visit this page often to review different parts of the command.

Sometimes, though, it is important to get to the specific piece of information you are looking for.

6. Press Ctrl+F to get the Find window. The Find window displays.

7. Type Distinct. All instances of the word Distinct are highlighted and easy to identify as you scroll through the document.

8. Click the Next option to scroll through the document and find all instances of the command.

9. Navigate back to the Main Home page by either using the back button or using the following link:

https://wiki.ucern.com/display/1101discernHP/Discern+Explorer+Main+Help

Locate Topics Using Help

Now that you can access and find topics in the Discern Explorer Help pages, let's make sure you can navigate to a few common and important topics. In this practice activity, you will locate information in the Discern Explorer Help Pages and answer questions.

1. Reviewing the main page of the Discern Explorer Main Help, find the main topic that contains:

· A list of Functions, Control Options and Commands available to use in Discern Explorer.

· Information about prompts, including the Prompt Builder Tutorial.

· Information specific to Discern Visual Developer.

Did you find the Command Reference section? The Command Reference section provides a wealth of information to increase the capabilities of your queries and reports.

Did you find the Discern Prompt Library? If you will be creating queries or reports that utilize prompts, plan on completing the Discern Prompt Tutorial.

Did you find Discern Visual Developer Help? This section provides information about the application, including Tips and Keyboard shortcuts.

2. From the Discern Explorer Main Help page, click on the Functions link located in the Command Reference section.

3. Scroll through the list of functions.

This section contains a comprehensive list of all of the different functions available to use in Discern Explorer.

You have been introduced to only handful of the functions available to use. As you can see, there are many more functions to learn about and utilize!

4. Find and click the link for the function CNVTLOOKBEHIND. Read the definition. Can you think of a usage for this function?

Yes, you can use CNVTLOOKBEHIND in place of CNVTDATETIME. You may find the syntax a little shorter and easy to look back in different units such as seconds, minutes and hours.

5. Navigate back to the Main Home page:

https://wiki.ucern.com/display/1101discernHP/Discern+Explorer+Main+Help

10. Select Programming Reference Help in Discern Explorer. You can now see the subtopics relating to programming reference

11. Scroll through the page to see the list of topics. Select PLANS and JOINS using Discern Explorer. The PLANS and JOINS topic page is displayed.

You learned about Plans and Joins previously in the tutorial and can review the information whenever you need to have a refresher on the topic. As a matter of fact, all topics covered in the tutorial are documented in the Discern Explorer Help pages!

12. Do you remember how to place a single or multiple line comment? From Search Wiki, type Comments using Discern Explorer. What commands are needed?

13. Navigate back to the Main page and click on the main topic, Discern Explorer System Information Help and then Discern Explorer Security.

14. What group are users assigned to by default, Group0 or Group1?

Yes, you are correct; the documentation tells us that by default, users are set up as a Group1. A user who is a Group0 can change a Group1 user to a Group0.

15. Using the Search Wiki, find and review the description for the function, AVG.

Yes, you can use the AVG functions to display an average for specified set of values. You might also investigate COUNT, MAX, MIN.

16. What 3 parameters are needed for the UAR_GET_CODE_BY? Can you find the function?

Great! You are correct; the function needs to have a string_type, code_set and a string_expression, for example:

SET F_VAR = UAR_GET_CODE_BY("DISPLAY", 57, "Female")

You have completed the activities for Using the Help File. Return to the Table of Contents of the WBT and click on the topic, Using Explorer Menu.

Using Explorer Menu

NOTE: With the release of 2017, Explorer Menu will no longer be available. Discern Analytics 2.0 (DA2) is its replacement. Running reports in DA2 is currently available in releases as early as 2012.01.40.

The following resources can be used to learn all about running reports from DA2:

Discern Analytics 2.0 Community

Discern Analytics 2.0 Query Builder and Navigator Concepts

Cerner Millennium Discern Analytics 2.0 Report Designer Basics

Explorer Menu is an application that lets you create a menu, or folders, that contain Discern Explorer programs you have built with Visual Explorer, Discern Visual Developer, or Interactive Discern Explorer. You can create menus to logically group and easily identify Discern Explorer programs. The programs can be run on demand by selecting them from the menu, supplying the necessary inputs for the program and running it. The source code is not accessible, only the object which can be executed as needed. The Explorer Menu application also gives you the flexibility to attach security to a menu, group of programs, or individual programs to restrict program access to designated users.

Add a Menu and Program

Typically, programs are located in menus. Any program listed under the Main menu can also be placed in a Personal Menu. The Personal Menu makes finding frequently run programs by a specific user easy to locate.

In this practice activity, add a folder for your reports and add programs to that folder.

1. Open Explorer Menu (ExplorerMenu.exe).

2. The Main Menu and Personal Menu root folders are displayed by default. Double-click Main Menu to open it.

3. You must add programs to the Main Menu before they can be added to the Personal Menu. To create a folder in the Main Menu section for your programs, select the Main Menu folder, and click the Add a Menu Item toolbar button or right-click the Main Menu folder and select New > Menu. The Menu Description window is displayed.

4. You need to add a name and a description for the menu you are adding. Enter <your initials> My Reports in the Menu Description box and click OK.

6. A menu folder labeled <your initials> My Reports is displayed under the Main Menu folder. To add a program to that folder, select the newly-created folder and click the Add a Program Item toolbar button or right-click your new folder and select New > Program.

7. You want to add the Medical Service report you created earlier. Enter your 1_<your_initials> in the Program Name box, and click the Search button (magnifying glass).

The Object Name dialog box opens. Click Query, and a list of all objects that begin with your initials is displayed. Select the Medical Service object named 1_<your_initials>_med_service.

Click OK. You are returned to the Add Program To dialog box.

8. Note the Group box is populated with the group level found in the object library for the object you created. Enter Medical Service Report in the Description box. Since you are entering multiple programs, click Apply and the Add Program To dialog box should remain open.

9. You want to add the Personnel Information program. Enter your 1_<your_initials> in the Program Name box and click Search (magnifying glass). The Object Name dialog box opens. Click Query, and a list of all objects that begin with your initials displays. Select the 1_<your_initials>_PRSNL_INFO object and click OK. You are returned to the Add Program To dialog box.

10. In the Description box, enter Personnel Information. Since you are done adding programs, click OK.

11. Locate the Medical Service Report and Personnel Information programs under the My Reports folder. Click on the Personnel Information program. The prompts for the program load in the right side of the application. Leave the default value of MINE selected and click Execute.

12. The Personnel report displays. Close the Output window to return to Explorer Menu window. To run the first program, select the Medical Service Report program, allow the prompt to load, accept the default value of MINE and click Execute. The Medical Service Report is displayed. Close the Output window to return to Explorer Menu.

13. Now that the two programs have been added to the Main Menu, you can add them to the Personal Menu. Right-click Personal Menu, and select New > Program. The Add User Program to Personal Menu box is displayed with all of the available programs that can be added.

14. Enter M to get to programs that start with M. Select the Medical Service Report and click OK. The program is now available to run from the Personal Menu folder.

Add Security

You can limit the number of people who can access this application by using the security feature. Security is set by associating either a menu or program item to one or more application groups.

In this practice activity, add a folder for your reports and add programs to that folder.

1. You need to add security so only persons who are associated with the DBA application group can access the Medical Service Report. Begin by selecting the Medical Service Report that you previously added to your My Reports menu.

2. Click the Security Setup toolbar button . You can also right-click and select Security.

3. The Application Groups available are listed in the box on the left. Since you want the DBA position, double-click it (or select it and click Move).

4. Click OK to accept. Now the Medical Service Report is only accessible by users associated with the DBA application group.

5. To test the security, identify a PERSON_ID that is not a DBA. Expand the Main Menu folder and double-click the Explorer Menu Audits. Select the Persons without DBA App Group Code program. When the prompts load, accept the default value of MINE. Change the number in the Enter max number of records box to 10. Click Execute.

6. Identify a user who is not a DBA and record the PERSON_ID. Close the Output window.

7. From the Task menu, select Enter Person ID to Test Security.

8. Enter the ID number you wrote down in the PERSON_ID box and click OK.

9. The message indicates that you are viewing the application as it would be viewed by the person whose ID you just entered. Click OK.

10. Navigate to My Reports menu. Open the menu and verify the Medical Service Report is not displayed. If it is not displayed, security has been properly defined.

11. Now reset the application back to your view. From the Task menu, select Load Menu Items.

12. To make sure the view is back to how it was previously, double-click the Main Menu then double-click My Reports. You should see the Medical Service Report again.

Congratulations! You have completed the last practice activity. Return to the course to access the Performance Check. You will do GREAT!

Appendix A

Using Data Types Practice Activity Key

Sample data matched to data types.

__I2___ 10

__C5__ CBC

VC100_ Call Dr. Jones when the CBC results are back.

__F8__ 12345.00

_DQ8_ 12/10/03

Appendix B

Using Field Names Practice Activity Key

A field that stores the unique primary identifier of the PERSON table is most likely PERSON_ID.

A field that indicates when an order was placed would most likely be ORIG_ORDER_DT_TM.

A field that indicates whether a row is active would most likely be named ACTIVE_IND.

A field that stores a person's last name in uppercase characters with all spaces and punctuation removed would most likely be named NAME_LAST_KEY.

A field that stores one of several numbers that correspond to other data would most likely be named ORDERABLE_TYPE_FLAG.