SSRS Basics

2011-02-03T09:51:00.001-08:00

SSRS Basics for Beginner
Please Book Mark this Page for future reference.

If you are new to SSRS, then this blog is for you. This is aimed to help all the developers who are struggling using SQL Server Reporting Services (SSRS 2005).

Basice Item you need:

1. Install Microsoft Visual Studio make sure that Business Intelligence tools is included.

2. Next you need a data base. I use Microsoft SQL Server Manager Studio Express (free)

3. Create a Project and give it a name in Visual Studio

4. In Shared Data Sources you need to connection to your database.

5. In Report you just left click and select Add >> New Item

6. Follow the Wizard to create your report

SQL Reporting Services tips

Add Matrix object to report (column data like Year with detail row like month)

1. Add Matrix from Tool Bar

2. Edit Dataset Name and add dataset

3. Go to Properties of Matrix and click on Groups Tab

4. Add Rows and Columns to Matrix

5. Notes that Rows and Colmuns need to be unique.

6. Row 1 Expression is =Fields!month.Value

7. Right click Row and select Subtotal to add this under the row data.

8. Column 1 Expression is =Fields!year.Value

9. Last Columns Expression: =SUM(Fields!year.Value)

Format Date

=Today

=DateAdd("M",12,Today)

=Format(Fields!theDate.Value,"d")

=CDate(Format(Now,"d")) 'set the data type to date

=CDate((Month(Now))& "/01/" & Year(Now)) (use to default date parameter to current month)

Show or Hide rows of data

1. Select the entire row and goes to properties

2. Add to Visible option =IIF(Fields!Income1.Value =1,False,True)

Connection to SQL Server

1. You can use SQL Server Authentication (database users)

2. The second connection is Window Authentication (network users)

Databases

1. All the database you have access

2. Northwind should a database you get by default. Go to my SQL101 blog for detail on this database.

3. Expand Northwind and you will see the different object

a. Tables (where the data is stored)

b. Views (Query of data from Tables)

c. Programmability (Stored Procedures and Functions)

Tables

1. Right Click the Tables (folder) and select New Table

2. Enter a Column Name and Data Type and check the Allow Nulls checkbox

3. Data Type (common)

a. Varchar (50) - eg. First Name that can store up to 50 characters

b. Datetime - eg. Dates

c. Smallint - eg. ProductID

d. Float -eg. Prices

4. Expand (+) the Products table to see that data type it use

5. Right Click the Tab on the current table and select Save Table_1. It will give you a chance to change the name.

6. Note avoiding changing table name, column names and data type after table has been use.

Views

1. Right click Views and select create New

2. You then can select the table(s)

3. Select Customers and click OK.

4. Right click Next to Customers and select add Table and select Order

5. See that the join between them is done automatic. Note that you can join table by select a column of the main table and drag it to the column on the second.

6. Now you can select your field you want

7. Save view and give it a name.

8. Note you can create view with script as well.

9. Right click one of the current view (one you like to copy) > Script View as > CREATE To > New Query Editor Window

10. Code:

a. Create view [dbo].NewVeiwName

b. Select * from Product

c. Click the Execute button

d. Click Views and select Refresh. You should see the new view

Below are my Sponsor/Advertisements ads. Please do not click if you do not want to find out more about their services or products.

New to SSRS

SSRS Basics