Transcript SQL Reporting - New Mexico Military Institute

SQL Reporting
Another tool in our IT toolbox.
It may not be the sharpest, but it’s free with msSQL
and it empowers the users, some.
By Bryan Yates - [email protected]
Programmer
New Mexico Military Institute
November 1, 2011
Agenda
What can it do for me?
 Pre-Req’s
 Server components
 Workstation components
 User empowerment
 Subscriptions
 Data sources
 Pros/Cons
 Live Demo and Questions

Microsoft SQL Reporting






Web based delivery of SQL
Reportedly works with Oracle, SharePoint
Lists, and several other SQL databases
Works on Microsoft SQL 2008, including
multiple Databases in a single report
Users have many export format options
Users can have nice filter and sort options
“Subscriptions” will send emails of reports in
any format on a schedule, even to non-users
Example
Pre-Requisites

SSRS ships with SQL server (Enterprise
or Standard)
◦ Server to support SSRS
◦ SQL license(s) (licensed by CPU)
◦ Optionally a cluster of servers

A Windows workstation for development
(optional)
Strategy





Install server components
Install the right client building tools
Build the right SQL queries
Put the queries into a report
Tweak the report to allow user
empowerment
◦ Add Filters, sorting, sub-reports


Add/verify permissions to allow user access
Testing, tweaking, testing, testing
Server install

Install from msSQL 2008 CD, with SQL or
after “SQL reporting services”

“Report services configuration manager” guides
through simple setup (DB location, credentials)

Can use default web server
Not Cluster Aware

 Install SQL first
 Install SSRS on each cluster node
 “Join scaled out deployment” option
 May require second DB instance
 Both nodes up and running web services
Client Tool 1
BIDS
Plugin to Visual Studio 2008 (not 2010)
called Business Intelligence Development
Studio
 Tricky install graphically, from the msSQL
CD
 setup.exe /qs /ACTION=Install
/PID=long-key-## /FEATURES=BIDS
 Adds VS2008 if you didn’t have it, and
allows new project type of “Report
Server Project”

Client Tool 2
SQL Server Management Studio
SQL building and troubleshooting tool
 This is a mature development tool
 On msSQL CD, install all management
tools
 Works on any Windows® workstation
 Impersonation of users is pretty simple

execute as login = 'domain\otherguy'
select top 10 * from server.database.dbo.table
revert
Video of the development lifecycle

Video link
◦ My local link for today’s presentation

Download it yourself later
◦ http://wordpress.nmmi.edu/computertraining/
◦ This link includes the video and this
PowerPoint presentation
Results
Common look and feel for all reports
 Header area includes filtering items
 Export list consistent with all reports

Example
“Just
send
me
the
report”
Subscriptions

Features ability to send a report to any
email address, even if they have no
permissions or even credentials. Board of
Directors, big boss, etc, may not have any
SQL permissions
◦ Create Data Source using SQL user and pw
◦ Can develop report with Windows Auth, then
switch live report to use other Data Source
◦ Power Users can be allowed to switch Data
Source, but no overwrite any sources
Pros






Power users and IT experts can use tools to
quickly develop and modify SQL reports
Reuse of existing SQL queries
Users better enabled to filter data to their liking
Many Output formats (Excel, PDF, etc), which do
not have to be updated with new releases
SharePoint Web parts allow easy inclusion in SP
SharePoint list support allows some power users
to have the ultimate power
Cons
Average user cannot develop SQL
 Limited reports, without Report Builder 3
 Poor Report searching, multiple page
issues
 Not easy to see number of results
returned
 If using SQL on Microsoft Cluster, setup
tricky

Summary
Good product for the cost
 Standardizes report output choices to
allow easy adoption, common look and
feel
 Enables user empowerment
 Can use SQL you’ve probably already
created
 Development not for average Joe
 Questions before live demo?
