Pivot Chart from database and Excel sheet

[mark]

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.

 

[Ron Coderre]

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)

 

[mark]

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.

 

[mark]

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.

 

[Paul V.S.]

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

 

[mark]

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