I.T. Discussion Community!
-Collapse +Expand
Paradox
Search Paradox Group:

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

August Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyCorel Paradox / ObjectPAL Coding BoardParadox to/from Other Data Sources Topic   Print This     

Excel 2007 pivot tables, Paradox data source

Excel 2007 pivot tables, Paradox data source in Paradox to/from Other Data Sources topic (part of our Corel Paradox / ObjectPAL Coding group).

Quick Search: Paradox   Excel 2007   Excel 2007 pivot   tables Paradox   data source  
dhowe
Neenah, WI USA

Just trying to get an idea if anyone else uses pivot tables that use a Paradox table as a data source.  If so, have you had issues upgrading from Excel 2003 to 2007?  We've found that some of the pivot tables won't even open in 2007 and have to be re-created.

We also have some users on 2007 already and some that are still on 2003.  If a 2007 user creates a pivot table and saves as an .xls (2003 compatible), sometimes there are issues with the 2003 users not being able to use the pivot table functionality.

The simple solution would be to convert everyone to 2007 at the same time, but that is not an option.  We've come to rely on pivot tables quite heavily in some areas of our company, as they provide a lot of reporting flexibility.

I was just wondering if anyone else had any of these same issues and what they have done to get around them.

Thanks, Doug.

 Posted 10 years ago (Thread Starter)
Comment Quote
About dhowe -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #13563, 7 replies
Thread Started 6/24/2009 9:44:59 AM
View Counter=4977
Last Reply Posted 9/6/2009 9:59:18 PM)
Location=Neenah, WI USA 
Joined=10 years ago   MB Posts=4  
Larry.Bell
Albuquerque, NM USA

Hello,

Excel pivot tables have proven to be a very versatile and well-accepted tool in our organization.  I have not used a Paradox table as a direct source for the pivot table (because I haven't mastered that interface yet), but I have exported Paradox table records to an Excel spreadsheet (version 5) and used that spreadsheet as a source for the Excel 2007 pivot table worksheet.  If we needed constant updates for the pivot table, this would not be acceptable, but since we only update once per month, it works for us.  I hope this gives you another avenue to use.

Larry Bell

Larry B.

 Posted 10 years ago
Comment Quote
About Larry.Bell -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Larry.Bell
Paradox programmer since DOS ver 2.0

Post ID #13657 (Level 1.1)  Reply to 13563
Thread Started 9/2/2009 12:40:08 PM
Location=Albuquerque, NM USA 
Joined=17 years ago   MB Posts=1   KB Comments=1  
Most Recent Post
RayfromOz
Melbourne, Australia

The interface is quite simple. You shpould try it. Like with excel 2003 there are limitations to the size. You can also save the crosstab results in another table by placing a button on the form (I prefer to use tabs on a form so can have multiple crosstab views and use the following code inside the button

method pushButton(var eventInfo Event)
const
CTAB = ":PRIV:OutPutCtab.db"
endConst

var
tvCtab TableView
endVar

MyCrosstab.action(DataSaveCrosstab)
tvCtab.open(CTAB)
endMethod

where MyCrostab is the name of the crosstab object on the form.

thr constant

CTAB = ":PRIV:OutPutCtab.db" can be changed to ":WORK:SelectYourOwnFileName.DB" and it will save your own entered file name into the working directory . It can then be exported from paradox with a script or imported as an extenal data source or copied and pasted into excel directly. Leaving in the private directory will mean it will be culled along with any other temporary files

Pivot tables format in office 2007 are differently structured to 2003 and change in VBA code is required where used.

Regards Rayfromoz

 Posted 10 years ago
Comment Quote
About RayfromOz -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #13660 (Level 1.2)  Reply to 13563
Reply Posted 9/6/2009 9:45:22 PM
Location=Melbourne, Australia  
Joined=14 years ago   MB Posts=7  
Steve Green
Myrtle Beach, SC USA

Doug.. this might seem like a dumb question, but it's sincere.. what can a pivot table do, that a simple query and report within paradox can't do much, much better?

--

Steven Green - Myrtle Beach, South Carolina USA
http://www.OasisTradingPost.com

 Posted 10 years ago
Comment Quote
About Steve Green -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Steve Green

Steven Green - Myrtle Beach, South Carolina USA
http://www.OasisTradingPost.com

Oasis Trading Post
- Collectibles and Memorabilia
- Vintage and Custom Lego Creations

Diamond Software Group - Paradox Support & Sales

Diamond Sports Gems - Sports Memorabilia and Trading Cards 


Post ID #13564 (Level 1.3)  Reply to 13563
Reply Posted 6/24/2009 2:24:31 PM
Location=Myrtle Beach, SC USA 
Joined=11 years ago   MB Posts=81   KB Comments=12  
Steve Green
Myrtle Beach, SC USA

many of the sales managers that use these pivot tables don't have Paradox and have no interest learning Paradox. 

 

sorry if this seems rude, but everyone here will have expected the above to be the root of your reason.. everything else in your response is based on that statement, applied due to that statement, and/or rationalized based on that statement.. it isn't you specifically, it's the standard thought process above, that they're throwing at you..

this doesn't just apply to Paradox.. it's the same for Access, dBase, Foxpro, whatever.. it's a DATA-base tool.. data, get it? (grin).. database programming is intended to do what you're describing.. better, faster, more flexible, more functional, more control, etc, etc.. the end user doesn't have to learn Paradox.. if the programmers know what they're doing, all the end user has to learn is how to use drop downs, radio buttons, check boxes, and selection processes that look and act exactly like every other similar process in Windows.. oh, wait.. they already know how to do that..

there is no paper to waste, unless they're gonna waste it anyhow.. Paradox reports have the same "display to screen" functionality as every other platform you use..

there are no per-seat licenses to pay for.. that's what the Runtime distribution option is for.. in fact, that totally cures your "who is using it?" issue, because Runtime only runs the processes that you give to the user.. they CAN'T create maverick reports and/or manipulate the data in non-standard or non-approved ways, like they can in Excel..

and again.. all of these theories apply to ANY database tool, not just to Paradox..

--

Steven Green - Myrtle Beach, South Carolina USA
http://www.OasisTradingPost.com

 Posted 10 years ago
Comment Quote
About Steve Green -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Steve Green

Steven Green - Myrtle Beach, South Carolina USA
http://www.OasisTradingPost.com

Oasis Trading Post
- Collectibles and Memorabilia
- Vintage and Custom Lego Creations

Diamond Software Group - Paradox Support & Sales

Diamond Sports Gems - Sports Memorabilia and Trading Cards 


Post ID #13566 (Level 1.4)  Reply to 13563
Reply Posted 6/25/2009 12:30:59 PM
Location=Myrtle Beach, SC USA 
Joined=11 years ago   MB Posts=81   KB Comments=12  
Steve Green
Myrtle Beach, SC USA

In order to create a report with buttons, dropdowns, check boxes, etc as you describe, does the report developer need to know ObjectPal?

yes..

We are in the process of transitioning the data we currently have in Paradox to an SAP data warehouse where we will have all of this functionality.

good luck with that, even if your company is really, really, really big.. the software platform doesn't matter, unless you have the right top-notch developers.. and that's even more true with SAP than most other options, because it's original intended audience was VERY narrow.. I work for a big defense contractor that switched to SAP about 5 years ago.. moving from their old platform was a necessity, but our data-life has been a daily struggle ever since..

I think the users without any programming experience found that the pivot tables were simple to set up. 

absolutely true.. when you let somebody extract data at their whim, without rules or controls, it's really easy to use.. doesn't mean it's still valid, but it's easy.. (sigh)

This is likely something that should have been explored 5 years ago

even then, you're really late to the table.. Paradox has essential been a dead-end for about a decade, ever since Borland abandoned the BDE..

--

Steven Green - Myrtle Beach, South Carolina USA
http://www.OasisTradingPost.com

 Posted 10 years ago
Comment Quote
About Steve Green -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Steve Green

Steven Green - Myrtle Beach, South Carolina USA
http://www.OasisTradingPost.com

Oasis Trading Post
- Collectibles and Memorabilia
- Vintage and Custom Lego Creations

Diamond Software Group - Paradox Support & Sales

Diamond Sports Gems - Sports Memorabilia and Trading Cards 


Post ID #13568 (Level 1.5)  Reply to 13563
Reply Posted 6/25/2009 2:09:48 PM
Location=Myrtle Beach, SC USA 
Joined=11 years ago   MB Posts=81   KB Comments=12  
dhowe
Neenah, WI USA

Here's an example of how one might be used.  One person may run a script that creates a table containing sales orders for a given time frame for a select group of products.  A pivot table can be created that links to that Paradox table via an odbc connection.  Whenever this table is created, the pivot table can be refreshed.  Essentially the data from the Paradox table is getting imported into Excel.  The users that have access to the pivot table have the ability to change reporting criteria (ie rep code, rep group, product code) on the fly.  They can also add or remove certain fields from the report by simply dragging and dropping them.  They can also drill down into order line detail, depending on how the pivot table is set up.

We can create one pivot table based on a broad range of data and allow the users to slice and dice it according their own needs.  We also benefit by not wasting paper printing reports.  Plus, many of the sales managers that use these pivot tables don't have Paradox and have no interest learning Paradox.  There is a bit of a learning curve and an acceptance hurdle to overcome with pivot tables, but they also save Paradox  license fees.  Another disadvantage though is being able to manage who is using the pivot tables.

The power end users really developed these on their own, but they have proven to be useful.  But now that we are having some issues with the Excel upgrade, we realize they can be a pain as well.

 Posted 10 years ago (Thread Starter)
Comment Quote
About dhowe -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #13565 (Level 1.6)  Reply to 13563
Reply Posted 6/24/2009 2:32:36 PM
Location=Neenah, WI USA 
Joined=10 years ago   MB Posts=4  
dhowe
Neenah, WI USA

Thanks for the info Steve.  In order to create a report with buttons, dropdowns, check boxes, etc as you describe, does the report developer need to know ObjectPal (we are using version 11.0.0.411)?  The only reports that we have built in Paradox are static reports using the report option.  We are in the process of transitioning the data we currently have in Paradox to an SAP data warehouse where we will have all of this functionality.  I'm sure we will still lean on Paradox for years to come, so it may be something worth learning how to do.  I think the users without any programming experience found that the pivot tables were simple to set up.  If there were non-programming options within Paradox to build similar reports, I think they would explore those options during this transition.  This is likely something that should have been explored 5 years ago, but the evolution of Paradox in our company is a little unique to say the least.

 Posted 10 years ago (Thread Starter)
Comment Quote
About dhowe -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #13567 (Level 1.7)  Reply to 13563
Reply Posted 6/25/2009 1:17:11 PM
Location=Neenah, WI USA 
Joined=10 years ago   MB Posts=4  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1181A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #13563 Counter
4977
Since 6/24/2009
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech