How Can I Filter The Data From Sp At Report Level Inssrs

By: | Updated: 2009-12-09 | Comments (16) | Related: > Reporting Services Dynamic Reports

Problem

I have a requirement where I need to provide ii report parameters for a report. The first parameter volition nowadays a list of columns that the user can choose to filter the report. The second parameter will contain a list of available values based on what was chosen for the start parameter. As an case my first study parameter provides the options Gender and Marital Status. Based on the user selection I need to populate my 2d report parameter's available list with Male and Female or Married and Single. How can I do this?

Solution

SSRS provides the adequacy to define a dataset to be used to populate the dropdown list for a report parameter. When y'all define the dataset you specify a query or stored procedure that volition return the list of values for the report parameter dropdown. In this case nosotros have a slight variation; the list of available values for the second report parameter isn't filtered based on the get-go parameter; it'southward completely unlike for each choice.

Every bit a starting bespeak let's implement some functions and stored procedures that we can use to solve this trouble; then we'll create a written report that volition leverage these objects.

Functions and Stored Procedures

I want to create a function that volition return the list of available values for each unlike filter parameter. By creating individual functions I can reuse them in multiple reports. The following functions volition be used to populate the study parameter dropdown lists for Gender and Marital Condition:

create role dbo.GetGenderChoiceList() returns tabular array as render (  select            'Female' every bit FILTER_LABEL  , 'F' as FILTER_VALUE  union all  select            'Male person'  , 'M' ) go create part dbo.GetMaritalStatusChoiceList() returns tabular array as render (  select            'Married' as FILTER_LABEL  , 'One thousand' equally FILTER_VALUE  wedlock all  select            'Single'  , 'S' ) become

The higher up functions are examples of tabular array-valued functions. They can be specified in the FROM clause of a select statement same as if they were actual tables. There are two column aliases that are used: FILTER_LABEL and FILTER_VALUE. When you define a study parameter and specify that the available options come from a query, you specify which cavalcade in the consequence set is the label (what you see in the dropdown list) and which column is the value (what you filter on). By aliasing these as FILTER_LABEL and FILTER_VALUE we make all parameter lists look the same.

Next let's create the stored procedures we demand. We'll be creating a stored procedures to populate the report parameter dropdown lists and query the customer table.

GetCustomerFilterOptions volition be used to populate the outset written report parameter dropdown, allowing the user to choose the filter column. Information technology is shown below:

create procedure dbo.GetCustomerFilterOptions as begin  fix nocount on;  select     'Gender' equally FILTER_LABEL         , 'Gender' as FILTER_VALUE  matrimony all  select     'Marital Condition'         , 'Marital Status' terminate become

The FILTER_LABEL and FILTER_VALUE are the same; we could have assigned numbers to the FILTER_VALUE but in this case information technology is more than intuitive to just get with the text descriptions.

GetFilterChoiceList will exist used to populate the second report parameter dropdown, giving us the list of bachelor values based on the filter column selected in the first report parameter dropdown; it is shown beneath:

create procedure dbo.GetFilterChoiceList    @filterby varchar(50) equally brainstorm  set nocount on;  declare @sql nvarchar(500)  set @sql = instance @filterby    when 'Gender' then       N'select FILTER_LABEL, FILTER_VALUE                from dbo.GetGenderChoiceList()'    when 'Marital Status' then       Due north'select FILTER_LABEL, FILTER_VALUE                from dbo.GetMaritalStatusChoiceList()'  stop    exec sp_executesql @sql end go

Annotation that GetFilterChoiceList simply calls the advisable part that we created earlier, based on the filterby parameter. The sp_executesql stored procedure is used to execute a dynamic SQL statement.

FilterCustomerList contains a query to select customers and utilise a filter based on parameters passed in to the stored procedure; it is shown below:

create procedure dbo.FilterCustomerList   @filterby varchar(50)  , @filtervalue varchar(fifty) as begin  fix nocount on;    select LastName, Gender, MaritalStatus  from dbo.DimCustomer  where     Gender = case when @filterby = 'Gender' and then                       @filtervalue else Gender end         and    MaritalStatus = case when @filterby = 'Marital Status' then                              @filtervalue else MaritalStatus finish cease go

The WHERE clause in FilterCustomerList utilizes a example statement that compares a cavalcade to the filter value passed in as a parameter (if that was the filter by option) or to itself (if the filter by option is on a different column). This simplifies the WHERE clause. The dbo.DimCustomer table is in the AdventureWorksDW database which y'all tin download from here.

We can run the post-obit script to test our stored procedures:

exec dbo.GetCustomerFilterOptions exec dbo.GetFilterChoiceList 'Gender' exec dbo.FilterCustomerList 'Gender', 'M'

The results returned from the above script are shown below:

filter label

The to a higher place results evidence the list of filter options, the filter choice listing for Gender, and the customers where Gender is Male person (M).

Create the Written report

The following steps testify creating the report using Business organisation Intelligence Development Studio (BIDS) 2005 where the study blueprint surface contains the following tabs:

choice list

The Data tab is used to create datasets (select New Dataset from the dropdown listing on the Data tab), the Layout tab is used to specify the report layout, and the preview tab allows you to run the report within BIDS. The annotations on the in a higher place screen shot call out the buttons on the data tab that you can click to execute the query (or stored procedure) and refresh the field list; i.e. the columns returned. You should execute the query (or stored process) in order to make sure that the result prepare columns are available in the dataset.

At a loftier level we need to perform the following steps:

Create datasets to populate the written report parameter dropdown lists and create a dataset to return the filtered list of customers
Configure the report parameters to populate the dropdown lists based on the datasets
Layout the written report

Create Datasets

Create a new dataset that will be used to populate the first report parameter dropdown listing; specify the command type equally StoredProcedure, enter the stored procedure name GetCustomerFilterOptions equally the query string:

dataset

Create a new dataset that will be used to populate the second report parameter dropdown list; specify the command blazon as StoredProcedure, enter the stored procedure proper noun GetFilterChoiceList as the query string:

dataset

Create a new dataset that will be used to query the DimCustomer table based on the report parameters; specify the command type as StoredProcedure, enter the stored procedure name FilterCustomerList as the query string:

dataset

If you follow a consequent naming convention for stored procedure parameters, the Parameters tab (shown in the Dataset screen shots above) volition become filled in automatically and will match the report parameters (discussed in the next section). Occasionally you volition have to manually edit the Parameters tab or the Report Parameters (discussed below) if y'all are non consistent with naming.

Configure Report Parameters

In this stride we will specify the dataset to utilise to populate each written report parameter dropdown list. Click Report on the meridian-level bill of fare in BIDS, then select Report Parameters (you may accept to click on the Layout tab in order to see the Written report top-level menu). Yous volition run into the Report Parameters dialog as shown below:

report parameters

Select filterby in the Parameters listbox and specify the Available values as shown beneath:

available values

Select filtervalue in the Parameters listbox and specify the Available values as shown below:

available values

Layout the Written report

Click on the Layout tab, elevate the Table report item from the Toolbox onto the layout design surface, and then fill up in the columns equally shown below past dragging them from the CustomerQuery dataset and dropping them onto the tabular array:

gender

Run the Study

Click the Preview tab, select a value from the filterby and filtervalue dropdown lists, then click View Written report and you will meet the following:

last name

Adjacent Steps

Recall about the functions and stored procedures that are required to recall the data you need for your reports. You will likely observe that creating these database objects then using them in a report is easier than embedding the SQL commands in the report. In addition you can employ the functions and stored procedures in other reports.
Download the sample code from this tip and experiment on your own.