How to design parameterize SSRS report.

In my previous article we learned How to design simple SSRS Report. The sample report contains the list of Employees and their Details from “North wind” database.

Now we are moving to design specific reports. I mean to say that in real world we have requirements to make reports for some specific purpose.

Suppose we need to find the particular details of an employee. Report Details include, Full Name of an Employee, Designation, Address, City, Region, and Country and most important is Employment Date (Hire Date of an Employee).

Lets Continue with the previous example of my previous article Simple steps for developing SSRS Report as we are more familiar with it now. Lets modify our requirement instead of listing details of all employees; lets provide facility to display report for particular employee.

This is the time when we required Report Parameter. We have to design the report in such a way that provides us the details of any employee (of course from the available entries in database).

We already designed the Simple Report, which displays all the employees and their details. For more details on How to Create Simple Report please first read my previous article on Simple steps for developing SSRS Report .

To add Report Parameter:

1. For adding Parameters to Report move your cursor towards Menu and clicks on Report Menu.
2. Choose Report Parameters Tab.
   
3. When you click on Report Parameters Tab the following Screen appears. From this we can add as many parameters as we want.
   

Here we can see 2 sections:

1. Parameters (In Left Side)
2. Properties (In Right Side)

From Parameter section we can add or remove the Parameters.

To Add New Parameters click on Add Button. After adding parameter, we have to set the Parameter Properties from the Properties Section (in Right side).
Here we have requirement that from the employee id we have to display the details of an employee.

So for that we need to add new parameter called “EmployeeID” which can helps us to retrieve the details of particular Employee.

After adding the Report Parameter we need to set properties of the added parameter. The parameter has following properties, which we have to set.

Parameter Properties:

• Name - Shows the parameter name and its must be unique name.
• Data Type – Data Type of Report
• Prompt - Shows the Display Name for Parameter in Report Viewer Control

The prompt string defines the label that identifies the parameter in the input area. The prompt can be the name of the parameter or directions to the user, for example, "Name" or "Enter Name". If the prompt is left blank and a default parameter value is specified, the default value is used, and the input box for the parameter is not displayed when the user runs the report. If prompt is left blank, and no default parameter value is specified, the report cannot run. If you do not want to prompt the user for parameter values, mark a parameter as Hidden or Internal.

Here we need EmployeeId as a parameter, so we can set the above properties like this way:

• Name - EmployeeID
• Data Type– Integer (As EmplyeeID is having integer data type in north wind database)
• Prompt - Employee ID

We can have more options to set the parameter behavior. We have more options to set the more properties, which we find in Check Boxes as like following:

• Hidden
• Internal
• Multi Value
• Allow Null Value
• Allow Blank Value (Not for Integer Data Type)

Lets see what ways the above properties are useful for parameter while designing the report.

• Hidden
  By using Hidden Parameter, you can hide the parameter on the parameter input area of the published report, yet set values for it on a report URL or in a subscription definition.
• Internal
  If you set a parameter to Internal, it is not exposed in any way except in the report definition. In other words Internal Parameter is a parameter that cannot be changed at runtime. A consumer of a published report will never see this as a parameter.
• Multi Value
  If you want to display multiple selection then MultiValue parameter is used.
  
  For e.g. Suppose we want to display list of Employees into Drop Down List Box and allowed user to choose among of the listed, we can set the MultiValue Parameter.
  
  For this lets make a new DataSet (suppose dsEmployees) to bind the Listbox with Employees
  
  For e.g. SELECT EmployeeID, EmployeeName From the Employees
  
  And then Click on Available Values section and Choose From Query Section. You have to select the associated data set (here dsEmployees) and then you can assign Value Field =EmployeeID and Label Field = EmployeeName
  
  When you see the Report Preview, you can see the Drop Down List box having filled up with employee names. When you select the Employee Name the associated EmployeeID will pass as a report parameter.
• Allow NULL Value
  Allow Null Value indicates that the parameter can have null values.
• Allow Blank Value
  If we want to allow an empty string as a valid value then we have to set this parameter.

After all the required parameters are defined, press OK Button.

Now turns to Add filters on our report. User can add filter from the Properties Window like shown in following screen:

When you press ” …” Button near Filter Properties, the window will open to set the filter criteria(s). Like following:

In this screen you can see the 4 Columns.

Expression Column	To Set the Filter on which Field.
Operator Column	Which Operator (=, <, >, Like, >=, <=, etc.)
Value Column	To Set the Value of the Field, which we set in Expression Column
And/Or	The Relation with different Filter Criteria

To set the Value Field you can add an Expression like following:

Here we want to add the value field of the Parameter Value ( EmployeeID). So we can add Parameter in the Expression window like following:

Here we set all required things that need for adding parameters are finished now Press OK and run the Report in Preview mode.

When you press View Report Button without Entering the Employee ID the report viewer shows an error regarding the Employee ID Report Parameter cannot have null value.

We need to pass the Employee ID parameter of an Integer type. Lets insert Employee ID=1 into the Employee ID Text Box and click on View Report.

You can see the details of an Employee which has Employee ID =1 like following:

These are the steps by which we can design the attractive SSRS Reports with the help of different parameters.

Conclusion:
When you are using reports, you need to have parameters that narrow down your report for better analysis. Parameters allow for dynamism by adding user input to the report-rendering process. A number of parameter options as well as a number of ways SSRS prompts the user are available using familiar controls. Parameters can be data-driven, or they can have a static list of valid values.
Over the course of this article, you have learned what report parameters are and how they can be used. This includes their use in queries or in expressions that can be used throughout the report.

DML, DDL, DCL and TCL-Statements in SQL SERVER

DML
DML is abbreviation of Data Manipulation Language. It is used to store (Insert), modify, delete, insert and update data in database.
Examples DML: INSERT, UPDATE and DELETE statements
Where,
UPDATE - updates data in the database
DELETE - deletes data from the database
INSERT INTO - inserts new data into the database

DQL
DQL is abbreviation of Data Query Language. Used to retrieve the records / data from database.
Many people include this category into DML Statements.
Example: SELECT statement
WHERE
SELECT - extracts data from the database

DDL
DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements
Where,
CREATE TABLE - creates new database table
ALTER TABLE - alters or changes the database table
DROP TABLE - deletes the database table
CREATE INDEX - creates an index or used as a search key
DROP INDEX - deletes an index

DCL
DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.
Examples: GRANT, REVOKE statements
Where,
GRANT – gives access privileges to users for database
REVOKE – withdraws access privileges to users for database

TCL
TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.
Examples: COMMIT, SAVE POINT, ROLLBACK statements
Where,
COMMIT – saves the work done
SAVEPOINT – saves the work done up to some point. Used with Rollback.
ROLLBACK - restore the database to original since the last COMMIT

Simple steps for developing SSRS Report

Steps to build Simple SSRS Report:

1) Create New Project with choosing Business Intelligence Projects\ReportServerProject.

2) By opening this Simple Report Project, click on Solution Explorer View. You are able to see following screen.

3) Now Right click on Shared Data Sources folder and choose Add New Data Source tab to add the Data Source for report.Write the appropriate connection string like following:

You can choose the database connection string from Edit Button displayed on above screen. Pressing Edit Button will redirect you on the following screen.

Choose the appropriate data base name and server name and if Test Connection is Succeed
then Press OK button. This will reflect to Shared Data Source connection string. It will be
automatically changed as per you selected in Connection Tab.

5)

Now turns to add report to our Report Server Project. Let’s create a sample report, which can display the Employee Details like First Name, Last Name, Address, City, Region, Country, etc.

For all this we need to add a report first, so for this right click on “Report” Folder in The Simple Report Project and choose “Add” Then “Add New Item” you can see the following screen:

Choose “Report” and give appropriate report name and click on ADD button.

6) After adding a new report into project, you will be able to see tree tabs.

a. Data – You have to provide the dataset for the report into this option. You can specify the SQL script into that.

b. Layout – This tab handle the layout of the report. You can specify the report layout in design mode. This works same as Report designers like Crystal report etc…

c. Preview – You can see the Preview of the report in this tab.

In Data tab User will need to create dataset for the report. The Dataset dropdown will have option by using this User will be able to create the dataset.

For this in DATA Region of the Report, click on Item of the Data Set Drop Down List Box. By clicking this you are redirected in following screen.

Here you have two options:

1. Write SQL Queries directly by selecting Command Type=”TEXT”
2. You can give Stored Procedure Name also by selecting the Command Type=” StoredProcedure”.
   

Press ok after applying proper queries or stored procedure depends as per requirements.

Following diagram shows the Data tab of the Report.

7)

Now turns to design the Actual Report as per our needs.

We want to display the Employee Details in our report. For this we are required to put
Labels, Text Boxes that shows the actual details of the Employees from the Employee table
as we applied into the Data Region.

Following image is showing the Layout view of the report in this we are using Table
object,which have the facilities of Header and Footer of the Rows. So we are displaying the
Column Title in Header and the actual record in Rows section of the Table.

To display actual data in the Table Rows, User can drag and drop those fields with Value into
the text box in report design view from the data set.

Second way to achieve this, by right click on that text box and click on “Expression”. This will
open new window like following: Here we want to display “Employee ID” so from “Fields
(dsEmployees)” we choose EmployeeID. And then apply Trim () to remove extra spaces.

Press OK button your Expression will set in Text Box. When we preview the Report it will display
the Employee ID from the database table.

You can have various options to set the values as per your need. As we need User
Name, First Name, Last Name, Email, so we can choose “Fields”. By double click on “Fields” we
can see the various columns returned by queries. As like the fields are displayed in right side
(EmployeeID, FirstName, LastName, Designation, etc.) Clicking on the particular filed, the
related expression will be reflect the at top side of the window like
“=Fields!EmployeeID.Value” .

You also can merge the values like to Display Full name you can merge TitleOfCourtesy,
FirstName and last name like this:

=Trim(Fields!TitleOfCourtesy.Value & " " & Fields!FirstName.Value & " " &
Fields!LastName.Value)

8) After proper design, user can be able to see preview the report like in following image from
Preview Tab of the Report.

We can use various inbuilt functions to make your design attractive.

For e.g.

1) Display Report Current Date:
=FormatDateTime(DateString(),1)

OutPut : Tuesday, May 13, 2008
(The day of week and date format in MMM-DD-YYYY)

2) Display Page Number out of Total Pages.

= "Page" & " " & Globals!PageNumber & " of " & Globals!TotalPages

OutPut : Page 1 of 2.

These are the simple steps to create a SSRS report but this is just creation of the report we still not integrated it with our application. For integration of the report to our application we need to deploy this report on the Report Server. After this we need to use Report Viewer Object to see the Report and have to bind this report with appropriate Report Server path and Report Path.

The next article will include following important things related while developing SSRS Report .

How t o Create Parameterized SSRS Reports?