user3654442 user3654442 - 1 month ago 24
Vb.net Question

Passing an int array into an SSRS chart?

I have a comma delimited string full of numbers and created a vb function to convert it into an integer array but this doesn't work. What data type is it expecting and how can I do something similar to what I'm trying to do?

Function StringArrayToIntArray(ByVal s As String) As Integer()

Dim ints = Array.ConvertAll(s.Split(","c), Addressof Convert.ToInt32)
return ints

End Function

Answer

In case you're still looking for an answer: SSRS charts don't accept arrays out of the box. They are meant to be used with datasets, so if at all possible, I'd recommend to create a dataset that provides the data you want, rather than trying to make it work with arrays.

That said, if you really need to (e.g. you have to plot a multi-value report parameter), you can make it work with a trick I've been using:

  1. Create a dummy dataset with integer indices into the array
  2. Create an array for the x values
  3. Create an array for the y values
  4. Hook them up in the chart

For step 1, create a new dataset you like. For example, if you have an SQL data source you could use

SELECT        row_number() OVER (ORDER BY ID) - 1 AS ID
FROM            [SomeTable]

Or, if you have an embedded XML dataset, you could use

<Query>
<XmlData>
<?xml version="1.0"?>
<IDs>
<ID>0</ID>
<ID>1</ID>
<ID>2</ID>
</IDs>
</XmlData>
</Query>

For steps 2 and 3, you can use the split function or custom code.

Step 4 is where it gets interesting. First, make sure to use the dummy ID dataset as the chart category (Example screenshot). Next, add a series to the chart. Open the series properties, for the "Value field", use the following expression

=<NumArrayY>(Fields!ID.Value)

where is your numeric value array (e.g. the output of your custom code). Then, for the "Category field", use a similar expression for the x values

=<NumArrayX>(Fields!ID.Value)

This will do what you want, but it's not particularly pretty or obvious to others working on the same report. Note that, if the ID dataset contains too many rows for your arrays, you will get a runtime error:

[rsRuntimeErrorInExpression] The X expression for the chart ‘Chart1’ contains an error: Index was outside the bounds of the array.

This report still works, though.