SSRS CASCADING PARAMETER

Question

I have two parameters @Branch & @Teller.

What I would like to do is; whatever @Branch parameter is selected, I would like my @Teller parameter to be populated based on @Branch parameter.

For @Branch parameter, I have set the Report Parameter Properties as

                General

                                Name: Branch

                                Prompt: Branch

                                Data Type: Text

                                Allow Multiple Values

                                Visible

                Available Values

                                Get values from a query

                                Dataset: Branch

                                Value Field: BranchID

                                Label Field: Branch

                Default Values

                                Get values from a query

                                Dataset: Branch

                                Value Field: BranchID

 

For @Teller parameter, I have set the Report Parameter Properties as

                General

                                Name: Teller

                                Prompt: Teller

                                Data Type: Text

                                Allow Multiple Values

                                Visible

                Available Values

                                Get values from a query

                                Dataset: Teller

                                Value Field: TellerNumber

                                Label Field: LastName

                Default Values

                                Get values from a query

                                Dataset: Teller

                                Value Field: TellerNumber

 

In my Teller dataset, my query looks like the following:

 

                SELECT

                [Name]

                ,LastName

                ,[TellerNumber]

                                 FROM [ARCUSYM000].[cu].[ActiveDirectoryUsers] u

                                  WHERE Terminated = 0

                                                AND Department IN (@Branch)                                                           

                                ORDER BY u.LastName

 

What is happening now when I run the SSRS Report from Report Builder,

Branch parameter is populated with all branches selected which is what I expected.

My Teller parameter is NOT populated with any values.

Any help is appreciated.

Thank you

Answer 1

Hi,

As your Branch parameter allows multiple values, you need to change your Teller dataset query to handle that.

See my response in this thread - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b5410cd2-65c7-48ac-98d1-3ace09ba0792/could-not-update-a-list-of-fields-for-the-query?forum=sqlreportingservices

---

sathya - My blog ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 2

make your query like

SELECT

                [Name]

                ,LastName

                ,[TellerNumber]

                                 FROM [ARCUSYM000].[cu].[ActiveDirectoryUsers] u

                                  WHERE Terminated = 0

                                                AND ',' + @Branch + ',' LIKE  '%,' + Department + ',%'                                                          

                                ORDER BY u.LastName

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page