Range of values in SSRS 2008 with stored procedure

Posted on 2011-03-08
Last Modified: 2012-08-14
I am generating a report in SSRS 2008. Let's just say it is a sales order. There is header information and detail information. Originally I designed it like I would a Crystal Report and had the header information on the page header (or report header) and created a subreport for the detail information (for ease and reuse). Then I realized that the user might want to print a 'range' of sales orders. So they may want to print 1355 through 1366. I then noticed that SSRS does not seem to have a range parameter. I tried a multi value parameter but when I passed the stored procedure 1355, 1356, 1357 it choked on it because it said it was a string (and the sales order key is an integer).
How do I handle printing multiple sales orders using one stored procedure (or two)? I was thinking of having a 'from sales order' parameter and 'to sales order' parameter which they would keep the same if it was for one sales order but if there is more than one sales order I need to call the stored procedure more than once? I am confused... and new....
Question by:bmorriso99
  • 3
  • 2
  • 2

Author Comment

ID: 35073706
I just wanted to supply more information about my real question. If a stored procedure returns multiple rows I want them each to be one 'report'. So if the user says they want to print sales order 1033 throiugh 1039 I want a report for each one. How do I do this? Do i have to use groups and have each group be a separate 'report'?
LVL 32

Assisted Solution

ewangoya earned 250 total points
ID: 35089531

Your stored procedure has to parse the parameter first before running queries using it
If you set the parameter to a varchar and in the SP, parse the parameter into a temp table and use that temp table as your input value.

You can find several examples for parsing a comma delimited parameter into a temp table from this site
LVL 32

Expert Comment

ID: 35089593

Here is simple example
-- assume this is the parameter ----
Declare @sParameter varchar(8000)  --1355, 1356, 1357

-- Table to get the incoming parameters ----
Declare @tID table(ID Int )

-- Now the loop ----
Declare @iPos integer
Declare @sTemp varchar(8000)

Set @iPos = CharIndex( ',', @sParameter )
While @sParameter <> ''
    If @iPos > 0 
        Set @sTemp = Substring(@sParameter, 1, @iPos - 1 )
        Set @sParameter = Substring( @sParameter, @iPos + 1, 8000 )
        Set @sTemp = @sParameter
        Set @sParameter = ''

    if ISNUMERIC(@sTemp) = 1 
      Insert @tID select Cast(@sTemp as Int )

    Set @iPos = CharIndex( ',', @sParameter )

//now in your current query where you use the parameter passed in, you change the where clause to

Open in new window

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 37

Accepted Solution

ValentinoV earned 250 total points
ID: 35094232
A while ago I wrote an article about using a multi-valued parameter in combination with a stored procedure.  And that seems to be exactly what you're wanting to achieve.

Check it out here:

Author Comment

ID: 35096162
I am going to accept these solutions because they are both very helpful and correct. What my real question was (which I didn't explain clearly enough) was how to genereate 1 report for each given parameter value in a multi parameter field. These users took care of helping with the parsing part but I didn't know how to handle mutliple results (rows) being returned. I finally determined that I need to make the entire report a tablix and have the value that I want the report to change on by the group and put a page break between groups. So, for example if I want the user to be able to enter multiple sales order numbers (1133 -> 1135) and print one report per sales order then I create a tablix with a grouping on sales order and when my stored procedure returns 3 rows (1 for 1133, 1 for 1134 and 1 for 1135) then it will page break between each one. Thanks for your help!

Author Closing Comment

ID: 35096174
Both people had the correct information. THanks!
LVL 37

Expert Comment

ID: 35106002
Thank you for clarifying how we've helped you and for the further info on how you got your report working, very nice!

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Alter a column in sql 34 30
SQL BULK INSERT Comma Delimited Issue 8 50
SSRS  - Dropdown with Null 3 25
sql 2014,  lock limit 5 32
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question