DR Reporting Made Easy with Report Builder 3.0
1 / 132

- PowerPoint PPT Presentation

  • Uploaded on

DR Reporting Made Easy with Report Builder 3.0. MUSE International Tuesday Workshop – Session 807 May 31, 2011, 1:00P – 3:30P Glen D’Abate. Introductions. Instructors Acmeware Course Participants. Course Overview. Objective.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about '' - cuthbert

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Muse international tuesday workshop session 807 may 31 2011 1 00p 3 30p glen d abate

DR Reporting Made Easy with Report Builder 3.0

MUSE International Tuesday Workshop – Session 807

May 31, 2011, 1:00P – 3:30P

Glen D’Abate




Course Participants


The objective of this workshop is to provide an overview and demonstration of SQL Server 2008 Release 2 (R2) Business Intelligence (BI) Report Model development process as well as a demonstration of the easy-to-use SSRS Report Builder 3.0 application.

Workshop overview
Workshop Overview

  • Overview of DR reporting strategies in context of Report Builder

  • Identification of DR data and strategies for extracting to a Report Model datamart

  • SSRS Report Model Projects

  • Creating Data Source Views

  • Creating Report Models

  • Using Report Builder to access Report Models

  • Demonstration of Development of Reports Using Report Builder:

    • Connecting to Report Models

    • Using the Report Wizard

    • Formatting Reports

    • Filtering Report Data

    • Advanced Features

Glossary of terms
Glossary of Terms

  • MEDITECH Application database – Proprietary database structures (NPR or Advanced Technology - AT) in which MEDITECH applications directly file data

  • Data Repository (DR) – Replications of data from Application Database into an open (ODBC or OLE DB) accessible SQL Server RDMS

  • SQL Server Databases (livedb, testdb, livefdb, livendb, livemdb) – DR databases in which application data (test or live, NPR or AT) is stored

  • SQL Server Reporting Services (SSRS) – Feature of SQL Server RDMS that allows reports to be designed, developed and deployed for consumption

  • Datamart – Extraction, transformation, filtering, and indexing of data into structures specifically formatted for construction of Report Models

  • SSRS Report Manager – Web based application for hosting and managing reports. Also the application from which the Report Builder tool is launched.

  • SSRS Report Models – IT developed data structures on which the Report Builder application runs

  • SSRS Report Builder – Web-based application for non-technical staff to develop ad hoc reports

Course format
Course Format

  • Modular sections

    • Lecture on topic with slide presentation

    • Provide example demonstration building Report Models and reports as we go

    • Each section builds on previous section

  • Interactive discussion is encouraged throughout!

Discussion of alternative dr report development options to ssrs 2008 r2
Discussion of Alternative DR Report Development Options to SSRS 2008 R2

  • Microsoft Access 2007

  • Crystal Reports 2008 / 2011

  • Microsoft Excel - SQL T-SQL Downloads to .CSV Files

  • SQL Server Analysis Services (OLAP Cubes)

  • SSRS – Report Server Projects (i.e., full use of Visual Studio tools for SSRS report development)

  • Earlier versions of SSRS Report Builder (v1.0, v2.0)

  • SQL Service Reporting Services (SSRS) Report Models and Report Builder Version 3.0

Sql server reporting suite flexibility complexity trade off
SQL Server Reporting Suite - Flexibility / Complexity Trade-off

Too Hot!

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.

Just Right!

Too Cold!

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.

Report model designer approach
Report Model / Designer Approach Trade-off


  • 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

  • No software to load / support on developer’s systems


  • Requires technical staff to develop and maintain underlying SSRS Report Models

  • Cannot address more complex reports (e.g., reports that require multiple passes at HCIS data to produce result set of interest)

Recipe for constructing reports
Recipe for Constructing Reports Trade-off

  • Identify data-of-interest for end users (typically within one or a few MEDITECH applications)

  • Develop data access strategy. 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.)

  • Build SSRS Report Views using Datamart tables. This re-establishes “relationships” implicit in the NPR or M-AT data structures.

  • Build SSRS Report Models using Report Views. Internal fields can be hidden, other fields identified for filter selection

  • Deploy Report Models to Web Portal (Report Manager or SharePoint Site)

  • Train super-users to build there own custom Reports Builder application

  • Monitor Report utilization, report sharing, data access, etc. with SSRS Report metadata (i.e., who ran a report, when, what parameters, etc.)

Where does the data come from
Where Does the Data Come From? Trade-off

Custom Datamart

Other Data Sources

Data Repository Tables Directly

Where are models developed
Where Are Models Developed? Trade-off

Visual Studio Business Intelligence Studio (BIDS)

Models are Based on Data Defined and Available from Data Sources

BIDs is installed from the SSRS 2008 R2 Media, Selecting the option under “Shared Features”

Where are reports developed
Where Are Reports Developed? Trade-off

Web Server Hosting SSRS Report Manager

Launches Report Builder application. Can open existing reports or build new reports from scratch.

Can also be Launched from Start Button

Ssrs report builder user interface
SSRS Report Builder User Interface Trade-off

Office 2010 Look & Feel

Design View for construction report format and layout

Grouping and Report Hierarchical or Cross Tab Layout

column data field in dataset

Ssrs data model s data sources
SSRS Data Model’s Data Sources Trade-off

  • SSRS Data Models can include data from any available open data source

  • Available data sources include any information that can be accessed using available Microsoft Data Access Components (MDAC)

  • Typically (for us) this is MEDITECH DR data but can include: Excel Spreadsheets, Comma Separated Files (.csv – NPR Report Downloads), other vendor data (e.g., Kronos T & A, Craig Cost Accounting, Picis ORM, etc.)

  • Other data must have a relationship key to Join with DR data

Ssrs model data access option
SSRS Model Data Access Option Trade-off

  • Directly using Data Repository Tables

  • Constructing SQL Views

  • Constructing and Updating Datamarts

Dr has 6000 tables as well as two live databases in 6 0
DR has 6000+ Tables as well as two Live Databases in 6.0 + Trade-off

Identifying the appropriate source of specific application data from within the DR is one of the most challenging aspects of any report development using the DR

Data is highly de-normalized in the DR. “Name” in the AdmVisits table is very different than “Name” in DLabTest table, and very different than “Name” in MriPatients table.

Many factors determine what data to include in datamart including focus of Report Model, inclusion of Inpatients and/or Outpatients, length of history required and more.

Using sql views or sql datamarts
Using SQL Views or SQL Datamarts: Trade-off

  • Multiple related DR tables have data combined into a single table (e.g., visit level admission and abstracting data combined)

  • Default values can be entered where NULL data (e.g., replaced with “N/A”)

  • Text values, common in MEDITECH fields are converted to move useful data types (e.g., Numeric Response to SQL Numeric datatype)

  • Data can be filtered to meet reporting criteria (e.g., remove confidential patients)

  • Data can be filtered to a smaller set for efficiency (do you really need the ND test results from 1998?) [SQL Datamart Only]

Example of registration datamart extract
Example of Registration Datamart Extract Trade-off

Internal ID fields are not for Report Builder but used in Report Views

Typical data fields available in Report Model

DR Fields modified to better format

New Age field computed for Report Model

Inpatient or Outpatient Locations recombined to one field

Visit Provider Type data flattened for ease-of-use in Model

Incremental data extracts to datamart
Incremental Data Extracts to Datamart Trade-off

Cancelled Admissions or other filtering (e.g., remove confidential patients) can be include in datamart extract

The DR table column RowUpdateDateTime can be used to identify “changes” to data for incremental updates

Adding dr livedb or datamart indexes
Adding DR livedb or Datamart Indexes Trade-off

Restricting the volume of data and highly indexing data, especially at the Report View highest aggregate level (i.e., typically visit for MEDITECH application data) is highly effective at making report output very responsive

Example datamarts at a client
Example Datamarts at a Client Trade-off

Customer Defined Screen queries and responses (includes multiples) and time dated repeat results

ADM registration data as well as some ABS data

Charge, receipt, adjustment, refunds and other transaction level details

Visit level financial (B/AR) data

ITS Report summary information (though no text reports yet)

Laboratory Specimen Tests & Results

Materials Management Data

Patient Allergy information including free text descriptions

Audit Activity

What is a report model project
What is a Report Model Project Trade-off

  • In order to build a Report Model, you need to create a Report Model Project using Visual Studio Business Intelligence Studio (BIDs) available from SQL Server Media

  • A Report Model Project contains the components or objects that are used to build and deploy Report Models to the Report Manager web site

  • A Report Model Project is comprised of one or more Data Source (.ds) files, one or more Data Source View (.dsv) files, and one or more Report Model file (.smdl) files

Report model project components
Report Model Project Components Trade-off

  • Data Source (.ds) – this determines the source(s) of data to be used within the Report Model and available for the Report Builder application to use when generate ad-hoc reports

  • Data Source Views (.dsv) – this file defines the relationship between data gathered from multiple sources (views, tables, etc.). This takes the burden of this complicated task of the Report Builder report developer (though they must still understand the data!)

  • Report Model (.smdl) – this is the “object” that is deployed to the Report Manager web site and made available for the Report Builder

Creating a new bi report model project
Creating a New BI Report Model Project Trade-off

OLAP Cubes

What we are working with today

Importing / Exporting Data

Report Server Projects use the capabilities of the full Visual Studio IDE for Report Development

Give a meaningful Report Model name

Add to existing Solution or Create a new Solution container

Data source view wizard
Data Source View Wizard Trade-off

Previously defined data connections

New data source designer
New Data Source Designer Trade-off

Your DR Server or IP address or the SQL Server where your datamart resides

Can use a SQL service account or Windows credentials (depends on security configuration)

Datamart database or DR livedb, livemdb, livendb, livefdb, etc.

Establishing a Data Source for the Report Model lets it know where to go to find data on upon which reports (using Report Builder) will be built

Solution explorer
Solution Explorer Trade-off

Solution is simply a container in which Projects are loaded

Project contains the objects (i.e., code) that comprise a Report Models

Data Sources

Data Source Views

Data Models

Project properties
Project Properties Trade-off

Deployment Folder Locations

Web server

Project Properties determine where the Report Models will be deployed (must be a web server where the Report Manager application is installed)

Visual studio 2008 r2 ide
Visual Studio 2008 R2 IDE Trade-off

Tabs for .dsv and .smdl

Solutions Explorer

Available Tables/Columns as Determined by Data Source View Design

Example View Design


Data source view features
Data Source View Features Trade-off

  • A Data Source View is a description of components of the database to which the Data Source is pointing (Typically the SQL Tables, Views or Datamart)

  • This Data Source View describes the selected DR tables, their contents, and the relationships between them

  • Table in a Data Source View MUST have primary keys defined explicitly to determine uniqueness of records in the table

  • Table relationships established in the Data Source View are critical to the correct output being generated in Report Builder developed reports

  • Typically, defined relationships reconstruct the data relationships that exist in the MEDITECH applications

Adding or importing a dsv file
Adding or Importing a .dsv File Trade-off

Acmeware typically has a Development project and a Production project

Add a New or Existing Data Source View

New Data Source View launches wizard

Allows an existing .dsv file to be copied into project (e.g., copying a development .dsv to production)

Default dsv relationship
Default .dsv Relationship Trade-off

DR lacks defined FK constraints

We typically uncheck this and build the relationships from scratch

Selecting this option usually selects some of the correct columns but inevitably the defined relationship will need to be modified after completing the wizard

While adding foreign key constrains to a datamarts would be ideal, in practice, this is very difficult since MEDITECH does not ensure the sequence in which data hits the DR (and therefore the potential order in which it hits a datamart). For example, it is technically possible for a new Lab Test Print # to appear in a patient result before the corresponding Lab Test Dictionary receives an entry for the Print #.

Select datamart tables in a dsv
Select Datamart Tables in a .dsv Trade-off

Creating a Data Source View to allow for ad hoc Abstract Module reporting

One patient visit may have multiple diagnosis, DRGs, or ICD-9 Procedures

Name the dsv
Name the .dsv Trade-off

Meaningful name to identify .dsv file when building Report Models

Relationships must be defined
Relationships Must be Defined Trade-off

Default relationship created by wizard is typically incorrect in the context of MEDITECH DR (and corresponding datamart) schema design

Relationship Arrows should point from summary level tables to detail level tables

All three keys are required to identify a unique record in this table. This is not typical to a relational database schema.

Relationships must be modified
Relationships Must Be Modified Trade-off

The labels do not seem to make sense but his configuration has been tested and appears to be what is required

Summary level data is the “Source”, Detail level data is the “Destination”

Design view of registration dsv
Design View of Registration .dsv Trade-off






Enter friendly name property
Enter Friendly Name Property Trade-off

Properties for selected object (Registration Data table/entity)

Modifying the Friendly Name Property will default to the Entity Name in the Report Builder

Report model features
Report Model Features Trade-off

  • A Report Model (.smdl file) provides metadata (data about data) for the data being referenced by the Data Source View.

  • When the Report Model file is generated, entities, roles, and fields are automatically created

  • Typically, these entities and fields relate back to the datamart columns and their data within the database

  • Field properties are automatically generated based on the system’s analysis of the data in a .dsv field

  • A Report Model Design wizard steps through each option and setting required to build a .smdl file from a .dsv file

  • After running the Report Model Design wizard, the model can be published to the report catalog, assigned the appropriate role permissions, and then used in Report Builder

  • To make it even easier for users to create reports using this model, it can be further refined after the wizard is completed

Adding a new report model
Adding a New Report Model Trade-off

Like .dsv files, Report Models (.smdl) files can be created as new its using a wizard or can be imported as existing Items

We recommend having a Production Project to which .dsv and .smdl files are only copied (i.e., never modify in PROD). If Data Source Name is the same in DEV and PROD, no changes are necessary when importing to PROD.

Report models are generated for a single data source view dsv file
Report Models are Generated for a Single Data Source View (.dsv) File

A single .dsv file is used by a single Report Model (.smdl file)

Report model generation options
Report Model Generation Options (.dsv) File

We have primarily taken the default generation rules

Report model statistics
Report Model Statistics (.dsv) File

Recompile statistics if the .dsv changed in any way. I typically always choose the “Update model statistics” option.

Naming a report model
Naming a Report Model (.dsv) File

Use a meaningful name as this will be seen in the Report Builder tool

Do not publish a new model with the same name because you will invalidate existing reports that are generated against this model. If you do create a new model with the same name and try to publish the model, you will see an error message. Always work on the same model to ensure that the IDs remain the same.

Modifying report model properties
Modifying Report Model Properties (.dsv) File

Internal fields should be hidden

Roles can be given a friendly name

ValueSelection property determines how a column will be treated when a filter is applied in Report Builder

Building deploying report models
Building & Deploying Report Models (.dsv) File

A Solution, one or more Projects, or one or more Report Models may be built or deployed

Build and deployment Status

Viewing report models in report manager
Viewing Report Models in Report Manager (.dsv) File

Report Models can bee seen in Report Manager assuming appropriate security settings

Launch Report Builder to develop ad hoc reports using the Report Models we have created

Give me a break
Give me a Break! (.dsv) File

Design a query
Design a Query (.dsv) File

Style options
Style Options (.dsv) File

Running the report
Running the Report (.dsv) File

Ide windows
IDE Windows (.dsv) File

Ide design toolbar
IDE Design Toolbar (.dsv) File

Report data window
Report Data Window (.dsv) File

Design view window
Design View Window (.dsv) File

Status Bar

Muse international tuesday workshop session 807 may 31 2011 1 00p 3 30p glen d abate

Report Data Filters (.dsv) File

Filtering result data
Filtering Result Data (.dsv) File

  • Report Builder provides a Filter Building Dialog Box that simplifies the construction of report filters

  • Filtering result data allows a report to contain only data records of interest

  • Reports can filter on data that is not displayed in the output

  • Filters can be on field data as well as computed fields

  • Filters can be statically entered in the report design or dynamically collected through prompts during report execution

  • Filter groups are available for sets of ‘OR’ statements

  • Filters can be applied to the Report Query or can be applied to the dataset returned by the Query

Adding a relative date filter
Adding a Relative Date Filter (.dsv) File

Using a fixed from/thru date range

Text filtering
Text Filtering (.dsv) File

User defined formula fields
User-Defined Formula Fields (.dsv) File

  • Formulas allow you to combine, aggregate, filter, and evaluate both numeric and text data. You can create formulas and save them as new fields

  • All formulas are defined within the context of an entity. This means that the formula returns a single value for each item within the entity. For example, suppose you create a formula within the Registration entity. The formula returns a single value for each Visit (Registration)

  • A formula can contain any or all of the following: user created fields, functions, operators, and constants

  • You will need to create fields for specific types of information such as the count of records or the count of any item. This is not something that can be pre-populated

  • From and through date ranges will need to be created if you want to set those values as a prompt

Users have great flexibility in report layout and formatting
Users Have Great Flexibility in Report layout and Formatting (.dsv) File

  • Data Type Formatting

  • Placement

  • Alignment

  • Borders

  • Fill

  • Images

Text box formatting
Text Box Formatting (.dsv) File

Muse international tuesday workshop session 807 may 31 2011 1 00p 3 30p glen d abate

Group Features (.dsv) File

Report grouping
Report Grouping (.dsv) File

  • Report Grouping allows data to be aggregated “by” some grouping

  • Grouping is typically by a value (e.g., Insurance, Facility, Provider) or by a component of the time domain (admissions by month, revenue by quarter)

  • Report Builder allows for Group Headers and Group Footers to be added to a Report

  • Grouping can be added as columns (in a typical tabular report, or as Rows in a cross-tap (or pivot table) report

Grouped by facility
Grouped By Facility (.dsv) File

Muse international tuesday workshop session 807 may 31 2011 1 00p 3 30p glen d abate

Charts & Maps (.dsv) File

(Time Permitting)

Ed turnaround times
ED Turnaround Times (.dsv) File

Thank you
Thank You! (.dsv) File

Acmeware Educational Sessions

  • Friday 6/3, 10:00A – Replacement Session - 6.0 Custom Reports with Data Repository and SSRS 2008, Ian Proffer presenting

  • Friday 6/3, 11:00A - 372 – Basic Meaningful Use Reporting From Data Repository, Glen D’Abate presenting

  • Friday 6/3, 1:30P - 334 – Inspiring Reporting Options in the World of 6.0, Jamie Gerardo presenting