1. The Best of Clinical Query – Helpful Reports and Tips & Tricks Rose Almonte, MS,RN-BC Clinical Systems Analyst Inova Loudoun Hospital Presenters have indicated they have no financial relationships to disclose relevant to the content of this presentation.

2. Objectives • Provide general information on Business Objects and Clinical Query. • Provide examples of how to leverage Clinical Query to improve workflow and management oversight. • Review tips and tricks to improve query build and report formatting.

3. Business Objects Clinical Query Clinical Query is our use of Business Objects to access the standard universes and objects provided by McKesson

4. Clinical Query Reports • Simple List with minimal formatting • List with moderate formatting • Reports with more formatting, aggregations, and calculations

10. Query 1 = Numerator How many patients did not have the charted result? Subquery 1.1

11. Query 2 = Denominator How many patients were admitted in the time frame specified?

12. Braden Score compliance formula (# new patients admitted)-(# pts w/o score) # new pts admitted • =((Count(<Patient Id(Query 2 with Pat760LV)>))-Count(<Patient Id(Query 1 with Pat760LV)>))/Count(<Patient Id(Query 2 with Pat760LV)>)

13. Clinical Query • Assessing the information need/Building the query • Formatting the report • The Finished Report…

14. Assessing the Need/Building the Query • Map out the information need • Use subqueries when possible • Inside (subqueries) queries run first, THEN outside queries • Use SQL button to see how many tables you are pulling data from • The more tables = Long query run time • Use a small sample when testing • Via OPTIONS button and limit # rows returned • Via CONDITIONS limiting query to a specific department • START SIMPLE THEN EXPAND

15. Formatting Reports • Standardize report formatting and layout • Tools -> Standard Report Styles • View -> Page Layout and Grid • Formatting cells/columns • Use your Report Manager • Save a raw data report in case you need to start formatting from scratch • Filtering results • Manually include/not include values • Use formula to define filter

16. Formatting Reports • Applying formulas • Replacing a result value with another value = If (<Result Value>)=“Legend” Then “Spelled Out Result” Else “” = If (ToNumber(<Result Value>))<=9 Then "Very High Risk" Else If (ToNumber(<Result Value>)) Between (10 ,12) Then "High Risk" Else If (ToNumber(<Result Value>)) Between (13 ,14) Then "Moderate Risk" Else If (ToNumber(<Result Value>)) Between (15 ,18) Then "Low Risk" Else "No Risk“ • All formulas begin with an equal sign (=) • Use double quotes (“”) to enclose text items • Use angle brackets (<>) to enclose objects

17. Formatting Reports • Formulas combining text • Useful to link freestanding cells • Use ampersand (&) or plus (+) to join cell contents • Use double quotes (“”) to enclose text items • Use angle brackets (<>) to enclose objects • Examples: =“Report Refresh Date: “+LastExecutionDate Report Refresh Date: 10/18/2007 =<Room - Patient> & " - " & <Bed - Patient> 2301-A

18. The Finished Report • Saving Report • Standardize Document Naming Format Example: Report Name = ABCReport Universe = PAT760LV Document Name = ABCReport(PAT760LV) • Able to save as .XLS, .TXT, or .PDF to send to non-CQ users • Save locally and/or to Corporate Documents (HCI server)

19. The Finished Report • Send report to Broadcast Agent • Schedule report to print or save to shared network folder. • Use of macros can help… • To burst specific reports within a single document to different printers. (InsightDailyDischargeReport file has a copy of this macro) • To send .pdf or .txt reports to a shared network folder with the date/time appended to the filename. (InsightBradenScoreAudit file has a copy of this macro) • But macros are generally outside of the scope of McKesson support services.

20. Helpful Resources • Business Objects Help Menu • Schmidt, Robert D., Creating Documents with Business Objects ; Schmidt Ink, Inc., 2005. • McKesson Support Services

21. Questions? Thank you for coming! Please don’t forget to complete your evaluation.