Report Development Best Practices

1. Report Development Best Practices Presented by: Thomas Harlan, Technical Team Lead Data Repository, Report Writing Services Thomas.Harlan@iatric.com

2. Building ReportsWe spend a lot of time building reports • Like you, we spend an inordinate amount of time building reports. So let’s refine that process around three foundation concepts: • Correct data…Fast to Run • Correct data…Well Presented • Correct data…Easy to Maintain • Luckily, these three concepts support each other and provide us with the guideposts for a report development best practice.

3. The Reporting Architecture When building a report we… • Use SQL Server Reporting Services. • Drive each data set from a Stored Procedure. • Work to a time metric on the stored procedure(s). • Store our database objects in zcus (or your existing custom catalog). • Make each report user-security aware. • Make each report parameterized by Facility and Date Range. • Implement Date/Time Mnemonics to support scheduling. • Drive parameter picklists from stored procedures, which may be data aware. • Support ALL in multi-select picklists. • Match the site style guide, if it exists. • Triage requests between appropriate tools.

4. Stored Procedures and the Time Metric Keeping all of your query code in parameterized stored procedures: Makes the code re-usable Makes the code searchable (especially for Crystal Reports users!) Makes the code easier to maintain Makes the reports easier to maintain Lets us improve performance dramatically Setting a time metric: Lets you identify performance issues sooner Saves maintenance and user time in report iterations

5. Request Tracking and Report Naming As you build reports – and you either have or will have hundreds of them! – it is critical to be able to find the component parts easily. Reports will wind up composed of multiple parts: .RDL > Stored Procedure(s) Or even… Main .RDL > sub-report .RDL(s) > Stored Procedures So it is very convenient for maintenance, reporting management, and request tracking to assign: A Data Request Number to each data request And include that DRN in each report, stored procedure, function name, etc.

6. What Does DRN Usage Look Like? Let’s assume that you have Data Request Numbers in play, then… • When a request comes in, it is logged and gets a DRN. • When you build a report to satisfy the request, you prefix the name of the report component with the DRN: M0251 M0251 OB Patient Log SP SQL M0251 OB Patient Log RDL • The DRN is also added to the report name on the report itself, so the end-user is aware of it. This lets us easily reference a report by DRN, find all of the components of a report by DRN (if necessary), dovetails with report inventory and is used on each ticket related to maintenance.

7. Questions?

8. The SSRS Environment Your DR + SSRS environment may consist of up to three servers: DRSERVER (the actual DR machine itself) RDLSERVER (the SSRS Report Manager machine, which might be your DR) FILESERVER (storage location for .RDL and .SQL files in development *Not your DR or RDLSERVER* ) RDLSERVER has a main “live” folder with subfolders per business area, and a “test” folder for validating new reports. FILESERVER has one group share (\Reports) that matches the structure of RDLSERVER and it’s folders, one for one. So you have an offline copy of the Report Manager structure. FILESERVER has another group share (\SQL) that breaks down into sub-folders for (\Stored Procedures, \Functions, \Views, etc.)

9. Data Request Triage Now we’re ready to deal with an neverending stream of requests for data! • Every request must be documented in a help-desk ticket! • Every request is logged in the Data Request Number System and a DRN generated. • Now we triage – what is the appropriate tool to meet this request? • We have up to four tools to decide amongst: • Report Designer (MT 6.x modules) • NPR (CS, Magic, and MT 6) • DR + SQL + SSRS (human readable output) • DR + SQL + SSIS (machine-readable files) How do we decide which to use?

10. Output Tool Selection When picking the right tool, we need to consider: • Which module(s) is/are the data coming from? • What latency is suitable for our end-user using the report? • Where does the report need to be visible? • Is the data in the DR? Generally speaking, if a report needs to be real-time (up-to-the-minute), or if it must be tied to a Rule or a CDS, or if the data is not available…then the DR is not the place to pull the report from. If a report needs to come directly from a MEDITECH menu, then you can do this with a stub NPR that calls an SSRS report.

11. Triage Flow

12. Development: LIVE vs TEST If you do not have a DEV instance of your LIVE DR available to do development on, then… Developing in TEST is almost always a waste of time. Develop in LIVE if you are not developing reports against build that is only available in TEST. By splitting the presentation layer of the reports into the RDL, and the data retrieval component in the SQL, we create a workflow where we can update and test the SQL separately from the report itself, and vice-versa.

13. Development Step by Step Laying out the report development cycle: • Create (or re-use) a stored procedure stored in zcus catalog. • Stored procedure name matches guidelines, includes DRN. • Store procedure is parameterized. • Report parameters are driven by re-usable stored procedures. • Stored procedure comments header is in standard format, references the DRN and ticket number, has example SQL code used to test the sp. • Stored procedure output is validated before report layout build commences. • When creating a new RDL, start from a template. • Make sure the RDL documentation (title, description, author properties) are filled in appropriately.

14. Development Step by Step Report development cycle, continued… • Format the report per the request, making sure to make it attractive and clear to read. • Where possible, use the dynamic features of SSRS (highlighted values, sortable columns, hidden sections, drill-through reports, graphing and charting widgets) where appropriate. A visually attractive report is more likely to be used! • Publish the report to the \Test folder on the RDLSERVER and notify the end-user requestor to validate.. • Once the report is validated, move the report to its “live” folder on the RDLSERVER.

15. Parameter Lookup Queries When setting up picklist queries in your reports (like Location, or Facility, or Patient Type) you will be tempted to embed the code in the .RDL and have done. Do not follow this path! Even little parameter lookup queries need maintenance. And when that maintenance is needed, you want to do it one place only…there may be hundreds of reports involved! Unless you are using an “Infrastructure” number to designate generic, supporting, data-base objects, lookup lists do not need a DRN.

16. The Core Query Nearly every report will need a main stored procedure to get the primary dataset. That stored procedure needs at least one parameter: • @cFacilityID And 99% of the time you will also have: • @dFromDate • @dThruDate Always, always, always include @cFacilityID. You may default it to your primary @.facility and never change it…but the day will come when you do need so, and you don’t want to update 500+ reports.

17. The Core Query, Part II You’ll also wind up adding, in many cases, more common parameters: • @cLocations • @cProviders Make things easy on yourself – create a template .RDL (or use ours) with the common list of parameters (and their associated data-sets), and then hide or delete what you do not need on a per-report basis. Have a stored procedure .SQL template as well, where you do the parsing of these lists of parameters and other common prep-work.

18. Another Enormous Time Saver Stop Using Possessive Fields Right Now! In the beginning, MEDITECH provided versions of common possessive fields in the DR tables, like: AdmVisits.FinancialClassName > ADM.PAT.financial.class’s.name And that was convenient…but now all of those fields are going away in 5.67! So every report that uses them will have to be fixed. So just stop using them now. Get our Data Def lookup utility out of the library and start using it when you’re building reports to quickly identify possessive fields in DR tables and generate the matching LEFT JOIN to get the dictionary table into your query.

19. Questions?

20. Common Report Components Once the core stored procedure is built and validated, then you want to move on to the report layout in .RDL. In the layout, we want to consistently provide: • Header with a title matching the name of the report in report manager, and including the DRN. • Header should also include the site logo, via a URL. • An expression-driven Parameter(s) listing. • In the Footer, the report path, date run and page X of Y should be shown. • In the .RDL properties, make sure to fill in the Author and Description properties. The Description field flows over into Report Manager, so is a key field for your end-user.

21. Common Report Problems To meet our time-metric per report, there are some common pitfalls to avoid if at all possible: In the presentation layer / RDL, we want to avoid: • DISTINCT in the report layout. (don’t do that if you can help it in the query, either…). • Stay away from sub-reports that are invoked per detail row. Segregate them to on-demand drill-downs or widget-style blocks for dashboards where you can call the sp as few times as possible. • Don’t filter data in the report (that should happen in the stored procedure/query) unless you are doing set of cross-tabs with specific filtering per cross-tab. Our model should always be to do the heavy lifting data-wise in the stored procedure, and save the RDL for formatting and layout.

22. Report Appearance Your users will respond very positively to: • Reports with a consistent color scheme • …and consistent font sizing and style • …properly aligned fields and columns • …long listings with alternating row colors. If you don’t have a style-sheet for your reporting from Marketing, see if they have one – or create one yourself. (If you need a style-sheet template, let us know and we’ll send you one). Nothing is more wasteful than a report that is not used because the end-user is repelled by its appearance.

23. Displaying Names and Mnemonics Where space permits on your report layout, aim to always display dictionary table values and their mnemonics like so: Name [ Mnemonic ] To avoid confusion…like you might encounter with these doctors: • John A Smith [ JASMITH ] • John A Smith [ JASMIT2 ] • John A Smith [ JASMIT3 ] This is a real example of grandfather, father, and son. Two of them are neurosurgeons!

24. Displaying Patient Identifiers A minimal patient identifier would be: Doe, John [ DA00000456 ] As in Patient Name and MRN. But this is very minimal, if at all possible, display five points of comparison for positive patient ID: Name [ MRN ] (Gender/Age) AccountNumber This is far less room for confusion with these identifiers, and the account number is handy for looking up the patient in LIVE.

25. Closeout Documentation At the end of the process, after the report has been validated by the end-user and checked off to move to the LIVE folder, and all is well…it’s time to close out the data request: • Update the Data Request Number tracking to indicate the final status of the request (Published, etc.) • Confirm that the latest stored procedure code and .RDL are in their respective FILESERVER folders. • Check into version control, if you are using VC. • Close out the help desk ticket. Everything we’ve done can – and should – be assembled into a standard work document for your developers or analysts to follow. If you haven’t encountered standard work yet, it is a checklist of what you need to remember to always do, but might forget.

26. Last call for Questions!

27. Wrap-up Copies of the PowerPoint and supporting documentation are available on our web-site at: http://new.iatric.com/muse-info Or send Thomas an email at: Thomas.Harlan@iatric.com Or give him a call at: (978) 674-8330 (PST)