Dynamic data views in FoxPro - How to access from Crystal Reports?

[groundclutter]

I am developing fairly complex reports in Crystal Reports 8.5 against an accounting software package called "ProcessPro" which integrates with AccPac Pro Series erp systems.

It utilizes several Visual FoxPro (vers 6) free tables that reside in folders on the database server. I have made connections through ODBC looking at each folder (and its subsequent tables found therein).

Due to the number of folders (broken out by functionality), such as AP, AR, GL, SO, etc, report development has become a pain due to the number of data sources, etc.

Now, to my question!!!!

Can I create an SQL view, such as one you can create in larger DBMS software such as Informix, Sybase, DB2, etc.?

I need to combine the data of two tables (using a union query I am thinking) and have been able to view the query on the server but the report does not access it. So my two questions are:

1) Can I create a dynamic sql view in FoxPro, and if so, how?

2) I created a view in FoxPro, albeit maybe incorrect, but Crystal says "no fields available". What's up with that?

Sorry for the long explanation but thank you in advance for any help you provide. Also, I am cross-posting this to one of the Crystal groups as well.

Thanks again!

 

[rgbean]

1) Yes, but do you really want a "view", or simply a "query"? Usually Views are used to update tables, while Queries are usually used to gather data together for easier analysis (or reporting).

2) Show us the code, and maybe we can spot the problem - both views and queries can show the underlying SQL.

Rick

 

[groundclutter]

rgbean,

1) In this context, a query would be all that is needed as there won't be any updating.

2) There is no code...I can construct a query but my question relates more to "how to create" a query in FoxPro that will dynamically run when accessed from Crystal Reports.

Bill

 

[groundclutter]

By the way, I may not have expressed the thought that I have never seen FoxPro tables or databases before this project...should have stated that before probably.

 

[Mike Gagnon]

markut

2) There is no code...I can construct a query but my question relates more to "how to create" a query in FoxPro that will dynamically run when accessed from Crystal Reports.

In order to "run" a query, you'll either need FoxPro installed to run the program, or the query will be compiled in a executable, and will be installed where it needs to be (including runtime files).

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[rgbean]

Bill,
While I don't use CR, I believe it just needs a table to report from. In that case, you can just build your SQL SELECT in code by creating one or more strings, and then use VFP's macro capability to run the SELECT.

* build these based on form info *
lcMatch = "ABCDEF"
lcFields = "Field1, field2, field3"
lcTable = "mytable"
lcWhere = "field1 = '"+lcMatch+"'"
lcOutput = "TABLE tempxyz"

* now put it together *
lcSQL = "SELECT "+lcfields+" FROM "+lcTable;
    +" WHERE "+lcWhere+" INTO "+lcOutput

* run it *
&lcSQL

Obviously based on your needs, the expressions can be more complex, including multiple tables, joins and more filtering.

Rick