I would like to create a chart based on two column values in a SharePoint list (Title column can be used as a filter):

Title: Entry 1

Produce: Apples; Oranges; Bananas

Quantity: 10; 8; 14

I would like a bar chart that has Apples, Oranges & Bananas on the X Axis and the quantity values on the Y axis. If there is a simple way to do this then one could easily use a "merged" data column as produced by MS InfoPath.

Hi,

We are not sure that we understand the organization of your dataset.

In general if you have multiple categories in a single field, you need to split that to records, so your dataset must be in some plottable format.
So you cannot have Apples; Oranges; Bananas in a single record value for the Produce column, instead you need to have a single record for Apples that has Quantity 10,
a single record for Oranges that has Quantity 8 and so on.

Please let us know if we did not interpret the dataset correctly...

Hi Bob,

A very tough requirement, since the dataset is not well organized for pivot purposes. However you can try these:

1. Create one category with grouping expression: =true - e.g. make a single data cell for the entire table.
2. For each choice that may appear in the list (Vanilla, Strawberry etc.) create a Values data groping with this expression:

=SUM(TONUM(LIKE(SPLIT(Fields!Produce, ";"), "Vanilla")) * TONUM(SPLIT(Fields!Quantity, ";")))

Lets explain this expresison a little. The split fuction splits an array of strings to another array of strings by a given delimiter.

The Fields!Produce and Fields!Quantity in our case return arrays, containing all records from the data set. If we have this set:

Vanilla; Bananas - 10; 20
Oranges; Vanilla - 30; 10

These functions will return these arrays:
Vanilla, Bananas, Oranges, Vanilla
10, 20, 30, 10

The LIKE function is a chain function, so we transform the first string array into a boolean array the arrays become:
true, false, false, true,
10, 20, 30, 10

The multiplication operator is also chained - so we multiply the two arrays giving us the result array of:
10, 0, 0, 10

Finally we sum with SUM funciton. So for the bar Vanilla we get a value of 20.
The other Values Data Grouping (for Bananas, Oranges etc.) simply have the same value expression but with Vanilla replaced with Bananas, Oranges etc.

So you can create a bar for each type of product and they will appear in a single cluster. Unfortunately you can not create a separate category, but you can use the legend to show the series - which will do the job we think.