SSRS:Creating a Report with Parameters

------------------------------------------------------------------------------------------------------------------------

Refer the Source Video:Creating a Report with Parameters


------------------------------------------------------------------------------------------------------------------------

Summary:

1. "DataSet" :  Select .... From ... Where ...
      
      Where condn. -> tells the "Parameter"

     After the "Where" condn. is specified , it creates a "Parameter"

2. To create a "Dropdown List"

    i."Dataset" - build the query to show the values.
    ii. "Parameter" - specify the above dataset.

------------------------------------------------------------------------------------------------------------------------

DataSets: choose query

1)Select...Where TerritoryID=@TerritoryID

It adds.

Report

Parameters

TerritoryID

2)Need a Dropdown & select

DataSet-Add DataSet

Query:Select Distinct TerritoryID ..

Go to Parameters: Avalaible value:specify it

------------------------------------------------------------------------------------------------------------------------

Refer the Source Video: Creating a SSRS Report with cascading parameters

------------------------------------------------------------------------------------------------------------------------

Summary:

Requirement: Based on "Customer" ,  the "Territory" drop down shows values.

1. "DataSet" : sql query for "Territory" add "Where" having the  @Customer parameter.

2. "DataSet": sql of main dataset, add both "Where' having @Customer , @ Territory

------------------------------------------------------------------------------------------------------------------------

CustomerId - then populate the Territory Id

DataSet: Query:Distinct CustomerID

Choose the DataSet:TerritoryId

Query:change sql having Where CustomerId=@CustomerID

Then it creates a paramerter:There choose and choose "Available Values"

2nd dropdown is greyed out.

Note: If you do want dropdown you specify in parameter no drop down,it shows just textbox.

------------------------------------------------------------------------------------------------------------------------