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

marl — Sun, 18 Jan 2009 20:02:00 GMT

*NOTE - This is a Cross-Post from my other blog http://dbalink.wordpress.com - Marlon Ribunal : -)

HThis 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:

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.

Read the Part II here.

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.

Southern California Code Camp - Day 1 - DevPinoy Was There!

marl — Sat, 26 Jan 2008 23:27:00 GMT

California State University - Fullerton, CA - January 26, 2008 Day # 1

The registration to the event started at around 8:00 AM. I really intended to go to the registration way ahead of most of the attendees (about 700 people according to Keith). So I thought showing up at 15 minutes before 8:00 AM would fulfill that intention. To my surprise, hundreds of people were already lining up to be registered (FREE Registration, sweet!). The main reason why I really wanted to show up early was for the freebies. I really wanted to have that "C# in a Nutshell" book but I was unsuccessful! Keith got a copy of the C# book published by Microsoft. Anyhow, I got two free books and two magazines (yes, read that F-R-E-E!):

"6 Microsoft Office Business Applications For Office Sharepoint Server 2007"

"So that's how: Timesaver, Breakthroughs, & Everyday Genius for 2007 Microsoft Office System"

(The 2 magazines I got were the "Code" and "ASP.Net Pro")

Honestly the only sessions that I have some knowledge of were the SQL Sessions. I attended the "SQL Server Analysis Services" & "Delivering Data Using SQL Server Reporting Services". I had the opportunity to work on some Development ventures in my old company which exposed me to SQL Server and these services. I was kind of exposed also to the Sharepoint Server but attending a session about Sharepoint 2007 is almost alien to me. Sharepoint has really evolved in three years!

It is such a privilege to be able to attend Paul Sherrif's sessions! I have to admit that I was almost listening to him with almost pure ignorance; despite that, I can really appreciate Paul's sessions on "Data Controls in ASP.NET 3.5" and "Testing .NET Apps for the Real World". Let me make this clear, guys, that I am still in my infancy (or prior to that) as far as programming is concerned. So do not expect me to write posts about these .NET sessions that I attended.

The presentation of Lynn Langit about "Data Mining with SQL Server/Excel 2007" has me struck with awe on the Data Mining capability of Excel 2007, specially on Business Analysis. And I am really surprised to hear from a Microsoft insider, such as Lynn, that the "Largest database out there in the real world is not SQL Server [or Oracle for that matter], but Excel." I never looked at Excel that way. I used to believe that Excel as a business tool will go down with history with the advent of products such as Reporting and Analysis Services and their integration to the SQL Server (and all other products provided by other vendors and all other third-party products out there that deal with the same business requirements). That mindset about Excel is gone now. Today, I have learned to respect Excel.

αρχάριος : Reporting Services

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

Southern California Code Camp - Day 1 - DevPinoy Was There!