Limiting values in a parameter 1

[Daytona00432]

I am new to SSRS and would like to know how to limit the values I am passing to a stored procedure. I am passing an Integer paramater and want the value to fall between 2 and 48.

The integer determines how many rows are returned by my query and is not a field that exists in the table.

 

[RiverGuy]

Make a dummy query for a dataset and use that as a list of values for your parameter. You can have it display as a drop down list. If SQL Server is the data source, an example query would be the following:

DECLARE @i INT
DECLARE @temp TABLE (NumCol INT)
SELECT @i = 2
WHILE @i <= 48
BEGIN
  INSERT INTO @temp SELECT @i
  SELECT @i = @i + 1
END

SELECT * FROM @temp

 

[Daytona00432]

I'm afraid you will need to spell it out for me. Exactly how do I accomplish this? Do I create the query inside of SSRS or inside of my database?

 

[RiverGuy]

Inside SSRS. If you click the data tab (SSRS 2005), click the dataset drop down and choose <New Dataset>. This is where you create your query text. Then, in your parameter's properties, you can choose that new dataset under available values.

 

[Daytona00432]

Once again you are the master! BTW - do you have any recommendations as far as textbooks or online resources? many of the links in the FAQ are invalid.

 

[RiverGuy]

For books, not really. SSRS is one of the couple areas of the SQL Server suite I never bothered to purchase a book for.

In regards to online resources, I've been known to consult some of the excellent articles on DatabaseJournal.com from time to time. Here's a link.

http://www.databasejournal.com/features/article.php/3593466/MS-SQL-Series.htm

Look under the articles for William E. Pearson about halfway down the page. There is a large sub-section called "MSSQL Server Reporting Services" with links to lots of articles he has written.