Multiple values within a single pivot table cell

[#1]

Hi,

I have a multivalued field that needs to be displayed in a single column(or expression) in a pivot table. Is there a way to split a pivot cell to hold more than one value ?(Ideally, I don't want to add another expression/column or group fields together).

For a particular date, I have a field AM_PM which can contain either a null value, a single entry(either 'AM' or 'PM') or mutliple entries(both 'AM' and 'PM'). My purpose is to display a list of dates with the corresponding AM_PM values in a single column in a pivot chart. I am able to display the first entry in case of multiple entries for the field, but cannot read the second(multiple) entry in the field.

Date AM_PM Category

12/2/2010 - -

12/3/2010 AM 50

12/4/2010 PM 53

12/5/2010 AM 50

12/5/2010 PM 53

In the last case(12/5/2010), I can get the AM entry and category properly to display properly in the pivot chart, but not the PM entry.

Thanks

[#2]

[#3]

Is this what you are looking for?

[#4]

Thanks for your prompt response, Priyadarshini. If I understand correctly, you have used date and AM_PM as dimensions and then used sum(Category) as an expression to get the pivot chart you displayed I think. If I have understood correctly, that is not exactly what I need.

The display above is the exact format in which I need the chart. The colors in the cells are based on the Category field and the entry in the cell itself is the AM_PM field. Note that blank white cell, that is where there is an entry equivalent to the 12/5/2010 entry I mentioned earlier in the post. I hope this explains my query better. Thanks again.

[#5]

If you have "12/5/2010 AM 53 " will not show up .. Now I understand the problem

[johnw · Accepted solution]

Perhaps this?

concat(distinct AM_PM,'&')

[#7]

Thanks a ton John. That allowed me to read multiple values for a field entry and in turn display them the pivot cell.