locked
SSRS Report parameter not working RRS feed

  • 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

    1. In the Report Data pane, right-click the dataset, click Dataset Properties, and then click Parameters.

    2. 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.

    3. 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