Solved

Range of values in SSRS 2008 with stored procedure

Posted on 2011-03-08
7
569 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 37

Accepted Solution

by:
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: 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

724 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