Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Range of values in SSRS 2008 with stored procedure

Posted on 2011-03-08
7
Medium Priority
?
574 Views
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....
0
Comment
Question by:bmorriso99
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:bmorriso99
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'?
0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 1000 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
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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 <> ''
Begin
    If @iPos > 0 
    Begin
        Set @sTemp = Substring(@sParameter, 1, @iPos - 1 )
        Set @sParameter = Substring( @sParameter, @iPos + 1, 8000 )
    End
    Else
    Begin
        Set @sTemp = @sParameter
        Set @sParameter = ''
    End

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

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

//now in your current query where you use the parameter passed in, you change the where clause to
where YourID IN (SELECT ID FROM @tID)

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 37

Accepted Solution

by:
ValentinoV earned 1000 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: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_2002-Reporting-On-Data-From-Stored-Procedures-part-2.html
0
 

Author Comment

by:bmorriso99
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!
0
 

Author Closing Comment

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

Expert Comment

by:ValentinoV
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!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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.
In this article I will describe the Copy Database Wizard 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.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

782 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