July 27, 2009 at 6:41 am
I have a sales report that has a multi-valued parameter labeled 'State'. It is a non-queried value, but I could create a data set if I need to. I was hoping not to. values are below
label value
IL IL
KS KS
OH OH
MI MI
Territory 1 IL,KS,MI
For Territory 1; the states include IL,KS,MI. how can I create a label and value to reflect this??
I have tried setting the value of label Territory 1 to IL,KS,MI <<
In the sql statement I have: State in (@state)
Everthing works if I individually select IL,KS,MI....but not if I select the label "Territory 1"
Any ideas, would be a great help. PLEASE, I am stuck
July 27, 2009 at 10:26 am
I'd initally suggest running a Profiler trace against the database when you run the report with Territory 1 selected to see what is being passed to the database. Odds are that reporting services is doing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 28, 2009 at 11:29 am
Like Jack suggested try run a profiler trace to see exactly what is being passed, but I'd imagine that the issue you are having is that your state abbreviations are a character datatype. You probably just need to put single quotes around them. Perhaps something like 'IL','KS','MI'
Cheers,
-Luke.
July 30, 2009 at 10:51 am
SSRS is passing the string to the procedure such that it's looking for a state value of 'IL,KS,MI '. Since that state doesn't exist, you're getting nothing.
I find that I often have to 'massage' parameters once SSRS sends them to the procedure. In your case, you'll probably have to format the SSRS parameter in your procedure so that it will work in a dynamic SQL statement, and execute that statement.
If don't want to/can't do that, there are other options that are more complex that I could detail if you need.
July 30, 2009 at 12:47 pm
I figured it out. In my sql statement I specified the following:
charindex(rtrim(SOP30200.STATE),@testing) >0
and set my value associated to label A to: (state "space" state )
IL KS MI
The report is now working. Do you have another method?
July 31, 2009 at 8:20 am
My solution would entail database-level alterations that would only be necessary if dynamic SQL was completely out of the question. I'm glad you found a better solution.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply