none
SSRS Report parameter not working

    Question

  • 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 8:29 AM

Answers

  • Hi Fawad,

    1. Parameters in SSRS are defined in the report and the SQL statement can only be stuff like: Select name from names where id = @id. That means we don't do Declare variable. If you would like to use declare, it is recommended that put your query into a Stored Procedure. Please refer to: SSRS Demo Using Stored Procedure with Parameters

    2. You have specified the value of variables in your query, just remove the values and only using declare variables. And please refer to the link shared above.

    Regards,

    Pirlo Zhang

                  


    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, March 21, 2017 2:44 AM

All replies

  • Hi Fawad,

    To be able to use parameters in Reporting Services, do not define them in the query itself, mening:

    - do not declare the parameters in the query;

    - do not assign values to parameters in the query.

    Instead, use the parameters as they would have been real fields, like for example:

    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)

    When you save the dataset, the parameters are created for you and you can find them under Parameters.

    You can also manually add parameters in there.

    After the parameters are created, define them:

    - right click on each parameter > Properties > Default Values > Specify values > Add > Expression (fx) > a new window will open and add there the logic for your parameter.

    - if a parameter depends on another parameter, you don't use @, but you use something like Parameters!DateRange.Value (where DateRange is your parameter name).

    - if you need to define a parameter through a SQL statement or function, you need to create a dataset for each parameter and in parameters properties select get values from a query, instead of specify values.


    I hope this will give you a good start. There are many properties and options you should consider when designing a report.

    Thanks,

    Gina


    Monday, March 20, 2017 12:32 PM
  • Hi Gina. Thanks for the reply. I have tried your way before. Once I did not define the parameter in the query but then it returned 0 for every field for every value of the parameter. I guess 0 is the default value. Also, as I mentioned that I got this report from someone else, I am not sure:

    1) why we have only one report parameter (@DateRange) in the parameter section when we have declared other variables as well (at the start of the code)?

    2) I have specified some possible values for the parameter by using specify values (not get from query. I think in this case we don't need query). but it only works for the value that I define manually in the code. Let's say I define @DateRange = 'year-to-date' in the query, it returns all the results matching year-to-date, but when I try to select another value from the drop down menu, it returns the same result. If I define @DateRange = 'Today' in the query, it returns the results matching 'Today' but then again for every value of the parameter, it returns the same result.

    Can you explain these two situations? Thanks

    Fawad

    Monday, March 20, 2017 3:17 PM
  • Hi Fawad,

    1. Parameters in SSRS are defined in the report and the SQL statement can only be stuff like: Select name from names where id = @id. That means we don't do Declare variable. If you would like to use declare, it is recommended that put your query into a Stored Procedure. Please refer to: SSRS Demo Using Stored Procedure with Parameters

    2. You have specified the value of variables in your query, just remove the values and only using declare variables. And please refer to the link shared above.

    Regards,

    Pirlo Zhang

                  


    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, March 21, 2017 2:44 AM
  • Thanks Pirlo. It worked.
    Tuesday, March 21, 2017 3:56 PM