CCL Reports

1. CCL Reports Tips on creating the best reports for both the clinician and the Cerner system Sue Nuyda Clarian Health, Indianapolis May 2, 2007

3. Objectives • Keep the end-user in mind • Keep the report-writer in mind • map front-end fieldsto back-end tables • use comments, documentation • create a report library • Keep the system in mind

4. Objectives • Keep the end-user in mind • Keep the report-writer in mind • map front-end fieldsto back-end tables • use comments, documentation • create a report library • Keep the system in mind

5. Bad Example: What’s wrong with this picture?

6. Bad Example: • report name is ambiguous • description and purpose are missing • no guidance for parameters

7. Good Example: • complete report description which includes original requestor’s name • dropdowns that eliminate guesswork

8. Objectives • Keep the end-user in mind • Keep the report-writer in mind • map front-end fieldsto back-end tables • use comments, documentation • create a report library • Keep the system in mind

9. Objectives • Keep the end-user in mind • Keep the report-writer in mind • map front-end fieldsto back-end tables • use comments, documentation • create a report library • Keep the system in mind

10. When mapping front-end fields to back-end tables, this CCL can be of enormous help. Cerner Field Listing SELECT DISTINCT DC.COLUMN_NAME, DC.TABLE_NAME, DC.CODE_SET, INDEXED = IF (D.COLUMN_POSITION = 1) "Y" ELSE "N" ENDIF, DC.ROOT_ENTITY_ATTR, DC.ROOT_ENTITY_NAME FROM DM_COLUMNS_DOC DC, DM_INDEX_COLUMNS D PLAN DC JOIN D WHERE D.COLUMN_NAME = OUTERJOIN (DC.COLUMN_NAME) AND D.TABLE_NAME = OUTERJOIN (DC.TABLE_NAME) AND D.COLUMN_POSITION = OUTERJOIN (1) WITH NOCOUNTER A Well-Kept Secret: dm_columns_doc and dm_index_columns act as an all-inclusive table of contents.

11. Field and table Code Set Indexed Parent Result:A listing of all fields, code set links, indexes, and parent table links. Load this into Access where you can then sort and do searches.

12. Case Study Request: A clinician has requested a report of all CBC orders for 24 hours that have a stat collection priority. Looking for clues: Ask which Cerner application is being used, then open that application to gain additional pieces of information. This information can narrow the search of the 250,000+ fields in the Access database.

13. Looking for clues: Clue 1: Department Order Entry is used Clue 2: Stat falls under Collection Priority

14. Code Set Listing SELECT CV1.CODE_SET, CV1.DISPLAY, CV2.DISPLAY, CV3.DISPLAY, CV4.DISPLAY FROM CODE_VALUE CV1, CODE_VALUE CV2, CODE_VALUE CV3, CODE_VALUE CV4 PLAN CV1 WHERE CV1.DISPLAY_KEY = "DRAW" AND CV1.ACTIVE_IND = 1 JOIN CV2 WHERE CV2.DISPLAY_KEY = "ROUTINE" AND CV2.ACTIVE_IND = 1 AND CV2.CODE_SET = CV1.CODE_SET JOIN CV3 WHERE CV3.DISPLAY_KEY = "NOW" AND CV3.ACTIVE_IND = 1 AND CV3.CODE_SET = CV2.CODE_SET JOIN CV4 WHERE CV4.DISPLAY_KEY = "TIMED" AND CV4.ACTIVE_IND = 1 AND CV4.CODE_SET = CV3.CODE_SET WITH NOCOUNTER Based on Clue 2, look for a code set that has the same entries as the drop-down.

15. Clue 3: Code set 2054 is the one that contains these 4 entries.

16. Go to Access: Using clue 3, go to the Access database. Sort by code_set to see all fields linked to code_set 2054.

17. Clue 4: The original list of 250k fields has dropped down to about 20 fields. Based on table name, order_laboratory is a good guess.

18. Clue 5: Using Access, it is easy to find indexed date fields. Based once again on table name, orders would be a good guess.

19. Case Study Response DECLARE STAT_VAR = F8 SET STAT_VAR = UAR_GET_CODE_BY (“DISPLAYKEY”, 2054, “STAT”) DECLARE CBC_VAR = F8 SET CBC_VAR = UAR_GET_CODE_BY (“DISPLAYKEY”, 200, “CBC”) SELECT O_CATALOG_DISP = UAR_GET_CODE_DISPLAY (O.CATALOG_CD), O.ORIG_ORDER_DT_TM, O.PERSON_ID, O.ORDER_ID, OL_COLLECTION_PRIORITY_DISP = UAR_GET_CODE_DISPLAY (OL.COLLECTION_PRIORITY_CD) FROM ORDERS O, ORDER_LABORATORY OL PLAN O WHERE O.ORIG_ORDER_DT_TM > CNVTDATETIME (CURDATE, 0) AND O.CATALOG_CD = CBC_VAR JOIN OL WHERE OL.ORDER_ID = O.ORDER_ID AND OL.COLLECTION_PRIORITY_CD = STAT_VAR WITH NOCOUNTER Result: Using both ‘best guesses’, this is how to structure the ccl. To test, place an order in Department Order Entry, and then run this ccl.

20. Objectives • Keep the end-user in mind • Keep the report-writer in mind • map front-end fieldsto back-end tables • use comments, documentation • create a report library • Keep the system in mind

21. CCL Bad Example: • No indication of report author • Report description, purpose is missing • Table listing is not included • No history of report changes • Required to read code itself

22. CCL Good Example: • Author and release date are listed • Purpose and general description are included, as well as a table listing • Further down is a list of prompts and maintenance schedule

23. Objectives • Keep the end-user in mind • Keep the report-writer in mind • map front-end fieldsto back-end tables • use comments, documentation • create a report library • Keep the system in mind

24. Microsoft Visual Source Safe • Report Library: • holds all ccl reports and prompts • holds a complete change history • allows version control and ability to restore previous versions

25. Objectives • Keep the end-user in mind • Keep the report-writer in mind • map front-end fieldsto back-end tables • use comments, documentation • create a report library • Keep the system in mind

27. CCLQuery: Report Optimizing CCLQuery:Shows the order in which tables are joined. For each join, you can see if the index is unique (the best), a range scan (ok), or a full table scan (awful).

28. CCL Reports Questions? Comments? Bookings? Call my agent. Sue Nuyda snuyda@clarian.org