MUSE International Tuesday Workshop – Session 807 - PowerPoint PPT Presentation

Loading...

PPT – MUSE International Tuesday Workshop – Session 807 PowerPoint presentation | free to download - id: 3dc462-NDFmM



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

MUSE International Tuesday Workshop – Session 807

Description:

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 – PowerPoint PPT presentation

Number of Views:28
Avg rating:3.0/5.0
Slides: 133
Provided by: acmewareC
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: MUSE International Tuesday Workshop – Session 807


1
DR Reporting Made Easy with Report Builder 3.0
MUSE International Tuesday Workshop Session
807 May 31, 2011, 100P 330P Glen DAbate
2
Introductions
  • Instructors
  • Acmeware
  • Course Participants

3
Course Overview
4
Objective
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.
5
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

6
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

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

8
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

9
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!
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.
Too Cold!
10
Report Model / Designer Approach
PROs
  • 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 developers
    systems

CONs
  • 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)

11
Recipe for Constructing Reports
  • 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.)

12
Where Does the Data Come From?
Custom Datamart
Other Data Sources
Data Repository Tables Directly
13
Where Are Models Developed?
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
14
Where Are Reports Developed?
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
15
SSRS Report Builder User Interface
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
16
DR Data Access Strategies for SSRS Report Models
17
SSRS Data Models Data Sources
  • 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

18
SSRS Model Data Access Option
  • Directly using Data Repository Tables
  • Constructing SQL Views
  • Constructing and Updating Datamarts

19
DR has 6000 Tables as well as two Live Databases
in 6.0
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.
20
Using SQL Views or SQL Datamarts
  • 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

21
Example SQL View Non Conf. Patients
22
Example of Registration Datamart Extract
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
23
Incremental Data Extracts to Datamart
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
24
Adding DR livedb or Datamart Indexes
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
25
Example Datamarts at a Client
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
26
Creating SSRS Report Model Projects
27
What is a Report Model Project
  • 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

28
Report Model Project Components
  • 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

29
Launching BIDs (Visual Studio 2008)
30
Creating a New BI Report Model Project
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
31
Data Source View Wizard
Previously defined data connections
32
New Data Source Designer
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
33
Solution Explorer
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
34
Project Properties
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)
35
Visual Studio 2008 R2 IDE
Tabs for .dsv and .smdl
Solutions Explorer
Available Tables/Columns as Determined by Data
Source View Design
Example View Design
Properties
36
Creating Data Source Views
37
Data Source View Features
  • 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

38
Adding or Importing a .dsv File
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)
39
Data Source View Wizard
40
Default .dsv Relationship
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 .
41
Select Datamart Tables in a .dsv
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
42
Name the .dsv
Meaningful name to identify .dsv file when
building Report Models
43
Relationships Must be Defined
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.
44
Relationships Must Be Modified
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
45
Design View of Registration .dsv
Peer
Peer
Parent
Child
Child
46
Enter Friendly Name Property
Properties for selected object (Registration Data
table/entity)
Modifying the Friendly Name Property will default
to the Entity Name in the Report Builder
47
Creating Report Models
48
Report Model Features
  • 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 systems 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

49
Adding a New Report Model
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.
50
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)
51
Report Model Generation Options
We have primarily taken the default generation
rules
52
Report Model Statistics
Recompile statistics if the .dsv changed in any
way. I typically always choose the Update model
statistics option.
53
Naming a Report Model
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.
54
Modifying Report Model Properties
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
55
Building Deploying Report Models
A Solution, one or more Projects, or one or more
Report Models may be built or deployed
Build and deployment Status
56
Viewing Report Models in Report Manager
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
57
Give me a Break!
58
Reporting Builder Wizard
59
Launching Report Builder
60
Report Builder Automatic Install
61
Create Report Options
62
Data Source Using Report Models
63
Design a Query
64
Simple Filter for Inpatients
65
Viewing Query Results
66
Defining the Matrix Adding Groups
67
Layout Options Group Drilldown
68
Style Options
69
Completed Wizard Output
70
Running the Report
71
Report Builders Interactive Development
Environment (IDE)
72
IDE Windows
73
IDE Design Toolbar
74
Office Button, Settings Resources
75
Report Data Window
76
Row Groups / Column Groups
77
Design View Window
Status Bar
78
Report Data Filters
79
Filtering Result Data
  • 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

80
Query Filter (Using Wizard )
81
Small Balance Write-off By FC/Ins
82
Modifying Filters by Editing Query
83
Adding a Relative Date Filter
Using a fixed from/thru date range
84
Numeric Filter with Range
85
Filter with Drop-down Box (Determined by Model)
86
Filtering with List (Multi-Selection)
87
Text Filtering
88
Report Output of Filtered Data
89
Report Parameters Formula Fields
90
Dropdown Parameter Filter
91
Parameter in Report Data Window
92
Parameter Filter as Dropdown Single Select
93
Adding a Second Filter Prompt Allowing Multiple
Selections
94
Multi-Select Parameter Filter
95
Parameters use Hidden Datasets
96
User-Defined Formula Fields
  • 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

97
Adding a Computed Query Field
98
Defining AR Age Days with a Fx()
99
Computed Fields in Output
100
Adding a Post Query Calculated Field
101
Formula Fields in Parameters
102
Adding From / Thru Date Range
103
Make Sure Parameter Type is Set
104
Date Range Prompts in Report
105
Dataset Based Filters
106
Report Comparing Two Data Fields
107
Formatting and Layout Options
108
Users Have Great Flexibility in Report layout and
Formatting
  • Data Type Formatting
  • Placement
  • Alignment
  • Borders
  • Fill
  • Images

109
Text Box Formatting
110
Report Title Formatting
111
Date Data Type Formatting
112
Layout Options (col. Widths, page , more)
113
Layout Options Output
114
Setting Property Based on Values
115
Images and Conditional Formatting
116
Group Features
117
Report Grouping
  • 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

118
Add Facility to Census Report
119
Group Methods and Properties
120
More Group Properties
121
Page Break with Each New Location
122
Tablix Member Properties
123
Advanced Mode Static Fields
124
Repeating Report Title on Pages
125
Adding a New Facility Group
126
Splitting Merging Text Boxes
127
Grouped By Facility
128
Charts Maps (Time Permitting)
129
Example of Admissions By Month
130
ED Turnaround Times
131
Discussion, Questions Answers
132
Thank You!
  • Acmeware Educational Sessions
  • Friday 6/3, 1000A Replacement Session - 6.0
    Custom Reports with Data Repository and SSRS
    2008, Ian Proffer presenting
  • Friday 6/3, 1100A - 372 Basic Meaningful Use
    Reporting From Data Repository, Glen DAbate
    presenting
  • Friday 6/3, 130P - 334 Inspiring Reporting
    Options in the World of 6.0, Jamie Gerardo
    presenting
About PowerShow.com