| Home |
| Search |
| Today's Posts |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Codeing behind a PivotTable
Perfect! thanks.
"Ron Coderre" wrote: You might try using the Pivot Play add-in available at this website: http://www.contextures.com/xlPivotPlay01.html It was primarily built for 2 purposes: 1) To redirect a Pivot Table's query to a different database 2) To directly edit the SQL code used by a Pivot Table to pull external data. Is that something you can work with? (Post back with more questions) *********** Regards, Ron XL2002, WinXP "Angus" wrote: Should I amend the SQL statement at MSQuery for pivottable? it doesn't work. But it works if I amend the SQL statement at ADO code. Then I have to extract the data to a worksheet and then make a pivottable from there. It is ok to make the report in that way but how to make the date sort properly if I directly put the data in pivottable? "Ron Coderre" wrote: Since those values are sorting as text and not numbers, you need to convert those values from text to dates. Try this: DATEVALUE(Writing_Order_Replenish.Order_month) Does that help? *********** Regards, Ron XL2002, WinXP "Angus" wrote: Now the order is 1/1/2008, 10/1/2007, 11/1/2007, 12/1/2007, 2/1/2008, 3/1/2008... "Ron Coderre" wrote: What are we dealing with? What values (instead of the proper dates) are displaying in the Pivot Table? *********** Regards, Ron XL2002, WinXP "Angus" wrote: I have similar question, but the data format in pivottable that i retrieve from database is in "mm/dd/yyyy" format. Pivottable doesn't recognise this as date. Following is my code at MS Query, i want to re-format Order_month: SELECT Writing_Order_Replenish.Market, Writing_Order_Replenish.Scode, Writing_Order_Replenish.Description, Writing_Order_Replenish.Port_destination, Writing_Order_Replenish.Status, Writing_Order_Replenish.Order_month, Writing_Order_Replenish.Quantity, Writing_Order_Replenish.First_order_month, Writing_Order_Replenish.Market_PO FROM `C:\Documents and Settings\zi38610\Desktop\MIQ\Data\MIQ`.Writing_Ord er_Replenish Writing_Order_Replenish "Ron Coderre" wrote: Try something like this in your SQL: cdate(mid(myTable.FakeDate,5,2) & '/' & right(myTable.FakeDate,2) & '/' & left(myTable.FakeDate,4)) AS 'MyDate' Note: replace "myTable" with your actual table name and replace "FakeDate" with your actual field name Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Simon Shaw" wrote: Hi, I am building a PivotTable connected to an ACCPAC database table. My problem is that the date fields in ACCPAC are stored as YYYYMMDD, as a result it is not regonized by the PivotTable as a date field. The PivotTable treats the date fields like numbers so I cannot group them by months, years, etc. I would like to manually modify the SQL in MS Query to format the field during the query so it returns as a date field. Does anyone know the SQL to acheive this? or should I be asking this in an SQL forum? Thanks -- Simon Shaw, CA President Kode101 Inc. www.kode101.com |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a PivotTable w/o selecting data in an existing PivotTable | Excel Discussion (Misc queries) | |||
| Codeing for hiding sheet | Excel Programming | |||
| How does one get the PivotTable report info that feeds another PivotTable report? | Excel Programming | |||
| PivotTable - PivotTable Field name is not valid - error! | Excel Programming | |||
| help in codeing | Excel Programming | |||
Threaded Mode
