I'm trying to create a SQL Report builder report that gets information from a column that allows multiple selections (checkboxes). The report runs ok except it displays the information from the fields with multiple selections with semi-colons and pound signs before and after each item. So if I'm displaying a selection that has multiple values it's displaying them like this; ;#Option1;#Option2;# Is there any way to fix that? Also, I'm sure it's probably difficult to do but can you report on individual values if they are a part of a multiple selection field essentially getting a total of that one item even though there are others with it?

Are you talking about a multi-value parameter and you are displaying the value in your report? Multi-value parameters have their values in an array. You should be able to format the display as you like by modifying the formula in the control that is displaying the values to something like =Join(Parameters!Multi.Value,", "). This will cause it to display as Option1, Option2. Change the join character(s) as you like. Yes you can report using individual values. Since the values are in an array, refer to the element that you want to use. =Parameters!Multi.Value(1) gets the second element of the parameter value array. You may need to check the target value first to ensure that enough values were selected for that element to have a valid value. =IIf(IsNothing(Parameters!Multi.Value(1)), "No value selected", Parameters!Multi.Value(1)).

Are you talking about a multi-value parameter and you are displaying the value in your report? Multi-value parameters have their values in an array. You should be able to format the display as you like by modifying the formula in the control that is displaying the values to something like =Join(Parameters!Multi.Value,", "). This will cause it to display as Option1, Option2. Change the join character(s) as you like. Yes you can report using individual values. Since the values are in an array, refer to the element that you want to use. =Parameters!Multi.Value(1) gets the second element of the parameter value array. You may need to check the target value first to ensure that enough values were selected for that element to have a valid value. =IIf(IsNothing(Parameters!Multi.Value(1)), "No value selected", Parameters!Multi.Value(1)).

Are you talking about a multi-value parameter and you are displaying the value in your report? Multi-value parameters have their values in an array. You should be able to format the display as you like by modifying the formula in the control that is displaying the values to something like =Join(Parameters!Multi.Value,", "). This will cause it to display as Option1, Option2. Change the join character(s) as you like. Yes you can report using individual values. Since the values are in an array, refer to the element that you want to use. =Parameters!Multi.Value(1) gets the second element of the parameter value array. You may need to check the target value first to ensure that enough values were selected for that element to have a valid value. =IIf(IsNothing(Parameters!Multi.Value(1)), "No value selected", Parameters!Multi.Value(1)).

Hi GWsysadmin, From your description, each value of the multi-value parameter is input in the ";#value" (without quotes) format, and you need to display the parameter values in a textbox using the "value1,value2,valu3" format . Right? If so, we can first join the multiple values of the parameter with comma and then replace the ";#" characters with null. The expression is like below: =Replace(Join(Parameters!p1.Value,","),";#",Nothing) To locate a specific value of the multiple values of a parameter, we can use =Parameters!p1.Value(n) as Tim posted. The "=Parameters!p1.Value(0)" expression points to the first value of the selected values for the parameter, and the "=Parameters!p1.Value(Parameters!p1.Count-1)". Reference: Using Single-Value and Multivalue Parameters Hope this helps. Regards, Mike Yin TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.Mike Yin TechNet Community Support

Hi GWsysadmin, From your description, each value of the multi-value parameter is input in the ";#value" (without quotes) format, and you need to display the parameter values in a textbox using the "value1,value2,valu3" format . Right? If so, we can first join the multiple values of the parameter with comma and then replace the ";#" characters with null. The expression is like below: =Replace(Join(Parameters!p1.Value,","),";#",Nothing) To locate a specific value of the multiple values of a parameter, we can use =Parameters!p1.Value(n) as Tim posted. The "=Parameters!p1.Value(0)" expression points to the first value of the selected values for the parameter, and the "=Parameters!p1.Value(Parameters!p1.Count-1)". Reference: Using Single-Value and Multivalue Parameters Hope this helps. Regards, Mike Yin TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.Mike Yin TechNet Community Support

Hi GWsysadmin, From your description, each value of the multi-value parameter is input in the ";#value" (without quotes) format, and you need to display the parameter values in a textbox using the "value1,value2,valu3" format . Right? If so, we can first join the multiple values of the parameter with comma and then replace the ";#" characters with null. The expression is like below: =Replace(Join(Parameters!p1.Value,","),";#",Nothing) To locate a specific value of the multiple values of a parameter, we can use =Parameters!p1.Value(n) as Tim posted. The "=Parameters!p1.Value(0)" expression points to the first value of the selected values for the parameter, and the "=Parameters!p1.Value(Parameters!p1.Count-1)". Reference: Using Single-Value and Multivalue Parameters Hope this helps. Regards, Mike Yin TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.Mike Yin TechNet Community Support

This is starting to make a little more sense to me. However, I don't believe I'm using Parameters to retrieve the data here. I'm getting data from a dataset that's pulling from a column on a SharePoint list that allows for multiple selections. My exact expression is below, can it still be modified to remove the ";#" characters that are coming across? =Count(Fields!Field1.Value)

This is starting to make a little more sense to me. However, I don't believe I'm using Parameters to retrieve the data here. I'm getting data from a dataset that's pulling from a column on a SharePoint list that allows for multiple selections. My exact expression is below, can it still be modified to remove the ";#" characters that are coming across? =Count(Fields!Field1.Value)

This is starting to make a little more sense to me. However, I don't believe I'm using Parameters to retrieve the data here. I'm getting data from a dataset that's pulling from a column on a SharePoint list that allows for multiple selections. My exact expression is below, can it still be modified to remove the ";#" characters that are coming across? =Count(Fields!Field1.Value)

Thanks for the clarification. Yes, you should be able to apply the same formula provided by Mike.

Thanks for the clarification. Yes, you should be able to apply the same formula provided by Mike.

Thanks for the clarification. Yes, you should be able to apply the same formula provided by Mike.

Please forgive my ignorance as I'm not a developer but how would I apply that formula to my own and make it work? I've tried a few things but keep getting this error, "The Y expression for the chart Chart1 has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset."

Please forgive my ignorance as I'm not a developer but how would I apply that formula to my own and make it work? I've tried a few things but keep getting this error, "The Y expression for the chart Chart1 has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset."

Please forgive my ignorance as I'm not a developer but how would I apply that formula to my own and make it work? I've tried a few things but keep getting this error, "The Y expression for the chart Chart1 has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset."

Replace his reference to the parameter with your field reference. =Replace(Join(Fields!Field1.Value,","),";#",Nothing)

Replace his reference to the parameter with your field reference. =Replace(Join(Fields!Field1.Value,","),";#",Nothing)

Replace his reference to the parameter with your field reference. =Replace(Join(Fields!Field1.Value,","),";#",Nothing)

I just read the thread again and it sounds like you want to split the multivalue field you are retrieving from SharePoint into separate fields. While the source is multivalue, it is all retrieved in a single field in your dataset. To make it into individual fields, you can add calculated fields. Open your dataset properties and select the fields tab. Click to add a calculated field. Assign a name to the field (NewField1). Click the function button (fx) to get the value for the new field using a formula. Enter a formula like: =Split(Fields!Field1.Value,";#")(1) You will notice that I am setting NewField1 value to the second element of the array that results when you split Fields!Field1.Value from the SharePoint list. This is because according to your original post, SharePoint is returning ";#" before the first value. When splitting, this will create an empty value in the 0 element. Repeat this for each possible value. If the sharepoint column might has as many as 5 values selected then create 5 calculated fields in your dataset using the same formula in each except that you will increment the element. So NewField2 will have a value formula: =Split(Fields!Field1.Value,";#")(2) When the source field (Fields!Field1) has fewer values than you have calculated fields, the additional calculated fields will have a null value. You can handle the null value in your report by testing for null in the cell that displays the field value. So if I have a table that displays the 5 calulated fields, 1 in each of 5 columns, rather than setting the table cell value to Fields!NewField1.Value, Fields!NewField2.Value, etc., use a formula to test for null and provide alternate text when null is found: =IIf(IsNothing(Fields!NewField1.Value),"No Value",Fields!NewField.Value)

I just read the thread again and it sounds like you want to split the multivalue field you are retrieving from SharePoint into separate fields. While the source is multivalue, it is all retrieved in a single field in your dataset. To make it into individual fields, you can add calculated fields. Open your dataset properties and select the fields tab. Click to add a calculated field. Assign a name to the field (NewField1). Click the function button (fx) to get the value for the new field using a formula. Enter a formula like: =Split(Fields!Field1.Value,";#")(1) You will notice that I am setting NewField1 value to the second element of the array that results when you split Fields!Field1.Value from the SharePoint list. This is because according to your original post, SharePoint is returning ";#" before the first value. When splitting, this will create an empty value in the 0 element. Repeat this for each possible value. If the sharepoint column might has as many as 5 values selected then create 5 calculated fields in your dataset using the same formula in each except that you will increment the element. So NewField2 will have a value formula: =Split(Fields!Field1.Value,";#")(2) When the source field (Fields!Field1) has fewer values than you have calculated fields, the additional calculated fields will have a null value. You can handle the null value in your report by testing for null in the cell that displays the field value. So if I have a table that displays the 5 calulated fields, 1 in each of 5 columns, rather than setting the table cell value to Fields!NewField1.Value, Fields!NewField2.Value, etc., use a formula to test for null and provide alternate text when null is found: =IIf(IsNothing(Fields!NewField1.Value),"No Value",Fields!NewField.Value)

I just read the thread again and it sounds like you want to split the multivalue field you are retrieving from SharePoint into separate fields. While the source is multivalue, it is all retrieved in a single field in your dataset. To make it into individual fields, you can add calculated fields. Open your dataset properties and select the fields tab. Click to add a calculated field. Assign a name to the field (NewField1). Click the function button (fx) to get the value for the new field using a formula. Enter a formula like: =Split(Fields!Field1.Value,";#")(1) You will notice that I am setting NewField1 value to the second element of the array that results when you split Fields!Field1.Value from the SharePoint list. This is because according to your original post, SharePoint is returning ";#" before the first value. When splitting, this will create an empty value in the 0 element. Repeat this for each possible value. If the sharepoint column might has as many as 5 values selected then create 5 calculated fields in your dataset using the same formula in each except that you will increment the element. So NewField2 will have a value formula: =Split(Fields!Field1.Value,";#")(2) When the source field (Fields!Field1) has fewer values than you have calculated fields, the additional calculated fields will have a null value. You can handle the null value in your report by testing for null in the cell that displays the field value. So if I have a table that displays the 5 calulated fields, 1 in each of 5 columns, rather than setting the table cell value to Fields!NewField1.Value, Fields!NewField2.Value, etc., use a formula to test for null and provide alternate text when null is found: =IIf(IsNothing(Fields!NewField1.Value),"No Value",Fields!NewField.Value)

This syntax doesn't generate an error but now I don't get my chart either. I'm trying to disaply a pie chart with the count of each field value and that syntax seems to remove the "count" formula so nothing gets displayed. Is it still possible to include that?

This syntax doesn't generate an error but now I don't get my chart either. I'm trying to disaply a pie chart with the count of each field value and that syntax seems to remove the "count" formula so nothing gets displayed. Is it still possible to include that?

Then you don't need to create separate fields. You can count the occurrences of a specific selection within that field as follows: =Sum(IIf(InStr(Fields!Field1.Value,";#Value1;#")>0,1,0)) The above will get the sum of 1 for each record that contains Value1 in the Fields!Field1.Value. This effectively Counts the occurences of Value1. Repeat for each value you want to count. The native format of the field value (;# before and after each distinct value) actually is a good thing. By adding ;# before and after the value to count, we know that we won't get false hits. For instance, if we have a value of Value1 through Value50 in the Sharepoint list that can be selected, if you search for just Value1 without the leading and trailing ;#, you will end up counting the occurrences of Value1, Value10, Value11, etc. because they all have the string Value1 in them. Counting the occurrences of ;#Value1;# means that Value10, Value11 etc will not match so we count only the Value1 strings.

Then you don't need to create separate fields. You can count the occurrences of a specific selection within that field as follows: =Sum(IIf(InStr(Fields!Field1.Value,";#Value1;#")>0,1,0)) The above will get the sum of 1 for each record that contains Value1 in the Fields!Field1.Value. This effectively Counts the occurences of Value1. Repeat for each value you want to count. The native format of the field value (;# before and after each distinct value) actually is a good thing. By adding ;# before and after the value to count, we know that we won't get false hits. For instance, if we have a value of Value1 through Value50 in the Sharepoint list that can be selected, if you search for just Value1 without the leading and trailing ;#, you will end up counting the occurrences of Value1, Value10, Value11, etc. because they all have the string Value1 in them. Counting the occurrences of ;#Value1;# means that Value10, Value11 etc will not match so we count only the Value1 strings.

Then you don't need to create separate fields. You can count the occurrences of a specific selection within that field as follows: =Sum(IIf(InStr(Fields!Field1.Value,";#Value1;#")>0,1,0)) The above will get the sum of 1 for each record that contains Value1 in the Fields!Field1.Value. This effectively Counts the occurences of Value1. Repeat for each value you want to count. The native format of the field value (;# before and after each distinct value) actually is a good thing. By adding ;# before and after the value to count, we know that we won't get false hits. For instance, if we have a value of Value1 through Value50 in the Sharepoint list that can be selected, if you search for just Value1 without the leading and trailing ;#, you will end up counting the occurrences of Value1, Value10, Value11, etc. because they all have the string Value1 in them. Counting the occurrences of ;#Value1;# means that Value10, Value11 etc will not match so we count only the Value1 strings.

Thanks Tim, I was able to come up with a solution using ideas from a few of your posts.

Thanks Tim, I was able to come up with a solution using ideas from a few of your posts.