Microsoft BI Analyst

SSIS

2009-09-09T04:42:00.000-07:00

Integration Services Information Worker InfoCenter prefix="o" ?>

In Microsoft SQL Server
2005 Integration Services (SSIS), a knowledge worker fulfills the traditional "domain
expert" role in business intelligence—someone who understands the data employed
by a solution and is able to translate the data into business information. Often,
the knowledge worker is the one analyzing the business to identify problems, opportunities,
or project characteristics. The variety in expertise offered by a knowledge worker
results in job titles such as Report Consumer (Business Analyst), Report Consumer
(Manager), Technical Trainer, Help Desk/Operation, or Network Administrator.

To help the knowledge
worker be as effective as possible, the following lists provide links to topics
concerning the design, creation, and configuration of Integration Services packages.

width="97%">

Integration Services

·

Integration Services Enhancements

Introducing Packages

·

Integration Services Packages

·

Control Flow Elements

·

Data Flow Elements

·

Integration Services Objects and Concepts

Creating Packages

·

Building Solutions and Integration Services Projects

·

Creating Packages in SSIS Designer

·

Creating Packages Using the SQL Server Import and Export
Wizard

Adding Features to Packages

·

Implementing Logging in Packages

·

Using Property Expressions in Packages

·

Using Variables in Packages

·

Creating Package Configurations

·

Using Checkpoints in Packages

·

Incorporating Transactions in Packages

Troubleshooting Packages

·

Debugging Control Flow

·

Debugging Data Flow

Deploying Packages

·

Deploying Integration Services Packages

Languages Used by Integration Services style="font-size: 10pt; color: black; font-family: Verdana">

·

Transact-SQL Reference (Transact-SQL)

·

Integration Services Expression Reference

Integration Services Developer InfoCenter

In Microsoft SQL Server
2005 Integration Services (SSIS), a developer designs and implements solutions based on the technology direction defined by the SSIS architect, the operational assistance
of the SSIS administrator, and the end-user feedback of the SSIS knowledge worker.
To these three sets of user groups, the title of SSIS developer may not be familiar.
These user groups may refer to their SSIS developer using job titles such as Application
Developer, Report Developer, ETL Developer, Data Warehouse Programmer, Database
Programmer, or Data Modeler.

To help the SSIS developer
be as effective as possible, the following lists provide links to a number of object
models and APIs for use in client-based and server-based applications.

width="97%">

SQL Server Integration Services style="font-size: 10pt; color: black; font-family: Verdana">

·

Integration Services Enhancements

Programming Integration Services style="font-size: 10pt; color: black; font-family: Verdana">

·

Integration Services Programming

·

Comparing Scripting Solutions and Custom Objects

Extending Packages with Scripting style="font-size: 10pt; color: black; font-family: Verdana">

·

Extending the Package with the Script Task

·

Extending the Data Flow with the Script Component

Extending Packages with Custom Objects style="font-size: 10pt; color: black; font-family: Verdana">

·

Creating a Custom Task

·

Creating a Custom Data Flow Component

Working with Packages Programmatically style="font-size: 10pt; color: black; font-family: Verdana">

·

Building Packages Programmatically

·

Enumerating Available Packages Programmatically

·

Managing Packages and Folders Programmatically

Running Packages from Other Applications style="font-size: 10pt; color: black; font-family: Verdana">

·

Understanding the Differences between Local and Remote
Execution

·

Loading and Running a Local Package Programmatically

·

Loading the Output of a Local Package

SQL Server Language Reference

·

Transact-SQL Reference (Transact-SQL)

·

Integration Services Expression Reference

·

XML for Analysis (XMLA)

·

Analysis Services Scripting Language (ASSL)

·

Data Mining Extensions (DMX) Reference

Programming Samples

·

SQL Server Integration Services Samples

Integration Services Object Model style="font-size: 10pt; color: black; font-family: Verdana">

·

Microsoft.SqlServer.Dts.Runtime

·

Microsoft.SqlServer.Dts.Pipeline

Integration Services Administrator InfoCenter

In Microsoft SQL Server
2005 Integration Services (SSIS), an administrator maintains and optimizes solutions
and projects at an operational level. Because these maintenance and optimization
tasks are wide ranging, an Integration Services administrator may be known by a
variety of job titles: Database Administrator, Report Server Administrator (Corporate
IT), Security Expert, Data Warehouse Administrator, or Lead Database Administrator.

To help the Integration
Services administrator be as effective as possible, the following lists provide
links to those topics considered most useful to the maintenance and optimization
of Integration Services solutions.

width="97%">

SQL Server Integration Services style="font-size: 10pt; color: black; font-family: Verdana">

·

Integration Services Enhancements

·

Integration Services Packages

Migrating Earlier Versions of Packages style="font-size: 10pt; color: black; font-family: Verdana">

·

SQL Server 2005 Integration Services Backward Compatibility

·

Migrating Data Transformation Services Packages

·

Using the Package Migration Wizard

Saving and Running Packages

·

Saving Packages

·

Backing Up and Restoring Packages

·

Running Packages

·

Scheduling Package Execution in SQL Server Agent

·

Using Checkpoints in Packages

Monitoring Package Execution

·

Integration Services Service

·

Monitoring Integration Services Performance and Activity

Deploying Packages

·

Creating Package Configurations

·

Creating a Deployment Utility

·

Installing Packages

·

Redeployment of Packages

2009-09-09T03:44:00.000-07:00

SQL Server FAQs
A collection of 14 FAQs on SQL Server 2005 Express Edition download and installation.
Clear answers are provided with tutorial exercises on installing SQL Server 2005
Express Edition; installing .NET Framework Version 2.0; installing Management Studio
Express Edition and Books Online.

More...

SQL Server Tutorials - Getting Started with Transact-SQL Statements

A collection of 10 tutorials for you to get started quickly with running Transact-SQL
statements on SQL Server 2005. Clear examples are provided for running CREATE TABLE,
INSERT, UPDATE, DELTE, SELECT, CREATE USER, GRANT EXECUTE statements.

SQL Server FAQs - Creating and Managing Databases and Physical Files

A collection of 16 FAQs on creating and managing databases on SQL Server 2005. Clear
answers are provided with tutorial exercises on creating, renaming, and deleting
databases; setting current database; bringing databases offline and online; reviewing,
moving and copy database physical files.

How to get a list all
databases on the SQL server?

Where is my database stored on the hard
disk?

How to create database
with physical files specified?

How to rename databases?

Why I am getting this error
when renaming a database?

What are database states?

How to set a database state to
OFFLINE?

How to move database
physical files?

How to set database
to be READ_ONLY?

How to set database
to be SINGLE_USER?

What are system databases?

More...

SQL Server
FAQs - Creating/Managing Tables and Adding/Altering/Deleting Columns

A collection of 16 FAQs on creating and managing tables on SQL Server 2005. Clear
answers are provided with tutorial exercises on creating, renaming, and deleting
tables; reviewing, renaming, deleting and adding table columns; changing column
data types.

What is a table?

What are DDL (Data Definition Language)
statements for tables?

How to create new tables with "CREATE
TABLE" statements?

How To Get a List of All
Tables with "sys.tables" View?

How To Get a List
of Columns using the "sys.columns" View?

How To Get a List
of Columns using the "sp_columns" Stored Procedure?

How To Get a List of
Columns using the "sp_help" Stored Procedure?

How To Generate
CREATE TABLE Script on an Existing Table?

How to create new tables
with "SELECT ... INTO" statements?

How To Add a New
Column to an Existing Table with "ALTER TABLE ... ADD"?

How To Delete an
Existing Column in a Table with "ALTER TABLE ... DROP COLUMN"?

How to rename an existing
column with the "sp_rename" stored procedure?

How to rename an
existing column with SQL Server Management Studio?

How to change
the data type of an existing column with "ALTER TABLE" statements?

How to rename an existing
table with the "sp_rename" stored procedure?

How To Drop an Existing Table
with "DROP TABLE" Statements?

More...

SQL Server FAQs - Understanding
INSERT, UPDATE and DELETE Statements

A collection of 18 FAQs on SQL INSERT, UPDATE and DELETE statements. Clear answers
are provided with tutorial exercises on inserting, updating and deleting single
and multiple rows; using column default values; using subqueries to INSERT and UPDATE
statements.

What Are DML (Data Manipulation
Language) Statements?

How To Create a Testing
Table with Test Data?

How To Insert a New
Row into a Table with "INSERT INTO" Statements?

How To Use
Column Default Values in INSERT Statements?

How to provide column
names in INSERT Statements?

What Happens If You Insert
a Duplicate Key for the Primary Key Column?

How To Insert Multiple
Rows with One INSERT Statement?

How To Update Values in a
Table with UPDATE Statements?

How To Update Multiple
Rows with One UPDATE Statement?

How to
use old values to define new values in UPDATE statements?

Is the Order of Columns
in the SET Clause Important?

How To Use Values
from Other Tables in UPDATE Statements?

What Happens If the UPDATE Subquery
Returns No Rows?

What Happens If the UPDATE
Subquery Returns Multiple Rows?

How To Delete an Existing
Row with DELETE Statements?

How To Delete Multiple
Rows with One DELETE Statement?

How To Delete All Rows with
DELETE Statements?

How To Delete All
Rows with TRUNCATE TABLE Statement?

More...

SQL Server FAQs
- Understanding SELECT Statements with Joins and Subqueries

A collection of 17 FAQs on SELECT statements with joins and subqueries. Clear answers
are provided with tutorial exercises on joining multiple tables with inner and outer
joins; using subqueries with IN, EXISTS, and FROM clauses; using UNION operations.

How To Join Two Tables in a
Single Query?

How To Write a Query
with an Inner Join?

How To Define and Use Table
Alias Names?

How To Write
a Query with a Left Outer Join?

How To
Write a Query with a Right Outer Join?

How To Write
a Query with a Full Outer Join?

How To Write an Inner Join
with the WHERE Clause?

How To Name Query Output Columns?

What Is a Subquery
in a SELECT Query Statement?

How To Use Subqueries with
the IN Operators?

How To Use Subqueries
with the EXISTS Operators?

How To Use Subqueries in the
FROM Clause?

How To Count Groups
Returned with the GROUP BY Clause?

How To Return the
Top 5 Rows from a SELECT Query?

How To Return the Second
5 Rows?

How To Use UNION
to Merge Outputs from Two Queries Together?

How To Use ORDER BY with UNION
Operators

More...

SQL Server FAQs - Understanding
SELECT Statements and GROUP BY Clauses

A collection of 20 FAQs on SQL SELECT query statements. Clear answers are provided
with tutorial exercises on simple SELECT query to select specific rows and returning
specific columns; applying group aggregation functions on selected rows; dividing
selected rows into multiple groups using GROUP BY and HAVING conditions; sorting
selected rows and groups.

What Is a SELECT Query Statement?

How To Create a Testing
Table with Test Data?

How To Select All
Columns of All Rows from a Table with a SELECT statement?

How To Select Some Specific
Columns from a Table in a Query?

How To Select Some Specific
Rows from a Table?

How To Add More Data to
the Testing Table?

How To Sort the Query
Output with ORDER BY Clauses?

Can the Query Output Be
Sorted by Multiple Columns?

How To Sort Query Output
in Descending Order?

How To Count Rows with the
COUNT(*) Function?

Can SELECT Statements Be Used on
Views?

How To Filter Out
Duplications in the Returning Rows?

What Are Group Functions in Query
Statements?

How To Use Group Functions
in the SELECT Clause?

Can Group
Functions Be Mixed with Non-group Selection Fields?

How To Divide
Query Output into Multiple Groups with the GROUP BY Clause?

How To Apply Filtering
Criteria at Group Level with The HAVING Clause?

How To Count Duplicated
Values in a Column?

Can Multiple Columns
Be Used in GROUP BY?

Can Group Functions
Be Used in the ORDER BY Clause?

More...

SQL Server FAQs - Introduction To
Transact-SQL Language Basics and Data Types

A collection of 19 FAQs on Transact-SQL language basics and data types. Clear answers
are provided with tutorial exercises on writing Transact-SQL statements; entering
comments; ending and running statement batches; GO and PRINT statements; exact and
approximate numeric data types; data and time, Unicode character strings and binary
strings.

What Is SQL Language?

What Is Transact-SQL Language?

What Is a Transact-SQL Statement?

How To Start and End
Transact-SQL Statements?

How To Enter Comments
in Transact-SQL Statements?

What Is a Transact-SQL Statement
Batch?

What Happens to a Statement
Batch If There Is a Compilation Error?

How To Use GO Command in
"sqlcmd"?

How To Create User Messages with PRINT
Statements?

How Many Categories of
Data Types Used by SQL Server?

What Are Exact Numeric
Data Types?

What Are Approximate
Numeric Data Types?

What Are Date and Time Data Types?

What Are Character String
Data Types?

What Are Unicode
Character String Data Types?

What Are Binary String
Data Types?

What Are the Differences between
CHAR and NCHAR?

What Are the Differences between
CHAR and VARCHAR?

What Are the Differences between
DECIMAL and FLOAT?

More...

SQL Server FAQs - Transact-SQL
Constants/Literals and Value Ranges

A collection of 18 FAQs on Transact-SQL language data constants or literals and
value ranges. Clear answers are provided with tutorial exercises on entering data
constants or literals; quoted character strings; date and time formats and ranges;
integer and exact number literals; floating number precisions and ranges.

What Is a Constant or Literal?

How To Write Character
String Constants or Literals?

What Is a Collation?

How To Specify
the Collation for a Character Data Type?

What Happens If Strings Are Casted
into Wrong Code Pages?

How To Find Out What Is the Default
Collation in a Database?

How Fixed Length Strings
Are Truncated and Padded?

How To Enter Unicode Character
String Literals?

How To Enter Binary String Literals?

How To Enter Date and Time Literals?

Why I Can Not Enter 0.001
Second in Date and Time Literals?

What Happens If Date-Only Values
Are Provided as Date and Time Literals?

What Happens If Time-Only Values
Are Provided as Date and Time Literals?

What Are Out-of-Range
Errors with Date and Time Literals?

What Happens If an Integer
Is Too Big for INT Date Type?

How Extra Digits
Are Handled with NUMERIC Data Type Literals?

How REAL and FLOAT
Literal Values Are Rounded?

What Are the Underflow
and Overflow Behaviors on FLOAT Literals?

More...

SQL Server FAQs - Transact-SQL
Numeric Expressions and Functions

A collection of 14 FAQs on Transact-SQL language numeric expressions and functions.
Clear answers are provided with tutorial exercises on writing numeric expressions;
arithmetic operations; implicit data type conversions; explicit data type conversions;
mathematical functions, random number generations.

What Is an Expression?

What Are Arithmetic Operators?

What Happens to an
Arithmetic Operation with Two Different Data Types?

How To Convert a Numeric
Expression from One Data Type to Another?

How To Convert
Numeric Expression Data Types by Assignment Operations?

How To Convert Numeric
Expression Data Types using the CAST() Function?

How To Convert Numeric
Expression Data Types using the CONVERT() Function?

How To Convert
Character Strings into Numeric Values?

What Happens
When Converting Big Values to Integers?

What Happens
When Converting Big Values to NUMERIC Data Types?

What Are the
Mathematical Functions Supported by SQL Server 2005?

How To Convert
Numeric Values to Integers?

How To Round a Numeric
Value To a Specific Precision?

How To Generate Random Numbers with
the RAND() Function?

More...

SQL Server FAQs -
Transact-SQL Managing Character Strings and Binary Strings

A collection of 14 FAQs on Transact-SQL language character string and binary string
operations. Clear answers are provided with tutorial exercises on concatenating
character/binary strings; converting Unicode character strings; converting binary
strings; inserting new line characters; locating and taking substrings; bitwise
operations.

How To Concatenate Two Character
Strings Together?

What Happens When Unicode
Strings Concatenate with Non-Unicode Strings?

How To Convert
a Unicode Strings to Non-Unicode Strings?

What Are
the Character String Functions Supported by SQL Server 2005?

How To Insert New Line
Characters into Strings?

How To Locate and
Take Substrings with CHARINDEX() and SUBSTRING() Functions?

How To Concatenate Two Binary Strings
Together?

Can Binary Strings
Be Used in Arithmetical Operations?

How To Convert Binary Strings
into Integers?

Can Binary Strings
Be Converted into NUMERIC or FLOAT Data Types?

Can Binary Strings
Be Converted into Character Strings?

Can Binary Strings
Be Converted into Unicode Character Strings?

How To Convert
Binary Strings into Hexadecimal Character Strings

What Are Bitwise Operations?

More...

SQL Server FAQs - Transact-SQL
Date and Time Operations and Functions

A collection of 18 FAQs on Transact-SQL language date and time operations and functions.
Clear answers are provided with tutorial exercises on converting date and time values
to integers or decimal values; incrementing and decrementing date parts; taking
date differences; formatting dates to strings; getting GMT times.

How To Add or Remove
Days on Date and Time Values?

Can Date and Time Values
Be Converted into Integers?

Can Integers Be Converted
into Date and Time Values?

Are DATETIME and NUMERIC Values
Convertible?

Can a DATETIME Value Be Subtracted from
Another DATETIME Value?

What Are the Date
and Time Functions Supported by SQL Server 2005?

How To Increment
or Decrement Parts of DATETIME Values?

How To Use DATEADD() Function?

How To Calculate
DATETIME Value Differences Using the DATEDIFF() Function?

How To Calculate Age in
Days, Hours and Minutes?

How To Get Month
and Weekday Names from DATETIME Values?

How To Get Parts of
DATETIME Values as Integers?

How To Get Year, Month
and Day Out of DATETIME Values?

What Is the Difference
Between GETDATE() and GETUTCDATE()?

How To Format Time Zone in
+/-hh:mm Format?

How To Format DATETIME
Values to Strings with the CONVERT() Function?

How To Truncate
DATETIME Values to Dates without Time?

How To Set Different
Parts of a DATETIME Value?

More...

SQL Server FAQs -
Transact-SQL Boolean Values and Logical Operations

A collection of 14 FAQs on Transact-SQL language Boolean values and logical operations.
Clear answers are provided with tutorial exercises on getting Boolean values with
comparison operations; CASE expressions; BETWEEN and IN comparisons; pattern match
with LIKE operator; EXISTS and IN subquery expressions; AND and OR logical operations.

What Is a Boolean Value?

What Are Conditional Expressions?

What Are Comparison Operations?

How To Perform Comparison
on Exact Numbers?

How To Perform Comparison
on Floating Point Numbers?

How To Perform Comparison
on Date and Time Values?

How To Perform Comparison
on Character Strings?

What To Test Value Ranges with the
BETWEEN Operator?

What To Test Value Lists with the
IN Operator?

What To Perform Pattern
Match with the LIKE Operator?

How To Use Wildcard
Characters in LIKE Operations?

How To Test Subquery Results with
the EXISTS Operator?

How To Test Values Returned
by a Subquery with the IN Operator?

What Are Logical/Boolean Operations?

More...

SQL Server FAQs - Transact-SQL
Conditional Statements and Loops

A collection of 5 FAQs on Transact-SQL language conditional statements and loops.
Clear answers are provided with tutorial exercises on IF ... ELSE statements; WHILE
loops; statement blocks; BREAK and CONTINUE statements.

How To Use "IF ... ELSE IF ...
ELSE ..." Statement Structures?

How To Use "BEGIN ... END" Statement
Structures?

How To Use WHILE Loops?

How To Stop a Loop Early with BREAK
Statements?

How To Skip
Remaining Statements in a Loop Block Using CONTINUE Statements?

More...

SQL Server FAQs
- Downloading and Installing Sample Scripts and Databases

A collection of 6 FAQs on download and installing SQL Server sample scripts and
sample databases. Clear answers are provided with tutorial exercises on downloading
and installing AdventureWorksLT; attaching AdventureWorksLT to SQL Server.

What Samples and
Sample Databases Are Provided by Microsoft?

How to download
and install SQL Server 2005 Sample Scripts?

How to download
and install the scaled-down database AdventureWorksLT?

How to attach
AdventureWorksLT physical files to the server?

How AdventureWorksLT tables
are related?

How to add an address
record into AdventureWorksLT?

More...

SQL Server FAQs - Understanding and
Managing Indexes

A collection of 23 FAQs on SQL Server database indexes. Clear answers are provided
with tutorial exercises on creating and dropping indexes; clustered and non-clustered
indexes; indexes created by primary key and unique constraints; index fragmentation
and rebuilding.

What Are Indexes?

How To Create an Index
on an Existing Table?

How To View Existing
Indexes on an Given Table using SP_HELP?

How To View
Existing Indexes on an Given Table using sys.indexes?

How To Drop Existing Indexes?

Is the PRIMARY KEY Column of
a Table an Index?

Does the UNIQUE Constraint
Create an Index?

What Is the
Difference Between Clustered and Non-Clustered Indexes?

How To Create a Clustered
Index?

How To Create an Index for
Multiple Columns?

How To Create
a Large Table with Random Data for Index Testing?

How To Measure Performance
of INSERT Statements?

Does Index Slows Down INSERT
Statements?

Does Index Speed Up SELECT Statements?

What Happens If You Add a New Index
to Large Table?

What Is the Impact on
Other User Sessions When Creating Indexes?

What Is Index Fragmentation?

What Causes Index Fragmentation?

How To Defragment Table Indexes?

How To Defragment Indexes
with ALTER INDEX ... REORGANIZE?

How To Rebuild Indexes with
ALTER INDEX ... REBUILD?

How To Rebuild All Indexes on
a Single Table?

How To Recreate an Existing
Index?

More...

SQL Server FAQs - Understanding and
Managing Views

A collection of 21 FAQs on SQL Server database views. Clear answers are provided
with tutorial exercises on creating and dropping views; defining and viewing view
columns; creating views from multiple tables; inserting, updating and deleting data
in underlying tables; creating schema binding view; creating indexes on views.

What Are Views?

How To Create a View
on an Existing Table?

How To See Existing Views?

How To Drop Existing Views from
a Database?

How To Get a List
of Columns in a View using "sys.columns"?

How To Get a List
of Columns in a View using the "sp_columns" Stored Procedure?

How To Get a List of
Columns in a View using the "sp_help" Stored Procedure?

How To Generate
CREATE VIEW Script on an Existing View?

How To Get the Definition
of a View Out of the SQL Server?

Can You Create a View
with Data from Multiple Tables?

Can You Create a View
using Data from Another View?

What Happens If You Delete
a Table That Is Used by a View?

Can You Use ORDER BY When Defining
a View?

How To Modify the Underlying
Query of an Existing View?

Can You Insert Data into a View?

Can You Update Data in a View?

Can You Delete Data from a View?

How To Assign New Column Names
in a View?

How Column Data Types Are
Determined in a View?

How To Bind a View
to the Schema of the Underlying Tables?

How To Create an Index on a View?

More...

Volume 2

SQL Server
FAQs - Transact-SQL Understanding Stored Procedures

A collection of 19 FAQs on SQL Server Transact-SQL stored procedures. Clear answers
are provided with tutorial exercises on creating and dropping stored procedures;
retrieving and modifying stored procedures; ending stored procedures properly; defining
and passing values through input and output parameters; temporary and permanent
stored procedures.

What Are Stored Procedures?

How To
Create a Simple Stored Procedure?

How To Execute
a Stored Procedure?

How
To List All Stored Procedures in the Current Database?

How
To Drop an Existing Stored Procedure?

How To Create a Stored Procedure with a Statement Block?

How To End a Stored
Procedure Properly?

How To Generate CREATE PROCEDURE Script on an Existing Stored Procedure?

How To Get the Definition of a Stored Procedure Back?

How To Modify an Existing Stored Procedure?

How To
Create Stored Procedures with Parameters?

How
To Provide Values to Stored Procedure Parameters?

What Are the
Advantages of Passing Name-Value Pairs as Parameters?

Can You Pass Expressions to Stored Procedure Parameters?

How To Provide Default Values to Stored Procedure Parameters?

How To Define Output Parameters in Stored Procedures?

How To Receive Output Values from Stored Procedures?

How To
Create a Local Temporary Stored Procedure?

Can Another
User Execute Your Local Temporary Stored Procedures?

More...

SQL
Server FAQs - Transact-SQL Understanding User_Defined_Functions

A collection of 17 FAQs on SQL Server Transact-SQL user defined functions. Clear
answers are provided with tutorial exercises on creating and dropping user defined
functions; defining and passing parameters into functions; providing and using parameter
default values; defining and using table-valued functions.

What Are User Defined
Functions?

What Are the Differences between User Defined Functions and Stored Procedures?

How
To Create a Simple User Defined Function?

How To
Use User Defined Functions in Expressions?

How
To List All User Defined Functions in the Current Database?

How To
Drop an Existing User Defined Function?

How To Generate CREATE FUNCTION Script on an Existing Function?

How To Get the Definition of a User Defined Function Back?

How To
Modify an Existing User Defined Function?

How
To Create User Defined Functions with Parameters?

How To Provide Values to User Defined Function Parameters?

Can You
Pass Expressions to Function Parameters?

How To Provide Default Values to Function Parameters?

How
Many Categories of Functions based Their Return Modes?

How Many
Ways to Create Table-Valued Functions?

How To Create an Inline Table-Valued Function?

How
To Create an Multi-Statement Table-Valued Function?

More...

SQL Server FAQs - Creating
and Managing Schemas

A collection of 11 FAQs on SQL Server on creating and managing schemas. Clear answers
are provided with tutorial exercises on listing all schemas and all objects in a
schema; transferring tables from one schema to another schema; changing ownership
of a schema.

What Is a Schema
in SQL Server 2005?

How To Create
a New Schema in a Database?

How
To List All Schemas in a Database?

How To Create
a New Table in a Given Schema?

How
To Transfer an Existing Table from One Schema to Another Schema?

How To List All Objects in a Given Schema?

What Is the
Default Schema of Your Login Session?

Who Is the Owner of
a Schema?

How To Change the Ownership of a Schema?

What Happens
If You Are Trying to Access a Schema Not Owned by You?

How To Drop
an Existing Schema?

More...

SQL Server FAQs - Introduction
to Triggers

A collection of 20 FAQs on creating and managing triggers. Clear explanations and
tutorial exercises are provided on creating DML and DDL triggers; altering, dropping,
enabling and disabling triggers; accessing inserted and deleted records; overriding
DML statements.

What Are Triggers?

What Are the Basic Features
of a Trigger?

How To Create a
Simple Table to Test Triggers?

How To Create
a DML Trigger using CREATE TRIGGER Statements?

How To Test a DML Trigger?

How To List All
Triggers in the Database with sys.triggers?

How To
Modify Existing Triggers using "ALTER TRIGGER"?

How To Delete
Existing Triggers using "DROP TRIGGER"?

How To Get the Definition of a Trigger Back?

How To Disable
Triggers using "DISABLE TRIGGER"?

How
To Create a Trigger for INSERT Only?

How To
See the Event List of an Existing Trigger using sys.trigger_events?

How
To Access the Inserted Record of an Event?

How To
Access the Deleted Record of an Event?

More...
SQL Server FAQs - Introduction to Cursors

A collection of 9 FAQs on working with cursors. Clear explanations and tutorial
exercises are provided on declaring cursor names and cursor variables; open and
close cursors; looping through cursor result set; backward scrolling; dynamic cursors.

More...

SQL Server FAQs - Introduction to NULL Values

A collection of 11 FAQs on working with NULL values. Clear explanations and tutorial
exercises are provided on assigning NULL values to columns, variables and parameters;
evaluating expressions with NULL values; testing NULL values; ISNULL() and NULLIF()
functions.

SQL Server FAQs -
Security - Server Login and Database User

A collection of 17 FAQs on SQL Server logins and users. Clear answers are provided
with tutorial exercises on security model; security principals, securables and permissions;
creating, changing and dropping login names; linking users with logins.

Reporting Services Analyst InfoCenter

2009-09-09T03:17:00.000-07:00

Reporting Services Analyst InfoCenter
Analysts create reports and data models for personal use and for other people in
an organization. Analysts can be data processing professionals, but might also be
persons responsible for analyzing enterprise data in the course of other job functions.
Often, an analyst creates reports to identify trends, opportunities, problems, or
project characteristics. Analysts might be interested in data mining and the features
of SQL Server Analysis Services.
In Reporting Services, analysts are responsible for creating reports that include
aggregated data, expressions, and parameters. Analysts also design report models
for others in an organization to organize data into meaningful entities that facilitate
report creation.
Job titles that map to the Reporting Services analyst role include
Data Modeler, Business Analyst, or Financial Analyst, to name a few.
To help you quickly get started with the Reporting Services tools and documentation,
the following topics provide key information about creating and publishing reports
and report models. To view additional information in the documentation, from these topics
you can sync to the Table of Contents in SQL Server Books Online, where you will
find related topics on advanced features in SQL Server 2005 Reporting Services.

Concepts

Introducing Reporting Services
Reports and Report Definitions
Report Models
Ways to Create a Report
Working with Data in Reporting Services
Understanding Data Regions
Working with Report Items
Understanding Report Layout and Rendering

Defining Report Data

Connecting to a Data Source
Defining Report Datasets for a SQL Server Relational Database
Working with Parameters in Reporting Services
Filtering Data in a Report
Using Expressions in Reporting Services

Designing and Publishing Reports
Designing the Report Layout
Adding Interactive Features
Previewing Reports
Publishing Reports to a Production Environment

Designing Report Models
Working with Model Designer
Creating a Report Model Project

Reporting Services Administrator InfoCenter

2009-09-09T03:03:00.000-07:00

Reporting Services Administrator InfoCenter
A report server administrator installs, configures, secures, and maintains a report
server deployment. A report server administrator determines report access and availability
by specifying role assignments, report execution and data source property settings,
and determining the availability of optional features. The administrator may also
be called upon to create a disaster recovery plan, troubleshoot problems, and monitor
and optimize report server performance. A report server administrator must also
understand the network topology and configuration of the computers used to host
and access SQL Server 2005 Reporting Services components.

Job titles that map to the report server administrator role include Database Administrator,
Report Server Administrator (Corporate IT), Security Expert, or Lead Database Administrator.

To help you be as effective as possible, the following links jump to those topics
considered most useful for the maintenance and optimization of a Reporting Services
installation. To view additional information in the documentation, from these topics
you can sync to the Table of Contents in SQL Server Books Online, where you will
find related topics on advanced features in Reporting Services.

Concepts

Report Server
Reports and Report Definitions
Linked Reports
Report Snapshots
Report Server Folder Hierarchy
Connections and Accounts in a Reporting Services Deployment

Configuration
Server Deployment Checklist
Report Deployment Checklist

Configuring Reporting Services Components
Configuring a Report Server for Internet Access

Security
Securing Reporting Services
Managing Permissions and Security for Reporting Services

Server Administration
Configuring Service Accounts and Passwords in Reporting
Services
Administering a Report Server Database
Managing and Working With Published Reports

Reporting Services Developer InfoCenter

2009-09-09T02:22:00.000-07:00

SQL Server 2005 Reporting Services (SSRS) provides the developer several programming interfaces that can be used to extend the functionality of Reporting Services। These interfaces include Web services for managing and executing reports, a WMI provider interface for configuration management, a set of Microsoft ।NET Framework managed APIs for creating Reporting Services extensions, and URL access for easy integration with Web portals and applications.
The following lists provide links to a number of object models and APIs for use in programmatically extending the functionality of Reporting Services.

Concepts

Introducing Reporting Services Programming
Report Server Web Service
URL Access
Reporting Services Extensions
Report Definition Language

Web Service Programming
Introducing the Report Server Web Service
Accessing the SOAP API
Building Applications Using the Web Service and the .NET
Framework

URL Access Programming
Choosing Between URL Access and SOAP
Integrating Reporting Services Using URL Access

Programming Extensions
Security Considerations for Extensions
Introducing Data Processing Extensions
Introducing Delivery Extensions
Introducing Rendering Extensions
Introducing Reporting Services Security Extensions

Administrative Programming
Introducing the Reporting Services WMI Provider
Reporting Services WMI Provider Library

Report Programming
Generating Report Definition Language Programmatically
Using Custom Assemblies with Reports

SQL Server Reporting Services FAQ

2009-09-09T01:42:00.000-07:00

Question 1: How to create a custom report template in Reporting Services?
Answer: A report template also exists as a normal report (RDL file). If you want to customize your own report templates, you can put the previously prepared reports (which will be used as new templates) in a specific folder. By default, the path is:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Then, to use these templates, you can right click on the Reports folder in your project and select New Item. You will then see the name of the report templates you added in the above folder

Question 2: Does SQL Server 2005 Reporting Services support horizontal tables which have fixed rows and dynamic columns?

Answer: Note: This Q&A is only for SQL Server 2005 Reporting Services.
By default, there is no native "horizontal table" report item. We can use Matrix to simulate simple horizontal tables:
1. Add a matrix to your report.
2. Add static rows to the matrix: Right-click in the Data cell and select "Add Row". Repeat for the number of fixed rows you want.
3. Drag fields into the Data cells:

You'll notice that the design tool automatically wraps your field reference in the "First" aggregate (e.g. =First(Fields!City.Value)). Since you're doing this in the context of a matrix, the design tool is ensuring that the expression is meaningful even in the context of a subtotal or if the matrix is showing aggregated data rather than detail data.
Note: Please do NOT remove the aggregate expression.
4. Add a column grouping:

1) Right-click on the column header and select "Edit Group".
2) Enter this for the group expression: =RowNumber(Nothing). This will cause the matrix to give you one column per row of data. Since horizontal tables can end up rather wide, you may want your table wrap around to the next "line" after a specific number of columns.

5. Add a list to your report and drag the table into it.
6. Group by a number of rows:
1) Right-click on the list and select Properties. Then click on Edit Details Group.
2) Enter this for the group expression: =Ceiling(RowNumber(Nothing)/3)
This will cause the list to group on every three rows. So you'll get a separate table for every three rows.

7. Adjust the group expression in the matrix:
Edit the column group expression in your matrix and change the RowNumber argument to be the list group name. For example: =RowNumber("list1_Details_Group")

More Information: SQL Server 2008 Reporting Services has a new Date Region control named Tablix, which can be used for the same requirement easily. For more information about Tablix, Please refer to the documents below:

Question 3: How to reset the page number back to 1 every time the report gets a group break?

Answer: Resetting the page number on group breaks is not natively supported. There is a workaround: you can track group breaks in a shared variable, and reset this variable to 1 when reaches a new group break.

Here are the steps:

1. Make sure there's a textbox in the report which contains the group expression.
2. Add shared variables to track the current group and page offset:

Shared offset as Integer
Shared currentgroup as Object

3. Add a custom function to set the shared variables and retrieve the group page number:

Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If Not (group = currentgroup)
offset = pagenumber - 1
currentgroup = group
End If
Return pagenumber - offset
End Function

4. Use the function in the page header or footer:

=Code.GetGroupPageNumber(ReportItems!Category.Value,Globals!PageNumber)

Question 4: How to open the drill- through report in a new browser window?

Answer: Generally, if you use the "Jump to URL" option for a textbox, it will redirect to the specific URL in the same browser when the user clicks on the textbox.

To open the link in a new window, you can use syntax similar to this:

="javascript:void window.open ('http://servername/reportserver?/freportfolder/ReportName&Parameter1=" & Fields!Field1.Value & "&Field2=" & Fields!Field2.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

Question 5: How to use multiple datasets?

Answer:You can combine two or more datasets in one report. There are multiple options to achieve this:

• If the data comes from different tables in the same database, join the datasets based on a common key present in both tables.

• Use the Linked Server functionality.

For more information, see Accessing External Data

• Use the OpenRowSet functionality to join data from another database server into the current query.

For more information, see OPENROWSET (Transact-SQL)

Question 6: How to upgrade report from SQL Server 2000 to SQL Server 2005?

Answer:You can use the following steps:

1. Back up ReportServer and ReportServerTemDB.
2. Back up encryption keys from Reporting services 2000 by using RsKeyMgmt.
3. Restore the databases backed up from step 1.
4. Connect to newly restored databases from step 3 from Reporting Services 2005 Configuration manager and database setup.
5. By using the provided credentials, Reporting Services will connect to new databases and perform the upgrade operations.
6. Confirm Reporting services upgrading the database and granting adequate rights to the credential account.
7. Restore backed up keys from step 2. Confirm that everything is displayed as green now.

Question 7: Is Report Builder available in SharePoint Integrated Mode?
Reporting Services and SharePoint Technology Integration
Answer:Yes. After integrating with SharePoint, what you need to do is to add Report Builder Model through Content Types:

1. Open SharePoint website as administrator.
2. Click Report Center website.
3. Click Site Actions list.
4. Select Site Settings and then select Modify Pages Library Settings.
5. On Content Types part, click Add from existing site content types.
6. Select Report Server Content Types from site content types drop-down list.
7. Select Report Builder.
8. Click OK to finish.

After that, you will be able to use Report Builder.

Please Note: Currently, Report Builder 2.0 has been released as a stand-alone installation for SQL Server 2008 Reporting Service. However, Report Builder 1.0 can still be used for SharePoint as a "click-once" application. This function will be included in the future service pack of SQL Server 2008.

Question8: How to manage user to view reports in SharePoint integrated mode?
Answer:In SharePoint website, if the default security settings do not meet your requirements, you can create new permission levels for the specific report server items or operations. Custom security settings are helpful if restricting access to a particular report is required.

To customize permissions of viewing reports, please refer to the following documents:

Question 9: How to refer the reports on the Report Server in SharePoint Services?
Answer:After integrating SQL Server Reporting Services with SharePoint Services, you can refer a SSRS report in SharePoint Services. Here is the steps:

1. Install the SQL Server Reporting Services Add-in for SharePoint

For more information, see How to: Install or Uninstall the Reporting Services Add-in

2. Refer to the documents below for using SQL Server Reporting Services Add-in:
Using the Report Viewer Web Part on a SharePoint Site

Question 10: How to integrate SQL Server Reporting Services and SharePoint Services?
Answer:1. Check the requirements for running reporting service in SharePoint integrated mode:
Requirements for Running Reporting Services in SharePoint Integrated Mode
2. Follow the documents below to configure the server:
Configuring Reporting Services for SharePoint 3.0 Integration
For more information for Reporting Services and SharePoint Technology Integration, please refer to:
Reporting Services and SharePoint Technology Integration

Question 11: How to deploy reports to SQL Server Reporting Services in SharePoint Services integrated mode?
Answer:You can use the following steps:
1. Create a web application in SharePoint with Report Center template.
2. Open Business Intelligence Development Studio and modify report solution properties as below:

TargetDataSourceFolder

Question 12: How to enable the Select All option for a multi-value parameter?

Problem:

When a user defines an available values list for a multi-value parameter, a Select ALL option on the report parameter drop-down list automatically appears. However, if you don’t want to make the parameter multi-value, how to enable the Select All option for the query parameter?

Solution:

Since the single-value query parameter does not provide the Select All option, you cannot query the data without any limitations. Sometimes, the end user may want to ignore the condition for the parameterized query. In this case, you can add the "Select All" option to the parameter dataset by using SQL statement.

Here are the detailed steps:

1. Create a dataset for the parameter using the following query string:

SELECT ‘ALL’
UNION ALL
SELECT Field from table

2. Edit your base dataset query:

The original query:
… AND Field=@Parameter…
Change it to this:
…AND (( Field=@Parameter ))OR(@Parameter=’ALL’))…

3. Set the report parameter.

1) Set the available values as the field created in Step 1.
2) Set the default value of parameter to All.

Question 13: How to improve PDF quality of the report exported in Reporting Services 2005?

Answer: The PDF renderer in Reporting Services 2005 resizes all images that it is given at 96 DPI no matter what DPI the image is when you pass it to the renderer. That means that a 300 DPI image or even a 600 DPI image will be sized in the PDF as if it is only 96 DPI. In other words, your high DPI image may render larger than expected.

Despite sizing the images as if they were 96 DPI, the PDF renderer appears to render higher DPI images at a higher quality than 96 DPI. Even if the sizing being wrong, the image actually is rendering at a higher quality.

A necessary workaround is to size the image to the proper number of inches based on 96 DPI calculations. Then use Bitmap.SetResolution to set the images to at least 300 DPI. That may provide a higher quality image that is the proper number of pixels to fit properly in the report.

Question 14: How to enable anonymous access to Report Server, and is there any security consideration?
Answer: Please note that when using anonymous access, every person accesses the reports under the anonymous user context, and no one will have rights to administer Reporting Services through Report Manager. This is the reason why it is not recommended to use anonymous access in Reporting Services.

In order to differentiate between users, we need to either disable anonymous access or use a custom security extension. We have a sample using Forms Authentication:
Using Forms Authentication in Reporting Services

In some special situations, it may be necessary to use anonymous access. For security considerations, we can use the steps as below:

1. Add the anonymous user to the System User role
2. Create an Item-level role with all of the tasks you want this user to be able to use, and then assign the anonymous user to that role at the root level.
3. Check permissions for the anonymous user on folders that SRS uses:
For example:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services (and subfolders)
C:\\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files
The temp folder for the anonymous user

Note: Step1 & Step2 can be accomplished through Report Manager only if anonymous access is disabled and the logged-on user is a member of the System Administrator role.

Question 15: Error of EXECUTE permission denied on object 'xp_sqlagent_notify'.

Problem:
In some special cases, you may receive the following error when trying to select the option execution from a report:

EXECUTE permission denied on object 'xp_sqlagent_notify', database '######', schema '###'.

Cause:

Usually, this error is caused by the user missing the "RSExecRole" role in the Master and MSDB system databases.

Solution:

Reporting Services uses a predefined database role called "RSExecRole" to grant report server permissions to the report server database. If you get the error mentioned above, try to create and provision the "RSExecRole" role in Master and MSDB system database.

For more information, see:

How to: Create the RSExecRole
http://technet.microsoft.com/en-us/library/cc281308.aspx

Question 16: How to combine connecting string via parameter?
Answer:You can include parameter reference in the data source connection string and allow the user to choose a data source for the report. For example, with an expression-based connection string, a user who is running a sales report can select a data source for a particular county before running the report.
The following example illustrates the use of a data source expression in a SQL Server connection string. The example assumes you have created a report parameter named ServerName:
="Data Source=" & Parameters! ServerName.Value & "; initial catalog=AdventureWorks"

Question 17: How to migrate SQL Server 2008 Reporting Services to another computer?

Answer: The migration process for Reporting Services includes manual and automated steps. The following tasks are part of a report server migration:

1. Back up database, application, and configuration files.
2. Back up the encryption key.
3. Install a new instance of SQL Server 2008. If you are using the same hardware, you can install SQL Server 2008 side-by-side your existing SQL Server 2000 or 2005 installation. Be aware that if you do this, you might need to install SQL Server 2008 as a named instance.
4. Move the report server database and other application files from your SQL Server 2000 or 2005 installation to your new SQL Server 2008 installation.
5. Move any custom application files to the new installation.
6. Configure the report server.
7. Edit RSReportServer.config to include any custom settings from your previous installation.
8. Optionally, configure custom Access Control Lists (ACLs) for the new Reporting Services Windows service group.
9. Test your installation.
10. Remove unused applications and tools after you have confirmed that the new instance is fully operational.

If you are reusing an existing report server database that you created in a previous installation, be aware that there are restrictions on the editions of SQL Server you use to host the report server database.

Report Server In SSRS

2009-07-29T06:05:00.000-07:00

The report server is the central component of a Reporting Services installation. It consists of a pair of core processors plus a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations. Processors are the hub of the report server. The processors support the integrity of the reporting system and cannot be modified or extended. Extensions are also processors, but they perform very specific functions. Reporting Services includes one or more default extensions for every type of extension that is supported. You can add custom extensions to a report server. Doing so allows you to extend a report server to support features that are not supported out of the box; examples of custom functionality might include support for single sign-on technologies, report output in application formats that are not already handled by the default rendering extensions, and report delivery to a printer or application.

A single report server instance is defined by the complete collection of processors and extensions that provide end-to-end processing, from the handling of the initial request to the presentation of a finished report. Through its subcomponents, the report server processes report requests and makes reports available for on-demand access or scheduled distribution.

The following illustration shows a report server instance as it is installed by Setup. It includes the core processors and default extensions shown in the diagram:

The report server is implemented as a Microsoft Windows service, called the "Report Server service", that hosts a Web service, background processing, and other operations. In the Services console application, the service is listed as SQL Server Reporting Services (MSSQLSERVER). For more information about service implementation, see Service Architecture (Reporting Services).

Third-party developers can create additional extensions to replace or extend the processing capability of the report server. To learn more about the programmatic interfaces available to application developers, see the Developer's Guide and Technical Reference.

Report Processor and Scheduling and Delivery Processor
The report server includes two processors that perform preliminary and intermediate report processing, and scheduled and delivery operations. The Report Processor retrieves the report definition or model, combines layout information with data from the data processing extension, and renders it in the requested format. The Scheduling and Delivery Processor processes reports triggered from a schedule, and delivers reports to target destinations. For more information about each processor, see Report Processor and Scheduling and Delivery Processor.

Report Server Database The report server is a stateless server that stores all properties, objects, and metadata in a SQL Server database. Stored data includes published reports, report models, and the folder hierarchy that provides the addressing for all items managed by the report server. A report server database can provide internal storage for a single Reporting Services installation or for multiple report servers that are part of a scale-out deployment. If you configure a report server to run within a larger deployment of a SharePoint product or technology, the report server uses the SharePoint databases in addition to the report server database. For more information about data stores used in Reporting Services installation, see Report Server Database and Storing and Synchronizing Report Server Content With SharePoint Databases.

Authentication, Rendering, Data, and Delivery Extensions The report server supports the following types of extensions: authentication extensions, data processing extensions, report processing extensions, rendering extensions, and delivery extensions. A report server requires at least one authentication extension, data processing extension, and rendering extension. Delivery and custom report processing extensions are optional, but necessary if you want to support report distribution or custom controls.

Reporting Services provides default extensions so that you can use all of the server features without having to develop custom components. The following table describes the default extensions that contribute to a complete report server instance that provides ready-to-use functionality:

Type	Default
Authentication	A default report server instance supports Windows Authentication, including impersonation and delegation features if they are enabled in your domain.
Data processing	A default report server instance includes data processing extensions for SQL Server, Analysis Services, Oracle, Hyperion Essbase, SAPBW, OLE DB, and ODBC data sources.
Rendering	A default report server instance includes rendering extensions for HTML, Excel, CSV, XML, Image, Word, and PDF.
Delivery	A default report server instance includes an e-mail delivery extension and a file share delivery extension. If the report server is configured for SharePoint integration, you can use a delivery extension that place report deliveries in a SharePoint library.

Report Manager In SSRS

2009-07-29T05:59:00.000-07:00

Report Manager is a Web-based report access and management tool that you use to administer a single report server instance from a remote location over an HTTP connection. You can also use Report Manager for its report viewer and navigation features. You can use Report Manager to perform the following tasks:

View, search, print, and subscribe to reports.
Create, secure, and maintain the folder hierarchy to organize items on the server.
Configure role-based security that determines access to items and operations.
Configure report execution properties, report history, and report parameters.
Create report models that connect to and retrieve data from a Microsoft SQL Server Analysis Services data source or from a SQL Server relational data source.
Set model item security to allow access to specific entities in the model, or map entities to predefined clickthrough reports that you create in advance.
Create shared schedules and shared data sources to make schedules and data source connections more manageable.
Create data-driven subscriptions that roll out reports to a large recipient list.
Create linked reports to reuse and repurpose an existing report in different ways.
Launch Report Builder 1.0 to create ad hoc reports that you can save and run on the report server. For more information, see How to: Start Report Builder.
You can use Report Manager to browse the report server folders or search for specific reports। You can view a report, its general properties, and past copies of the report that are captured in report history। Depending on your permissions, you might also be able to subscribe to reports for delivery to an e-mail inbox or a shared folder on the file system.

To use Report Manager, you must have Microsoft Internet Explorer 6.0 or later.

Report Manager is used only for a report server that runs in native mode. It is not supported for a report server that you configure for SharePoint integrated mode.

Some Report Manager features are not available in SQL Server Express with Advanced Services. For more information, see Features Supported by Reporting Services in SQL Server Express.

Starting and Using Report Manager
Report Manager is a Web application that you open by typing the Report Manager URL in the address bar of a browser window. When you start Report Manager, the pages, links, and options that you see will vary based on the permissions you have on the report server. To perform a task, you must be assigned to a role that includes the task. A user who is assigned to a role that has full permissions has access to the complete set of application menus and pages available for managing a report server. A user assigned to a role that has permissions to view and run reports sees only the menus and pages that support those activities. Each user can have different role assignments for different report servers, or even for the various reports and folders that are stored on a single report server.

The following illustration shows Report Manager as it appears to a user who has Content Manager permissions on the report server. A user who has less permission will see fewer links and options on the Home page.

For more information about roles, see Granting Permissions on a Native Mode Report Server. For instructions on how to start Report Manager, see How to: Start Report Manager.


If you are using Windows Vista or Windows Server 2008, you must configure the report server for local administration before you can use Report Manager to manage a local report server instance. For instructions on how to configure the server, see How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008.

Configuring Report Manager
Report Manager configuration consists of defining a URL for the application. Additional configuration is required if your deployment includes running Report Manager on a separate computer.

You can customize Report Manager in very limited ways. For example, you can modify the application title on the Site Settings page. If you are a Web developer, you can modify the style sheets that contain the style information used by Report Manager. Because Report Manager is not specifically designed to support customization, you must thoroughly test any modification that you make. If you find that Report Manager does not meet your needs, you can develop a custom report viewer or configure SharePoint Web parts to find and view reports in a SharePoint site.

Reporting Services Components and Tools

2009-07-29T05:52:00.000-07:00

SQL Server Reporting Services is a set of processing components, tools, and programmatic interfaces that support the development and use of rich reports in a managed environment. The tool set includes development tools, configuration and administration tools, and report viewing tools.

Processing is distributed across multiple components. Central and specialized processors are used to retrieve data, process report layout, render presentation formats, and deliver to target destinations. Presentation processing occurs after the data is retrieved and is decoupled from data processing, allowing multiple users to review the same report simultaneously in formats designed for different devices or quickly change the viewing format of the report, from HTML to PDF or Microsoft Excel or XML, with a single click. The modular architecture is designed for extensibility. Developers can include reporting functionality in custom applications or extend reporting functionality to support custom features.

Programmatic interfaces for easy integration with new or existing applications and portals include:

Simple Object Access Protocol (SOAP)
URL endpoints
Windows Management Instrumentation (WMI)

In This Section

Report Server Describes the Report Server component, which provides data and report processing, and report delivery. The Report Server component includes several subcomponents that perform specific functions. This topic introduces each component of Report Server.

Report Designer Report Designer is a comprehensive report design tool that is appropriate for both administrators and developers. Report Designer runs in the Business Intelligence Development Studio, which is fully integrated with Microsoft Visual Studio 2005 or later, or can be run as a separate application. Report Designer provides several query designers specific to various supported data source types. Report Designer supports advanced reporting functionality such as free-form reports and custom report items.There is also a Report Wizard associated with Report Designer. The Report Wizard steps you through the report design process to produce a simple tabular or chart report, which can be opened and enhanced in Report Designer or used as is.
Report Builder 2.0 Report Builder 2.0, the new version of Report Builder, features a Microsoft Office-like authoring environment. It offers many new features such as wizards for creating new tables, matrices, and charts, enhanced data layout, data visualization, richly formatted text, and on-demand rendering. You can customize and update all existing reports, regardless of where they were initially designed.
Report Builder 1.0 Report Builder 1.0 allows information workers to explore business information in a report model without having to understand the underlying data source structures and create ad hoc reports for personal or organizational use. Report Builder 1.0 is fully integrated with Reporting Services. Ad hoc reports can be revised, managed, and secured by using the other Reporting Services tools. An unique feature of Report Builder is clickthrough functionality that enables people to explore the business data that is available to them via ad hoc reports.
Model Designer Model Designer is used to generate and customize report models of business data for use with Report Builder or Report Designer.
Report Manager Report Manager is a Web-based report access and management tool that you access through Microsoft Internet Explorer 6.0 or later. Information workers as well as content managers use Report Manager to find, run, and subscribe to published reports. Content managers use Report Manager to manage reports, data sources, and resources; configure report processing options and security
Reporting Services Configuration Tool The Reporting Services Configuration tool is used to configure a Reporting Services installation. Reporting Services can be reconfigured after installation to support changing business requirements and scenarios.
Report Server Command Prompt Utilities: SQL Server Reporting Services includes three command line utilities that you can use to administer a report server. These utilities are installed automatically when you install a report server.
Reporting Services in Business Intelligence Development Studio: SQL Server Business Intelligence Development Studio is a Visual Studio shell that hosts Report Designer and Model Designer.
SQL Server Tools for Report Server Administration: SQL Server also provides tools that administrators can use to manage a Reporting Services installation.

Reporting Services Scenarios

2009-07-29T05:39:00.000-07:00

Reporting Services is an enterprise reporting solution that meets a wide range of implementation scenarios. You can use the tools and applications included in Reporting Services to create, manage, and distribute reports. Out-of-the-box tools and applications are available for expert report designers as well as information workers who need an easy way to create their own reports and explore business data. If you are developer, you can integrate Reporting Services functionality within a custom application, or extend it to support the type of functionality you require.

End-to-End Report Processing and Management with Reporting Services Tools and Applications
The tools and applications in Reporting Services provide a full spectrum of features to support enterprise reporting requirements. Reporting Services provides out-of-the-box support for reporting against a number of commonly used data source types, and reports can be rendered and distributed in a variety of popular formats, such as Microsoft Office Word and Excel or Adobe PDF. Although report definitions can be revised programmatically, no programming experience is necessary to use Reporting Services tools and applications.

Report server administrators and content managers can use SQL Server Management Studio, Report Manager, and the Reporting Services Configuration tool to deploy and manage report server content, and they can use Model Designer to create report models of business data for use as report data sources.
Report authors can use Report Designer or Report Builder to create reports that visualize business data in traditional and interactive formats.
Business users can use Report Manager, SharePoint Web Parts, or a Web browser to view reports on demand, or subscribe to reports that are delivered through e-mail.
For more information about the tools available, see Reporting Services Features, Reporting Services Components and Tools, and Reporting Services in Business Intelligence Development Studio. For more information about developing and managing enterprise reporting solutions, see Designing and Implementing Reports and Administration.

SharePoint Integration

You can configure a report server to run within a deployment of a SharePoint product or technology and use the collaboration and centralized document management features of Windows SharePoint Services 3.0 or Office SharePoint Server 2007 with Reporting Services. For more information, see Reporting Services and SharePoint Technology Integration.

Ad hoc Reporting with Report Builder and Model-driven Data Exploration
Information workers need an easy-to-use tool to access and report on business data on an ad hoc basis. Reporting Services includes Report Builder and Model Designer, which simplify the process of creating ad hoc reports by providing report templates and pre-generated models of business data. Report models are stored and secured on a report server as data sources that can be used by all the Reporting Services authoring tools. Reports created in Report Builder can also be saved to a report server, where they can be managed and distributed like any other Reporting Services report.

Report Builder reports also provide clickthrough functionality, which enables users to click data in the report and see related data in the report model on-the-fly. You can attach customized reports to the report model that pops up when the data field is clicked. For more information about ad hoc reporting, see Designing and Implementing Reports Using Report Builder 1.0. For more information about the Report Builder or Model Designer used in this scenario, see Reporting Services Components and Tools.

Development Scenarios for Corporate and Third-Party Developers
Reporting Services supports a variety of scenarios for developers who want to add reporting features to corporate applications or third-party commercial software.

Application Integration

Developers can use Reporting Services to include reporting features in their applications. For some applications, the addition of reports completes a feature set by providing a way to present data that the application tracks, creates, or monitors. Use Report Designer to create reports based on either a data source provided in your application or one that is publicly available. Use the APIs to define access and add support for any built-in report server features. For more information, see the Reporting Services Developer's Guide.

Alternatively, if your application does not require all of the features provided in a report server, the ReportViewer controls can be included in Microsoft Visual Studio 2005. In contrast with Reporting Services, the ReportViewer controls are distributable with an application.

Visual Studio ReportViewer Web Server and Windows Forms Controls for Embedding Reports in Custom Applications

Visual Studio provides Reporting Services functionality through a ReportViewer control that can be embedded in other applications and through a version of the Report Designer and Report Wizard that is included with Visual Studio. The control embeds report processing and viewing capabilities directly into your application. For more information about the Visual Studio Report Designer and ReportViewer controls, see Reporting Services and ReportViewer Controls in Visual Studio.

Redistribution Through SQL Server Express with Advanced Services

You can include SQL Server Express in custom applications to provide an internal database engine to store and manage application data. If you want to redistribute reporting functionality in a custom application, SQL Server Express with Advanced Services includes a subset of Reporting Services features that you can use or distribute with your application. SQL Server Express is a free Web download that can be used with a separate Web download called SQL Server Express Toolkit. The SQL Server Express Toolkit provides a report authoring tool. You can use SQL Server Express with the ReportViewer controls. There are some limitations with regard to the report server SOAP endpoints. For more information, see Reporting Services in SQL Server Express with Advanced Services.

Hosting Reports in Custom Dashboards and SharePoint Sites

Because reports can accommodate and present data from a variety of sources, many organizations use the interactive reporting features of Reporting Services to distribute data in tabular, chart, and gauge formats via portal applications. Reports can be hosted in a portal page or created so that they mimic a dashboard-style Web application by embedding multiple reports, charts, gauges, and data-driven images into a single free-form report layout. Whenever tabular or graphic data needs to be incorporated into a line-of-business application, consider adding a Reporting Services report instead. For more information, see Nesting Data Regions.

Building Custom Report Design and Report Management Tools

The tools and applications included with Reporting Services are based on programmatic interfaces that are available to all users. This means that you can extend or replace the applications and tools included in Reporting Services with a custom tool set. For example, if you want an alternative to the Business Intelligence Development Studio authoring environment used for Report Designer, you can develop a custom report authoring tool to replace it. To build a custom Web portal or report management tool, review the API to learn about the report server management functions that you must support. Reporting Services includes a Windows Management Instrumentation (WMI) provider that you can use to develop Windows-based tools used for server administration. For more information, see the Reporting Services Developer's Guide.

Extending Reporting Services Functionality

Reporting Services is designed for extensibility. You can create custom extensions to support additional kinds of data sources, delivery approaches, security models, and report items. When creating custom extensions, the degree of difficulty can vary considerably depending on the kind of extension you are creating and the functionality you need it to support. Data processing extensions are typically the most straightforward to create, while rendering extensions can be very difficult if you are supporting the entire report schema.

Reporting Services Features

2009-07-29T05:31:00.000-07:00

Relational, multidimensional, XML, and custom data sources Build reports using data from any data source type that has a Microsoft .NET Framework-managed data provider, OLE DB provider, or ODBC data source. You can create reports that use relational and multidimensional data from SQL Server and Analysis Services, Oracle, Hyperion, and other databases. You can use an XML data processing extension to retrieve data from any XML data source. You can use table-valued functions to design custom data sources.
Tabular, matrix, chart, and free-form report layouts Create tabular reports for column-based data, matrix reports for summarized data, chart reports for graphical data, and free-form reports for everything else. A new data region introduced in SQL Server 2008 enables you to create reports that combine table and matrix layouts. Reports can embed other reports and charts, along with lists, graphics, and controls for dynamic Web-based applications.
Ad hoc reports Create and save reports on the fly and save them locally or publish them directly to a report server or SharePoint site, where they can be managed like other reports. Ad hoc reporting is supported through report models and report templates using a ClickOnce application called Report Builder.
Drillthrough reports and interactivity Add interactive features by providing links to subreports and drillthrough reports. Use parameters to filter data for customized views. Model-based reports provide infinite clickthrough functionality so users can explore the data that is available to them.
Presentation formats Choose a presentation format when you open the report, or after you open the report. You can choose Web-oriented, page-oriented, and desktop application formats. Formats include HTML, MHTML, PDF, XML, CSV, TIFF, Word, and Excel.
Custom controls and report items You can embed custom controls or report items that you create or purchase from a third-party vendor. A custom control requires a custom report processing extension. You can add scripted expressions in Microsoft Visual Basic.
Navigation You can add bookmarks and document maps to provide navigation options within a large report.
Aggregations You can aggregate and summarize data using controls and expressions.
Graphical elements You can embed or reference images and other resources, including external content.

Report and Model Design Tools

Report Designer Create reports in a full-featured report authoring application। Report Designer is hosted within Business Intelligence Development Studio. With Report Designer, you can create complex reports based on a number of different data source types, define simple to complex layouts, add chart and gauge data presentations, include interactive features like drillthrough reports and document maps, preview reports, and save reports to your local computer or publish them to a test or production report server. Report Designer includes query builders and an expression editor.
Model Designer Create models that support ad hoc reporting in Reporting Services. You can generate models automatically based on an existing schema or view, refine the model, and then publish it to a report server.

Report Builder 2.0 Create reports in a Microsoft Office-like authoring environment in the new version of Report Builder. Take advantage of new features such as enhanced data layout, data visualization, richly formatted text, and on-demand rendering. Create reports with multiple data regions, pull data from multiple data sources into a single report, and directly query relational and multidimensional data sources. Customize and update all existing reports, regardless of whether they were designed in Report Designer or in Report Builder 1.0.
Report Builder 1.0 Create ad hoc reports using templates and pre-organized data and save them directly to a report server. Reports that you create in Report Builder 1.0 can be accessed, distributed, and managed just as you would any other report.

Deployment and Administration

Server-based platform Centralize report storage and management, provide secure access to reports and folders, control how reports are processed and distributed, and standardize how reports are used in your business.
Integration with a deployment of a SharePoint product or technology You can configure a report server to run in SharePoint integrated mode, providing access to report server content and operations from within a SharePoint site or farm. For more information, see Reporting Services and SharePoint Technology Integration.
Reporting Services Configuration Deploy and maintain an existing report server using a graphical user interface to configure service accounts, virtual directories, report server databases, encryption keys, and scale-out deployment.
Report Manager Configure role-based security and manage report server content by setting properties on data sources, reports, folders, resources, and report models. You can configure report execution, report history, and set limits on processing time, monitor and cancel pending or in-process reports, and create and manage data source connections and schedules independently of the reports that they are associated with.
Integration with SQL Server Management Studio and SQL Server Configuration Manager Report server administrators can use the tools provided in SQL Server to manage a Reporting Services installation.
Command-line utilities Command-line utilities support report server configuration, key management, and scripted operations.
Role-based security Use role-based security to control access to folders, reports, and resources. Security settings follow an inheritance pattern through the folder structure. You can vary security at any branch to redefine user access down to the item level.

Report Access and Delivery Features

On-demand access over Web connections Use a browser to navigate a folder hierarchy to find and work with reports and other items. You can reference reports from a Favorites list in a Web browser, or link from a Web portal.
SharePoint Web parts Reporting Services supports different levels of integration. If you deploy a report server in SharePoint integrated mode, you can access reports from libraries on a SharePoint site. If you are using a native mode report server, you can embed Web parts to view reports and navigate the report server folder hierarchy.
My Reports and My Subscriptions In Report Manager, you can store and manage reports and subscriptions in a personal workspace.
Subscriptions for E-mail or File share Delivery Automate report delivery through a standard subscription and set report presentation preferences. Users who prefer to view a report in Microsoft Excel, for example, can specify that format in a subscription. Deliver a rendered report to an e-mail inbox. Set delivery options that control whether the report is delivered as a link or attachment. Deliver a rendered report to a shared folder. Set delivery options that control whether the report is overwritten or added to an existing folder.
Data-driven subscriptions Automate report distribution through data-driven subscriptions, which generate a recipient list and delivery instructions at run time from an external data source. Use a query and column-mapping information to customize report output for a large number of users.

Programmability and Extensibility

Report Definition Language (RDL) RDL describes all possible elements of a report using an XML grammar that is validated by an XML schema. The report definition of an individual report is based on RDL and contains instructions for rendering the design at run time. RDL is extensible. You can add support for elements or features that are not present in the existing RDL schema, and then build custom tools and report rendering extensions to handle the features you create.
SOAP API Use Report Server Web service methods to access a report server and Report Server Web service programmatically.
URL access You can access report server items through parameterized URL strings. All reports and items stored in a report server are addressable through the report server namespace.
WMI provider Reporting Services includes a Windows Management Instrumentation (WMI) provider that you can use to manage the Report Server Windows service.
Extensible delivery, data processing, rendering, security, and report processing You can create custom delivery extensions to route reports to file shares, internal archive stores, or internal applications. You can extend data processing to query, convert, or transform data from new data source types. You can create custom rendering extensions to support report presentation in application formats or Web-oriented formats that are not provided with the product. You can build or integrate a security extension that provides an alternative to the Windows Authentication model.

Understanding Groups (Reporting Services)

2009-07-29T04:55:00.000-07:00

In Report Designer, a group is a named set of data from the report dataset that is bound to a data region. Basically, a group organizes a view of a report dataset. All groups in a data region specify different views of the same report dataset.

To help visualize what a group is, refer to the following figure that shows the Tablix data region in Preview. In this figure, the row groups categorize the dataset by product type and the column groups categorize the dataset by geographic region and year.

The following sections help describe the various aspects of groups.

What Makes a Group?
A group has a name and a set of group expressions that you specify. The set of group expressions can be a single dataset field reference or a combination of multiple expressions. At runtime, Report Designer combines and applies group expressions to data in a group. For example, you have a group that uses a data field to organize the data in the data region. At runtime, Report Designer first organizes the data by date, and then displays and totals other dataset values for each date.

When Do I Create Groups?
In most cases, Report Designer automatically creates a group for you when you design a data region. For a table, matrix, or list, groups are created when you drop fields on the Grouping pane. For a chart, groups are created when you drop fields on the chart drop-zones. For a gauge, you must use the gauge properties dialog box. For a table, matrix, or list, you can also create a group manually. For more information, see see How to: Add or Delete a Group in a Data Region (Reporting Services). For an example of how to add groups when you create a report, see Tutorial: Creating a Basic Table Report.

How Can I Modify a Group? After you create a group, you can set data region-specific properties, such as filter and sort expressions, page breaks, and group variables to hold scope-specific data. For more information, see Working with Group Features (Reporting Services).

To modify an existing group, open the appropriate group properties dialog box. You can change the name of the group. Also, you can specify group expressions based on a single field or multiple fields, or on a report parameter that specifies a value at run time. You can also base a group on a set of expressions, such as the set of expressions that specify age ranges for demographic data. For more information, see Examples of Group Expressions (Reporting Services).

How are Groups Organized?
Understanding group organization can help you design data regions that display different views of the same data by specifying identical group expressions.

Groups are internally organized as members of one or more hierarchies for each data region. A group hierarchy has parent/child groups that are nested and can have adjacent groups.

If you think of the parent/child groups as a tree structure, each group hierarchy is forest of tree structures. A Tablix data region includes a row group hierarchy and a column group hierarchy. Data associated with row group members expands horizontally across the page and data associated with column group members expands vertically down the page. The Grouping pane displays row group and column group members for the currently selected Tablix data region on the design surface. For more information, see Grouping Pane.

A chart data region includes a category group hierarchy and a series group hierarchy. Category group members are displayed on the category axis and series group members are displayed on the series axis.

Although typically not needed for gauge data regions, groups do let you specify how to group data to aggregate on the gauge.

What Types of Groups are Available per Data Region?
Data regions that expand as a grid support different groups than data regions that display summary data visually. Thus, a Tablix data region, and the tables, lists, and matrices that are based on the Tablix data region, support different groups than a chart or gauge. The following sections discuss the type of and purpose for grouping in each type of data region.


Although groups have different names in different data regions, the principles behind how you create and use groups are the same. When you create a group for a data region, you specify a way to organize the detail data from the dataset that is linked to the data region. Each data region supports a group structure on which to display grouped data.

Groups in a Tablix Data Region: Details, Row, and Column Groups

As shown earlier in this topic, a Tablix data region enables you to organize data into groups by rows or columns. However, row and column groups are not the only groups available in a Tablix data region. This data region can have the following types of groups:

Details Group The Details group consists of all data from a report dataset after Report Designer applies dataset and data region filters. Thus, the Details group is the only group that has no group expression.

Basically, the details group specifies the data that you would see when you run a dataset query in a query designer. For example, you have a query that retrieves all columns from a sales order table. Thus, the data in this detail group includes all the values for every row for all the columns in the table. The data in this detail group also includes values for any calculated dataset fields that you have created.

By default, when you add a table or list to your report, Report Designer automatically creates the Details group for you, and adds a row to display the detail data. By default, when you add dataset fields to cells in this row, you see simple expressions for the fields, for example, [Sales]. When you view the data region, the details row repeats once for every value in the result set.
Row groups and column groups You can organize data into groups by rows or columns. Row groups expand vertically on a page. Column groups expand horizontally on a page. Groups can be nested, for example, group first by [Year], then by [Quarter], then by [Month]. Groups can also be adjacent, for example, group on [Territory] and independently on [ProductCategory].

When you create a group for a data region, Report Designer automatically adds rows or columns to the data region and uses these rows or columns to display group data.
Recursive hierarchy groups A recursive hierarchy group organizes data from a single report dataset that includes multiple levels. For example, a recursive hieararchy group could display an organization hierarchy, for example, [Employee] that reports to [Employee]. Reporting Services provides group properties and built-in functions to enable you to create groups for this kind of report data. For more information, see Creating Recursive Hierarchy Groups (Reporting Services).
The following list summarizes the way you work with groups for each data region:

Table Define nested row groups, adjacent row groups, and recursive hierarchy row groups (such as for an organizational chart). By default, a table includes a details group. Add groups by dragging dataset fields to the Grouping pane for a selected table.
Matrix Define nested row and column groups, and adjacent row and column groups. Add groups by dragging dataset fields to the Grouping pane for a selected matrix.
List By default, supports the details group. Typical use is to support one level of grouping. Add groups by dragging dataset fields to the Grouping pane for a selected list.
After you add a group, the row and column handles of the data region change to reflect group membership. When you delete a group, you have the choice between deleting the group definition only or deleting the group and all its associated rows and columns. For more information, see Understanding Tablix Data Region Cells, Rows, and Columns.

To limit the data to display or use in calculations for detail or group data, set filters on the group. For more information, see Filtering Data in a Report.

By default, when you create a group, the sort expression for the group is the same as the group expression. To change the sort order, change the sort expression. For more information, see Grouping and Sorting Report Data.

Understanding Group Membership for Tablix Cells

Cells in a row or column of a Tablix data region can belong to multiple row and column groups. When you define an expression in the text box of a cell that uses an aggregate function (for example, =Sum(Fields!FieldName.Value), the default group scope for a cell is the inner most child group to which it belongs. When a cell belongs to both row and column groups, the scope is both innermost groups. You can also write expressions that calculate aggregate subtotals scoped to a group relative to another set of data. For example, you can calculate the percent of a group relative to the column group or to all data for the data region (such as =Sum(Fields!FieldName.Value)/Sum(Fields!FieldName.Value,"ColumnGroup")). For more information, see Understanding the Tablix Data Region and Calculating Totals and Other Aggregates (Reporting Services).

Groups in a Chart: Category and Series Groups

A chart supports nested category and series groups. Charts do not display detail data. Add groups to a chart by dragging dataset fields to the category and series drop zones for a selected chart.

Shape charts such as pie charts support category groups and nested category groups. Other charts such as bar charts support category groups and series groups. You can nest groups, but must make sure that the number of categories or series do not obscure the presentation of information about the chart. For more information, see Adding Data to a Chart Data Region.

Groups in a Gauge: Data Groups

A gauge typically does not use groups, but supports specifying group data. When you specify a group in a gauge, the group is used for aggregations which calculate the displayed data values. Add groups to a gauge through shortcut menu for a selected gauge. For more information, see Adding Data to a Gauge Data Region.

Adding Parameters to Your Report

2009-07-29T04:43:00.000-07:00

In Reporting Services, parameters are used to specify the data to use in a report,
connect related reports together, and vary report presentation. To design a report
that uses parameters effectively, you must understand how parameters and dataset
queries work together, how parameters and expressions work together, how parameters
can be managed on the report server for a published report, and what questions a
report is designed to answer. What you choose to parameterize can influence the
report design and layout.

Query parameters are added to a dataset query by way of the query designers or the
Dataset Properties dialog box. After you create a query with parameters, Reporting
Services automatically links query parameters to report parameters with the same
name. New report parameters are added and modified by way of the Report Data pane.

Using Parameters in Reporting Services
The most common use of parameters is to vary report data retrieved by dataset queries.
In this scenario, users are prompted for a value or values when they run the report,
and the dataset query retrieves only the data that is requested. The dataset query
includes query parameters and Reporting Services automatically creates corresponding
report parameters that are indirectly linked to the query parameters. You can change
the report parameter properties in the report design tools to include a valid values
list and a user prompt that displays at run time.

You can also create cascading parameters, which retrieve hierarchical data from
a data source. For example, the first parameter might filter on product category,
and the second parameter might filter on product. Cascading parameters are used
to organize and limit the number of available values for the user.

Not all data sources support queries parameters. For those times when you cannot
filter data at the source, you can use report parameters to provide a users with
the ability to filter data after it has been retrieved for the report. While all
query parameters have corresponding report parameters, you can define report parameters
independently in the report designer tools. Report parameters can also be used to
change the appearance or organization of a report after the data has been retrieved.

Parameters are used to connect reports to sub reports and drillthrough reports.
These report parameters might be hidden, using values in the main report to select
the correct data for the connected report, or they might be visible to the user
at run time.

After a report parameter is created, you can modify the default values and other
parameter properties, such as visibility. For example, report parameters can use
built-in variables like UserID, so you might want to create a report with a hidden
parameter that selects data specific to the user running the report. Parameters
can contain single values or multiple values, use a static or query-based valid
values list, and accept null or blank values. Multivalue parameters enable users
to select more than one value at run time.

After a report is published, parameters can be managed independently from the report
definition. Depending on how you configure the parameter in the report at design
time, report server administrators can modify many parameter properties in the published
report. They can also create sets of parameters linked to the same report definition
that customize the report for multiple audiences.

Parameters in Reporting Services

Using Parameters to Control Report Data Discusses how to use parameters to filter report data, to change the sort order
for detail and group data, and to modify queries to work with specific data providers.

Using Query Parameters with Specific Data Sources (Reporting
Services) Describes the query syntax expected by the various data sources.

Using Parameters to Control Report AppearanceDiscusses how to use parameters to change report appearance using expression-based
properties, including conditionally hiding report items and conditionally changing
text color based on the values of a text box.

Using Parameters to Connect to Other ReportsDiscusses how to use parameters to link to drillthrough reports, subreports, and
linked reports.

Using Single-Value and Multivalue Parameters Discusses how to use single-value and multivalue parameters.

Creating Report Parameters and Setting Report Parameter
Properties Discusses how to create and set properties on a report parameter when you author
the report, including data type, default values, parameter prompts, available values,
multiple values, and whether parameters are internal, hidden, or visible to the
user at run time.

Adding a Document Map

2009-07-29T04:19:00.000-07:00

Adding a Document Map

You can use a document map in a report to provide users with a way to navigate to certain areas of the report. When you view a report that includes a document map, a separate side pane appears next to the report. Report sections and groups are arranged in a hierarchy of links. Clicking items in the document map refreshes the report and displays the area of the report that corresponds to the item in the document map.

The document map is intended for use in the HTML rendering extension. Other rendering extensions have different ways of articulating a document map:

PDF renders a document map as the Bookmarks pane.
Excel renders a document map as a named worksheet that includes a hierarchy of links. Report sections are rendered in separate worksheets that are included with the document map in the same workbook.
Word includes a document map as the table of contents.
TIFF, XML ,and CSV ignore document maps.

For more information, see Comparing Interactive Functionality for Different Report Rendering Extensions.

To create a document map, add document map labels to those report items that should appear in the map, such as report items and groups. If any report items have a value set for the document map label, a document map is automatically generated when a user views the report.

To toggle the document map on and off, use the Document Map button on the report viewer toolbar. For reports rendered in HTML, you can specify whether to show the document map or not when the report is opened via a URL. The following example initially hides the document map for the report DocumentMapReport:

http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fDocumentMapReport&rs:Command=Render&rc:DocMap=False

How to: Add Items to a Document Map (Reporting Services)

A document map provides a set of navigational links to report items in the rendered report. A user can click links in the document map to jump to the report page that displays that item.

To add links to the document map, you set the DocumentMapLabel property of the report item to text that you create or to an expression that evaluates to the text that you want display in the document map. You can also add the unique values for a table or matrix group to the document map. For example, for a group based on color, each unique color is a link to the report page that displays the group instance for that color. For nested groups, the document map displays the group values in a hierarchy.

You can also create a URL to a report that overrides the display of the document map, so that you can .run the report without displaying the document map, and then click the Show/Hide button on the report viewer toolbar to toggle the display.

To add a report item to a document map

In Design view, make sure the Properties pane is visible. Click the report item such as a table, matrix, or gauge that you want to add to the document map. The report item properties appear in the Properties pane.

In the Properties pane, type the text that you want to appear in the document map in the DocumentMapLabel property, or enter an expression that evaluates to a label. For example, type Sales Chart.
Repeat step 1 for every report item that you want to appear in the document map.
Click Preview. The report runs and the document map displays the labels you created. Click any link to jump to the report page with that item.

add unique group values to a document map

In Design view, select the table, matrix, or list that contains the group that you want to display in the document map. The Grouping pane displays the row and column groups.
In the Row Groups pane, right-click the group, and then click Edit Group. The Generalpage of the Tablix Group Properties dialog box opens.
Click Advanced.
In the Document map list box, type or select an expression that matches the group expression.
Click OK.
Repeat steps 1-4 for every group that you want to appear in the document map.
Click Preview. The report runs and the document map displays the goup values. Click any link to jump to the report page with that item.

To hide the document map when you view a report

In Report Manager, browse to the report that has the document map.
For example, for the AdventureWorks sample reports, the following URL specifies the report named Product Catalog.
Copy Code
```
http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fAdventureWorks+Sample+Reports%2fProduct+Catalog
```
Copy the report path on the server. In the example, the report path is %2fAdventureWorks+Sample+Reports%2fProduct+Catalog.
Create a new URL with the following three components:
The report viewer on the report server: http://localhost/ReportServer/Pages/ReportViewer.aspx?
- The name of the report you copied in step 1, for example: %2fAdventureWorks+Sample+Reports%2fProduct+Catalog
- The device information parameters that specify hiding the document map: &rs%3aCommand=Render&rc%3aFormat=HTML4.0&rc%3aDocMap=False
The following URL consists of these three components appended in the order they are listed.
Copy Code
```
http://localhost/ReportServer/Pages/ReportViewer.aspx?
%2fAdventureWorks+Sample+Reports%2fProduct+Catalog
&rs%3aCommand=Render&rc%3aFormat=HTML4.0&rc%3aDocMap=False
To use this URL, copy it and remove all line breaks.
       
      
```
Paste the URL in Report Manager, and then press ENTER. The report runs, and the document map is hidden.

Adding Links to a Report

2009-07-29T04:02:00.000-07:00

You can add links that allow users to open Web pages or other reports, or jump to another location within the same report. To add a link, you define an action for a report item such as a text box, image, chart, or gauge. Use the Action page of the corresponding properties dialog box to set the type of link. When the user clicks that report item, the action that you define takes place. Reporting Services supports three types of links with the following actions:

Bookmark links Jump to other areas within the report.
Drillthrough report links Jump to other reports on the same report server.
Hyperlinks Jump to URLs that specify the address of Web pages or reports on a report server by using URL access.

Bookmark Links

A bookmark link is a link that a user clicks to move to another area or report item in a report. To create a bookmark, set a bookmark on the destination report item and add bookmark links on report items that users should click, such as a word or image, to jump to the bookmarked report item. You can set bookmarks on any report item, including text boxes, images, charts, and gauges. You can add multiple bookmark links to the same bookmark. For more information, see How to: Add a Bookmark to a Report (Reporting Services).

Drillthrough Report Links :

A drillthrough report is a report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. For example, you might have a sales summary report with a list of orders and sales totals. When a user clicks an order number in the summary list, another report opens that contains details about the order.

A drillthrough report serves a different purpose than a subreport. A subreport is processed as part of the main report. For example, if a subreport that displays order detail information is added to a table cell in the detail row, the subreport is processed once per row of the table and rendered as part of the main report. A drillthrough report is only processed and rendered when the user clicks the drillthrough link in the summary main report.

A drillthrough report typically contains parameters that are passed to it by the summary report. In the sales summary report example, the summary report includes the field [OrderNumber] in a text box in a table cell. The drillthrough report contains a parameter that takes the order number as a value. When you set the drillthrough report link on the text box for [OrderNumber], set the parameter for the target report to [OrderNumber]. When the user clicks order number in the summary report, the target detail report opens and displays the information for that order number. To view instructions about customizing drillthrough reports based on parameter values, see Adding Parameters to Your Report and InScope Function (Reporting Services).

To preview a summary report with drillthrough links on the report client, you must set the drillthrough report name to a report in the current project. To view a summary report with drillthrough links after it is published, you must ensure that the drillthrough reports reside on the same report server as the summary report. The drillthrough report name can contain a relative or absolute path to the report. You can add drillthrough links to text boxes, images, charts, and gauges. For more information, see How to: Add a Drillthrough Action on a Report (Reporting Services).

Hyperlinks :You can add a hyperlink to a report item so that a user can access a Web page by clicking the item. A hyperlink can be a static URL or an expression that evaluates to a URL. If you have a field in a database that contains URLs, the expression can contain that field, resulting in a dynamic list of hyperlinks in the report. You can add hyperlinks to text boxes, images, charts, and gauges. For more information, see How to: Add a Hyperlink to a URL (Reporting Services).

You can also specify URLs to reports on any report server that you and your users have permission to view using URL requests to the report server, known as URL access. For example, you can specify a report and hide the document map for the user when they first view the report. For more information, see URL Access.

List Data Region in SSRS

2009-07-29T03:50:00.000-07:00

A list data region repeats with each group or row in the report dataset. A list can be used for free-form reports or in conjunction with other data regions. You can define lists that contain any number of report items. A list can be nested within another list to provide multiple groups of data.

Adding a List to Your Report

Add a list to the design surface from the Toolbox. By default, the list initially has a single cell in a row associated with the detail group.

When you select a list on the design surface, row and column handles appear, as shown in the following figure.

The list you start with is a template based on the Tablix data region. After you add a list, you can continue to enhance the design by changing the the content or appearance of the list by specifying filter, sort, or group expressions, or changing the way the list displays across report pages. For more information, see Controlling the Tablix Data Region Display on a Report Page (Reporting Services). Although the list starts with a single column and row, you can further continue to develop your list design by adding nested or adjacent row groups or column groups, or adding additional detail rows. For more information, see Exploring the Flexibility of a Tablix Data Region.

If you have a report model data source, you can also create simple list reports using Report Builder. For more information, see How to: Start Report Builder.

Displaying Data in a Free-form Layout To organize report data in a free-form layout instead of a grid, you can add a list to the design surface. Drag fields from the Report Data pane to the cell. By default, the cell contains a rectangle that acts as a container. Move each field in the container until you have the design you want. Use the snaplines that appear when you drag text boxes in the rectangle container to help you align edges vertically and horizontally. Remove unwanted white space by adjusting the size of the cell. For more information, see How to: Change Row Height or Column Width (Reporting Services).

The following figure shows a list that displays information about an order, including these fields: Date, Order, Qty, Product, LineTotal, and an image.

In Preview, the list repeats to display the field data in the free-form format, as shown in the following figure:

Displaying Data with One Level of Grouping Because a list automatically provides a container, you can use a list to display grouped data with multiple views. To change the default list to specify a group, edit the Details group, specify a new name, and specify a group expression.

For example, you can embed a table and a chart that show different views of the same dataset. You can add a group to the list so that the nested report items will repeat once for every group value. The following figure shows a list grouped by product category. Notice that there is no detail row. Two tables are nested side by side in the list. The first table displays the subcategories with total sales. The second table displays the category grouped by geographical area, with a chart that shows the distribution of subcategories.

In Preview, the table displays total sales for all subcategories of bicycles, and the table beside it displays the breakdown of sales per geographical area. By using an expression to specify the background color for the table and a custom palette for the chart, the first table also provides the legend for the chart colors.

Gauge Data Regions In SSRS

2009-07-29T03:45:00.000-07:00

The Gauge data region is a one-dimensional data region that displays a single value in your dataset. An individual gauge is always positioned inside a gauge panel, where you can add child or adjacent gauges. You can use the gauge panel to create multiple gauges inside a single gauge panel that share common functions such as filtering, grouping, or sorting.

You can use gauges to perform many tasks in a report:

Display key performance indicators (KPIs) in a single radial or linear gauge.
Place a gauge inside a table or matrix to illustrate values inside each cell.
Use multiple gauges in a single gauge panel to compare data between fields.

The following illustration shows the basic elements of a single gauge in the gauge panel.

Reporting Services provides two types of gauges: radial and linear. Unlike the chart types available in the Select Chart Type dialog box, the gauge types available in the Select Gauge Type dialog box are created by using a combination of gauge properties. Therefore, you cannot change the gauge type the same way you change a chart type. To change the gauge type, you must remove the gauge and re-add it to the design surface.

A radial gauge has at least one scale and one pointer. You can have multiple scales by right-clicking the gauge and selecting Add Scale. By default, this will create a smaller scale that is positioned inside the first scale. Radial gauges have three types of pointers: marker, bar, and needle. The needle pointer is only available for the radial gauge. You can have multiple pointers by right-clicking the gauge and selecting Add Pointer. This will create another pointer on the same scale, but if you have multiple scales, you can associate a pointer with any scale on the gauge.

The scale on a radial gauge is a circular scale that displays labels and tick marks. There are two sets of tick marks, minor and major.

A linear gauge functions the same as a radial gauge, except for its shape and orientation. Linear gauges are useful for integrating into the table or matrix data regions to show progress data.

Chart Data Region in SSRS

2009-07-29T03:33:00.000-07:00

When you want to summarize data in a visual format, use the Chart data region. Charts enable you to present large volumes of aggregated information at a glance. It is important to carefully prepare and understand your data before you create a chart, as this will help you design your charts quickly and efficiently.

The following illustration shows many of the different elements used in the chart.

Tablix Data Region in SSRS

2009-07-29T03:28:00.000-07:00

Use the Tablix data region to display fields from a dataset either as detail data or as grouped data in a grid or free-form layout। Reporting Services provides three templates for a Tablix data region that you can add directly from the Toolbox: table, matrix, and list।

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. The following figure shows the initial table template, selected on the design surface:

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. The following figure shows the initial matrix template, selected on the design surface:
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. For example, you can define a group for a list; add a table, chart, and image; and display values in table and graphic form for each group value, as you might for an employee or patient record.

You are not limited by your initial template choice. As you add groups, totals, and labels, you may find you want to modify your Tablix design. For example, you might start with a table and then delete the details row and add column groups. For more information, see Exploring the Flexibility of a Tablix Data Region.

You can continue to develop a table, matrix, or list by adding any Tablix feature. Tablix features include displaying detail data or aggregates for grouped data on rows and columns. You can create nested groups, independent adjacent groups, or recursive groups. You can filter and sort grouped data, and easily combine groups by including multiple group expressions in a group definition. For more information, see Grouping Data in a Data Region.

You can also add totals for a group or grand totals for the data region. You can hide rows or columns to simplify a report and enable the user to toggle the display of the hidden data, as in a drilldown report. For more information, see Controlling the Tablix Data Region Display on a Report Page (Reporting Services).

Matrix Data Region in SSRS

2009-07-29T03:20:00.000-07:00

Use a matrix to display grouped data and summary information. You can group data by multiple fields or expressions in row and column groups. Matrices provide functionality similar to crosstabs and pivot tables. At run time, as the report data and data regions are combined, a matrix grows horizontally and vertically on the page. Values in matrix cells display aggregate values scoped to the intersection of the row and column groups to which the cell belongs. You can format the rows and columns to highlight the data you want to emphasize. You can also include drilldown toggles that initially hide detail data; the user can then click the toggles to display more or less detail as needed.

After your initial design, you can continue to develop a matrix to improve the viewing experience for the user. For more information, see Controlling the Tablix Data Region Display on a Report Page (Reporting Services).

To start working with a table data region right away, see Tutorial: Creating a Basic Matrix Report.

Adding a Matrix to Your Report

Add a matrix to the design surface from the Toolbox. The matrix initially has a row group, a column group, a corner cell, and a data cell, as shown in the following figure.

When you select a matrix on the design surface, row and column handles appear, as shown in the following figure.

Add groups by dragging dataset fields to the Row Groups and Column Groups areas of the Grouping pane. The first field that you drag to the row groups or column groups pane replaces the initial empty default group. You can then apply formatting for each cell, depending on the data.

In Preview, the matrix expands to show the row group and column group values. The cells display summary values, as shown in the following figure.

The matrix you start with is a template based on the Tablix data region. You can continue to develop your matrix design by adding nested or adjacent row groups or column groups, or even adding detail rows. For more information, see Exploring the Flexibility of a Tablix Data Region।

Adding a Parent Group or Child Group to a Matrix
To add a group based on a single dataset field, drag the field from the Report Data pane to the appropriate Row Groups or Column Groups area of the Grouping pane. Drop the field in the group hierarchy to set its relationship to existing groups. Drop it above an existing group to create a parent group, or drop it below an existing group to create a child group.

Several things happen when you drop a field in the Grouping pane:

A new group with a unique name based on the field name is automatically created. The group expression is set to the simple field name reference, for example [Category].
A new row or column appears in the corresponding row group or column group area.
In the new column, a row group cell appears for the default data rows from the report dataset. Cells in the Tablix body for this row are now members of the row group. If there are any column groups defined, cells that are in the columns are members of those column groups. Group indicators provide visual cues for the group membership of each cell.

To customize the group after it is created, use the Tablix Group dialog box. You can change the group name, and edit or add additional expressions to the group definition. To add or remove rows from the table, see How to: Insert or Delete a Row (Reporting Services).

When the report runs, dynamic column headers expand right (or left, if the Direction property of the matrix is set to RTL) for as many columns as there are unique group values. Dynamic rows expand down the page. The data that appears in the Tablix body cells are aggregates based on the intersections of row and column groups, as shown in the following figure.

In preview, the report displays as in the following figure.

To write expressions that specify a scope other than the default scope, you must specify the name of a dataset, data region, or group in the aggregate function all. To calculate the percentage each subcategory contributes to the Clothing category group values, add a column inside the Category group next to the Total column, format the text box to show percentage, and add an expression that uses the default scope in the numerator, and the Category group scope in the denominator, as shown in the following example.

=SUM(Fields!Linetotal.Value)/SUM(Fields! Linetotal.Value,"Category")
For more information, see Calculating Totals and Other Aggregates (Reporting Services).

Adding an Adjacent Group to a Matrix

To add an adjacent group based on a single dataset field, use the shortcut menu in the Grouping pane. For more information, see How to: Add or Delete a Group in a Data Region (Reporting Services). The following figure shows a group based on geography and an adjacent group based on year.

In this example, the query has filtered data values to only include those values for Europe and for the years 2003 and 2004. However, you can set filters on each group independently. In preview, the report displays as in the following figure.

To add a total column for an adjacent column group, click in the column group definition cell and use the Add Total command. A new static column is added next to the column group, with a default aggregate sum for every numeric field in the existing rows. To change the expression, manually edit the default aggregate, for example, Avg([Sales]). For more information, see How to: Add a Total to a Group or Tablix Data Region (Reporting Services).

Unifying Data Storage and Access with Microsoft BI

2009-07-29T01:56:00.000-07:00

Unifying Data Storage and Access

Most organizations have multiple business systems, each
with its own dedicated data store. Although you can often generate reports from
individual applications and perform analysis on the data they contain, you can
only achieve complete and consistent insight into the business by consolidating
disparate data throughout the enterprise to create a central source of business
data for reporting and analysis.

Consolidating Corporate Data for Analysis and Reporting

SQL Server 2008 supports two common approaches to unifying
business data for analysis and reporting:

· Data warehouse. A data warehouse is a dedicated data store for enterprise-wide
data, which is populated and synchronized with business data from disparate
data sources throughout the enterprise. The key advantage of this approach is
that you can design the data warehouse for optimal analytical and reporting
performance with no impact on the performance of the line-of-business
applications from which the data originates. Another advantage is that you can
clean and consolidate data that comes from multiple sources into a single
consistent version of the truth.

Data source abstraction. SQL Server 2008 Analysis Services enables the creation of data source views to provide an abstraction layer over one or more data sources. You can then use
the data source view as a single source for Analysis Services, Integration
Services, and Reporting Services. With a data source view, the data is
retrieved from the underlying source systems when analysis occurs or a report
is generated. This enables real-time analysis of the data in your business
applications. Additionally, the data source view through its added layer of
abstraction can be used to create friendly names to replace long or cryptic
table names.

These approaches are shown in the following illustration. With SQL Server 2008, you can use either one or a combination of the two.

Work with All of Your Data the Way You Want to

Regardless of which approach you take to unify your business data, SQL Server 2008 builds on its strong legacy of support for both relational and non-relational data by providing data types that enable developers and administrators to efficiently store and manage unstructured data
such as documents and images, so you can store, manage, and analyze data in the
format that best suits your business.

SQL Server 2008 includes enhanced support for XML data storage as well as a FILESTREAM data type that enables large binary data to bestored in the file system yet remain an integral part of the database with
transactional consistency. Additionally, restrictions on the size of user-defined data types have been removed, which makes it possible to exceed the 8-KB limit imposed in earlier releases of SQL Server. Support for spatial and location data types makes it possible to store and analyze
geographical data that conforms to industry standards.

Integrate All Enterprise Data Sources

SQL Server 2008 data source views enable integration of data and access to data that originates from heterogeneous enterprise-wide data stores, such as SQL Server, Oracle, DB2, and Teradata। These views also provide an OLAP store of enterprise scale; the breadth of support for diverse data sources in SQL Server Integration Services means that you can extract
data from all kinds of existing business applications. Therefore, unifying the
data in all of your enterprise data sources is easy to accomplish, regardless
of whether you want to build an abstraction layer through a data source view or
use an ETL process to synchronize a dedicated data warehouse for analysis and
reporting.Additionally, through support for Web services and the
Microsoft .NET Framework, SQL Server 2008 supports interoperability with
multiple platforms, applications, and programming languages, so you can
maximize your investment in new and existing systems by integrating and
connecting your disparate data sources. Support for existing and emerging open
standards, such as HTTP, XML, SOAP, XQuery, and XSD, further facilitate
communication across your extended enterprise systems.

Data Warehouse Performance

SQL Server 2008 provides a comprehensive and scalable data
warehouse platform that enables your organization to integrate data into the
data warehouse faster so you can scale and manage growing volumes of data and
users.

Optimize ETL Processes

Data warehouses are usually populated and updated with
data from source systems through an ETL process. After the initial load of data
into the data warehouse, periodic refreshes of new and changed data from the
source systems are performed to ensure that the data warehouse is up-to-date.

SQL Server Integration Services provides a comprehensive
platform that you can use to extract data from diverse source systems, make any
required transformations to the data or its structure and format, and then load
the transformed data into the data warehouse. Performing lookups to match
records from source systems to existing records in the data warehouse is a
common operation in ETL processes, and in SQL Server 2008 the performance
of lookups has been significantly improved so that these operations scale to
extremely large tables.

To help track data changes and ensure data warehouse
consistency, SQL Server 2008 introduces change data capture functionality to log updates in change tables,
which makes it easy to identify rows that have been modified and to determine
the details of the modification and its cause.

Manage Hardware Resources Efficiently

Through support for data compression, SQL Server 2008
enables you to store your data more effectively and reduce storage costs. In
addition, the compression significantly improves performance for large
input/output bound workloads. SQL Server 2008 also provides native support
out-of-the-box for backup compression. Additionally, the VARDECIMAL data type
and new sparse columns in SQL Server 2008 help you get the most efficiency
from your disk storage resources by reducing the amount of space that is
required for the large tables that contain the predominantly numeric or NULL
values that are often found in data warehouses.

In addition to efficiencies in data storage, SQL
Server 2008 supports dynamic allocation of Address Windowing Extensions
(AWE) mapped memory that supports up to 64 gigabytes of memory with
Windows Server^® 2003, Datacenter Edition, and 2 terabytes with the 64-bit edition, to support
large data warehouse environments.

SQL Server 2008 also introduces Resource Governor,
which enables administrators to define resource limits and priorities for
different workloads, so that concurrent workloads can provide consistent
performance and make it possible to manage resource contention issues
proactively.

Optimize Database Performance

SQL Server 2008 includes a high-performance relational
database engine that enables you to build highly effective data warehouse
solutions. Innovations, such as query optimizations for star schemas and tools
to help you tune indexes and data structures, make SQL Server a natural
choice for a heavily queried data warehouse. With the introduction of the MERGE
Transact-SQL statement in SQL Server 2008, developers can more effectively
handle common data warehousing scenarios, such as checking whether a row exists
and then executing inserts or updates. Additionally, an extension to the GROUP
BY clause enables users to define multiple grouping in the same query. Grouping
Sets produce a single result set that is equivalent to a UNION ALL of
differently grouped rows, which makes aggregation querying and reporting easier
and faster.

SQL Server 2008 supports partitioned tables to help you
optimize the performance and management of large tables, and with new support
for partitioned table parallelism, you can significantly optimize a data
warehouse by using partitioned tables.

Building and Managing Sophisticated BI Solutions

As organizations demand ever increasingly complex
analytics, the need to be able to build and deliver effective BI solutions
quickly and to reduce the management overhead of your BI infrastructure has
become a major consideration. SQL Server 2008 includes innovative tools
that increase developer productivity and manageability, which enables faster
capitalization on new analysis and reporting capabilities while incurring
reduced administrative overhead.

Developer Productivity

SQL Server 2008 simplifies the development of business
intelligence solutions. BI developers benefit from easy-to-use utilities and
tools that increase control and automate routine, time-consuming tasks, and can
use the productivity features of SQL Server 2008 to create effective analysis
and reporting solutions more quickly than ever; so your organization can take
advantage of them sooner.

Use a Rich, Modern Programming Environment.

Through tight interoperability with Microsoft Visual
Studio^®, developers can easily build and maintain robust, secure, scalable BI applications.
SQL Server Business Intelligence Development Studio offers a single tool
that covers multiple types of BI solution and provides a single, consistent
environment for developing ETL, analysis and reporting solutions. Intuitive BI
wizards that are delivered as part of the Business Intelligence Development
Studio make it easy for even novice developers to build advanced Business
Intelligence models and projects.

By embedding the common language runtime (CLR) in the database
engine, SQL Server 2008 enables developers to choose from a variety of
languages to develop applications, including Transact-SQL, Microsoft Visual
Basic^®, and C#^®. This flexible environment
enables developers to use their existing skills to efficiently develop database
applications.

When integrated with Visual Studio, the development
experience across all of the SQL Server 2008 BI technologies is optimized for
providing a true application development environment that supports the full
project development life cycle (develop, test, deploy, modify, and test).

Implement Best Practice Solutions

Enabling developers to build solutions more quickly is
only effective if those solutions are optimally designed. To help ensure the
best possible performance and correct functionality, SQL Server 2008 includes
the following development environment features that promote best practices and
help developers create effective analysis solutions:

· A consistent development environment for all BI
solutions, including Analysis Services, OLAP, and data mining applications.

· Built-in support for the full development
lifecycle, including design, build, debug, and deploy operations; and support
for team-based development through integrated support for source control.

· A number of intuitive designers and wizards that
make it easy to create Analysis Services solutions quickly.

· An attribute relationship designer that includes
built-in validations to help in creating optimal dimension designs.

· A dimension editor that has been slimmed down to
provide better productivity and the presence of parent child relationships is
automatically detected.

· A cube designer that has been streamlined and
improved to provide better detection and classification of attributes along
with identification of member properties.

· Aggregation of individual partitions, which
enables you to optimize measures from different periods or areas.

· The aggregation designer has a new algorithm to
help create initial aggregations. The aggregation designer is optimized to work
with usage driven aggregations. You can now look at the aggregations that have
been created and add to those aggregations or remove them. Intelligent support
is provided to help with merging existing and new aggregation designs.

Additionally, SQL Server 2008 provides
AMO warnings to alert developers when their design breaks one of over
40 best practices. These warnings are integrated into real-time designer
checks, and provide a non-intrusive way for developers to detect potential
problems with their design.

Increase Reporting Flexibility

Reporting is a significant element of any BI solution, and
business users are demanding increasingly complex reports. SQL Server Reporting
Services provides the following features to make it easy to build reporting
solutions:

· A Visual Studio-based report development
interface in Business Intelligence Development Studio that developers can use
to build, debug, and deploy reports.

· A business-focused report development tool named
Report Builder that business users can use to create and deploy reports.

· A wide range of data display structures, including tables, matrices, lists, and charts.

Additionally, SQL Server 2008
includes extensive enhancements to Reporting Services that improve reporting
performance and provide increased flexibility for formatting and publishing
reports. One of the enhancements made to Reporting Services in SQL
Server 2008 is support for a new layout structure that combines table and
matrix data regions into a new Tablix data region. Tablix enables developers to
generate reports that combine fixed and dynamic rows. Previously, layouts of
this kind had to be developed by using multiple matrix data regions and
shrinking row headers. Support for Tablix data regions simplifies the inclusion
of combined static and dynamic data in reports, and extends the formatting and
layout capabilities of Reporting Services significantly.

Manageability

Through innovations such as a unified management tool,
enhanced self-tuning capabilities, and a powerful management programming model,
SQL Server 2008 extends the SQL Server ease-of-use leadership and
increases the productivity of database administrators (DBAs). These
enhancements enable DBAs to focus on high value tasks, like database
architecture, while spending less time on routine maintenance, configuration,
and tuning.

Use a Single, Unified Tool

SQL Server 2008 provides DBAs with SQL Server Management Studio; a single, unified management tool that provides integrated management of Analysis Services, Reporting Services, Integration Services, and multiple versions of SQL Server, from the same interface for increased DBA productivity, flexibility, and manageability across extended SQL Server
implementations.

Monitor Data Warehouse Resources

SQL Server 2008 includes performance data collection and
warehousing, which enables monitoring and reporting of resources across your
data services solution.

Enterprise
Scalability

A key factor related to the successful delivery of truly
sophisticated BI solutions requires sustained developer productivity and
manageability even through large-scale implementations. Furthermore, the
infrastructure must provide performance-related scalability, which in SQL
Server 2008 has achieved a wide variety of investments that are focused on
ensuring this enterprise-scale sustainability even through the largest scale of
BI implementation.

Scalable Analytics

The premise of Online Analytical Processing (OLAP) is that
instant access to accurate information enables end users to answer even the
most complex questions at the speed of thought. Thus, the aim to continuously
excel in providing even faster query times and data refresh rates is a priority
during the development process of any SQL Server Analysis Services
release, an aim that naturally also has been driving the release of SQL
Server 2008 Analysis Services.

SQL Server 2008 includes Analysis Services that enable you
to drive broader analysis with enhanced capabilities, including complex
computations and aggregations. Analysis Service provides enterprise-scale performance
through:

· A flexible caching model. With Analysis Services, you can control how data
and aggregations are cached to optimize query performance while maintaining an
acceptable level of latency between the cache and its underlying data store.

· Declarative attribute relationships. In an Analysis Services dimension, you can
explicitly declare relationships between attributes in a hierarchy. This
enables Analysis Services to pre-generate aggregations when a cube or dimension
is processed, which improves runtime query performance.

· Block computation. Block computation eliminates unnecessary aggregation
calculations (for example, when the values to be aggregated are NULL) and
provides a significant improvement in analysis cube performance, which enables
users to increase the depth of their hierarchies and complexity of
computations.

· Write-back to MOLAP. Analysis Services 2008 removes the requirement to query
ROLAP partitions when performing write-backs, which results in huge performance
gains.

· Scale-out Analysis Services. A single read-only copy of an Analysis Services database
can be shared between many Analysis Servers through a virtual IP address. This
creates a highly scalable deployment option for an Analysis Services solution.

· Execution
plan persistence. SQL Server 2008 provides functionality to lock down
query plans so that, to the maximum extent possible for correctness, the query
plans survive server restart, server upgrade, and production deployments. This
ensures consistent optimal performance of queries against SQL Server data.

Scalable रिपोर्टिंग

For many organizations, getting the right information to
the right people at the right time is a significant challenge. SQL Server 2008
provides a high-performance reporting engine for processing and formatting
reports along with a complete set of tools for creating, managing, and viewing
reports. An extensible architecture and open interfaces enable easy integration
of reporting solutions in diverse IT environments.

You can generate reports from multiple diverse data
sources, including SQL Server, DB2, and Oracle, without first building a
centralized data warehouse. You can deliver reports throughout the organization
both internally and externally through the simple deployment and configuration
capabilities that are provided by Reporting Services. This enables users to
easily create and share reports of any size or complexity. You can also deliver
reports to customers and suppliers easily by deploying reports over the
Internet.

Reporting Service provides support and the ability to
control server behavior with memory management, infrastructure consolidation,
and straightforward configuration through a centralized store and an API for
all configuration settings.

Extending the Reach of Your BI Solution

In the past, BI solutions were used by a small group of
business analysts. Now, more and more organizations are realizing the benefits
of extending the insights that are available through BI to all employees and
embedding those insights into the day-to-day operations of the business.

SQL Server 2008 enables you to create a BI solution that
can scale to thousands of users and deliver a rich user experience to everyone
through an extensible, open, and embeddable architecture that is optimized for
interoperability with Microsoft Office.

Extending Business Insight to Everyone Through Familiar Tools

Microsoft Office is a ubiquitous productivity suite that
most information workers in organizations throughout the world use to perform
their daily tasks. Through close integration with Office, SQL Server 2008
enables you to empower your employees with critical, timely business
information that is tailored to their specific information needs.

Tight integration with the 2007 Microsoft Office
System and PerformancePoint Server 2007 enables organizations to save time
and money by using technology that works well together. The integration also
provides a quicker return on investment in terms of end-user scale by
delivering business insight to everyone through familiar tools that are already
installed on every desktop computer.

Extend Your Reporting Solution with Microsoft Office

Reporting Services 2008 supports rendering to both Excel
and Word formats. Rendered reports are fully editable in the appropriate Office
application, which extends your reporting solution so that users can create
custom documents based on business reports.

Use Excel for Analysis

Excel is the tool of choice for many financial and
business analysis tasks. By combining Excel with SQL Server Analysis
Services, you can bring the full power of your OLAP solution to business users
through PivotTable dynamic views and Microsoft PivotChart^® dynamic views in an Excel spreadsheet.

You can use the SQL Server Data Mining Add-Ins for
Excel 2007 to enable business analysts and executives to go through the
entire life cycle of a data mining project, including preparing data, building,
evaluating, and managing mining models, and predicting results by using either
spreadsheet data or external data that is accessible through your Analysis
Services database.

Publish Business Insights Throughout the Enterprise

SQL Server 2008 integrates tightly with SharePoint
Services to make it easy to publish and manage reports centrally in a
SharePoint site and to build user-specific dashboards that provide customizable
views of relevant reports.Additionally, PerformancePoint Server provides a central
interface for data analysis that is built on SQL Server Analysis Services
and enables customers to monitor, analyze, and plan their business, as well as
drives alignment and provides actionable insight across the entire
organization.

Empowering End Users Through Flexible Reporting

SQL Server 2008 provides a number of reporting enhancements that enable you to
quickly and easily generate the reports that your organization needs, in the
format that you want, and in a layout that makes sense of the data they
contain.

Create Ad Hoc Reports with Report Builder

Report Builder has been enhanced extensively in SQL Server 2008
to enable users to easily build ad hoc reports with any structure. The
intuitive design interface makes it easy for non-developers to create business
documents, such as purchase orders, invoices, and contracts, based on report
data.

Include Richly Formatted Data

Rich formatting can make business documents and reports
more intuitive and significantly easier to understand. The rich text component
of SQL Server 2008 enables mixed-formatting text boxes and importing of
marked up text strings, and supports the new chart formats and the Tablix data
region so that users can generate reports with high standards of visual design
to convey business information clearly and logically.

Benefit from Increased Responsiveness

As reports become more central to the way organizations
distribute business information, ensuring the highest levels of performance and
scalability of your reporting solution has become more critical. The Reporting
Services engine in SQL Server 2008 has been significantly re-engineered to
resolve current limitations, and now includes on-demand processing and
instance-based rendering to provide the best possible reporting performance.

Deploy Reports Securely Over the Internet

SQL Server 2008 enables you to extend the reach of your
Reporting Services solution to external users, such as customers and suppliers,
by publishing reports securely over the Internet.

Data Regions in SSRS

2009-07-28T23:02:00.000-07:00

Data regions are report items that display the rows of data from report datasets. Data from datasets can be displayed in a table, matrix, list, chart, or gauge data region. Data regions expand as needed to display the data from the dataset. Table, Matrix, and List data regions are based on the Tablix data region. A Tablix data region supports multiple row and column groups and with static and dynamic rows and columns.

Table
A table is a data region that presents data row by row. Table columns are static. Table rows expand downwards to accommodate the data. You can add groups to tables, which organize data by selected fields or expressions. For information about adding a table to a report, see Adding a Table (Reporting Services).

Matrix
A matrix is also known as a crosstab. A matrix data region contains both columns and rows that expand to accommodate the data. A matrix can have dynamic columns and rows and static columns and rows. Columns or rows can contain other columns or rows, and can be used to group data. For information about adding a matrix to a report, see Adding a Matrix (Reporting Services).

List
A list is a data region that presents data arranged in a freeform fashion. You can arrange report items to create a form with text boxes, images, and other data regions placed anywhere within the list. For information about adding a list to a report, see Adding a List (Reporting Services).

Chart
A chart presents data graphically. Examples of charts include bar, pie, and line charts, but many more styles are supported. For information about adding a chart to a report, see Working with Chart Data Regions.

Gauge
A gauge presents data as a range with an indicator pointing to a specific value within the range. Gauges are used to display key performance indicators (KPIs) and other metrics. Examples of gauges include linear and circular. For more information about adding a gauge to a report, see Working with Gauge Data Regions.

Nested Data Regions
You can nest data regions within other data regions. For example, if you want to create a sales record for each sales person in a database, you can create a list with text boxes and an image to display information about the employee, and then add table and chart data regions to the list to show the employee's sales record. For more information, see Nesting Data Regions.
Multiple Data Regions Linked to the Same Dataset
You can link more than one data region to the same dataset to provide different views of the same data. For example, you can show the same data in a table and in a chart. You can author the report to provide interactive sort buttons on the table, so that when you sort the table, the chart is also automatically sorted. For more information, see Linking Multiple Data Regions to the Same Dataset

Types of Reports

2009-07-28T22:33:00.000-07:00

In Reporting Services, you can use reports in a variety of ways. This topic describes the terminology used to describe the various types of reports and the ways reports get created and used. A single report can have characteristics from more than one type; for example, snapshot reports can be parameterized, ad hoc reports incorporate clickthrough report functionality due to the report models upon which they are based, and subreports can be linked reports.
With Reporting Services, you can create the following types of reports:
Parameterized reports
Linked reports
Snapshot reports
Cached reports
Ad hoc reports
Clickthrough reports
Drilldown reports
Drillthrough reports
Subreports
Report Appearance, Processing, and Delivery Terminology
There are several ways to think about report type. You might think about it as the way data appears in the report. In Reporting Services, the appearance of data in a report depends on the type of data region you use; for example, tabular reports and chart reports use different data regions. For more information about how to display data, see Data Regions. Likewise, the functionality that is available in a report depends on the output format; for example, interactive features like drillthrough reports are available in Web-based export formats but not in all Image-based export formats. A report's final output format affects which features you can include in a report.
Parameterized Reports
A parameterized report uses input values to complete report or data processing. With a parameterized report, you can vary the output of a report based on values that are set when the report runs. Parameterized reports are frequently used for drillthrough reports, linked reports, and subreports, connecting and filtering reports with related data.
Using Parameters
Parameters are used in dataset queries to select report data, to filter the result set that the query returns, or to set layout properties used to display or hide parts of a report. You can also specify cascading parameters that populate a series of dependent, drop-down parameter lists. For example, a drop-down list of Region parameter values can be used to populate a drop-down list of City parameter values.
You can use parameters with linked reports by pairing a specific parameter with each linked report to change the outcome. For example, you can create a single regional sales report that shows the sales for all regions, and then use a parameter for each linked report to filter data for a particular region. Specific parameter values can be stored with the report so that users do not have to type values.
Not all parameters may be visible in the report at run time. A report author, report server administrator, or content manager can specify which values to use and then hide the input fields on the report.
Query Parameters and Report Parameters
Reporting Services supports two kinds of parameters: query parameters and report parameters. Query parameters are used during data processing to select or filter data. Query parameters are specified in the syntax of a data processing extension. If a query parameter is specified, a value must be provided either by the user or by default properties to complete the SELECT statement or stored procedure that retrieves data for a report. Report parameters are used during report processing to show a different aspect of the data. A report parameter is usually used to filter a large set of records, but it can have other uses depending on the queries and expressions used in the report. Report parameters differ from query parameters in that they are defined in a report and processed by the report server, while query parameters are defined as part of the dataset query and processed on the database server.

Linked Reports
A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.
A linked report is derived from an existing report and retains the original's report definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.
You can create a linked report on the report server when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.
Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings.

Snapshot Reports
A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, report snapshots are processed on a schedule and then saved to a report server. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database and shows the data and layout that were current for the report at the time the snapshot was created.
Report snapshots are not saved in a particular rendering format. Instead, report snapshots are rendered in a final viewing format (such as HTML) only when a user or an application requests it. Deferred rendering makes a snapshot portable. The report can be rendered in the correct format for the requesting device or Web browser.
Report snapshots serve three purposes:
Report history. By creating a series of report snapshots, you can build a history of a report that shows how data changes over time.
Consistency. Use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next. A report snapshot, by contrast, allows you to make valid comparisons against other reports or analytical tools that contain data from the same point in time.
Performance. By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours.

Cached Reports
A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period, usually in minutes.

Ad Hoc Reports
An ad hoc report can be created from an existing report model by using Report Builder. Ad hoc reports refer specifically to Report Builder reports, and not to reports that you can create using the Report Wizard. Ad hoc reports leverage report models and pre-defined templates to enable information workers to quickly and easily explore business data using familiar terminology and data structures that have been implemented in the report model. Ad hoc reports can be saved and run locally or published to a report server, just like other Reporting Services reports.

Clickthrough Reports
A clickthrough report is a report that displays related data from a report model when you click the interactive data contained within your model-based report. These reports are generated by the report server based on the information contained within the report model. The person who created the model determines which fields are interactive and which fields are returned when a clickthrough report is opened. These field settings cannot be changed in the report authoring tools.
Clickthrough reports are autogenerated. However, you can create an alternative customized report to the model for interactive data items that is displayed instead. The custom report is a standard Reporting Services report.

Drilldown Reports
Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report.
reports with large amounts of data, consider drillthrough reports instead.

Drillthrough Reports
Drillthrough reports are standard reports that are accessed through a hyperlink on a text box in the original report. Drillthrough reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart. The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display greater details based on the aggregate in the main report. Drillthrough reports can be filtered by parameters, but they do not have to be. Drillthrough reports differ from subreports in that the report does not display within the original report, but opens separately. They differ from clickthrough reports in that they are not autogenerated from the data source, but are instead custom reports that are saved on the report server. They differ from drilldown reports in that they retrieve the report data only for the specified parameters or for the dataset query.

Subreports
A subreport is a report that displays another report inside the body of a main report. Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The subreport can use different data sources than the main report. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report. You can set up the parent report to pass parameters to the subreport.
Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports.

SSIS

2009-07-28T22:14:00.003-07:00

SSAS

2009-07-28T22:14:00.001-07:00

SSAS

Microsoft BI Analyst

SSIS

Reporting Services Analyst InfoCenter

Reporting Services Administrator InfoCenter

Reporting Services Developer InfoCenter

SQL Server Reporting Services FAQ

Report Server In SSRS

Report Manager In SSRS

Reporting Services Components and Tools

Reporting Services Scenarios

SharePoint Integration

Application Integration

Visual Studio ReportViewer Web Server and Windows Forms Controls for Embedding Reports in Custom Applications

Redistribution Through SQL Server Express with Advanced Services

Building Custom Report Design and Report Management Tools

Extending Reporting Services Functionality

Reporting Services Features

Understanding Groups (Reporting Services)

Groups in a Tablix Data Region: Details, Row, and Column Groups

Understanding Group Membership for Tablix Cells

Groups in a Gauge: Data Groups

Adding Parameters to Your Report

Adding a Document Map

Adding a Document Map

Adding Links to a Report

List Data Region in SSRS

Gauge Data Regions In SSRS

Chart Data Region in SSRS

Tablix Data Region in SSRS

Matrix Data Region in SSRS

Unifying Data Storage and Access with Microsoft BI

Integrate All Enterprise Data Sources

Enterprise Scalability

Publish Business Insights Throughout the Enterprise

Data Regions in SSRS

Types of Reports

SSIS

SSAS

Enterprise
Scalability