SQL Reporting - New Mexico Military Institute
Download
Report
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?