none
Optional parameters in SSRS report RRS feed

  • Question

  • Hi Team,

    Have a requirement like bellow 

    I have 3 parameters like CustomerKey, FirstName and EmailAddress.

    I want to pass either one parameter while fetching data.

    for example am passing only CustomerKey is 11000 and am getting those records.

    Sample data:

    USE [AdventureWorksDW2016]
    GO
    /****** Object:  Table [dbo].[DimCustomer]    Script Date: 11-04-2019 13:23:57 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[DimCustomer](
    [CustomerKey] [int] IDENTITY(1,1) NOT NULL,
    [GeographyKey] [int] NULL,
    [CustomerAlternateKey] [nvarchar](15) NOT NULL,
    [Title] [nvarchar](8) NULL,
    [FirstName] [nvarchar](50) NULL,
    [MiddleName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [NameStyle] [bit] NULL,
    [BirthDate] [date] NULL,
    [MaritalStatus] [nchar](1) NULL,
    [Suffix] [nvarchar](10) NULL,
    [Gender] [nvarchar](1) NULL,
    [EmailAddress] [nvarchar](50) NULL,
    [YearlyIncome] [money] NULL,
    [TotalChildren] [tinyint] NULL,
    [NumberChildrenAtHome] [tinyint] NULL,
    [EnglishEducation] [nvarchar](40) NULL,
    [SpanishEducation] [nvarchar](40) NULL,
    [FrenchEducation] [nvarchar](40) NULL,
    [EnglishOccupation] [nvarchar](100) NULL,
    [SpanishOccupation] [nvarchar](100) NULL,
    [FrenchOccupation] [nvarchar](100) NULL,
    [HouseOwnerFlag] [nchar](1) NULL,
    [NumberCarsOwned] [tinyint] NULL,
    [AddressLine1] [nvarchar](120) NULL,
    [AddressLine2] [nvarchar](120) NULL,
    [Phone] [nvarchar](20) NULL,
    [DateFirstPurchase] [date] NULL,
    [CommuteDistance] [nvarchar](15) NULL,
     CONSTRAINT [PK_DimCustomer_CustomerKey] PRIMARY KEY CLUSTERED 
    (
    [CustomerKey] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[DimCustomer] ON 
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11000, 26, N'AW00011000', NULL, N'Jon', N'V', N'Yang', 0, CAST(N'1971-10-06' AS Date), N'M', NULL, N'M', N'jon24@adventure-works.com', 90000.0000, 2, 0, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'1', 0, N'3761 N. 14th St', NULL, N'1 (11) 500 555-0162', CAST(N'2011-01-19' AS Date), N'1-2 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11001, 37, N'AW00011001', NULL, N'Eugene', N'L', N'Huang', 0, CAST(N'1976-05-10' AS Date), N'S', NULL, N'M', N'eugene10@adventure-works.com', 60000.0000, 3, 3, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'0', 1, N'2243 W St.', NULL, N'1 (11) 500 555-0110', CAST(N'2011-01-15' AS Date), N'0-1 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11002, 31, N'AW00011002', NULL, N'Ruben', NULL, N'Torres', 0, CAST(N'1971-02-09' AS Date), N'M', NULL, N'M', N'ruben35@adventure-works.com', 60000.0000, 3, 3, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'1', 1, N'5844 Linden Land', NULL, N'1 (11) 500 555-0184', CAST(N'2011-01-07' AS Date), N'2-5 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11003, 11, N'AW00011003', NULL, N'Christy', NULL, N'Zhu', 0, CAST(N'1973-08-14' AS Date), N'S', NULL, N'F', N'christy12@adventure-works.com', 70000.0000, 0, 0, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'0', 1, N'1825 Village Pl.', NULL, N'1 (11) 500 555-0162', CAST(N'2010-12-29' AS Date), N'5-10 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11004, 19, N'AW00011004', NULL, N'Elizabeth', NULL, N'Johnson', 0, CAST(N'1979-08-05' AS Date), N'S', NULL, N'F', N'elizabeth5@adventure-works.com', 80000.0000, 5, 5, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'1', 4, N'7553 Harness Circle', NULL, N'1 (11) 500 555-0131', CAST(N'2011-01-23' AS Date), N'1-2 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11005, 22, N'AW00011005', NULL, N'Julio', NULL, N'Ruiz', 0, CAST(N'1976-08-01' AS Date), N'S', NULL, N'M', N'julio1@adventure-works.com', 70000.0000, 0, 0, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'1', 1, N'7305 Humphrey Drive', NULL, N'1 (11) 500 555-0151', CAST(N'2010-12-30' AS Date), N'5-10 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11006, 8, N'AW00011006', NULL, N'Janet', N'G', N'Alvarez', 0, CAST(N'1976-12-02' AS Date), N'S', NULL, N'F', N'janet9@adventure-works.com', 70000.0000, 0, 0, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'1', 1, N'2612 Berry Dr', NULL, N'1 (11) 500 555-0184', CAST(N'2011-01-24' AS Date), N'5-10 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11007, 40, N'AW00011007', NULL, N'Marco', NULL, N'Mehta', 0, CAST(N'1969-11-06' AS Date), N'M', NULL, N'M', N'marco14@adventure-works.com', 60000.0000, 3, 3, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'1', 2, N'942 Brook Street', NULL, N'1 (11) 500 555-0126', CAST(N'2011-01-09' AS Date), N'0-1 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11008, 32, N'AW00011008', NULL, N'Rob', NULL, N'Verhoff', 0, CAST(N'1975-07-04' AS Date), N'S', NULL, N'F', N'rob4@adventure-works.com', 60000.0000, 4, 4, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'1', 3, N'624 Peabody Road', NULL, N'1 (11) 500 555-0164', CAST(N'2011-01-25' AS Date), N'10+ Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11009, 25, N'AW00011009', NULL, N'Shannon', N'C', N'Carlson', 0, CAST(N'1969-09-29' AS Date), N'S', NULL, N'M', N'shannon38@adventure-works.com', 70000.0000, 0, 0, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'0', 1, N'3839 Northgate Road', NULL, N'1 (11) 500 555-0110', CAST(N'2011-01-27' AS Date), N'5-10 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11010, 22, N'AW00011010', NULL, N'Jacquelyn', N'C', N'Suarez', 0, CAST(N'1969-08-05' AS Date), N'S', NULL, N'F', N'jacquelyn20@adventure-works.com', 70000.0000, 0, 0, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'0', 1, N'7800 Corrinne Court', NULL, N'1 (11) 500 555-0169', CAST(N'2011-01-14' AS Date), N'5-10 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11011, 22, N'AW00011011', NULL, N'Curtis', NULL, N'Lu', 0, CAST(N'1969-05-03' AS Date), N'M', NULL, N'M', N'curtis9@adventure-works.com', 60000.0000, 4, 4, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Professional', N'Profesional', N'Cadre', N'1', 4, N'1224 Shoenic', NULL, N'1 (11) 500 555-0117', CAST(N'2010-12-30' AS Date), N'10+ Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11012, 611, N'AW00011012', NULL, N'Lauren', N'M', N'Walker', 0, CAST(N'1979-01-14' AS Date), N'M', NULL, N'F', N'lauren41@adventure-works.com', 100000.0000, 2, 0, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Management', N'Gestión', N'Direction', N'1', 2, N'4785 Scott Street', NULL, N'717-555-0164', CAST(N'2013-03-16' AS Date), N'1-2 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11013, 543, N'AW00011013', NULL, N'Ian', N'M', N'Jenkins', 0, CAST(N'1979-08-03' AS Date), N'M', NULL, N'M', N'ian47@adventure-works.com', 100000.0000, 2, 0, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Management', N'Gestión', N'Direction', N'1', 3, N'7902 Hudson Ave.', NULL, N'817-555-0185', CAST(N'2013-04-13' AS Date), N'0-1 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11014, 634, N'AW00011014', NULL, N'Sydney', NULL, N'Bennett', 0, CAST(N'1973-11-06' AS Date), N'S', NULL, N'F', N'sydney23@adventure-works.com', 100000.0000, 3, 0, N'Bachelors', N'Licenciatura', N'Bac + 4', N'Management', N'Gestión', N'Direction', N'0', 3, N'9011 Tank Drive', NULL, N'431-555-0156', CAST(N'2013-03-23' AS Date), N'1-2 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11015, 301, N'AW00011015', NULL, N'Chloe', NULL, N'Young', 0, CAST(N'1984-08-26' AS Date), N'S', NULL, N'F', N'chloe23@adventure-works.com', 30000.0000, 0, 0, N'Partial College', N'Estudios universitarios (en curso)', N'Baccalauréat', N'Skilled Manual', N'Obrero especializado', N'Technicien', N'0', 1, N'244 Willow Pass Road', NULL, N'208-555-0142', CAST(N'2013-01-18' AS Date), N'5-10 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11016, 329, N'AW00011016', NULL, N'Wyatt', N'L', N'Hill', 0, CAST(N'1984-10-25' AS Date), N'M', NULL, N'M', N'wyatt32@adventure-works.com', 30000.0000, 0, 0, N'Partial College', N'Estudios universitarios (en curso)', N'Baccalauréat', N'Skilled Manual', N'Obrero especializado', N'Technicien', N'1', 1, N'9666 Northridge Ct.', NULL, N'135-555-0171', CAST(N'2013-02-09' AS Date), N'5-10 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11017, 39, N'AW00011017', NULL, N'Shannon', NULL, N'Wang', 0, CAST(N'1949-12-24' AS Date), N'S', NULL, N'F', N'shannon1@adventure-works.com', 20000.0000, 4, 0, N'High School', N'Educación secundaria', N'Bac + 2', N'Skilled Manual', N'Obrero especializado', N'Technicien', N'1', 2, N'7330 Saddlehill Lane', NULL, N'1 (11) 500 555-0195', CAST(N'2011-01-12' AS Date), N'5-10 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11018, 32, N'AW00011018', NULL, N'Clarence', N'D', N'Rai', 0, CAST(N'1955-10-06' AS Date), N'S', NULL, N'M', N'clarence32@adventure-works.com', 30000.0000, 2, 0, N'Partial College', N'Estudios universitarios (en curso)', N'Baccalauréat', N'Clerical', N'Administrativo', N'Employé', N'1', 2, N'244 Rivewview', NULL, N'1 (11) 500 555-0137', CAST(N'2011-01-17' AS Date), N'5-10 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11019, 52, N'AW00011019', NULL, N'Luke', N'L', N'Lal', 0, CAST(N'1983-09-04' AS Date), N'S', NULL, N'M', N'luke18@adventure-works.com', 40000.0000, 0, 0, N'High School', N'Educación secundaria', N'Bac + 2', N'Skilled Manual', N'Obrero especializado', N'Technicien', N'0', 2, N'7832 Landing Dr', NULL, N'262-555-0112', CAST(N'2013-02-12' AS Date), N'5-10 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11020, 53, N'AW00011020', NULL, N'Jordan', N'C', N'King', 0, CAST(N'1984-03-19' AS Date), N'S', NULL, N'M', N'jordan73@adventure-works.com', 40000.0000, 0, 0, N'High School', N'Educación secundaria', N'Bac + 2', N'Skilled Manual', N'Obrero especializado', N'Technicien', N'0', 2, N'7156 Rose Dr.', NULL, N'550-555-0163', CAST(N'2012-12-29' AS Date), N'1-2 Miles')
    GO
    INSERT [dbo].[DimCustomer] ([CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance]) VALUES (11021, 536, N'AW00011021', NULL, N'Destiny', NULL, N'Wilson', 0, CAST(N'1984-03-02' AS Date), N'S', NULL, N'F', N'destiny7@adventure-works.com', 40000.0000, 0, 0, N'Partial College', N'Estudios universitarios (en curso)', N'Baccalauréat', N'Skilled Manual', N'Obrero especializado', N'Technicien', N'0', 1, N'8148 W. Lake Dr.', NULL, N'622-555-0158', CAST(N'2013-01-23' AS Date), N'1-2 Miles')
    GO
    SET IDENTITY_INSERT [dbo].[DimCustomer] OFF
    GO
    ALTER TABLE [dbo].[DimCustomer]  WITH CHECK ADD  CONSTRAINT [FK_DimCustomer_DimGeography] FOREIGN KEY([GeographyKey])
    REFERENCES [dbo].[DimGeography] ([GeographyKey])
    GO
    ALTER TABLE [dbo].[DimCustomer] CHECK CONSTRAINT [FK_DimCustomer_DimGeography]
    GO


    Thanks Bala Narasimha

    Thursday, April 11, 2019 7:54 AM

All replies

  • Hi Baluchalla,

     According to your description, it seems that you want to use parameter option , right? If so, you could try to use parameter like below

    SELECT      CustomerKey , GeographyKey, CustomerAlternateKey, Title, FirstName, MiddleName, LastName, NameStyle, BirthDate, MaritalStatus, Suffix, Gender, EmailAddress, YearlyIncome, TotalChildren, 
                             NumberChildrenAtHome, EnglishEducation, SpanishEducation, FrenchEducation, EnglishOccupation, SpanishOccupation, FrenchOccupation, HouseOwnerFlag, NumberCarsOwned, AddressLine1, AddressLine2, 
                             Phone, DateFirstPurchase, CommuteDistance
    FROM            DimCustomer
    WHERE        (CustomerKey IN (@CustomerKey)) OR
                          
                             (FirstName IN (@FirstName)) OR
                            
                             (EmailAddress IN (@EmailAddress)) 
                            

    Then try to set parameter like below

    Set this for other parameter , then you will get the result like below

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 12, 2019 2:22 AM
    Moderator
  • Thanks for your solution.

    In same report i want to implement bellow requirement

    If when i select parameter1 then parameter 2 & 3 goes to hide 

    If when i select parameter2 then parameter 1 & 3 goes to hide 

    If when i select parameter3 then parameter 1 & 2 goes to hide 

    If it is possible please help on this.


    Thanks Bala Narasimha

    Friday, April 12, 2019 6:26 AM
  • Hi  BaluChalla,

     As I know , there is no way to set hidden based on other parameter's status. So workaround is that use column name as parameter. Below is my design.

    Create a dataset for colname and create a parameter @AA

    select 
    COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'DimCustomer'

     Then create a dataset for column value and a parameter @colvalue

    DECLARE @sql varchar(max);
    SET                @sql = '
                             SELECT       ' + @AA + '  AS COLUMN1
                                  FROM            DimCustomer ' EXEC (@sql)


     Then create a dataset for table, you need to run SELECT       *  FROM            DimCustomer first in dataset, then replace this  query with 

    DECLARE @sql varchar(max);
    SET                @sql = 'SELECT       *
                                  FROM            DimCustomer
    where ' + @AA +' IN ('+char(39)+replace(@COLVALUE,',',''',''')+char(39)+')' 
    
    EXEC (@sql)


    You will get below result


            

    Best Regards,
    Zoe Zhi
                     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, April 16, 2019 2:03 AM
    Moderator
  • Thanks for your support. 

    Have query like bellow, could you please suggestion on it will work or not.

    Declare @FromDate as date = NULL,@ToDate as date = NULL                    
              
    if @FromDate is null or @ToDate is null            
    begin           
     set @FromDate = CONVERT(date, GETDATE())          
     set @ToDate= CONVERT(date, GETDATE())          
    end ;
     

    select * from ReportDB..SalesReport with (nolock)
     where (RefernceNo in (@ReferenceNo)) OR
       (ClienNo in (@ClientNo)) OR
       (ClientName in (@ClientName)) OR
           ( issueDate >= @FromDate and issueDate < DATEADD(dd,1,@ToDate))


    Thanks Bala Narasimha

    Monday, April 22, 2019 1:32 PM
  • Hi BaluChalla, 

    Above query will work, it will  return today's date. If you want to choose date from calendar picker, I think you could use below query 

    select * from ReportDB..SalesReport with (nolock)
     where (RefernceNo in (@ReferenceNo)) OR
       (ClienNo in (@ClientNo)) OR
       (ClientName in (@ClientName)) OR
           ( issueDate >= @FromDate and issueDate < DATEADD(dd,1,@ToDate))

    Then set @FromDate and @ToDate's default value like below

     And your solution need to create multiple parameters and can't hide it based on other parameter's status. If you want to achieve this goal, you could refer to my above suggestiosn.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 23, 2019 6:34 AM
    Moderator
  • Once again thanks.

    Am not getting any data when parameters as @FromDate and @ToDate.

    Please help on this script 

    Declare @FromDate as date = NULL,@ToDate as date = NULL                    
             
    if @FromDate is null or @ToDate is null            
    begin           
     set @FromDate = CONVERT(date, GETDATE())          
     set @ToDate= CONVERT(date, GETDATE())          
    end ;
     
    select * from ReportDB..SalesReport with (nolock)
     where (RefernceNo in (@ReferenceNo)) OR
       (ClientNo in (@ClientNo)) OR
       (ClientName in (@ClientName)) OR
           ( issueDate >= @FromDate and issueDate < DATEADD(dd,1,@ToDate))


    Thanks Bala Narasimha

    Tuesday, April 23, 2019 9:51 AM
  • Hi  BaluChalla,

    You have define @FromDate and @To Date in query, and set its value to today, so you don't need to create parameter for this in report parameter list . You said you get nothing when you use above result, you need to check whether you have value today.

    DECLARE @FromDate AS date = NULL, @ToDate AS date = NULL IF @FromDate IS NULL OR
    @ToDate IS NULL BEGIN
    SET                @FromDate = CONVERT(date, GETDATE())
    SET                @ToDate = CONVERT(date, GETDATE()) END;
    
    
    select DATEADD(dd,-1800,@FromDate),DATEADD(dd,1,@ToDate)
                                 SELECT        *
                                  FROM            [dbo].[DimProduct]
                                  WHERE        ([StartDate] <= DATEADD(dd,1,@ToDate))  and ([StartDate] > DATEADD(dd,-2500,@FromDate)) 
    							  /*  here is data  from 2014/5/20-2019/4/25*/
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 24, 2019 7:01 AM
    Moderator
  • Thanks for you response.

    My requirement is as shown in bellow image.

    Please suggest above issue.

    thanks in advance.


    Thanks Bala Narasimha

    Wednesday, April 24, 2019 7:15 AM
  • Hi  BaluChalla,

    If you use single parameter(date have two parameter) and want to show only one parameter each time, my original post might be work. But you have two parameters for date, if it is be defined in query, I think this can't be hidden by expression or other operations. So it is difficult to achieve this. Did you want to use hard code for date parameter?

    Or you could see my first reply,  set null for parameter(which will visible all the time but you could choose parameter you want to filter dataset)

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 25, 2019 7:58 AM
    Moderator
  • Thanks for your valuable information.

    Have one more question on same requirement.

    Have 1000000 in  source table. I want to show the 100000 records in report. If parameters all are null that time want to show  the 100000 records else it shows which you want from table.


    Thanks Bala Narasimha

    Tuesday, May 7, 2019 10:19 AM