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

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!