Post reply

Parameter for Top N records

  • December 10, 2008 at 12:01 pm

    #216392

    I'm trying to set up a parameter that would allow report users to select the top N records. I've got a parameter, @sample, set to integer and default to 1000.

    My dataselect is

    select top (@sample) *

    from.....

    and it comes back with an error "Must declare the scalar variable @sample.."

    Ideas?

    Thanks in advance.

  • December 10, 2008 at 12:16 pm

    #910598

    select top (@sample) *

    from.....

    Use

    Declare @sql varchar(255)

    Declare @Sample varchar(2)

    Set @Sample='5'

    set @sql='Select top '+ @Sample +' * from

    EXEC (@SQL)

  • December 10, 2008 at 2:28 pm

    #910677

    I like that idea but I'm not having any luck getting reporting services to recognize the parameters in the select statement.

    To take a simpler case, say I've got a @country parameter that's being selected by the user (and set up in the report parameters section), when I attempt

    use globalcontactdetail

    declare @sql nvarchar(4000)

    set @sql = 'select * from globalcontactdetail..listview where country = @country'

    EXEC (@sql)

    It gets confused and doesn't recognize @country

  • December 10, 2008 at 3:02 pm

    #910696

    I think I answered my own question, I needed to create a local variable @country2 (or whatever) and declare it and assign it the value of the country parameter, seems to work now, many thanks!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply