Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
andros30
Yak Posting Veteran
80 Posts |
 Posted - 2010-10-06 : 15:23:54
|
| Can someone confirm that you cannot use multi-value option in a stored procedure in SSRS? |
|
|
bushfoot
Yak Posting Veteran
53 Posts |
Posted - 2010-10-06 : 16:28:05
|
| I am currently using multi value parameters in rdl docs. I just have to set the proc fields as an expression that passes a common delimited string. The sql side needs to handle the delimited string.. but its workable at least.For example, =Join(Parameters!CallTypes.Value, ",")(go to the data tab: go to the dataset properties: go to parameters - set expression).. Sorry I dont have BI installed at home so I can't get the exact captions... |
 |
|
|
andros30
Yak Posting Veteran
80 Posts |
Posted - 2010-10-06 : 16:46:21
|
| Maybe I should give an example of my current setup.The stored procedure takes in a parameter: usp_GenerateRpt @OfficeMy SSRS has two datasets, one is the stored procedure, the other is a query that returns a list of offices.I have added a report parameter in my stored procedure dataset and I want to allow for Multi-Value so that the user can have the option to select one or more offices to run the report...However, the report will only return the first office on the list and I'm not sure if it's summing up all the reports together onto that first office. Or is it excluding all the other offices altogether. |
|
|
|
bushfoot
Yak Posting Veteran
53 Posts |
Posted - 2010-10-06 : 16:50:24
|
| if you select 2 offices are you expecting to see 2 reports? Are you expecting to see one report with 2 rows (one for each office)? Are you expecting to see one report with total of 2?in case one.. that is app issue and I dont know how you would do that herein case two .. sounds like your group by in sql in case three.. sounds like you are getting that and you dont like itYou can always run profiler and capture the sql that is getting executedThen copy/paste to ssms and run the proc to see what comes out |
|
|
|
andros30
Yak Posting Veteran
80 Posts |
Posted - 2010-10-06 : 17:58:49
|
| I want a report for each office separately. So which number did that fall into exactly hehe... |
|
|
|
bushfoot
Yak Posting Veteran
53 Posts |
Posted - 2010-10-06 : 19:40:09
|
| that is case one ;)if you are using ssrs report manager or a vs report server project you have to pick first office, run report, save; repeat for second office ( as far as I know anyway)if you sql proc returns the data something like this:office stat stat office stat statoffice 2 stat statoffice 2 stat statyou might be able to set properties on the table to start on a new page when it goes from office to office 2. you would still have to handle the multiple office selection tho |
|
|
|
|
|
|
Multi-Value parameters with Stored Procedure