Inspiring Reporting Options in the World of 6.0

2011 MUSE International Session 334 – 1:30 pm June 3rd Inspiring Reporting Options in the World of 6.0 Presenter: Jamie Gerardo

Todays Agenda • Report Development Options in 6.0 • DR-based report development options • SQL Server Report Services (SSRS) • Report Builder • Analysis Services (OLAP)

Report development options in 6.0 • NPR Report Writer – can only be used for 6.0 NPR databases/modules (ABS, ADM, AP, BAR, CA, BBK, FA, GL, ITS, LAB, MIC, MM, MIS, MRI, PHA, PP, PTH, SCH). • 6.0 Custom Report Designer – for newly written 6.0 applications. • These two tools are mutually exclusive – can’t use one to write reports across new 6.0 and other applications/modules. • IDAD (Iatric Data Access Driver): iDad creates DPMs and Data Definitions to replicate the 6.x application data structure in an NPR format. • Meditech Data Repository and SQL Server Tools

What is SQL Reporting Services? Reporting Services is a server-based reporting application that provides reporting functionality for a variety of data sources. SSRS tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components such as Share Point. Reporting Services provides a range of ready-to-use tools and services to help you create, deploy, and manage reports for your hospital. Reporting Services is included with your licensed copy of SQL Server for your Meditech Data Repository.

SQL Reporting Services • Advantages: most flexibility in report design and functions; can link to T-SQL stored procedures for powerful data analysis and manipulation; wide variety of report design objects (tables, pivot-table like matrix, charts, gauges, etc.). • Disadvantages: not for the average user; requires experienced programmer/analyst to design reports; user’s cannot design and manipulate their own reports

Basic components of SSRS • Visual Studio development environment • Connection to database and dataset • Report development • Report deployed to the Report Manager (other application, Share Point, etc.) • Report Manager (Intranet) • Security is assigned • Users access reports

Reporting Services Example 1 Nursing Report with date range parameters (selection criteria). Because, all of the data processing occurs on the SQL Server, this summary returns in a matter of seconds.

Reporting Services Example 1a Date Range entered from parent report is passed to the drill thru report.

Reporting Services Example 2 Reports are deployed on the Report manager in a folder format.

Reporting Services Example 3 This report has drop down menus for both Fiscal Year and Fiscal Month. Drill thru reports on Amount Total and Count Total values.

Reporting Services Example 4 You have the ability to export report in various formats; most commonly used are PDF and Excel LabPhyscianUtilization.xls

Reporting Services Example 5 – Exported to Excel An exported version of the Cardiology Billing Stats report in Excel.

Reporting Services Example 6 An hourly volumes report with a single date drop down along with comparative day data. VolumesByHourAndDay.xls

What is a Report Builder? • An ad-hoc online Report Builder for non technical end users to create their own reports. • Reports can be saved, modified, and deleted. • Similar reporting options as SSRS: tables, matrix and graphs. • The Report Builder application and the saved reports are accessed via the Report Manager.

Report Model / Report Builder Advantages: • Allows reports to be dynamically generated by “end-users.” No need to come to IT for “one more column” on a report • Web-based reports can be nicely designed and formatted • Report Models can be indexed for excellent performance • Report Manager provides secure access to report execution • Disadvantages: • Requires technical staff to develop and maintain underlying SSRS • Cannot address more complex reports (e.g., reports that require multiple passes at HCIS data to produce result set of interest) • Another copy of data (in application db, in DR, now in Report Model datamart)

Basic Steps for Report Builder • We typically recommend scheduled extraction to a datamart but it is possible to build Report Model Views directly against the DR using SQL Tables or SQL Views. • Construct helpful data transformations in datamart extract logic (e.g., convert text Lab Results values to numeric fields, convert CDS Response to Datetime datatype, etc.). • Create the Report Views (data connections) and the Report Model • Deploy Report Models and Train end user on Report Builder application.

Report Builder – Design Mode The table report template is blank The chosen Data Model, available tables and fields The end user then drags and drop fields on to the report template.

Report Builder – Design Mode I’ve brought over Procedure Description, Total Amount and Total Transaction Count and Create a Report Title Filters have been applied

Ability to apply multiple filters Almost all fields are available to use as a filter. Many have drop down menus which makes it easier for the end user.

Report Results Report output still in Design Mode. From here you can print, save and adjust the print layout

What is Analysis Services? • SQL Server Analysis Services is used to perform OLAP analysis. Analysis Services analyzes data in advance and figures out those daily, weekly, and monthly numbers and stores them. The basic unit of storage and analysis in SSAS is the cube. A cube is a collection of data that's been aggregated to allow queries to return data quickly. • The cube functions similar to a pivot table in Excel.

What’s In a Cube? • Fact Tables define events occurring over time (e.g., patient visits, medication administrations, warnings, lab tests, etc.) • Measures are numeric computations of facts (e.g., count number of visits, total number of administration warnings, etc.) • Dimensionsare the ways you might want to analyze the measures. Examples include items from MEDITECH dictionaries (e.g. providers, drugs, locations) as well as regular time increments (days, months, years).

SQL Analysis Services • Advantages: easy to use for end users; very powerful and offers quick analysis; allows end user to slice and dice the data easily • Disadvantages: requires experienced programmer/analyst to design cubes; they are difficult to develop and modify; only offers numeric analysis.

SQL Server 2005 Analysis Services Cubes are Multidimensional Databases that allow data to be easily analyzed, sorted, filtered, and manipulated to look for trends, outliers, and other information hidden within the data.

Deployed OR Cube Example Filters Drag and drop items into the row, column, aggregate values and filters

OR Cube Example 2 Drag and drop items to create different report views.

BMV Warning Summary Dimensions Measures Facts

Warning Analysis by Administered Location Dimension data can be hidden, but used to filter summary data. Dimensions can be arranged to show logical groupings; subtotals are automatically updated.

Warnings by Location With Chart Chart can be displayed side-by-side with detail data, or on its own.

Flexibility / Complexity Trade-off SSAS is easiest to use. Drag & Drop, Slice & Dice. Only provides numeric analysis. Difficult to develop & modify data. Report Builder allows non-technical users to build and format custom ad-hoc reports. Report developers need not understand data relationships. Requires IT to build Report Models which limit available data columns. Complex data analysis / manipulation is not possible. Report Projects require programmer-level technical skill set to develop reports. Uses Visual Studio programmer environment for report development. Most flexibility in report design and can link to complex T-SQL statements for reports requiring data manipulation and analysis.

Questions? Thank you for attending! Jamie Gerardo Acmeware, Inc 781-329-4300 ext. 203 jgerardo@acmeware.com

Inspiring Reporting Options in the World of 6.0