Range of values in SSRS 2008 with stored procedure
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....
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....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
ASKER
Both people had the correct information. THanks!
Thank you for clarifying how we've helped you and for the further info on how you got your report working, very nice!
ASKER