Pivot Table Woes

[jonbrown]

Hi All-
I have learned a great deal about pivot tables by reading the articles in this board but this one has me stumped. I have a table with data that is pulled from an odbc database. I then summarize the data into a pivot table.

Rows Across

Currently I have

<TABLE style="WIDTH: 450pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=598 border=0><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" span=2 width=78><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #b8cce4 1pt solid; WIDTH: 84pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=112 height=20>JUNE UNITS</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 26pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=35></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=110>JUNE SALES</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=71></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=72>UNITS YTD</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 32pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=42></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 59pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=78>SALES YTD</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 59pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=78></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20>2007</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2008</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2007</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2008</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2007</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2008</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2007</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2008</TD></TR></TBODY></TABLE>

What I need however is

June Units 2007 2008 +/-


the plus minus column would be governed by this formula
assuming that June Units 2007 = A1 and June Units 2008 = A2 for sake of argument and example.

=(A1-A2)/A2

To calculate. However I cant seem to be able to generate a calculation like this in excel. Any ideas? Here is the excel file for those who can help!

http://www.donfoshays.com/excel.zip

 

[mack5511]

Jon,


If you can test this in SQL Server or another Query editor then it is easier to manipulate the query till it gives the results you are looking for.

SQL Server Express 2005
Oracle SQL Developer
these are free tools by the companies and can connect to almost any odbc database
and sourceforge.org has some freeware SQL editors that are independent of database type


mack

 

[jonbrown]

I get an error

[MERANT][ODBC PROGRESS driver][PROGRESS]**Invalid Statement. (254)

And

[MERANT][ODBC PROGRESS driver][PROGRESS]**Unable to understand after --"DECLARE". (247)

I am assuming that my odbc query might be different than odbc query, i know there is a difference between sql and odbc as far as queries. This might clarify and or even help find a better solution but I need that -/+ column after each of my headers. so it would be.

June Units 2007 2008 +/-, June Sales 2007 2008 +/-, YTD Units 2007 2008 +/-, YTD Sales 2007 2008 +/-.

Perhaps by calculating the -/+ for the YTD figures, we can use that data to drive the columns for the June based figures through the pivot table? Just an idea....

Thanks so much for your help!!!

 

[mack5511]

Jon,

ODBC is a connection type to a database. SQL is a universal query language. Although every database type has subtle differences in the way SQL works with it. The query I sent should work but it may need to be tweaked against the odbc database. That is why I suggested using one of the SQL query editors as many of them have hints as to the nature of an error. I am getting ready to leave for the day in an hour or so. I will continue to try and help you get this figured out. I am not sure of your time frame but I will be available again on Monday. You should be able to pull that data into excel with calculated fields. I have done this many times with MS Query.

Also if you can use aliases you should be able to name your fields however you want them within reason. I also think you can edit the names in the Pivot Table after you have the data in place.


Mack

 

[mack5511]

Jon,

Try the query like this.


SELECT customer.CNum,
customer.L-Name ,
customer.r-slm1 ,
customer.LocNum ,
r-sales.munits__1 ,
r-sales.munits__2 ,
r-sales.munits__3 ,
r-sales.munits__4 ,
r-sales.munits__5 ,
r-sales.munits__6 ,
(
(SELECT r-sales.munits__6 FROM r-sales WHERE r-sales.rs-YEAR = 2007
)-
(SELECT r-sales.msales__6 FROM r-sales WHERE r-sales.rs-YEAR = 2008
) /
(SELECT r-sales.msales__6 FROM r-sales WHERE r-sales.rs-YEAR = 2008
)) ,
r -sales.munits__7 ,
r -sales.munits__8 ,
r -sales.munits__9 ,
r -sales.munits__10 ,
r -sales.munits__11 ,
r -sales.munits__12 ,
r -sales.msales__1 ,
r -sales.msales__2 ,
r -sales.msales__3 ,
r -sales.msales__4 ,
r -sales.msales__5 ,
r -sales.msales__6 ,
r -sales.msales__7 ,
r -sales.msales__8 ,
r -sales.msales__9 ,
r -sales.msales__10 ,
r -sales.msales__11 ,
r -sales.msales__12 ,
r -sales.rs-YEAR
FROM adist.customer customer,
adist.r -sales r-sales
WHERE customer.CNum = r-sales.custnum
AND ((customer.LocNum ='05'))

 

[jonbrown]

Yea, I have no specific time frame, the report that I have now took me two weeks to figure out including the query and the table framing it up etc... I gave it to my boss, and he was amazed he is happy for now but I know he wants those pesky -/+ to show exactly where each customer is compared to last year. Basically this is a once a month report so my thought process was to gather a large pool of data so that when it came time to make a new report all i would have to do is refresh the query get the new data, refresh the pivot table and change the month. I dont like to put a lot of effort into something I know excel can help me do easily! Thanks again have a great weekend we will pick back up on this Monday I will also try another editor.

Jon

 

[jonbrown]

Nope same exact errors

 

[mack5511]

Jon,

OK well I was hoping it was the "ratios" alias but no luck. I am not sure how your database handles numbers vs text you could try '2007' for the year. I am making the assumption that
r-sales.rs-YEAR is 2007, 2008 etc. Another thing you can do is google the error your getting and see if others are having problems with certain types of sql statements. That is where I found the alias error for your type of database. Have a good weekend and I will be around on Monday.


Mack

 

[jonbrown]

Thanks Mack, see you monday I will keep plugging away!

Jon

 

[mack5511]

Jon,

I found a database forum that deals with Progress. That might be another place for you to ask about your problem. Here is the url http://www.dbforums.com
It is a free registration and if you look in the forums and go to the "Other" forum it deals with Progress. There will be people who have experience with Progress that will know the correct syntax for the query. I will keep trying to figure it out. I hope this will help I know with many of my SQL Server or Oracle questions these forums have been invaluable.


Let me know how it is going.

Mack

 

[RoryA]

I think you just need a Calculated Item added to your table that is based on the rs_year and uses: =('2007'-'2008')/'2008' as the formula.