TRANSFORM statements in ADO (Excel)

[Guest]

I require a report (in Excel) using data stored in Excel whcih uses the
PIVOT/TRANSFORM SQL function. I've attempted this in ADO as unfortunately
the Pivot Table function in Excel doesn't appear to be able to support my
complex calculation (Sum(AVE)/Sum(Amt))

My connection string etc works - But the SQL string is rejected error.

Basically the query reads as follows:

TRANSFORM (Sum([Ave])/Sum([Amt])) AS EXP1
SELECT [Deal Type], Subbranch, Ccy
FROM [Group Data$]
GROUP BY [Deal Type], Subbranch, Ccy
PIVOT Sheet;

I could build this in Access (as I know it works there) but I've started in
Excel and I thought ADO could handle this.

however I get an error message saying
"Jet engine does not recognise the field name sheet" (abreviated version)

Any help would be much appreciated.

Kind Regards
Rob

 

[cpnet]

TRANSFORM is something that is specific to Access. I think ADO MD might
allow you to do some pivot table-like stuff, but that's not .NET, that's
just regular Win32.

However, you say your source data is in Excel, and the result should be an
Excel Pivot table, so I'd just stick with Excel. Without knowing your whole
spreadsheet setup, I think you can probably do what you want by creating a
calculated field:

= Ave / Amt

If you add this calculated field to your Pivot Table as a Data Field, then
Excel will actually treat it as "Sum(Ave)/Sum(Amt)"

 

[Guest]

Thanks cpnet

Unfortunatly I wanted the sum of the values to divide each other which Pivot
tables did not appear to support.

My code was correct and does actually work. The only issue was - I was
importing the data into the sheet and then trying to run the ADO without
saving the sheet first. (which I forgot)

Thanks again for your help.