Data driven subscription use 'select all' default option and avoid '' in input parameter

SSAS and SSRS
1

Hi,
I have report that has input parameter with region. So when user go to URL, they can run the report manually with region but when it comes to Data Driven subscription then my requirement is it run with default as "All" Region.
Currently I am succeeded with using UNION ALL select '' in Region dataset. But that shows '' in input drop down as well.
Is any other way that avoid showing '' in input drop down?

T.I.A

2

It's been a while since I worked in SSRS but I did similar things like this. The code below illustrates the whole thing.

The drop-down input value would need to have the two values from the SELECT statement (below first comments) and it would use RegionTypeId as the parameter value and RegionTypeDescription as the display text. That should get you what you need or far enough along to see the next steps.

Hope it helps!

IF OBJECT_ID('dbo.RegionType') IS NOT NULL DROP TABLE dbo.RegionType;

CREATE TABLE dbo.RegionType
(
RegionTypeId INT NOT NULL IDENTITY(1,1),
RegionTypeDescription VARCHAR(32) NOT NULL,
CONSTRAINT pkRegionTypeRegionTypeId PRIMARY KEY (RegionTypeId),
CONSTRAINT ukRegionTypeRegionTypeDescription UNIQUE (RegionTypeDescription)
);
INSERT INTO dbo.RegionType (RegionTypeDescription)
SELECT 'West' UNION ALL
SELECT 'East' UNION ALL
SELECT 'North' UNION ALL
SELECT 'South';

/* This would give you the drop-down list to select from in SSRS */

SELECT NULL As RegionTypeId, 'All Regions' As RegionTypeDescription
UNION ALL
SELECT RegionTypeId, RegionTypeDescription
FROM dbo.RegionType
ORDER BY 1
GO

IF OBJECT_ID('dbo.get_RegionData', 'P') IS NOT NULL DROP PROCEDURE dbo.get_RegionData;
GO
CREATE PROCEDURE dbo.get_RegionData
(
@RegionTypeId INT = NULL
)
AS
BEGIN

SELECT rt.RegionTypeDescription, rt.RegionTypeId
FROM dbo.RegionType As rt
/*
This makes the procedure able to use a specific value or NULL for all
Note that this is not the best option for index usage and performance.
*/
WHERE rt.RegionTypeId = COALESCE(@RegionTypeId, rt.RegionTypeId);

END;
GO

EXEC dbo.get_RegionData @RegionTypeId = 1;
EXEC dbo.get_RegionData @RegionTypeId = 3;
EXEC dbo.get_RegionData @RegionTypeId = NULL;

3

But we have already "Select All" as option in drop down. As per your code we will get two Select All values
Select All
All Regions

T.I.A

4

Where are those two values coming from? Are they from a table or defined as values in the list of available values when the parameter was setup? What does the SELECT statement look like that is getting the values for the drop-down select list?

5

Is the parameter setup as a "Select Multiple Values" as in the example on this link: https://www.mssqltips.com/sqlservertip/2844/working-with-multiselect-parameters-for-ssrs-reports/

?

That would explain it. If that is the case you can use the example on that page to show that "all" are selected by using an expression on the report.

6

Is the parameter setup as a "Select Multiple Values" as in the example on this link: - Yes

But how do I pass "All" from data driven subscription?

I have developed my report using below ref link

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b1c828ba-b3d7-4e32-a327-5f334972a658/data-driven-subscription-and-select-all-option-with-multiple-value-parameter?forum=sqlreportingservices&prof=required

T.I.A