 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008Pivot Chart from database and Excel sheet
Hello. Our Ops department here would like to take advantage of the dynamic capabilities of Pivot Tables and Pivot Charts, in order to present Load vs. Capacity planning data. The Load dea is in an Oracle database, and the Capacity data is in an Excel worksheet. Is there a way to get those two sources into the same Pivot Table? We could load the capacity data into the database, of course, but Ops would like to retain the flexibility of the what-if capabilities of Excel. Conversely, we could potentially summarize the database data enough to fit into an Excel worksheet's row count, but, not as much detail would as desired would be possible on that side. Thanks for any suggestions. The job will be much easier if you can upload the Excel data into an Oracle staging table. That will simplify the connection and facilitate any joins and criteria you'd need to use. Your IT Dept surely has a utility or process to handle the upload. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Hello. > [quoted text clipped - 18 lines] > > Thanks for any suggestions. I am in the IT department, and yes, I could do that. But I was asking for opinions on things I might not have thought of, because of the nature of the user's request. Thanks. > The job will be much easier if you can upload the Excel data into an Oracle > staging table. [quoted text clipped - 34 lines] > > > > Thanks for any suggestions. > Your IT Dept surely has a utility or process to handle the upload. The Ops department is very proficient in Excel, and would want to be doing lots of "what-if" analysis... repeatedly. As of yet, we haven't opened up the database for write access, to end users, directly. I could write the whole thing in Excel, or PL/SQL, but without direct write access to the database from Excel, it wouldn't be as seamless and flexible as desired. That was the reason for my query. If it were possible to combine two ADO recordsets and apply the result to a Pivot Cache, or an ADO recordset and an array, or something like that... that would give the users the flexibility they're looking for, without going through changing IT management's viewpoint on database security. thanks. Hi Mark...I had very similar issues and what I did what link in the Oracle database to access and import the spreadsheet files to access....run an append query to a flat table and use excel to pivot off of that table.... If these users are good at excel, they'll have no problem picking up the Access piece (not talking about heavy access programming). Paul > > Your IT Dept surely has a utility or process to handle the upload. > [quoted text clipped - 14 lines] > > thanks. Thanks, Paul. That's actually what I've started to think about this afternoon, and discuss with the Ops manager... questions to him about whether he wants to archive the load data, or do a full replace on the Access table each time it's updated. Thanks for the suggestion. It should work. Mark > Hi Mark...I had very similar issues and what I did what link in the Oracle > database to access and import the spreadsheet files to access....run an [quoted text clipped - 23 lines] > > > > thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.