Creating a basic Tabular SSRS Report using Report Wizard

SSRS has now become a defracto reporting tool and has become a necessity rather than luxury to become familiarise with it.I have found many peoples who have interest in the BI side, but didnot get any chance to work with because of many reason(may be they are not getting the exposure in their work field, lack of time to spend on the subject, frequent movement of projects etc.).Henceforth, I thought of writing this series of articles (SSRS/SSIS/SSAS) where basically I will talk about those features which I have touched upon as of now in my real time project. I will try to compile this series of articles more of step by step hands on approach so that people can refresh/learn by looking into it.Afterall, one picture is worth a thousand words.

Data Source

For this experiment, we will use the below script to generate and populate the Players table which is named as (tbl_Players)

-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players' AND type = 'U')
    DROP TABLE tbl_Players
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_Players (
 PlayerID INT IDENTITY,
 PlayerName VARCHAR(15),
 BelongsTo VARCHAR(15),
 MatchPlayed INT,
 RunsMade INT,
 WicketsTaken INT,
 FeePerMatch NUMERIC(16,2)
)

--Insert the records
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 400000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,800000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 999999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 888888)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444499)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 22222)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,99999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,12)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890)

The partial output after running a simple Select query

Select * from tbl_Players 

is as under

We will also have the below stored procedure created in our database whose script is as under

If Exists (Select * from sys.objects where name = 'usp_SelectRecordsByPlayerName' and type = 'P')
    Drop Procedure usp_SelectRecordsByPlayerName Go
-- Create the  stored procedureCreate Procedure [dbo].[usp_SelectRecordsByPlayerName]
( @PlayerID int )
As
Begin
Select
PlayerID
,PlayerName
, BelongsTo
, MatchPlayed
,RunsMade
,WicketsTaken
,FeePerMatch
From
tbl_Players
Where PlayerId = @PlayerID
End(A) Creating a basic Tabular SSRS Report using Report Wizard

Step 1: Open BIDS
Step 2: Choose Report Server Project from the available project type, give a suitable name to the project and click on OK button.

Step 3: After clicking on OK button the below screen will appear

Step 4: Let us click on the Shared Data Source - > Add New Data Source

Step 5: Let us enter proper information in the Shared Data Source Properties and click on OK button.

Step 6: After clicking on the OK button, the following screen will appear

Step 7: Next click on Reports - > Add New Report

and the report wizard will appear as under

Upon clicking on Next button, we get the Design the Query window where we will write an inline query to fetch the records from the Players table.

Click on Query Builder button and the Query Designer screen opens up .Note that 2 options are there in the table type

1. Text (where we write the normal /inline SQL query)
2. Stored Procedure

But in this example, we are interested only in the inline query; so we will choose Text.
Note: ~ we can click on the Import button and can specify the SQL file that contains the script. For specifying stored procedure , we need to specify the stored procedure name with a parameter name (since our's accept a parameter)

Now let us write the below query and click on the Run Icon (!)

Collapse | Copy Code

Select 
 PlayerID
 ,PlayerName,
 BelongsTo
 ,MatchPlayed,
 RunsMade
 ,WicketsTaken
 ,FeePerMatch
From tbl_Players

Click OK button and let's come back to the Design the Query window again

Click Next and from the Select the Report Type Screen that appears, let us choose Tabular as the report type and click on Next button.

And that brings up Design the Table Screen. From the Available Fields List box, let us choose all the fields and put those under the Displayed Fields of the Detailed section by clicking on Details button.

Clicking on Next button will display Choose the Table Style Screen from where we can choose the various table styles. We will choose Ocean here

Click on the Next button brings up the final screen where we will specify a suitable name to our report.

Click Finish.
Step 8: Report created. If we look now into the Reports folder, Player Report.rdl has been generated.

The Report design view is

Now let us click the Preview button to get the full report

Note: If we have specified a parameter, then at the time of preview we need to give some value to it as shown under

So far we have seen how to build the report. Now comes the part of deployment.