0

The table has a field that may contain any combination of the four letters"DICS." For example any of these: "I", "DC", "DIS", "CS", or "DICS."

I put "WHERE DISCScores IN (@DISCScores)" in the Dataset and set the Parameter properties to include each of the four letters, but the report will only list records where the DISCScores field contains only one of the letters. With all of them selected it will display only the records that contain single letters, but I need it to contain all the records where DISCScores includes ANY of the selected leters.

For example if only "S" is selected in the parameters, then I want all the records with "S", "DIS", "CS", "IS", "DS" or "DICS", instead of just the ones with "S." If "I" and "S" are checked in the parameter prompt, then I want records where DISCScores is "IS", "DIS", "ICS" or "DICS."

I'm expecting to need to use an expression in the parameter properties, but I wouldn't know how to craft that expression.

Thanks!

David Megnin
  • 79
  • 1
  • 1
  • 10
  • See http://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services – StevenWhite Sep 08 '16 at 16:01

2 Answers2

1

I accomplished the above requirement by creating a parameter for each of the four letters and using [field] LIKE '%' + @parameter + '%' for each of them.

David Megnin
  • 79
  • 1
  • 1
  • 10
0

Unless I'm not understanding your query properly, it looks like you want

WHERE DISCScores LIKE '%' + @DISCScores + '%'

This would only work in case the parameter @DISCScores contains a single letter though.

Louis
  • 593
  • 4
  • 13
  • I tried that. If more than one letter is selected I get an error. If only one letter is selected, then I get what you would expect... all records that contain that letter. I need to be able to select any combination of D, I, S, or C. and have any record containing the selected letters. – David Megnin Sep 08 '16 at 16:06
  • Oh, sorry. I see you said that it would only work in case the parameter contains a single letter. Right. But, I do need to select more than one letter in the parameter. – David Megnin Sep 08 '16 at 16:18
  • In an ASP.Net GridView, I used a CheckBoxList for the DISC letter selection. – David Megnin Sep 08 '16 at 16:23
  • Then in the IndexChanged event of the CheckBoxList I've got a function that looks like this: protected void cbDISC_SelectedIndexChanged(object sender, EventArgs e) { string selected = ""; foreach (ListItem item in cbDISC.Items) { selected += item.Selected ? string.Format("{0}", item.Value) : ""; } if (!string.IsNullOrEmpty(selected)) { selected = string.Format("{0}", selected.Substring(0, selected.Length = 0)); } – David Megnin Sep 08 '16 at 16:31
  • Sorry, I couldn't figure out how to format the C# code above. – David Megnin Sep 08 '16 at 16:35