Pivot Table Formula

[daveWP]

Hi guys, I have a table of data in excel, that I'm merging into a pivot table.

One of the fields is called "Transaction Type", which is coded in each cell as either "Debit" or "Credit", with no null values.

One of the fields I want to calculate in my report is the (debit - credit). I know how to do cross field calculations, but I was wondering if its possible to do this type of calculation within a field based on this criteria without changing the source data?

For instance, a work around would be putting a negative (-) infront of all the values in the rows coded as "credit" and just using the regular subtotal feature.

But that would be changing the data. I'm also trying to accomplish this without using a crutch column.

I'm working with excel 2003.

 

[Fazza]

Hi, Tai.

When problems like this arrive, please test on just one simple example.

Excel 2000 might be quite different - so interesting to hear how you go with Excel 2003.

A comment. I see the posted SQL contains "double quotation marks" around field names. I'm not familiar with these in my work with Excel [99.99%] or Access [0.01% of my work]. Maybe OK for you though; I mention is just in case.

I suggest starting with a single pivot table and checking it refreshes OK before any changes. Then the simplest step, such as,

sub simple()
 with activesheet.pivottables(1).pivotcache
  debug.print .sql
  .sql=.sql
  debug.print vbcr & .sql
 end with
end sub

Or something like that. Just to see the original working SQL before changes, test if you can write back to the SQL and it still works OK, and the SQL after.

Maybe add a variable in there instead. Whatever. Same difference.

sub simple()
 
 dim strSQL as string
 
 with activesheet.pivottables(1).pivotcache
  strSQL=.sql
  debug.print strSQL
  .sql=strSQL
  debug.print vbcr & .sql
 end with
 
end sub

F

 

[taigovinda]

Fazza,

I really appreciate your patience with this. Your sub simple (top version, no variable) got an error 1004 on the .sql=.sql line. However, I noticed that the symbols that look like this | did not show up in the immediate window. This is what showed up in the immediate window:

SELECT US_CATSProd.Region, US_CATSProd."Sub-region", US_CATSProd.Division, US_CATSProd.CC, US_CATSProd.PIN, US_CATSProd.ConstCode, US_CATSProd.Date, US_CATSProd.TimeSlipNo, US_CATSProd.District, US_CATSProd.SubDistrict, US_CATSProd."Run Number", US_CATSProd."Orig Station", US_CATSProd."Orig Station Name", US_CATSProd."Dest Station", US_CATSProd."Dest Station Name", US_CATSProd."Train ID", US_CATSProd."Employee Type", US_CATSProd."Job Type", US_CATSProd."Gross Amount", US_CATSProd."OT Amount", US_CATSProd.BA, US_CATSProd.Pct, US_CATSProd."Order date", US_CATSProd."Order time", US_CATSProd."Complete date", US_CATSProd."Complete time", US_CATSProd."Pay Year", US_CATSProd."Pay period", US_CATSProd.Extra, US_CATSProd."Dist-Sub", US_CATSProd."Short Train ID", US_CATSProd.Split, US_CATSProd."Split High-Low", US_CATSProd."Emp Name", US_CATSProd.Company, US_CATSProd."Regular HHMM", US_CATSProd."Overtime HHMM", US_CATSProd."OT Dollars", US_CATSProd."Regular Time", US_CATSProd."OT Time", US_CATSProd."Reg HH", US_CATSPr
od."Reg MM", US_CATSProd."OT HH", US_CATSProd."OT MM", US_CATSProd.Terminal, US_CATSProd.Year, US_CATSProd.Month
FROM sqlp_cats.oconno05.US_CATSProd US_CATSProd
WHERE (US_CATSProd.BA='401') AND (US_CATSProd.Year=10) AND (US_CATSProd.Month=3)
ORDER BY US_CATSProd.Date

...So I'm wondering if I need to try and add the | symbols back into there. I tried this:

  Debug.Print Replace(.Sql, Chr(10), Chr(124))
  .Sql = Replace(.Sql, Chr(10), Chr(124))

and it still gave error 1004, though it seemed to add the symbols back in correctly, as this is what appeared in the immediate window:

SELECT US_CATSProd.Region, US_CATSProd."Sub-region", US_CATSProd.Division, US_CATSProd.CC, US_CATSProd.PIN, US_CATSProd.ConstCode, US_CATSProd.Date, US_CATSProd.TimeSlipNo, US_CATSProd.District, US_CATSProd.SubDistrict, US_CATSProd."Run Number", US_CATSProd."Orig Station", US_CATSProd."Orig Station Name", US_CATSProd."Dest Station", US_CATSProd."Dest Station Name", US_CATSProd."Train ID", US_CATSProd."Employee Type", US_CATSProd."Job Type", US_CATSProd."Gross Amount", US_CATSProd."OT Amount", US_CATSProd.BA, US_CATSProd.Pct, US_CATSProd."Order date", US_CATSProd."Order time", US_CATSProd."Complete date", US_CATSProd."Complete time", US_CATSProd."Pay Year", US_CATSProd."Pay period", US_CATSProd.Extra, US_CATSProd."Dist-Sub", US_CATSProd."Short Train ID", US_CATSProd.Split, US_CATSProd."Split High-Low", US_CATSProd."Emp Name", US_CATSProd.Company, US_CATSProd."Regular HHMM", US_CATSProd."Overtime HHMM", US_CATSProd."OT Dollars", US_CATSProd."Regular Time", US_CATSProd."OT Time", US_CATSProd."Reg HH", US_CATSPr
od."Reg MM", US_CATSProd."OT HH", US_CATSProd."OT MM", US_CATSProd.Terminal, US_CATSProd.Year, US_CATSProd.Month
|FROM sqlp_cats.oconno05.US_CATSProd US_CATSProd
|WHERE (US_CATSProd.BA='401') AND (US_CATSProd.Year=10) AND (US_CATSProd.Month=3)
|ORDER BY US_CATSProd.Date

...I also tried replacing the double quotes with single quotes and still got the same error 1004. Is that what you meant when you mentioned the double quotes - that it would ordinarily be single quotes? Or you just wouldn't expect to see field names that are more than one word long, AND there would be no quotes?

SELECT US_CATSProd.Region, US_CATSProd.'Sub-region', US_CATSProd.Division, US_CATSProd.CC, US_CATSProd.PIN, US_CATSProd.ConstCode, US_CATSProd.Date, US_CATSProd.TimeSlipNo, US_CATSProd.District, US_CATSProd.SubDistrict, US_CATSProd.'Run Number', US_CATSProd.'Orig Station', US_CATSProd.'Orig Station Name', US_CATSProd.'Dest Station', US_CATSProd.'Dest Station Name', US_CATSProd.'Train ID', US_CATSProd.'Employee Type', US_CATSProd.'Job Type', US_CATSProd.'Gross Amount', US_CATSProd.'OT Amount', US_CATSProd.BA, US_CATSProd.Pct, US_CATSProd.'Order date', US_CATSProd.'Order time', US_CATSProd.'Complete date', US_CATSProd.'Complete time', US_CATSProd.'Pay Year', US_CATSProd.'Pay period', US_CATSProd.Extra, US_CATSProd.'Dist-Sub', US_CATSProd.'Short Train ID', US_CATSProd.Split, US_CATSProd.'Split High-Low', US_CATSProd.'Emp Name', US_CATSProd.Company, US_CATSProd.'Regular HHMM', US_CATSProd.'Overtime HHMM', US_CATSProd.'OT Dollars', US_CATSProd.'Regular Time', US_CATSProd.'OT Time', US_CATSProd.'Reg HH', US_CATSPr
od.'Reg MM', US_CATSProd.'OT HH', US_CATSProd.'OT MM', US_CATSProd.Terminal, US_CATSProd.Year, US_CATSProd.Month
|FROM sqlp_cats.oconno05.US_CATSProd US_CATSProd
|WHERE (US_CATSProd.BA='401') AND (US_CATSProd.Year=10) AND (US_CATSProd.Month=3)
|ORDER BY US_CATSProd.Date

Thanks again.
Tai

p.s. The tables do update when I click the refresh button.

 

[Fazza]

One step at a time. To make it simpler again, what about changing to a simpler query? Such as "SELECT one field FROM table"

The .sql = .sql didn't work previously, what about with a simpler query?

[BTW, I'm guessing the | symbols and double quotes are pecuiliarities of the database: that is, I have no reason to think they are errors. They are unfamiliar to me, that is all.]

I don't know if you have tested in both Excel 2000 & Excel 2003. I would suggest just focus on the first step, with the simpler code,

sub simple()
  with activesheet.pivottables(1).pivotcache
   debug.print .sql
   .sql=.sql
 end with
end sub

What do you think?

 

[taigovinda]

What do I think? Of course I think you're right - and glad you're helping me!!

On the Excel version issue, I don't think I can try it in 2003 after all, because my work uses only 2000 and I can't connect to their servers from my home computer with 2003...

However, I think I am finally getting somewhere (reminder: the goal is to be able to use code to replace the month and year within the SQL). This code works:

Sub simple()
Dim pt As PivotCache
Debug.Print ActiveSheet.PivotTables(1).Name
Set pt = ActiveSheet.PivotTables(1).PivotCache
Debug.Print pt.Sql
Application.EnableEvents = False
  pt.Sql = "SELECT " & _
" US_Unprod.""mccode 4"", US_Unprod.""mccode 2"", US_Unprod.""mccode desc"", US_Unprod.Month " & _
    "FROM sqlp_cats.oconno05.US_Unprod US_Unprod WHERE (US_Unprod.centre Not Like '1%') AND (US_Unprod.Month='02') AND (US_Unprod.Year='10') ORDER BY US_Unprod.busarea"
Application.EnableEvents = True
Debug.Print vbCr & ActiveSheet.PivotTables(1).PivotCache.Sql
End Sub

And if I run that, then this code works (on a pivot with the sql from above):

pt.sql = pt.sql

However, if I try to lengthen the red part of the first code, it will fail.

For instance, if I replace it with this, it will fail:

" US_Unprod.""mccode 4"", US_Unprod.""mccode 2"", US_Unprod.""mccode desc"", US_Unprod.Month, US_Unprod.Year " & _

However, it is not the US_Unprod.Year that caused the failure, because this works when I remove the first field from the list:

" US_Unprod.""mccode 2"", US_Unprod.""mccode desc"", US_Unprod.Month, US_Unprod.Year " & _

...So it seems like choosing more than four fields will make it crash on my machine? I don't understand how it can read the longer SQL string, but not write it back into there... Interestingly to me, I was able to replace the red line with this and it did not crash:

    " * " & _

I don't know enough about how these SQL strings are likely to look, to be able to guess really... I mean, should I just replace from the beginning of the string up to "FROM" and put "SELECT * " in place of what is there? Or will that not always work (in addition to slowing things down considerably if the person who built the queries actually bothered to select fields)? Am I missing something obvious?

What do you think? What should I try next?
Thanks!
Tai

 

[Fazza]

I think I can't help much & that you're doing well.

If you just can't get it to work whatever you do, then try another approach. Such as an ADO query of the table and then FILTER the recordset and make it (the recordset) the data source for the pivot table.

 

[taigovinda]

OK. Thanks again for all your help. I've seen some other of your threads that can probably show me how to do ADO recordset if that is needed

 

[taigovinda]

Fazza,

As an update: I did get it working!

Also - this will be no surprise to you - I did check to see if I could overwrite the sql in 2003 and it worked right away (using .sql = .sql the way that wouldn't work in 2000). Of course, I couldn't get the data because my computer with 2003 can't connect to my company's system.

So I guess if I ever need to do something where using SELECT * doesn't cut it, I can get my company to upgrade to 2003

Thanks!
Tai

 

[Fazza]

Well done, Tai. Regards, Fazza