Remove From My Forums

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

0

Sign in to vote
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

Asked by:

SSRS Report parameter not working

Question

All replies