Introduction

When you are using reports, you need to have parameters that narrow down your report for better analysis. You will often see that you need to have more than one parameter, where the second parameter may depend on the first parameter, which many require you to refresh the second parameter depending on the first parameter.


In addition, you may have to have parameters with multiple and/or default values. This article addresses how to create parameters in SQL Server 2005 Reporting Services (SSRS).

Pre-Requests


To understand the article content, you should have a fair knowledge of creating a report in SSRS, as this article does review how to create a report in SSRS in detail. However, I will try to cover as much as possible when it comes to creation of SSRS report. Also, you will need to know how to write basic T-SQL queries joining two or more tables.

Resource wise, you need to have SQL Server 2005 installed with SQL Server Business Intelligence Development Studio. As I am going to use data in the AdventureWorks sample database, it will be easier if you have installed an AdventureWorks database with your version of SQL Server.

Sample Case

Since it is always helpful to the reader to explain things through example, let us assume we want to list out employees depending on their country, state/province and city. Users should have an option of selecting a country. Depending on the selection of country, we need to list state/provinces which belong to the selected country. After selecting a state, we should list all the related cities and the user should have the option of selecting one or more cities from the list. Depending on the selected city or cities, the user should get a final list of employees that fit the criteria.

Implementation

First, you must create a Report Server Project from SQL Server Business Intelligence, then add a report to the project. Next is to create a shared data source that is pointed to the AdventureWorks database. We now need to add a dataset for country. You can add this dataset from the dataset tab by selecting ‘<New Dataset> Option’ from the dataset list box. Then configure the dsCountry dataset as depicted in the below image.



The next task is to assign this dsCountry to a report parameter. Select the ‘Report Parameters’ option from the Report menu.

Below is an image of the screen you should see.



The options in the dialog box above are:

Name – Name of the parameter. When you select a value, that value is stored in this parameter. As this is a variable name, you cannot have special characters (*, ! or spaces, etc.) in this field.

Data Type – Data type of the parameter. Options of this field include: Boolean, Datetime, Integer, Float and String. In this case, we will chose ‘String’ for the country parameter.

Prompt – Prompt is what you see in the report. As this is a label, you can have any characters for the prompt.

The following attributes are as simple as their names indicate. The difference between Hidden and Internal is that hidden variable can be changed from mechanisms and internal variables cannot be changed.

The next step is to assign values for the parameters.

The non-queried option should be used when a parameter has fixed values, such as Yes/No, Male/Female etc. From query option is to fill values from a dataset to parameter. In this example, the dataset is dscountry and we need to fill in two values: value field and label field. The label field is what users will see and the value field is what will be stored in the parameter. In this case, Name is the label field while CountryRegionCode is the value field. For example, if the user selects the United Status, US will be stored in the Country parameter.

The next step is to assign default values. A default value makes it easier for users to work with reports because it makes the most probable value the user will chose the default value. For example, in this report users be selecting United States, so having United States being the default value makes it easier for the user to fill out the form.

Reasons why you must trust ASPHostPortal.com

Every provider will tell you how they treat their support, uptime, expertise, guarantees, etc., are. Take a close look. What they’re really offering you is nothing close to what ASPHostPortal does. You will be treated with respect and provided the courtesy and service you would expect from a world-class web hosting business.


You’ll have highly trained, skilled professional technical support people ready, willing, and wanting to help you 24 hours a day. Your web hosting account servers are monitored from three monitoring points, with two alert points, every minute, 24 hours a day, 7 days a week, 365 days a year. The followings are the list of other added- benefits you can find when hosting with us:

- DELL Hardware
Dell hardware is engineered to keep critical enterprise applications running around the clock with clustered solutions fully tested and certified by Dell and other leading operating system and application providers.
- Recovery Systems
Recovery becomes easy and seamless with our fully managed backup services. We monitor your server to ensure your data is properly backed up and recoverable so when the time comes, you can easily repair or recover your data.
- Control Panel
We provide one of the most comprehensive customer control panels available. Providing maximum control and ease of use, our Control Panel serves as the central management point for your ASPHostPortal account. You’ll use a flexible, powerful hosting control panel that will give you direct control over your web hosting account. Our control panel and systems configuration is fully automated and this means your settings are configured automatically and instantly.
- Excellent Expertise in Technology
The reason we can provide you with a great amount of power, flexibility, and simplicity at such a discounted price is due to incredible efficiencies within our business. We have not just been providing hosting for many clients for years, we have also been researching, developing, and innovating every aspect of our operations, systems, procedures, strategy, management, and teams. Our operations are based on a continual improvement program where we review thousands of systems, operational and management metrics in real-time, to fine-tune every aspect of our operation and activities. We continually train and retrain all people in our teams. We provide all people in our teams with the time, space, and inspiration to research, understand, and explore the Internet in search of greater knowledge. We do this while providing you with the best hosting services for the lowest possible price.
- Data Center
ASPHostPortal modular Tier-3 data center was specifically designed to be a world-class web hosting facility totally dedicated to uncompromised performance and security
- Monitoring Services
From the moment your server is connected to our network it is monitored for connectivity, disk, memory and CPU utilization – as well as hardware failures. Our engineers are alerted to potential issues before they become critical.
- Network
ASPHostPortal has architected its network like no other hosting company. Every facet of our network infrastructure scales to gigabit speeds with no single point of failure.
- Security
Network security and the security of your server are ASPHostPortal’s top priorities. Our security team is constantly monitoring the entire network for unusual or suspicious behavior so that when it is detected we can address the issue before our network or your server is affected.
- Support Services
Engineers staff our data center 24 hours a day, 7 days a week, 365 days a year to manage the network infrastructure and oversee top-of-the-line servers that host our clients’ critical sites and services.