SQL Codeing behind a PivotTable

[Simon Shaw]

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

 

[Ron Coderre]

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]

Hi Ron,

Were do I place your code in the SQL?

I have

Select mytables.myfields
From mytables
Where mytable.DateField>=20060101

I tried your code as part of the Select statement, then after the where
statement. no luck so far.

 

[Simon Shaw]

here is my actual code:

SELECT OEINVD.ITEM, OEINVD.CATEGORY, OEINVH.CUSTOMER, OEINVH.BILNAME,
OEINVH.TERRITORY, OEINVD.LOCATION, OEINVH.SHIPTO, OEINVD.ORDNUMBER,
OEINVH.PONUMBER, OEINVH.INVNUMBER, OEINVH.INVDATE, OEINVD.UNITPRICE
FROM OEINVD OEINVD, OEINVH OEINVH
WHERE OEINVD.INVUNIQ = OEINVH.INVUNIQ AND ((OEINVH.CUSTOMER In ('1200')) AND
(OEINVH.INVDATE>=20090101 And OEINVH.INVDATE<=20091231))
cdate(mid(OEINVH.INVDATE,5,2) & '/' & right(OEINVH.INVDATE,2) & '/' &
left(OEINVH.INVDATE,4)) AS InvoiceDate

I also tried

SELECT OEINVD.ITEM, OEINVD.CATEGORY, OEINVH.CUSTOMER, OEINVH.BILNAME,
OEINVH.TERRITORY, OEINVD.LOCATION, OEINVH.SHIPTO, OEINVD.ORDNUMBER,
OEINVH.PONUMBER, OEINVH.INVNUMBER, cdate(mid(OEINVH.INVDATE,5,2) & '/' &
right(OEINVH.INVDATE,2) & '/' & left(OEINVH.INVDATE,4)) AS InvoiceDate,
OEINVD.UNITPRICE
FROM OEINVD OEINVD, OEINVH OEINVH
WHERE OEINVD.INVUNIQ = OEINVH.INVUNIQ AND ((OEINVH.CUSTOMER In ('1200')) AND
(OEINVH.INVDATE>=20090101 And OEINVH.INVDATE<=20091231))

 

[Ron Coderre]

Your basic structure in the second example seems correct. You may want to
check that the concatenation operators are correct for the database your
querying against. The ampersand (&) may be inappropriate. Also, the single
quotes may need to be double-quotes (").

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Ron Coderre]

One other thought.....

Maybe ACCPAC SQL doesn't have LEFT, RIGHT, and MID functions. Check the SQL
documentation...perhaps those should be replaced by SUBSTRING (or something
else).

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Simon Shaw]

I have tried a variety of options. nothing seems to work

I found the valid functions for the Pervasive database listed at:
http://www.pervasive.com/library/docs/iarc/860/rifl_language/rifl_language-3-001.html

It looks like cdate, mid, left and right functions are all valid.

the error I keep getting in MS Query is:
"Driver not capable."

Any idea which driver? is this something I can update?

I can't seem to find anything on the pervasive site regarding driver
problems with excel.

Thanks,

 

[Simon Shaw]

Is there some way that I could progamatically change the PivotItem values of
the Date Field in the PivotCache? so that it is recognized as a date field
for grouping?

 

[Angus]

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_Order_Replenish
Writing_Order_Replenish

 

[Ron Coderre]

What are we dealing with?
What values (instead of the proper dates) are displaying in the Pivot Table?

***********
Regards,
Ron

XL2002, WinXP

 

[Angus]

Now the order is 1/1/2008, 10/1/2007, 11/1/2007, 12/1/2007, 2/1/2008,
3/1/2008...

 

[Ron Coderre]

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]

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]

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]

Perfect! thanks.

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