SSRS - Dependent Parameter and Wild Search in Parameter
Introduction
The client requests for a Report via which the client can track the Selling of the Products Month by Month. The requested information is available in the Adventure Works 2012 Database [Production ].[Product] table. Data requested is mapped to ProductID,Name,ProductNumber,ReorderPoint and SellStartDate Columns of the [Production].[Product] table. Client should be able to Filter the Records based on the Month and Name.
**Developer's Problem **
1. Create two parameters on the SSRS report which help the client to sort the data based on the Month and Name of the Product.
2. The Month Data will come from the SellStartDate column but required to be displayed in Month Year Format. 3. The Name field contains around 502 unique values making the Parameter drop down very large in size.
4. Enable Wild Search on the Name Parameter.
Development Environment
- Visual Studio 2008 / 2010 or Report Builder.
**Solution
**1. Write the queries to retrieve the data from the table that will contain the parameters for the SSRS report.
Query 1 : Fetch Records from the requested columns + Parameter to Search the Month.
** Select ProductID,Name,ProductNumber,ReorderPoint,SellStartDate
From [Production].[Product]
Where
DATEPART(mm, SellStartDate) = DATEPART(MM, CAST(@ReportMonth as datetime))
AND DATEPART(yy,SellStartDate) = DATEPART(YY, CAST(@ReportMonth as datetime))
** Query 2 : Fetch the Records from the SellStartDate Column of the table and convert it into Month Year format.
Select Distinct DATENAME(MM, SellStartDate) + ' ' + cast(datepart(YYYY, SellStartDate) as varchar(4)) as ShortDate
** From [Production].[Product]**
This query will be used for the ReportMonth parameter in our SSRS report .
Query 3 : This final query in the SSRS will help us to sort our data in the Name parameter based on the Month as the column contains nearly 502 unique values
for the Name field.
** Select Name from (
Select Distinct Name
From [Production].[Product]
Where
DATEPART(mm, SellStartDate) = DATEPART(MM, CAST(@ReportMonth as datetime))
AND DATEPART(yy,SellStartDate) = DATEPART(YY, CAST(@ReportMonth as datetime))
) as Namelist
where Namelist.Name LIKE '%'+@Name+'%' or @Name is null
2.** Open the Visual Studio or Report Builder for developing the report . ( Currently I am using the Visual Studio 2010 )
Steps :
1. File --> New -- > Project --> Report Server Project Wizard .
Define the Data Source and Credentials according to your machine while selecting the Database Adventure Works 2012 .
- In the Query Designer paste the Query1
**
**
2. Follow the Wizard by clicking next and selecting Report type as Tabular -- > Selecting all the available Fields using the Details Button in the wizard ..
3. Any of those listed Table Style can be selected . I have selected the corporate style and give the name to the report .
4. Final Report will look like the below image.
5. Click on View in the Visual Studio tool bar menu at top -- Report Data or the short cut key can be used -- Ctrl + Alt + D.
6. Click on the Data Sets folder to add a new Data Set. The existing Data set is the one we created using the report wizard.
7. Name the New Data Set as ReportMonth and past the query 2 in it.
8. Use the Data Source embedded in the report.
**
**9. Click on OK once done.
10. Repeat the same step 6,7,8 and 9 while inserting the Query 3 this time . I have named the Data Set as Namelist.
11. Click on the Parameters folder by default there will two Parameters . The SSRS created those parameters for us as we mentioned in our SQL Query.
12. Double Click the Name Parameter and check the Allow Blank Value (" "). This parameter will help us to do wild searches on the Name list parameter which we will create in the next step.
13. Click on the Parameters folder -- > Add new Parameter. Name it has Namelist same in the Prompt Namelist .
14. Check the Allow multiple Values.
15. Now we will populate this Parameter with Values that will help the Client to sort Data on the Product Name available in our table. Click on the Available Values tab
---- > Get Values from a query --- > Data Set ----> namelist ----> Value Field --- > Name --- > Label Field --- > Name .
16 . Click on the Default Values tab and repeat the same steps as Step 15.
17. Double Click on the ReportMonth Parameter. Available Values tab --- > Get Values from a query ----- > Data Set ----> reportmonth ----> Value field and
label field --- > ShortDate
18. Repeat the same steps for the tab Default Values.
In the Report Designer Click on any of the Columns and then click on the grey tab on Column Heading Field . Right Click and click on the Tablix Properties.
19.
Click on the Filters -- > ADD --> Expression --- > Name . Operator ---- > In . Values --- > Parameter
Click on OK Once done.
Click on the Preview Report select the desired month you wish to view the Production Product Detail .
The Name field will act as wild Search if you wish to search for specific Name in the Name list drop Down.
Logic Implemented
- The Same report parameter Report Month values are passed in the Name list Query minimizing the Namelist Drop Down to only the Product Names sold for that month reducing the effort of the Client to search in the massive 502 Values Drop Down. Hence the Namelist Parameter dependent on the Month Parameter .
- The Blank Field Name text Search Box further Provides the Client to have a more Refine Search if the Client is not interested scrolling down the Namelist Parameter
and finding the values they are interested in.
See Also
- TechNet Guru Contributions - SQL Server Reporting Services, Power View, & SQL Server Integration Services
- SQL Server Reporting Services Portal