Asked by:
SSRS Report parameter not working

Question
-
User-839267906 posted
I am new to Sql server report builder. I have a report in which report parameter is not working. No matter what value I select, it does not seem to get that value. It is only picking the defined value from the query. And if I define the parameter in query to null then it returns null. Someone else gave me that report and I don't know if this is the correct way to define a parameter. (If this is too long, please just tell me if I am using the parameter wrong.) The parameter name is @DateRange and the query is:
DECLARE @Date datetime = getdate() DECLARE @DateStart datetime DECLARE @DateEnd datetime DECLARE @CorpStart int = 0 DECLARE @CorpEnd int = 0 DECLARE @ReportTitle varchar(30) = '' DECLARE @DateRange varchar(50) = N'Year-To-Date' IF @DateRange = 'Today' BEGIN SET @DateStart = [dbo].[get_today_start](@Date) SET @DateEnd = [dbo].[get_today_end](@Date) SET @CorpStart = [dbo].udfGetCorpWeek(@DateStart) SET @CorpEnd = [dbo].udfGetCorpWeek(@DateEnd) END IF @DateRange = 'Yesterday' BEGIN SET @DateStart = [dbo].[get_yesterday_start](@Date) SET @DateEnd = [dbo].[get_yesterday_end](@Date) SET @CorpStart = [dbo].udfGetCorpWeek(@DateStart) SET @CorpEnd = [dbo].udfGetCorpWeek(@DateEnd) END IF @DateRange = 'Last Week' BEGIN --SET DATEFIRST 1 --SELECT DATEADD(dd,-7,[dbo].[get_week_start](getdate())) --SELECT DATEADD(dd,-7,[dbo].[get_week_end](getdate())) SET @DateStart = DATEADD(dd,-7,[dbo].[get_week_start](@Date)) SET @DateEnd = DATEADD(dd,-7,[dbo].[get_week_end](@Date)) SET @CorpStart = [dbo].udfGetCorpWeek(@DateStart) SET @CorpEnd = [dbo].udfGetCorpWeek(@DateEnd) END IF @DateRange = 'Week-To-Date' BEGIN SET @DateStart = [dbo].[get_week_start](@Date) SET @DateEnd = @Date --[dbo].[get_week_end](@Date) SET @CorpStart = [dbo].udfGetCorpWeek(@DateStart) SET @CorpEnd = [dbo].udfGetCorpWeek(@DateEnd) END IF @DateRange = 'Month-To-Date' BEGIN SET @DateStart = [dbo].[get_month_start](@Date) SET @DateEnd = @Date --[dbo].[get_month_end](@Date) SET @CorpStart = [dbo].udfGetCorpWeek(@DateStart) SET @CorpEnd = [dbo].udfGetCorpWeek(@DateEnd) END IF @DateRange = 'Quarter-To-Date' BEGIN SET @DateStart = [dbo].[get_quarter_start](@Date) SET @DateEnd = @Date --[dbo].[get_quarter_end](@Date) SET @CorpStart = [dbo].udfGetCorpWeek(@DateStart) SET @CorpEnd = [dbo].udfGetCorpWeek(@DateEnd) END IF @DateRange = 'Year-To-Date' BEGIN SET @DateStart = [dbo].[get_year_start](@Date) SET @DateEnd = @Date --[dbo].[get_year_end](@Date) SET @CorpStart = [dbo].udfGetCorpWeek(@DateStart) SET @CorpEnd = [dbo].udfGetCorpWeek(@DateEnd) END IF @DateRange = 'Previous 4 Weeks' BEGIN SET @DateStart = DATEADD(dd,-29,[dbo].[get_week_start](@Date)) SET @DateEnd = DATEADD(dd,-1,[dbo].[get_week_start](@Date)) SET @CorpStart = [dbo].udfGetCorpWeek(@DateStart) SET @CorpEnd = [dbo].udfGetCorpWeek(@DateEnd) END DECLARE @Result TABLE( DateStart datetime, DateEnd datetime, CorpStart int, CorpEnd int, Region varchar(3), ID int, Program varchar(50), -- Program group [2016 Leads] int, -- within 200 miles with phone number [2017 Leads] int, -- within 200 miles with phone number [Budget] int, -- budgeted lead #s [SortOrder] int ) INSERT INTO @Result(DateStart,DateEnd,CorpStart,CorpEnd,Region,ID,Program,[2016 Leads],[2017 Leads],[Budget],[SortOrder]) SELECT DateStart = @DateStart, DateEnd = @DateEnd, CorpStart = @CorpStart, CorpEnd = @CorpEnd, Region = 'TNC', ID = pg.[ProgramGroupID], Program = pg.[Name], 0,0,0,1 FROM refProgramgroup pg WHERE pg.ProgramGroupID in(1,2,3,4,5,6,7,13,28,29,31) INSERT INTO @Result(DateStart,DateEnd,CorpStart,CorpEnd,Region,ID,Program,[2016 Leads],[2017 Leads],[Budget],[SortOrder]) SELECT DateStart = @DateStart, DateEnd = @DateEnd, CorpStart = @CorpStart, CorpEnd = @CorpEnd, Region = 'TRG', ID = pg.[ProgramGroupID], Program = pg.[Name], 0,0,0,2 FROM refProgramgroup pg WHERE pg.ProgramGroupID in(1,2,3,4,5,6,7,13,28,29,31) INSERT INTO @Result(DateStart,DateEnd,CorpStart,CorpEnd,Region,ID,Program,[2016 Leads],[2017 Leads],[Budget],[SortOrder]) SELECT DateStart = @DateStart, DateEnd = @DateEnd, CorpStart = @CorpStart, CorpEnd = @CorpEnd, Region = 'RFR', ID = pg.[ProgramGroupID], Program = pg.[Name], 0,0,0,3 FROM refProgramgroup pg WHERE pg.ProgramGroupID in(1,2,3,4,5,6,7,13,28,29,31) INSERT INTO @Result(DateStart,DateEnd,CorpStart,CorpEnd,Region,ID,Program,[2016 Leads],[2017 Leads],[Budget],[SortOrder]) SELECT DateStart = @DateStart, DateEnd = @DateEnd, CorpStart = @CorpStart, CorpEnd = @CorpEnd, Region = 'TRO', ID = pg.[ProgramGroupID], Program = pg.[Name], 0,0,0,3 FROM refProgramgroup pg WHERE pg.ProgramGroupID in(1,2,3,4,5,6,7,13,28,29,31) INSERT INTO @Result(DateStart,DateEnd,CorpStart,CorpEnd,Region,ID,Program,[2016 Leads],[2017 Leads],[Budget],[SortOrder]) SELECT DateStart = @DateStart, DateEnd = @DateEnd, CorpStart = @CorpStart, CorpEnd = @CorpEnd, Region = 'BLR', ID = pg.[ProgramGroupID], Program = pg.[Name], 0,0,0,4 FROM refProgramgroup pg WHERE pg.ProgramGroupID in(1,2,3,4,5,6,7,13,28,29,31) INSERT INTO @Result(DateStart,DateEnd,CorpStart,CorpEnd,Region,ID,Program,[2016 Leads],[2017 Leads],[Budget],[SortOrder]) SELECT DateStart = @DateStart, DateEnd = @DateEnd, CorpStart = @CorpStart, CorpEnd = @CorpEnd, Region = 'NSL', ID = pg.[ProgramGroupID], Program = pg.[Name], 0,0,0,4 FROM refProgramgroup pg WHERE pg.ProgramGroupID in(1,2,3,4,5,6,7,13,28,29,31) INSERT INTO @Result(DateStart,DateEnd,CorpStart,CorpEnd,Region,ID,Program,[2016 Leads],[2017 Leads],[Budget],[SortOrder]) SELECT DateStart = @DateStart, DateEnd = @DateEnd, CorpStart = @CorpStart, CorpEnd = @CorpEnd, Region = 'LSR', ID = pg.[ProgramGroupID], Program = pg.[Name], 0,0,0,5 FROM refProgramgroup pg WHERE pg.ProgramGroupID in(1,2,3,4,5,6,7,13,28,29,31) UPDATE @Result SET Budget = ISNULL((Select sum(u.LeadCount) from [ETL].[dbo].[TRA_LeadBudget2016_Unpivot] u where u.CorpWeek >= @CorpStart and u.CorpWeek <= @CorpEnd and u.ProgramGroupID = [@Result].ID and u.Region = [@Result].Region) ,0) UPDATE @Result SET [2016 Leads] = ISNULL((Select count(distinct(L.PersonID)) --count(LD.LeadID) from tblLeadDistance LD with(nolock) join tblLead L with(nolock) on LD.LeadID = L.LeadID join tblCampaignLead CL with(nolock) on CL.LeadID = L.LeadID join tblCampaign C with(nolock) on C.CampaignID = CL.CampaignID join tblProgram prog with(nolock) on C.ProgramID = prog.ProgramID where prog.ProgramGroupID = [@Result].ID and LD.LandmarkID = (CASE WHEN [@Result].Region = 'TNC' THEN 1 WHEN [@Result].Region = 'TRG' THEN 2 WHEN [@Result].Region = 'RFR' THEN 5 WHEN [@Result].Region = 'TRO' THEN 4 WHEN [@Result].Region = 'BLR' THEN 16 WHEN [@Result].Region = 'NSL' THEN 23 WHEN [@Result].Region = 'LSR' THEN 25 END) --and LD.DistanceMiles <= 201 and L.LeadStatusID <> 9 and L.DateCreated >= DATEADD(week, -52, @DateStart) and L.DateCreated <= DATEADD(week, -52, @DateEnd) and not(prog.ProgramID = 1000000265) and L.OriginatingTable like '%Staging%' ),0) UPDATE @Result SET [2017 Leads] = ISNULL((Select count(distinct(L.PersonID)) from tblLeadDistance LD with(nolock) join tblLead L with(nolock) on LD.LeadID = L.LeadID join tblCampaignLead CL with(nolock) on CL.LeadID = L.LeadID join tblCampaign C with(nolock) on C.CampaignID = CL.CampaignID join tblProgram prog with(nolock) on C.ProgramID = prog.ProgramID where prog.ProgramGroupID = [@Result].ID and LD.LandmarkID = (CASE WHEN [@Result].Region = 'TNC' THEN 1 WHEN [@Result].Region = 'TRG' THEN 2 WHEN [@Result].Region = 'RFR' THEN 5 WHEN [@Result].Region = 'TRO' THEN 4 WHEN [@Result].Region = 'BLR' THEN 16 WHEN [@Result].Region = 'NSL' THEN 23 WHEN [@Result].Region = 'LSR' THEN 25 END) and L.LeadStatusID <> 9 and L.DateCreated >= @DateStart and L.DateCreated <= @DateEnd and L.OriginatingTable like '%Staging%' ),0) SELECT * from @Result
PS: Query is working fine in SSMS
Monday, March 20, 2017 11:20 AM
All replies
-
User-2057865890 posted
Hi Qureshi92,
To associate a query parameter with a report parameter
-
In the Report Data pane, right-click the dataset, click Dataset Properties, and then click Parameters.
-
In the column Parameter Name, find the name of the query parameter. Parameter names are automatically populated based on the query. Every time you change the query, the query is checked for new query parameters. Query parameters that you create manually are not changed when the query changes.
-
In Parameter Name, find the query parameter name as it exists in the query. You can also manually add a new query parameter and enter a name.
-
In Parameter Value, type or select an expression that evaluates to the value to pass to the query parameter. This is typically the name of the report parameter.
-
-
Repeat step 2 for additional query parameters.
reference:
https://www.mssqltips.com/sqlservertip/3466/cascaded-parameters-in-sql-server-reporting-services/
http://www.c-sharpcorner.com/UploadFile/7d3362/ssrs-report-for-parameterize-stored-procedure/
Best Regards,
Chris
Tuesday, March 21, 2017 3:03 AM -