PivotTable: possible to display Data Item field as it is?
 

I have two Data Item fields. One is numeric and the other is text. For
the numeric field, I want to use the sum summarization function. But
for the text field, I just want to display it as it is.

Is it possible to display the data item field as it is instead of using
any summarization function?

Thanks!
blackpuppy

PivotTable: possible to display Data Item field as it is?
 

Yes, very easy - put the column heading of the text field in your list of
fields to be summarised, then add the same field to the data section of the
layout, and summarise as Count. Double click the text field name and click to
No total to streamline the report. The report will include all the values in
the text field and give each one an incidence of 1.
Good luck!
Hope your text is less than 255 characters... (my problem is that my text
fields are mostly longer and Excel truncates them)

"blackpuppy" wrote:

I have two Data Item fields. One is numeric and the other is text. For
the numeric field, I want to use the sum summarization function. But
for the text field, I just want to display it as it is.

Is it possible to display the data item field as it is instead of using
any summarization function?

Thanks!
blackpuppy

PivotTable: possible to display Data Item field as it is?
 

Thanks, katep!

I want to use Excel to produce some reports. Although Reporting Service
may be a better choice for this task, due to some limitations on the
user side, we have to use Excel for the time being.

The layout of my report is like this. Please view it with fixed font
instead of proportional font.

Product 1 Product 2 ... SubTotal
Area A <Text A1 <Amount A1 <Text A2 <Amount A2 ... <SubTotal A
Area B <Text B1 <Amount B1 <Text B2 <Amount B2 ... <SubTotal B
....
GrandTotal <GrandTotal 1 <GrandTotal 2 <GrandTotal

1. use the Area as a Row Field
2. use the Product as a Column Field
3. use the Text and Amount fields as the Data Item fields.

Is it possible to realize the above layout? Using the method from
katep in the previous post, I can show the Text as it is. But I find
the Text will be shown in the Row area instead of the Data Item area.
Maybe I didn't do it correctly?

Another problem is that the data is different every time the report is
produced. My .NET 2.0 WinForm application will produce the data in XML
format. And then import the XML into Excel 2003 using the menu Data --
XML -- XML Data Source. Then I drag the XML data into a data
worksheet and create the PivotTable in another worksheet. The problem
is that I do not know how many rows there will be before running the
app to produce the XML. From the PivotTable doc and sample, I think it
requires the data source to be fixed size with no blank rows.

Thanks!
blackpuppy

PivotTable: possible to display Data Item field as it is?
 

I can use a dynamic XML mapping as a PrivotTable's data source now.

See Excel -- Pivot Tables -- Dynamic Data Source
(http://www.contextures.com/xlPivot01.html).

I do it in the following steps.
1. Put the XML in a worksheet named Data.
2. Define a named range called ReportData.
3. Create a PivotTable and specify the named range ReportData as its
data source.
4. Every time the external XML data is generated or changed, refresh
XML data, then refresh PivotTable Data.

Thanks!