Transcript SQLSaturday_SSRS_101x

SQL Server Reporting
Services (SSRS) 101
William Assaf and David Nguyen
2
MISSION
To make a meaningful impact in our clients’
businesses by solving their business
challenges through leadership and
technology.
3
What is SSRS?
• SSRS is really nothing more than a website that
hosts web-based reports. It can be made public, or
kept internal to your intranet.
• If you want to show basic tabular reports and graph
reports in a web browser, this is your tool of choice.
• SSRS also has some really cool features behind it,
including:
• scheduling reports to be run and emailed out to users as a
PDF or excel spreadsheet
• report caching
• scaling out to multiple servers for heavy duty workloads
4
How Do I Get SSRS?
•If you own SQL Server, you own SSRS.
• SSRS is included in the SQL Server license Standard
edition and above,
• but also including "Express with Advanced Services", however SQL Express in
this feature and many is extremely limited.
• Express edition is not recommended for anything more than
limited trial purposes, not production.
• SSRS isn't sold alone, you buy a SQL Server license
and you get the database engine, integration
services, reporting services, analysis services, agent,
etc.
5
How Do I Install SSRS?
•SSRS installs from the original SQL Server .iso
media you choose Native (standalone) mode
or SharePoint integrated mode.
• Many functions of SSRS development behave
the same for either MODE, but they're hosted,
administered and secured differently.
•Keep in mind that one SQL Server license
only allows one SSRS install.
6
How Do I Install SSRS?
For this presentation we’ll be focusing on the Native mode installation.
7
How Do I Develop Reports in SSRS?
• You can develop reports in either of two tools:
• Report Builder (standalone Office-type application)
or
• SQL Server Data Tools – Business Intelligence (SSDT-BI)
(a shell of Visual Studio 2010, 2012 or 2013)
• Both are free downloads.
• Report Builder is more for the business power-user.
• SSDT-BI is where developers will work because it
can integrate with source control, deployments, etc.
• Developers will prefer working in SSDT-BI.
8
How Do I Develop Reports in SSRS?
• You can keep up to date with the latest SSDT-BI to
deploy reports to older versions of SSRS servers,
back to SQL 2008.
From project properties:
• Improvements have been made at each step, so it is
recommended to keep up to date with SSDT-BI.
• Keep in mind that the entire team should use the
same version of SSDT-BI to develop the same
reports.
9
SSRS Can Query Many Data Sources
10
AdventureWorks
We’re going to launch a live demo of
SSRS Development using SQL Server Data Tools – BI
and using the sample AdventureWorks database
as our data source.
Need sample data for your own learning?
AdventureWorks is Microsoft’s regularly-updated
sample database, a free download here:
http://msftdbprodsamples.codeplex.com/
11
Real Simple Things in SSRS
1. Start with a wizard and a query.
2. Design/Preview tabs, and folders for shared
DataSources and DataSets.
3. Point out ReportData tab.
4. How to display a date and convert a date in the
SSRS expression language
5. Concatenate two values together
6. Map a parameter through to the dataset query
7. Deploy the report
12
Top 10 Tips to Know About SSRS
1. Don't do page 1 of x ([&OverallTotalPages])
13
Top 10 Tips to Know About SSRS
2. Never do
SELECT * FROM [table]
Make SQL Server do the work.
Filter in your queries with parameters as much as
possible. Query on the data (rows AND columns) that
you will actually use.
Use parameters with default values that are fast-performing, so have
start/end dates default to this week, not this year. Have a multiselect
drop down default to none or one, not all. (Example: branches)
14
Top 10 Tips to Know About SSRS
3.
Use the ExecutionLogn views to analyze performance
• Data Retrieval
• Processing
• Rendering
Get report duration data per report, per parameter set, per
presentation format, etc.!
The ExecutionLog has been incremented in major releases,
adding data to the view each time:
• SQL Server 2005 – ExecutionLog
• SQL Server 2008 – ExecutionLog2
• SQL Server 2008 R2 – ExecutionLog3
15
Top 10 Tips to Know About SSRS
4. Don’t write queries against your live production data, if
you can help it.
Your read-only reports could have a negative performance
impact on your read-write queries.
Lots of other options here:
• Stage data on another database (even on the same
server). Create a copy of the data and keep it up to date
using:
• Change Tracking and/or SSIS
• Transactional Replication (live)
• Database Snapshots (nightly)
• AlwaysOn Availability Groups (Enterprise edition, live)
16
Top 10 Tips to Know About SSRS
5. Don’t include extraneous datasets, they will get run
even if you don't use them!
17
Top 10 Tips to Know About SSRS
6. Always include a timestamp for the report of when it was
run in the header.
If possible, also include a timestamp for when the underlying
data was last updated, for example, if your data warehouse
process runs nightly, record the time it successfully finishes, or
when the cube successfully populates, and include that on
reports.
That way, end-users will immediately know if a problem
occurred with the overnight data warehouse process.
18
Top 10 Tips to Know About SSRS
7. Use drilldowns to provide detail, but set drilldown
groups to be collapsed by default, not fully
expanded by default.
Use subtotal and grand total rows to provide
immediate value, let users drill into the detail row
data as they see necessary.
19
Top 10 Tips to Know About SSRS
8. Don’t do too much.
Lots of clients will ask for a single giant report with
massive amounts of data.
They may want some sort of dashboard, but when
possible, avoid subreports and break out reports into
individual instances. Considering building a
dashboard as a single new report, independent of
subreports.
20
Top 10 Tips to Know About SSRS
9. Copy report Parameters into the header of the
report, so that it is obvious that the data is
filtered.
List out parameter selections, date filters, etc., in
the header of the report.
In our demo example, adding
“Displaying transactions after [startdate]” would
be appropriate in the header.
21
Top 10 Tips to Know About SSRS
10. Rapid, frequent prototypes
The most efficient way to develop reports for
users is to show them the report in development
early on and regularly. No surprises.
You will find more bugs and bugs earlier with
many early demo’s to the target audience.
In these meetings, work with the client to focus
on data presentation and data quality, not
cosmetic issues. (This is difficult!)
22
Questions?
23
Bios and Contacts
• William D Assaf, MCSE
– Principal Consultant
– [email protected]
– Twitter: @william_a_dba
• David H Nguyen, MCP
– Associate Application Development Consultant
– [email protected]
– Twitter: not yet
This presentation, including all source code and this slide deck, has
been posted at William’s blog & David if he gets one:
SQLTact.com