CODEWITHSUNDEEP
sql-server-2005reporting-services
Node17
Node17

Reputation: 537

query column returns multiple readings and needs to be multiple cells in ssrs

I have a cell from a query, returning multiple readings as below with a maximum of 8

|_____readings_____|
|1;2;3;..., 8      |

In my SSRS report, I need each reading to be in a seperate column, e.g

| a | b | c | ...|
| 1 | 2 | 3 | ...|

I am using the 2005 version of ssrs and sql server Could anyone help? Kind regards

Upvotes: 0

Views: 96

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

Report-level

You can use the Split function to take a delimited string and return an array; based on this you can specify the element you want from 0-7 to get your eight columns.

In an expression you'd do something like this:

=Split(fields!readings.Value, ";")(0) (1st element) or

=Split(fields!readings.Value, ";")(7) (8th element)

The problem with this is when there is less than 8 elements in the readings field; you will get an error reported - wrapping the expression in an IIf is not enough as this doesn't short circuit in SSRS and any problem string will error regardless.

To deal with these issues you can move the logic to custom code embedded in the report:

Function ElementByNumber(fieldValue As String, elementNumber As Integer) As String

  If Split(fieldValue, ";").Length < elementNumber

    ElementByNumber = Nothing

  Else

    ElementByNumber = Split(fieldValue, ";")(elementNumber - 1)

  End If

End Function

You can then reference this in the report like:

=Code.ElementByNumber(fields!readings.Value, 8)

Repeat as required for each column you need.

Database level

The other non-SSRS specific workaround would be to handle this, if possible, at the database level, and just use the unpivoted data as a base for a Matrix in the report.

Erland Sommarskog has a series of articles under Arrays and Lists that present any number of methods to split strings in SQL Server; this SO question has a bunch of other alternatives.

Obviously if you're dealing with a fixed Data Source/DataSet this might not be an option.

Upvotes: 1

Related Questions