OWC11.PivotTable

OWC11.PivotTable

• TimHa

  Alex,

  Saying OWC do you mean activex placed on a form IMHO you're sailing in dangerous waters:) BTW instead of MSGraph with MSChart you would feel much better.

  For OWC.pivottable this would be a starter:

  * A button's click code

  TEXT to m.lcSQL noshow
  Select c.cust_id, c.company, ;
  o.order_id, o.order_date, ;
  e.First_Name-(' '+e.Last_Name) As Employee, ;
  oi.line_no, p.prod_name,;
  oi.unit_price, oi.quantity;
  FROM testdata!customer c ;
  INNER Join testdata!orders o;
  ON c.cust_id = o.cust_id ;
  INNER Join testdata!Employee e ;
  ON o.emp_id = e.emp_id ;
  INNER Join testdata!orditems oi;
  ON o.order_id = oi.order_id ;
  INNER Join testdata!products p ;
  ON oi.product_id = p.product_id ;
  order By p.prod_name
  ENDTEXT
  
  m.lcSQL = Chrtran(m.lcSQL,';'+Chr(13)+Chr(10),'')this.Parent.owcpivot.connectionString = ;
  'Provider=VFPOLEDB;Data Source='+_samples+'data'
  this.Parent.owcpivot.commandText = m.lcSQL

  However it would be much easier and safer if you need for customers with Excel installed:

  TEXT to m.lcSQL noshow

  Select c.cust_id, c.company, ;

  o.order_id, o.order_date, ;

  e.First_Name-(' '+e.Last_Name) As Employee, ;

  oi.line_no, p.prod_name,;

  oi.unit_price, oi.quantity;

  FROM testdata!customer c ;

  INNER Join testdata!orders o;

  ON c.cust_id = o.cust_id ;

  INNER Join testdata!Employee e ;

  ON o.emp_id = e.emp_id ;

  INNER Join testdata!orditems oi;

  ON o.order_id = oi.order_id ;

  INNER Join testdata!products p ;

  ON oi.product_id = p.product_id ;

  order By p.prod_name

  ENDTEXT

  m.lcSQL = Chrtran(m.lcSQL,';'+Chr(13)+Chr(10),'')

  lcPivotTableName = 'myCustomers'

  lcConnPath = Addbs(_samples+'data')

  lcConnStr = [Provider=VFPOLEDB;Data Source=]+lcConnPath

  Alines(laRowFields,'prod_name')

  Alines(laColumnFields,'Employee')

  Alines(laPageFields,'cust_id')

  *** Constant Group: XlPivotFieldOrientation

  #define xlColumnField 2

  #define xlDataField 4

  #define xlHidden 0

  #define xlPageField 3

  #define xlRowField 1

  *** Constant Group: XlPivotTableSourceType

  #define xlScenario 4

  #define xlConsolidation 3

  #define xlDatabase 1

  #define xlExternal 2

  #define xlPivotTable -4148

  *** Constant Group: XlCmdType

  #define xlCmdCube 1

  #define xlCmdSql 2

  #define xlCmdTable 3

  #define xlCmdDefault 4

  oExcel = Createobject('Excel.Application')

  With oExcel

  With .Workbooks.Add

  _CreatePivotTable(.ActiveSheet.Range('A1'),;

  m.lcPivotTableName,m.lcSQL,;

  m.lcConnStr)

  With .ActiveSheet.PivotTables(lcPivotTableName)

  .DisplayErrorString = .T.

  .MergeLabels = .T.

  .SmallGrid = .F.

  _AddPivotFields(@laRowFields, @laColumnFields, @laPageFields)

  .PivotFields('Quantity').Orientation = xlDataField

  Endwith

  Endwith

  .Visible = .T.

  If Type('.ActiveWorkbook.ShowPivotTableFieldList') = 'L'

  .ActiveWorkbook.ShowPivotTableFieldList = .F.

  Endif

  Endwith

  Function _CreatePivotTable

  Lparameters toDestination,tcPivotTableName, tcSQL,tcConnStr

  With .PivotCaches.Add(xlExternal)

  .Connection = 'OLEDB;'+m.tcConnStr

  .CommandType = xlCmdSql

  .CommandText = m.tcSQL

  .BackGroundQuery = .T.

  .CreatePivotTable(m.toDestination, m.tcPivotTableName)

  Endwith

  Endfunc

  Function _AddPivotFields

  Lparameters taRowFields, taColumnFields, taPageFields

  If !Empty(taPageFields[1])

  .AddFields( @taRowFields, @taColumnFields, @taPageFields ) && Fields added

  Else

  .AddFields( @taRowFields, @taColumnFields )

  Endif

  Endfunc

  
  

• Robert Hulme

  Alex,

  You can have a comprehensive package with a lot of flexibility w/o using OWC:)

  I'm not an expert on OWC (I'm not an expert on any type of automation I think, just relatively worked more with Office applications' object model than an average user:) IMHO if it's not an HTM page or a web browser control on a form then using OWC is asking for trouble. If it's possible (office installed that means) then directly use Excel itself (from graphing to pivoting and anything like that). If you need to show a graph or print, then still yet, MSChart is a much better choice than OWC,MSGraph,Excel charting (IMHO).

  universalthread.com has plenty of samples posted on each and a wrapper for Excel posted by Sergio (name is Srdjan G. - hard to spell for me:). Check them I advice.

  
  

• slabanum

  Thank you very much.

  It is very impressive how you quickly figured out where the problem lay.

  The code ran to the end. Now I am getting to the real stuff.

  There will be more questions, Cetin, please stand by.

  Thanks.

  
  
  

• Sybs

  However it would be much easier and safer if you need for customers with Excel installed

  I do have it installed. I simply do not want it to be visible. I am planning to populate Excel spreadsheet if necessary and then feed OWC11 charts.

  I also have this question for you. There is a header file graph9.h which contains constants for MSGraph. It is very convenient to use. I could not find a corresponding file for OWC11. Is there one What's it name

  Thanks.

  
  
  

• Eric Schultz

  You should not be sorry. Most likely it is my software problem.

  What's happened is that I installed VFP + MS Office 2003 + Office Web Components on WinSer2003 platform on a totally clean, new partition. Too many problems got accumulated on my primary Win2K partition,

  You are using the syntax

  oExcel = Createobject ('Excel.Application')

  which is recognized by my VFP Compiler but this syntax apparently does not allow the full access to all methods. Mine should have been;

  o = CREATEOBJECT ("OWC11.Spreadsheet") or OWC11.pivottable or OWC11.ChartSpace, etc.

  I started yesterday an attempt to review and compare all these objects via the Object Browser and/or OLEviewer.exe. Here I also ran into some problems: massive amount of data on one hand, the existence of two versions of OLEViewer.exe with different bugs in them on the other hand.

  What is your platform: is it WinXP Mayby I should move over there. I do have a clean OS installed on another partition.

  Also I have this question. On universalthread.com I found only one graphic related article by Chalom from Brazil, I think. It is on pie graphs. It is a difficult website to use since they do not have a search engine, at least I could not see one. Do you know of any other articles on OWC or GDI I would appreciate a reference.

  I will come back with more knowledge soon after I studied the objects closely.

  Thanks.

  
  
  

• kpvgiri

  Alex,

  I'm sorry too. I ca't make it fail to see the problem.

  
  

• Chicks_in

  Saying OWC do you mean activex placed on a form

  Yes, I should have made it clear.

  IMHO you're sailing in dangerous waters:) - I know but I need a comprehensive package with a lot of flexibility. I hope it will give me that. Also with your help I should not be afraid to wade in dangerous waters.

  Thank you very much. I did not know you were an expert on OWC too. I may have more questions on it later.

  Thanks.

  
  
  

• Jamzb

  Cetin, I am sorry. It was a mistake on my part. The code DOES NOT RUN still. I am getting the same error message. It appears as this function is not recognized.
  
  

• Me.Chris

  Hi Cetin,

  I am trying to run your code with modifications according to my input data and running into two problems at once.

  First one seems to be an error message in the function _addPivotFields at this statement:

  .AddFields ( @taRowFields, @taColumnFields, @taPageFields ) && Fields added

  The message is "OLE Dispatch Exception Code 0 from Microsoft Office Excel: Unable to get the AddFields property of the PivotTable class."

  Problem # 2: After a single run of my form with your function my whole database where the input table is gets locked and I cannot unlock it by any means. This is of course a consequence of the fact that the execution is broken down in the middle.

  CLOSE DATABASES ALL - does not work, oExcel.release() has been tried but oExcel is not recognized as an object.

  I have to go to the Task Manager and kill Excel job in there or shut off and restart a computer.

  This second problem is not crucial if you help me to resolve the first one and the code starts running to the end. Then this problem will become moot.

  Thanks.

  
  
  

• raj2007

  Alex,

  It's not my fault really. Pasting code here screws formatting. Remove extra lines and be sure SQL code lines start with a space. Or:

  m.lcSQL = Chrtran(m.lcSQL,';'+Chr(13)+Chr(10),space(1))

  
  

• Vivek Ayer

  Thank you Cetin.

  The problem for me is that I do not have plans to become a professional FoxPro developer with all my enormous respect for MS, FoxPro, yourselves and others in the field and I mean it.

  I am always looking for a quick and efficient resolution of an immediate problem.

  
  
  

• Vania B

  Concerning my last question on the header file: never mind. All defined constants for OWC appear to be the same as for MSGraph and therefore graph9.h seems valid.

  Thanks.

  
  
  

• sumari

  I'm using XP Pro SP2. However the code skeleton there is in use all over the world with VFP5/7/9 compiled executables (Excel range from 97 to 2003 and OS from win9x to win2003).

  I don't understand why you're using a createobject() with OWC instead of Excel. The code I sent is for Excel's object model not OWC.

  On universalthread I'm sure there are many Excel (pivoting, charting and all other stuff) and MSGraph,MSChart, OWC.Cahrt etc code samples because at least I posted tons of them - and actually the one here I copy&pasted from one of my replies there:=)

  On UT there are 2 types of membership one is Premier (PUTM) and the other is basic. Basic is free but some features like extended search is not available. IMHO it has the best forum interface and the richest foxpro content (and I'm not working for Michel the owner:) If you visit there daily (do nothing but at least login once) you'd see what I mean. You get answers almost instantly (if someone who knows the answer is online and under 20 users online is very rare), you don't have to brwse all topics to remember and/or see if you have a reply etc. Most of viistors of this forum are also members of UT.

  
  
• OWC11.PivotTable