February 24, 2015 at 1:15 pm
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,
February 24, 2015 at 3:39 pm
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.
-- Itzik Ben-Gan 2001
February 25, 2015 at 2:41 am
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,
February 25, 2015 at 6:28 pm
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.
-- 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