Skip to content

How To: Report Authoring in SSRS 2005 – Part I (The Database)

January 17, 2009

This is an introduction to SQL Server Reporting Services 2005. I am not going to explain the architecture and concepts around the Reporting Services (MSDN will do a great job on that). If you’re following my “How-To” articles, all the examples are outright step-by-step of the “how’s” and not the “why’s”. This series will demonstrate how to create report from ground up. We’ll start from creating our database and the tables and gradually progressing towards creating our simple report to the advanced report design. Knowledge of SQL Server and TSQL is required.

This will be a multipart series about SQL Server Reporting Services 2005.

In this first part (“The Database”) we will create the database and its structure. This database is just a simple one. There’s nothing fancy about it.

Here we go.

-- Create the demo database
CREATE DATABASE SQLServerBlogDemo

In this demo, we will be needing a simple structure which is composed of two tables – Employee and Department. There are lots of Best Practices on how to structure your database objects. We’re skipping those right now because that is not the goal of this series. So let’s create those two tables:

--Create Employee Table
CREATE TABLE Employee
(
EmpID INT IDENTITY(1,1) NOT NULL,
DeptID INT NULL,
EmpFirstName NVARCHAR(50) NULL,
EmpLastName NVARCHAR(50) NULL,
EmpBirthDay SMALLDATETIME NULL,
EmpSSN NVARCHAR(50) NULL,

CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED

)

-- Create Department
CREATE TABLE Department
(
DeptID INT IDENTITY(1,1) NOT NULL,
DeptName NVARCHAR(50) NULL,
CONSTRAINT PK_Department PRIMARY KEY CLUSTERED
)

Let’s add a Foreign Key to the Employee table referencing the Department table. We do that by modifying the Employee table

--Add FK to Employee Table
ALTER TABLE Employee  WITH NOCHECK
ADD CONSTRAINT FK_Employee_Department FOREIGN KEY(DeptID)
REFERENCES Department (DeptID)
Now that the table structure is in place, let’s insert some data that we can use for our report:
-- Insert Values to the Employee Table
INSERT INTO Employee VALUES ( 1, 'Marlon''Ribunal''1/1/2009''123-45-6789' )
INSERT INTO Employee VALUES ( 2, 'Bill''Gates''2/2/2007''456-78-9101' )
INSERT INTO Employee VALUES ( 3, 'George''Obama''3/3/2006''789-10-1112' )

– Insert Values to the Department Table
INSERT INTO Department VALUES (‘IT’)
INSERT INTO Department VALUES (‘Sales’)
INSERT INTO Department VALUES (‘Marketing’)

Since we’re starting from basic report, we want to start from this simple requirement - as simple as this:

reportdemo21

As you might have noticed already, this article does not only introduce you to SSRS per se, but also to basic object management in SQL Server. Anyway, We can achieve that result by issuing the following TSQL:

-- TSQL query to satisfy our report requirement
SELECT
e.EmpFirstName,
e.EmpLastName,
e.EmpBirthDay,
e.EmpSSN,
d.DeptName
FROM
Employee AS e
JOIN Department AS d
ON e.DeptID = d.DeptID

Now that we have created the database and defined the result that we want through the TSQL query, we’re ready to move on.

NEXT >> Designing The Report in Wizard

Creative Commons License
How To: Report Authoring in SSRS 2005 by Marlon Ribunal is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.

kick it on DotNetKicks.com

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 28 other followers

    %d bloggers like this: