Microsoft SQL Reporting Services
Download
Report
Transcript Microsoft SQL Reporting Services
Session 8:
Reporting
MVC
Gerard Beckerleg
Course Website
http://sharepoint.ssw.com.au/Training/UTSNET/
Course Materials
Admin Stuff
Attendance
Hands On Lab
You initial sheet
You get me to initial sheet
Certificate
At end of 10 sessions
If I say if you have completed successfully
Last week
Postback
Client
Post request
with data
Response
Server
Last week
Postback
Bring data to web
Databinding
Data source
Data
Last week
Postback
Show/view
Bring data to web
Databinding
Passing parameters
Validation
Enter
Client
Data
Server
Last week - Additionals
Database connection with LINQ2SQL
Where is it?
How can I change it?
How do I maintain it across different servers/installations?
Reporting
SQL Server Reporting Services
"SSRS"
Initially released as add-on for SQL Server 2000 in 2004
Part of SQL 2005 / SQL 2008
Web-based reporting solution
Who has done a report?
History of Reporting
1991-3 – .XLS and .DOC
1992 – Access 1.0
1994 – VB 3 with Crystal Built-In
1995-99
VB 4, 5, 6 (VB Report Designer)
A lot continuing with Crystal
1998 – Web
.ASP
.DOC, .XLS, .PDF
2001 – Visual Studio .NET with Crystal Reports
2004 – Reporting Services as add-on for SQL Server 2000
2005 – Reporting Services with SQL Server 2005
2008 – Reporting Services with SQL Server 2008
2009 – ReportBuilder v3 released
Reporting Services Architecture
Browser
Data Sources
(SQL, OLE DB, XML/A,
ODBC, Oracle, Custom)
Office
Custom Application
XML Web Service Interface
Report Server
Security Services
(NT, Passport,
Custom)
Report Processing
Output Formats
(HTML, Excel, PDF, Custom)
Data Processing
Security
Rendering
Scheduling & Delivery
SQL Server
Delivery Targets
(E-mail, SharePoint,
Custom)
Reporting Lifecycle
Reporting Services is an open and extensible platform
supporting the authoring, management and delivery of rich,
interactive reports to the entire enterprise.
Authoring
Management
Delivery
Report Authoring
Reports are defined in Report Definition Language (RDL), a
documented XML schema
Use Microsoft or 3rd party tools that support RDL
Create single reports from multiple data sources
(SQL, OLE DB, ODBC, Oracle, and .NET data providers)
More info on the RDL spec: www.microsoft.com/sql/reporting
Report Authoring - Controls
Data regions
Tablix (SQL 2008)
Table -> Tablix
List (like Access) -> Tablix
Matrix -> Tablix
Chart
Subreports
Images
Tablix
Generalized layout report item
Grouped and Detail data
Tablix - Table
Use a table to display detail data
Organize the data in row groups, or both.
The Table template contains three columns with a table header
row and a details row for data.
Tablix - Matrix
Use a matrix to display aggregated data summaries
Grouped in rows and columns, similar to a PivotTable or
crosstab.
The number of rows and columns for groups is determined by
the number of unique values for each row and column groups
Tablix - List
Use a list to create a free-form layout. You are not limited to a
grid layout, but can place fields freely inside the list.
You can use a list to design a form for displaying many dataset
fields or as a container to display multiple data regions side by
side for grouped data.
Charts
Charts
Charts
Gauge
Report Management
Report definitions, folders, and resources are published
and managed in a reporting web service
Managed reports can be executed either on-demand or
via schedule and can be cached for consistency and
performance
Scalable & Extensible server architecture
Role-Based Security Model
Tasks
Sets of tasks
Default roles installed by default (browser, publisher)
Default roles can be customized, new ones created
Roles identified by name, localized
Groups/Users
Sets of low-level operations
Item-level (e.g., create report) or system-level (e.g., manage jobs)
Not customizable
Task
Roles
Operation
Role
Group or
User
Role
Assignment
Windows/Active Directory or custom authentication users
Role Assignments
Associates groups/users with Roles
Inherited from parent in namespace
Item
Logging and Monitoring
Performance Monitoring
Event Log Integration
Counters for performance
Report Execution for analysis
Critical events and errors
Trace Events
Can traces all server activities, response times, security
events
Detail level depends on configuration setting
Report Delivery
Traditional (paper) and interactive (web) reports
On-demand (“pull”) or event-based (“push”) delivery
Choose from multiple formats (HTML, Excel, PDF, XML)
Deliver reports to many devices (e-mail, file share, etc.)
Ad-hoc Reporting
Scheduling
Management events can be scheduled on the report server
Schedules are stored in database and integrated with SQL
Agent
Caching, Subscriptions, History
When triggered, Agent adds entry to queue
Scheduled events are queued in database and polled by
Windows service
Delivery - Report generation
Server side
Client side
Reportviewer Control in "local mode"
ASP.NET
Windows Forms
Silverlight
WPF
Client-side
What did we see?
Table Control
List Control
Chart Control
Matrix Control
Subreports
What didn't we see?
ASP.NET Chart Control
http://weblogs.asp.net/scottgu/archive/2008/11/24/new-asp-net-chartingcontrol-lt-asp-chart-runat-quot-server-quot-gt.aspx
Dundas Chart (SSRS)
http://www.dundas.com/Products/Chart/RS/Features/NewFeatures.aspx
ASPOSE
http://www.aspose.com/categories/ssrs-rendering-extensions/default.aspx
Summary
History of reporting
Architecture
Report Life Cycle
Capabilities
Hands on lab
What is ASP.NET MVC
...and is WebForms dead?
Our ancestors developed for the web
in ASP script...
We have had WebForms...
Now we have ASP.NET MVC
Agenda
Background of ASP.NET MVC
Difference between WebForms and ASP.NET MVC
Tips and Tricks
Conclusion
ASP.NET MVC
Model
-
View
-
Controller
MVC is not new
SmallTalk
Ruby on Rails
Spring, Struts
Monorail
Does ASP.NET MVC replace WebForms?
WebForms == WinForm developers
MVC == HTTP + jQuery + CSS developers
ASP.NET Webform Development
Add a new page
Drag a label and button control to canvas
Double click on button control for event handling
Code ‘Hello World’
Group a few controls via a ‘user control’
ASP.NET MVC Development
Add a new action
Code ‘Hello World’
Add a new view
Implement HTML mark-up for the view (ASPX)
Development
WebForms
Controls dragged
onto canvas
Composition via
controls
Encourages design
patterns
Control of all html
output
MVC
Why choose ASP.NET MVC?
Testability
Unit test for ‘Hello World’ action
More control over HTML
Why ASP.NET MVC?
Not just cool...
Not just for testability...
Not just because you get more control...
Because you get more perspective!
Web is an HTTP highway
Controls
Cool Tech
Background to ASP.NET MVC
Separation of concerns
Controllers (Actions)
Views
Models
More expressive of HTTP protocol
Testable features
Strongly typed support
The key actors in ASP.NET Webforms
ASPX page gets request
Page lifecycle with events
Codebehind methods
The key actors in ASP.NET MVC
Browser
Request
Controller
Response
View
ViewModel
Model
So lets dive into it
Default
Project
ASP.NET MVC Examples
Dispatch King (Implemented by SSW) –
http://dispatchking.com
Stack Overflow – http://www.stackoverflow.com
ASP.Net Music Store
http://www.asp.net/mvc/tutorials/mvc-music-store-part-1
What you need to get started in MVC
.Net 3.5 SP1 http://www.microsoft.com/downloads/
MVC
http://www.asp.net/MVC/
Install VS2010
Advanced steps
MVCContrib http://www.codeplex.com/MVCContrib
Moq
MVC Futures http://aspnet.codeplex.com/releases/view/41742
http://code.google.com/p/moq/
To get going quickly
Watch the Music Store Tutorial
http://www.asp.net/MVC
Watch the MVC StoreFront Demo
http://blog.wekeroad.com/
Nerd Dinner
http://nerddinner.com
So what is there to control
URL structure
Testability
HTTP Verbs
Controllers/Actions/Views
View engine
ASP.NET MVC structure
(aka closer to the real thing)
Model
Browser
Request
Routing
Controller
Response
View
ViewModel
Biz
Repository
e.g. SQL/Xml
MVC
SnippetDesigner http://www.codeplex.com/SnippetDesigner
Peter Gfader
http://blog.gfader.com/
Phil Haacked
http://haacked.com
Stephen Walther http://stephenwalther.com/
Tatham Oddie
http://blog.tatham.oddie.com.au/
Nhaml
http://code.google.com/p/nhaml
Summary
Resources
MSDN – Download
Whitepaper
www.ssw.com.au/ssw/standards/DeveloperSQLReportingServices
.Net User Groups
Monthly Meetings
www.ssw.com.au/NetUG
Resources
Book
www.mannpublishing.com/
The Rational Guide To: SQL
Server Reporting Services
by Anthony Mann
Resources
Free Web Application Toolkits
UX design patterns
http://quince.infragistics.com/
AJAX Control Toolkit May
http://www.microsoft.com/web/downloads/webapptoolkits
http://stephenwalther.com/blog/archive/2011/05/09/may-2011-releaseof-the-ajax-control-toolkit.aspx
MVC 101 Video
http://channel9.msdn.com/Events/DevDays/DevDays-2011Netherlands/Devdays002
Thank You!
Gateway Court Suite 10
81 - 91 Military Road
Neutral Bay, Sydney NSW 2089
AUSTRALIA
ABN: 21 069 371 900
Phone: + 61 2 9953 3000
Fax: + 61 2 9953 3105
[email protected]
www.ssw.com.au