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