1 / 28

CCL Reports

CCL Reports. Tips on creating the best reports for both the clinician and the Cerner system Sue Nuyda Clarian Health, Indianapolis May 2, 2007. Objectives. Keep the end-user in mind Keep the report-writer in mind map front-end fields to back-end tables use comments, documentation

britanney-williams
Download Presentation

CCL Reports

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

  2. 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

  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. Bad Example: What’s wrong with this picture?

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

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

  7. 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

  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. 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.

  10. 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.

  11. 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.

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

  13. 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.

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

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

  16. 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.

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

  18. 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.

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

  24. 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

  25. 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).

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

More Related