Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Working With Multi-Select Parameters for SSRS Reports

MSSQLTips author Mickey Stuewe By:   |   Read Comments (35)   |   Related Tips: > Reporting Services Development
Problem

Multi-select parameters give your users control over their reports while reducing the number of reports they have to work with. In this example, I will demonstrate how to create a multi-select parameter list and pass the values to a stored procedure that will then populate the report. I will be working with the AdventureWorks2008R2 database to create a report which will list sales quotas and amounts for selected Sales Reps.

Solution

The key to this solution is a delimited list of values that can be passed from the report to a stored procedure. I will be using the comma as a delimiter, but any delimiter will work.

For this example I will be using two parameters. The first parameter will provide a list of Sales Territories. The second parameter will provide a list of Sales Reps based on the selected Sales Territories from the first list. The report will show sales quotas and amounts for the selected Sales Reps.

Step 1

Create a stored procedure that will return a list of Sales Territories. This stored procedure will be used by the first parameter of the report.

  CREATE PROCEDURE dbo.ListSalesTerritory_s 
  AS 
 SET NOCOUNT ON
 SELECT
    TerritoryID
    ,[Name] AS TerritoryName
 FROM
    Sales.SalesTerritory
 ORDER BY 
    [Name]
  
 SET NOCOUNT OFF
  GO

Step 2

Create a second stored procedure that will return a list of Sales Reps for 1 to N Sales Territories. This will be used by the second parameter of the report. The list of selected Sales Territories will be passed to the stored procedure as a comma delimited list of TerritoryIDs in a parameter called @TerritoryID. (If your list of values have commas, then you'll have to use a different delimiter.) The size of the parameter should be determined by the maximum list of values that might be sent to the stored procedure.

There are many different ways to break up a delimited list of values. They are usually referred to as Split Functions. I like to use a recursive CTE (Common Table Express) to split the values up. After the list of values are in a table structure, the table structure can be joined to the rest of the tables needed to return the list of Sales Reps.

CREATE PROCEDURE ListSalesRep_s (@TerritoryIDs AS varchar(100))
 AS 
   SET NOCOUNT ON;
 
   WITH CTE_Pieces
   AS 
   (
      SELECT
         1 AS ID
         ,1 AS StartString
         ,CHARINDEX(',', @TerritoryIDs) AS StopString
 
      UNION ALL
 
      SELECT
         ID + 1
         ,StopString + 1
         ,CHARINDEX(',', @TerritoryIDs, StopString + 1)
      FROM
         CTE_Pieces
      WHERE
         StopString > 0
   )
 
   ,CTE_Split
    AS
   (
      SELECT
         CONVERT(int,SUBSTRING(@TerritoryIDs, StartString,
                                             CASE 
                                                WHEN StopString > 0 THEN StopString - StartString
                                                ELSE LEN(@TerritoryIDs)
                                                END)) AS TerritoryID
      FROM
         CTE_Pieces 
   )    
   SELECT
         P.BusinessEntityID
         ,P.LastName +  ', ' + P.FirstName AS SalesRep
   FROM 
         CTE_Split AS S
         JOIN Sales.SalesPerson AS SP ON sp.TerritoryID = s.TerritoryID
         JOIN Person.Person AS P ON SP.BusinessEntityID = P.BusinessEntityID
 
   SET NOCOUNT OFF

Step 3

Create the stored procedure for the body of the report. In this example, it will also have a parameter, @BusinessEntityIDs that will contain a comma delimited list of selected Sales Reps.

  
  CREATE PROCEDURE dbo.RptSales_s(@BusinessEntityIDs AS varchar(100))
  AS 
    SET NOCOUNT ON;
    WITH CTE_Pieces
    AS 
    (
        SELECT
            1 AS ID
            ,1 AS StartString
            ,CHARINDEX(',', @BusinessEntityIDs) AS StopString
   
        UNION ALL
  
        SELECT
            ID + 1
            ,StopString + 1
            ,CHARINDEX(',', @BusinessEntityIDs, StopString + 1)
        FROM
            CTE_Pieces
        WHERE
            StopString > 0
    )
    ,CTE_Split
    AS
    (
        SELECT
            CONVERT(int,SUBSTRING(@BusinessEntityIDs, StartString, 
                                  CASE 
                                     WHEN StopString > 0 THEN StopString - StartString
                                     ELSE LEN(@BusinessEntityIDs)
                                     END
                                  )
                   ) AS BusinessEntityID
        FROM
            CTE_Pieces 
    ) 
    SELECT
        P.LastName +  ', ' + P.FirstName AS SalesRep
        ,ST.Name AS TerritoryName
        ,ST.CountryRegionCode
        ,SP.SalesQuota
        ,SP.Bonus
        ,SP.SalesYTD
        ,SP.SalesLastYear
    FROM 
        CTE_Split AS s
        JOIN Sales.SalesPerson AS SP ON s.BusinessEntityID = sp.BusinessEntityID
        JOIN Sales.SalesTerritory AS ST ON SP.TerritoryID = ST.TerritoryID
        JOIN Person.Person AS P ON SP.BusinessEntityID = P.BusinessEntityID
  
    SET NOCOUNT OFF
  GO

Step 4

Add the three stored procedures that were created in steps 1 through 3 to a new report. Then create the layout for the report. My report is laid out by grouping the Sales Reps by Territory.

Report Layout
Finished Report

Step 5

Set the Properties of both parameters by right clicking on the parameter and selecting Parameter Properties from the drop down list.

  • Check the Allow multiple values checkbox.
  • Select Available Values from the left hand list.
    • Select the Get values from a query option button.
    • Set the Dataset drop down list to the proper dataset.
    • Set the Value field drop down list to the proper field. This is the value that will be returned to the dataset that needs it.
    • Set the Label field drop down list to the proper field. This is the value that will be displayed to the user.
Parameter Properites
Parameter Properites

Step 6

The list of selected values need to be returned to the ListSalesRep_s and the RptSales_s stored procedures. This will be done using the JOIN expression.

  1. Right click on the stored procedure that needs the list of values and select Dataset Properties from the drop down list.
  2. Select Parameters in the left hand list of the Dataset Properties dialog box.
  3. In this example, the delimited list of Territories need to be assigned to the @TerritoryIDs parameter. Click the function button to enter a function for the parameter value.
  4. Add the following code for the expression. Make sure to use the Value property, not the Label property or the wrong list of values will be sent to the stored procedure.
Parameter Properites

 =Join(Parameters!TerritoryIDs.Value,",")

Step 7

It's time to run your report. When the drop down lists are pulled down, there should be check boxes to select only the rows you want. Each time the selected items are changed in the Territory IDs drop down, the Sales Rep list will automatically be regenerated when the Sales Rep list is pulled down.

Parameter Properites
Parameter Properites

BUT THAT'S NOT ALL...

I think all reports should display the values of the parameters used in the report. This way when someone brings up a concern about a report, you know exactly which parameter values were used.

If you'll notice in the sample below, I listed only the three Territories that were selected, but since all the Sales Reps for those territories are used in the report, the word ALL is used instead of listing each Sales Rep out individually. This can be done with an IIF expression in a Label control.

Parameter Properites

The first parameter of the IIF expression compares the count of how many items are in the parameter list, with how many that were selected. If the two values match, then the label ALL is used. If they don't match, then the JOIN expression is used again, but this time the Labels are joined together. Make sure to include a space after the delimiter so that the values don't run together.

Parameter Properites
Next Steps


Last Update: 1/7/2013


About the author
MSSQLTips author Mickey Stuewe
Mickey Stuewe is a Microsoft Certified Professional in Querying Microsoft SQL Server 2012. She has been with Microsoft development products like SQL Server, SSRS, Excel, and VB since 1995.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, January 07, 2013 - 8:55:48 AM - Henrik Read The Tip

Hi Mickey,

The second and the third storedproc av the same name, is it an ALTER or...?

Kind regards

/Henrik


Monday, January 07, 2013 - 10:45:33 AM - Marlon Ribunal Read The Tip

This is an awesome post Mickey!

We usually call this as Multivalued Parameters. It's good to know that there's a lot of other ways on how to implement Multivalued Parameters.

;-)

 

@MarlonRibunal


Monday, January 07, 2013 - 11:51:43 PM - CK Read The Tip

The 2nd SP name should be ListSalesRept_s


Tuesday, January 08, 2013 - 12:18:12 AM - Mickey Stuewe Read The Tip

Thank you Henrik and CK. The 2nd Stored procedure should be the following: 

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

 

CREATE PROCEDURE ListSalesRep_s (@TerritoryIDs AS varchar(100))

AS 

   SET NOCOUNT ON;

 

   WITH CTE_Pieces

   AS 

   (

      SELECT

         1 AS ID

         ,1 AS StartString

         ,CHARINDEX(',', @TerritoryIDs) AS StopString

 

      UNION ALL

 

      SELECT

         ID + 1

         ,StopString + 1

         ,CHARINDEX(',', @TerritoryIDs, StopString + 1)

      FROM

         CTE_Pieces

      WHERE

         StopString > 0

   )

   ,CTE_Split

   AS

   (

      SELECT

         CONVERT(int,SUBSTRING(@TerritoryIDs, StartString,

                                             CASE 

                                                WHEN StopString > 0 THEN StopString - StartString

                                                ELSE LEN(@TerritoryIDs)

                                                END)) AS TerritoryID

      FROM

         CTE_Pieces 

   )    

   SELECT

         P.BusinessEntityID

         ,P.LastName +  ', ' + P.FirstName AS SalesRep

   FROM 

         CTE_Split AS S

         JOIN Sales.SalesPerson AS SP ON sp.TerritoryID = s.TerritoryID

         JOIN Person.Person AS P ON SP.BusinessEntityID = P.BusinessEntityID

 

   SET NOCOUNT OFF

 


Tuesday, January 08, 2013 - 12:19:10 AM - Mickey Stuewe Read The Tip

Thank you Marlon. 

-Mickey


Wednesday, January 09, 2013 - 3:52:40 PM - Jeremy Kadlec Read The Tip

Mickey,

Thank you for the update.  The tip has been updated.

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, January 18, 2013 - 5:14:09 AM - manu Read The Tip

Hi, Thank You so much for this post. It was one of my last interview question also. Really helpful for me.

Thank You once again.

Manu


Friday, January 18, 2013 - 7:57:43 AM - Naveen Read The Tip

seems a bit complicated for a multi select parameter. I usually create 2 datasets and use the first parameter for the second one like below. this solves my purpose. 

Parameter1 (available values) - select * from salesterritories

Parameter2 (avialble & default values) - select * from salesreps where territoryid in (@Parameter1)

 

 

 

 


Friday, January 18, 2013 - 1:30:02 PM - Jeremy Kadlec Read The Tip

Naveen,

Thank you for the feedback and alternative approach.  I think Mickey was trying to outline an option she had had success with in her development efforts.

Thank you,
Jeremy Kadlec
Community Co-Leader


Monday, January 21, 2013 - 2:13:17 AM - Davos Read The Tip

Wow Mickey this is very advanced stuff, I'm sure it will help intermediate users though. 

I've done something similar using a scalar function to get the comma separated valued from a list. As was already posted, there are easy ways to do this but they certainly don't apply to stored procs so if you are you need to worry about this.

You come up against problems trying to do that against Oracle client or ODBC connections which I've written about here

A few points:

The input parameter for your proc is a varchar(100) ? That limits how many territories you can select.

Calling the territory names "Territory ID" is misleading, these aren't IDs they are names or labels, but that's a philosophical argument so just ignore me ;P

The recursive CTE you are using doesn't specify a limit so it will use the default limit which is 100 rows - this inbuilt limit of CTE prevents the recursion looping infinitely, although your where condition should prevent that anyway when it finds the last comma.


Tuesday, January 22, 2013 - 5:49:02 PM - JohnF Read The Tip

I took the same approach using Join with comma (",") as the delimiter, but it turns out we have a lot of client data that has comma's already in it. Guess what happens??

So basically I came up with an approach to use an obsure delimiter for Join ("~"), and then doing an outer Replace on the joined string using the comma (",").

When you have a value such as "Company Name, Inc.", your resulting Join just using comma (",") would be:

     'Name of Company 1','Name of Company 2','Company Name','Inc',....

Using a differenct Join delimiter ("~"), you would get

     'Name of Company 1'~'Name of Company 2'~'Company Name,Inc'~'....

You can then use Replace to replace the ~ with a comma so you get this...

     Name of 'Company 1','Name of Company 2','Company Name,Inc',....

Hope that might save someone some grief :)


Wednesday, January 23, 2013 - 11:43:26 PM - Mickey Stuewe Read The Tip

Hi Davos,

Thanks for your great comments and questions. I thought about addressing them when I wrote the article, but I wanted to limit the scope of the article to the multi-select parameter. 

In my environment at work we use a scalar table function to split the string up. I did not use that technique here, because I didn't want to address the performance implications of use user defined functions. 

I also didn't want to get into a discussion of varchar(max) and the varchar(100) was plenty for returning integers for this database. We do use a varchar(max) in my environment at work. 

I do apologize for the confusion of the label. 

And finally, the recursive CTE. Yes there is default a limit of 100 loops, but you can override it with a query hint "OPTION (MAXRECURSION X)" where X can be 0 to 32,767. The query hint is added at the end of the SELECT statement. My split function uses 4000. Why? When I created the table function, we could not pass a varchar(max) to a function so we used varchar(8000). This meant the maximum number of loops I would need was 4000. I haven't changed it, because if more than 4000 values need to be split, I would want to consider a redesign of the approach being taken for that code base.

Thanks again for your comments. 

 

Mickey


Wednesday, January 23, 2013 - 11:50:13 PM - Mickey Stuewe Read The Tip

Hi JonF

Another way to approach the solution, would be to pass the primary key IDs instead of the company names. That way you don't need to address the special characters. This will also cut down on the size of the string that is passed to the stored procedure. In step 5, I use the primary key for the Value field and the Territory Name for the label. I then join the selected Values to create a string of delimited primary keys. 

Thank you for pointing out this crucial design element that needs to be considered strings are being manipulated. 

Mickey


Tuesday, February 26, 2013 - 10:22:44 AM - Bob Armstrong Read The Tip

Mickey,

I'm having a problem using a subreport inside a Master page.  My Master page has 4 parameters with one parameter that varies.

The one subreport inside the Master page will take 3 parameters that are constant and the 4th parameter will change by way of a loop.  The output will create many reports generated from the 4 parameter.

How do you create a loop from the Master page changing the value of the 4th parameter to run the subreport as many times as there are values in the 4 parameter?

 

 

 


Thursday, February 28, 2013 - 12:23:57 AM - Mickey Stuewe Read The Tip

Bob, 

What you can do is create a table on your master report that only contains the subreport in a cell in the detail row. This "outer table" would have a row for each value of your 4th parameter. You would then pass the value from the row (something like =Fields!YourFieldname.Value) to your fourth parameter. If the subreport only appears once per page, then you can set the page break to occur after each row. 

In my experience subreports are very costly. Depending on what you are having the subreport do, you might be able to use a combination of a table control  and a list control. The Table control would be the outer table like I explained above, and the list control would allow you to lay out all the data elements you had in your subreport. The List control would then nest inside the table control. I use this technique in lieu of a sub report 99% of the time. 

Let me know if you have any more questions. I'm happy to help.

Mickey


Thursday, February 28, 2013 - 8:44:07 AM - Bob Armstrong Read The Tip

Hi Mickey,

Thanks for replying back.

I need to do something like this:

Header 1
Header 2
Header 3
Header 4 Contains State Name
Header 5

Detail 1 Widget Name    Cost   City
Detail 2 Widget Name    Cost   City
Detail 3 Widget Name    Cost   City
Detail 4 Widget Name    Cost   City
Detail 5 Widget Name    Cost   City
Detail 6 Widget Name    Cost   City

Page break on State Name

Header 1
Header 2
Header 3
Header 4 Contains State Name
Header 5

Detail 1 Widget Name    Cost   City
Detail 2 Widget Name    Cost   City
Detail 3 Widget Name    Cost   City
Detail 4 Widget Name    Cost   City
Detail 5 Widget Name    Cost   City
Detail 6 Widget Name    Cost   City
Detail 7 Widget Name    Cost   City
Detail 8 Widget Name    Cost   City
Detail 9 Widget Name    Cost   City
Detail 10 Widget Name   Cost   City
Detail 11 Widget Name   Cost   City

Page break on State Name

Header 1
Header 2
Header 3
Header 4 Contains State Name
Header 5

Detail 1 Widget Name    Cost   City
Detail 2 Widget Name    Cost   City
Detail 3 Widget Name    Cost   City
Detail 4 Widget Name    Cost   City


Page break on State Name

And it continues


Thursday, February 28, 2013 - 10:29:35 PM - Mickey Stuewe Read The Tip

Hi Bob,

You can do that by following these steps.

1. Put a Table control on your page butting up to the header at the top.

2. Set the table to your dataset that contains your widgets

3. Set the detail row too your Widgets data(Detail 1 Widget Name    Cost   City).

4. Group by the State Name. Make sure to check the box that asks if you want a header row.

4.a A new column will be added. Delete it. You don't need it.

5. In the properties of the Grouping row, set "break between each instance of a group"

6. There is a triangle (drop down menu) to the right of the text "Column Groups" at the bottom of IDE. Click it and select "Advanced Mode". This will expose some new rows in the "Row Groups" and "Column Groups" area. They all say static. These rows represent the header rows that are displayed in the table. 

7. Select the "Static" row in the "Row Groups" that is right above the detail row and go to the property window. Set the following properties.

7.a RepeatOnNewPage = True

7.b KeepWithGroup = After

Note: If you are going to keep any of the other static rows in "Row Groups" that are above the one whose properties you just set, then you need to set their properties as well. 

 

I'm sorry there aren't any pictures to help with this. Good luck. Let me know if there are any other questions. I can work with MSSQLTips.com to get some images loaded for you.

 

Mickey


Friday, March 01, 2013 - 1:12:21 PM - Bob Armstrong Read The Tip

Mickey,

It worked fine.... Thank you so much.

I'm now trying to figure out how to put the State Name on the Excel Tabs when exported.  The Tablix Page Name only gives the first State and then State(1), State(2), State(3), etc...

Patrick LeBlanc did it on his example at the site http://www.bidn.com/blogs/PatrickLeBlanc/ssis/762/ssrs-r2-naming-excel-worksheet-tabs, but I cannot recreate the same work on a new project.  I was able to get his example to work. 


Tuesday, April 02, 2013 - 10:08:45 AM - Bob Armstrong Read The Tip

Mickey,

I finally resolved my issue with the FieldName.Value issue that worked on the tabs, but would not work properly in the Tablix header.

The solution was to create a child group from the parent group and then I could reference that Field value.

Here is an example: The Parent Group is StateName. To have the StateName go into the Tablix header and change in each tab, I created a Child Group StateName1 both in the report query and in the Child Group. Now it works.


Wednesday, April 03, 2013 - 2:38:23 PM - Kajan Read The Tip

Hi All, 

Does anyone know how to set a checkbox defaulted to "Checked"?

 

 


Thursday, April 04, 2013 - 6:42:26 AM - Bob Armstrong Read The Tip

Hi Kajan,

I just tried making a Parameter value Checked by default.

Click View, then Report Data.  Right click a Parameter value and then click Report Parameter Properties.

Click Default Values and then click the Radio Button "Specify Value", then click Add.

The value of the check box goes into the input box and then click okay.


Tuesday, July 23, 2013 - 5:34:02 AM - Khwaza Read The Tip

Hi Mickey,

In the report parameter, I have 3-multi value drop-down select values are there with unchecked. In these 3-value I want to set first  2-values to default value (i.e. check box should be selected) and last value should be un-checked in default condition. Let me know this can be possible in SSRS.


Thursday, August 22, 2013 - 12:36:29 PM - Brian Read The Tip

Hi Mickey,

 

We recently upgraded our SSRS instance from 2008 to 2012.  After the upgrade any reports with a multi-select list no longer function correctly.   When the user selected the parameters for the report then presses view report - it just posts back with no execution or even an attempt at execution - just your normal .net postback.

 

Any idea what is going on here in 2012?   I have had very little luck searching for a soltuion online.


Wednesday, December 18, 2013 - 8:13:22 PM - Jason Williams Read The Tip

Great Article! I emphatically agree with your comments about parameters always appearing on reports. I had something very similar going with showing values of multi-select parameters on the report and was looking for a way to display "ALL". Your final tip is just what I needed. Thanks!


Tuesday, December 24, 2013 - 2:36:47 PM - Tim Read The Tip

Hi Mickey,

Thanks for the post. I have a question that maybe you can help with, since I haven't been able to find anything online anywhere else. I am doing something similar to what you are doing here. Howvever, I need to take it one step further. I have a multi-select parameter with a large list of values. The reporting screen used to build the report has a "Select All" option in the list. However, this report is displayed in a webbased UI and the control used to display the list of values does not have a "Select All" option just yet. We don't want the user to have to select or unselect the individual items when checking or unchecking all items. So I want to use a radio button that lets the user to check or uncheck all items in the list.

So my question, is there a way to programmatically check or uncheck all items in a multi-select parameter? Nothing jumps out at me when I look at my options and I've done plenty of searching online about this.

I appreciuate any feedback you have regarding this issue.

 

Thanks

Tim


Wednesday, January 01, 2014 - 8:45:13 PM - Mickey Stuewe Read The Tip

hi Khwaza,

 

I appologize for not seeing your question until now. You have hopefully found the answer. If you haven't, then the answer is below.

 

There are two ways to set defaults for a multiselect parameter.

  1. Go into the properties for the parameter and select Default Values from the list on the left.
  2. The second option in the list, "Specify Values" will allow you to hard code values. 
    1. Click the Add button to add a row to the value list.
    2. Put a value in each row.
  3. The thrid option "Get Values From a Query" will allow you to get a list from the database. This is my prefered method.
    1. Add the dataset that has only the values you want to be marked as selected to the Dataset property
    2. Add the field with the default values to the Value Field property.
Mickey

 


Wednesday, January 01, 2014 - 8:46:18 PM - Mickey Stuewe Read The Tip

hi Brian,

 

I'm sorry I didn't see your comment until now. Are you still having issues with the multiselect? If you are, please let me know some more details.

 

thanks,

Mickey


Wednesday, January 01, 2014 - 8:46:53 PM - Mickey Stuewe Read The Tip

Thanks Jason!

 

Mickey


Thursday, January 02, 2014 - 11:50:39 PM - Mickey Stuewe Read The Tip

hi Tim,

 

There are two ways you can do this. The first solution involves adding a "Select All" row to your stored procedure and handling that option in the stored procedure that generates the report. The second solution involves a raido button, drop down list, or check box like you suggested, but it does involve an additional call to the in the middle of the parameter selection process for the report. 

 

Solution 1.

1. Add a row using the UNION ALL statement in the stored procedure that returns your list of values. Also, include a fake sort column so that the "Select All" always appears at the top. Note: Use UNION ALL whenever you don't need to have duplicates removed from the second select statement.

Example code:

SELECT

ID

,MyValue

,1 as MySort

FROM

ListTable

UNION ALL

SELECT

-1

,'Select All'

,0

ORDER BY

MySort

,MyValue

Note: Make sure that the "ID" for the "Select All" is not a real value in your real dataset. 

2. In your stored procedure that returns the report data, use a technique that will return some or all values. 

a. I use a split function to split my list up, but you need to also see if the  -1 was returned. My list of values is in @List. If -1 was passed in, then I'll select the full list from the master list table. Otherwise I will only return the values they selected and returned in a comma seperated list in the parameter @List.

 

IF (PATINDEX('-1',@list) = 0)

SELECT

ID

FROM

ListTable

INTO

#MyList

ELSE

SELECT

SeperatedValue

INTO

#MyList

FROM

dbo.fn_Split(',', @list) AS fs

 

Solution 2.

  1. Create a control that will determine if the list is selected or unselected. Have it return 1 or 0 respectively.
  2. Add a Parameter to the stored procedure that return your list of values. We'll call it @SelectAll
  3. Add the stored procedure twice to your project. 
      1. The first time will have the list of Available values and the @SelectAll will always be 1. Will call this SprocA
      2. The second time will have the list of default values (the selected list). This list will be controled with the value from the new control (step 1) as the value for @SelectAll. Each time the control from step 1 is changed, this stored procedure will be executed and will reoppulate the list control. Tieing the control to the procedure is done on the Will call this SprocB
  4. In the properties for the list control
    1. Use Sproc A on the Available Values properties. 
    2. Use Sproc B on the Default Values properties.
 
Note: this solution can affect the performance of the report if the stored procedure that populate the list control takes a long time to run. 
 
If you have any other questions, please let me know.
 
Mickey

Tuesday, January 07, 2014 - 8:32:53 PM - Vignesh Read The Tip

Hi Mickey,

 

I have hit into a roadblock in my report where in which i need to populate multiple dropdown when i select a dropdown in the report.

 

The below is the scenario

DL1: --Values-- 

Dl2: --Values-- 

Dl3: --Values-- 

Dl4: --Values-- 

 

On selecting the values of DL1, i need to update all the values in Dl2,Dl3,Dl4. All the dropdowns are dynamic and get values from a dataset with defaul value set to the same dataset.

Here Dl2 is dependent on dl1 and dl3 is dependent on dl2 and dl4 is dependent on dl3.

 

Its easier for me to populate the dropdown list based on the previous selection, but the way to populate multiple drop down list looks a bit complicated to me.

 

Any help in this regard would be appreciable.


Monday, January 13, 2014 - 10:11:33 PM - Mickey Stuewe Read The Tip

hi Vignesh,

You are almost there. Steps 6 and 7 are doing exactly what you need. In your case, you will use the selected values from DL1 in the JOIN function and pass that value to the stored procedure for DL2 through it's Parameter. Continue the process for DL3 and DL4. When looking at the code above, ListSalesRep_s would be DL1 and RptSales_s would be DL2. Then continue the pattern for the other two.

 

Mickey


Monday, January 13, 2014 - 10:13:22 PM - Mickey Stuewe Read The Tip

Vignesh, 

 

I have a type-o in my reply. DL2 would be like ListSalesTerritory_s. 

 

Mickey


Tuesday, January 14, 2014 - 9:04:45 PM - Vignesh Read The Tip

Mickey, Thanks for your reply. I achieved the same using the stored procedure to load the drop downs either by the returned value or by default with an -ALL- value and generate the report.


Friday, January 24, 2014 - 12:30:32 AM - govind Read The Tip

Thanks, Excelent stuff.

 

Govindu


Monday, March 24, 2014 - 12:52:18 PM - Kris Read The Tip

Awesome post



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.