SSRS Multi-Value Prompt

Question

Post reply

SSRS Multi-Value Prompt

bob_levine

SSC Veteran

Points: 243
More actions
August 3, 2010 at 11:30 am

#224971

I have a multi-value prompt in my SSRS report. I need to include all values selected in this prompt in my page header. How do I do this?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 1

Drag and drop the Parameter on the Header .. It will display the value at runtime

bob_levine SSC Veteran Points: 243 More actions · Answer 2

I dragged and dropped the parameter onto a text box in the page header. It reads "=Parameters!P_ApptType.Value" meaning the multiple Appointment Types. #Error displays in the preview.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

Try using split and/or join to turn the selections into an array, and then back to a string.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

bob_levine (8/3/2010)
I dragged and dropped the parameter onto a text box in the page header. It reads "=Parameters!P_ApptType.Value" meaning the multiple Appointment Types. #Error displays in the preview.

Do you get that error only in the design mode or in the real execution?

Do you get the error when you have nothing selected? Or perhaps everything selected?

Ganesh Lohani Hall of Fame Points: 3091 More actions · Answer 5

Hello,

Drag a textbox on the header and use an expression like below.

=" Sales Report generated for: " & JOIN( Parameters!STATE.Value, ", ")

This is what I used in my report. I have a sales report for each STATE( a multivalued parameter).

If I select all the states in the parameter field when I run the report, it shows all the states and when I select a state (let's say CA), it shows only CA on the report header.

I hope It would give you the idea.

Thanks

ramdas.narayanan Ten Centuries Points: 1214 More actions · Answer 6

I agree with Ganesh's post, use the JOIN function:

=Join(Parameters!state.Value,",")

jvanderberg SSCertifiable Points: 6762 More actions · Answer 7

Ganesh has lead you down the right track. That is exactly what you need to do.

However, I usually take it a step further. When they select ALL of the states (which is probably fairly common), they probably don't want 50 states listed. Something like "State(s): All" will usually suffice. To do that you just need to compare the parameter count against the dataset count:

="State(s): " & iif(Count(Fields!State.Value, "StateDataSet") =

Parameters!State.Count, "All", join(Parameters!State.Value, ", "))

This won't do much if they pick every state but one, but choosing every state is far more common.

ramdas.narayanan Ten Centuries Points: 1214 More actions · Answer 8

jvanderberg (8/4/2010)
Ganesh has lead you down the right track. That is exactly what you need to do.
However, I usually take it a step further. When they select ALL of the states (which is probably fairly common), they probably don't want 50 states listed. Something like "State(s): All" will usually suffice. To do that you just need to compare the parameter count against the dataset count:
="State(s): " & iif(Count(Fields!State.Value, "StateDataSet") =
Parameters!State.Count, "All", join(Parameters!State.Value, ", "))
This won't do much if they pick every state but one, but choosing every state is far more common.

Good one, having all states can appear very messy on the header.

Thank you

jvanderberg SSCertifiable Points: 6762 More actions · Answer 9

It's worth mentioning that because my example references a dataset, it can't be used in the header. It can still be placed at the very top of the body, however.

Ganesh Lohani Hall of Fame Points: 3091 More actions · Answer 10

You are right.

We rarely select all 50 states in practice. However, we might have customers only in 7 states or we might sale the product through 7 distribution centers ( in my case) , and the users might want to see the weekly sales data from all the distribution centers or from each location or from any combination.

So, intensionally I wanted to tell <= 10 state, but missed the number.

Thanks

jvanderberg SSCertifiable Points: 6762 More actions · Answer 11

Ok, that makes sense.

Typically when I set up a multi-value parameter, the default is to select all. This way they get all the data, but have a flexible filter. When it comes to parts or customers, there's no way I'm going to list of all of them.