Cascading drop-downs

  • Hi,

    SSRS 2008

    I have created a report that contains a cascading drop-down. My problem is, when selections are changed/updated

    on the parent (Customer Stopped Status) drop-down I need all of the resulting child (Customer Account) drop-down to be selected.

    At present, only previously selected items are selected if the parent selection alters.

    I have included objects/items:

    SQL table:

    USE [myDB]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[myTable](

    [AccountNum] [nvarchar](20) NOT NULL,

    [Name] [nvarchar](60) NOT NULL,

    [Blocked] [int] NOT NULL,

    [CompanyId] [nvarchar](3) NOT NULL

    ) ON [PRIMARY]

    GO

    --[AccountNum], [Name], [Blocked], [CompanyId]

    INSERT dbo.myTable

    VALUES('1001bn', 'Customer A', 0, 'xxx');

    INSERT dbo.myTable

    VALUES('1002bn', 'Customer B', 0, 'xxx');

    INSERT dbo.myTable

    VALUES('1003bn', 'Customer C', 1, 'xxx');

    INSERT dbo.myTable

    VALUES('1004bn', 'Customer D', 1, 'xxx');

    INSERT dbo.myTable

    VALUES('1005bn', 'Customer E', 2, 'xxx');

    INSERT dbo.myTable

    VALUES('1006bn', 'Customer F', 2, 'xxx');

    INSERT dbo.myTable

    VALUES('1007bn', 'Customer G', 3, 'xxx');

    INSERT dbo.myTable

    VALUES('1008bn', 'Customer H', 3, 'xxx');

    INSERT dbo.myTable

    VALUES('1009bn', 'Customer I', 4, 'xxx');

    INSERT dbo.myTable

    VALUES('1010bn', 'Customer J', 4, 'xxx');

    Report items:

    Dataset 1: dslk_CustomerStoppeedStatus

    Text:

    SELECT 0 AS Type, 'Reason 1' AS Description

    Union

    Select 1 AS Type, 'Reason 2' AS Description

    Union

    SELECT 2 AS Type, 'Reason 3' AS Description

    Union

    SELECT 3 AS Type, 'Reason 4' AS Description

    Union

    SELECT 4 AS Type, 'Reason 5' AS Description

    Dataset 2: dslk_CustomerStoppedStatus_Default

    Text:

    SELECT 0 AS Type, 'Reason 1' AS Description

    Union

    Select 1 AS Type, 'Reason 2' AS Description

    Union

    SELECT 2 AS Type, 'Reason 3' AS Description

    Union

    SELECT 3 AS Type, 'Reason 4' AS Description

    Dataset 3: dslk_CustomerAccount

    Text:

    SELECT DISTINCT AccountNum, AccountNum + ', ' +[Name] AS [Name]

    FROM dbo.myTable

    WHERE Blocked IN (@CustomerStoppedStatus)

    AND CompanyID = @CompanyID

    Parameter 1: @CompanyID

    Data type: Text

    (Hidden)

    Default value of 'xxx'

    Parameter 2: @CustomerStoppedStatus

    Data type: Text

    Allow Multiple values

    Visible

    Available Values

    Dataset: dslk_CustomerStoppedStatus

    Value field: Type

    Label field: Description

    Default Values

    Dataset: dslk_CustomerStoppedStatus_Default

    Value field: Type

    Parameter 3: @CustomerAccount

    Data type: Text

    Allow Multiple values

    Visible

    Available Values

    Dataset: dslk_CustomerAccount

    Value field: AccountNum

    Label field: Name

    Default Values

    Dataset: dslk_CustomerAccount

    Value field: AccountNum

    Any ideas how I can refresh the child list so that all items are always selected (ticked)?

    Thank you in advance,

  • I have created a report that contains a cascading drop-down. My problem is, when selections are changed/updated

    on the parent (Customer Stopped Status) drop-down I need all of the resulting child (Customer Account) drop-down to be selected.

    You would handle this in the default value for each child parameter. Make the default value the same as the available values. I hope that makes sense, if not let me know and I can put together a more detailed explanation with screenshots.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • You would handle this in the default value for each child parameter. Make the default value the same as the available values

    Hi Alan,

    Thank you for your reply, I appreciate your response.

    The thing is... The requirement specifies that the Available vales are different from the Default values, that is why I created a different dataset!!!

    i.e. The available values are 0,1,2,3,4 whereas the default should only include 0,1,2,3 (4 is NOT required as a default)

    Unless I'm not reading your explanation correctly, it doesn't make sense that the values have to be the same in each. If that is the case, why bother having BOTH the Available and Default options?

    Sorry to reiterate but only the previously selected child records remain 'ticked', yet (in this instance) by additionally selecting '4' to the Parent, the relivent Customer I and Customer J records are un-ticked although they would now appear (since the update) in the child drop-down

    Regards and Thanks again,

  • DerbyNeal (2/25/2015)


    You would handle this in the default value for each child parameter. Make the default value the same as the available values

    Hi Alan,

    Thank you for your reply, I appreciate your response.

    The thing is... The requirement specifies that the Available vales are different from the Default values, that is why I created a different dataset!!!

    i.e. The available values are 0,1,2,3,4 whereas the default should only include 0,1,2,3 (4 is NOT required as a default)

    Unless I'm not reading your explanation correctly, it doesn't make sense that the values have to be the same in each. If that is the case, why bother having BOTH the Available and Default options?

    Sorry to reiterate but only the previously selected child records remain 'ticked', yet (in this instance) by additionally selecting '4' to the Parent, the relivent Customer I and Customer J records are un-ticked although they would now appear (since the update) in the child drop-down

    Regards and Thanks again,

    My apologies, I misunderstood your requirement. I don't have a solution :(. The only way I have seen this thing handled is by handling this outside of SSRS via .NET web form.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply