External BAQs and DB Visualizer

Yahoo Archive
1
  I've been able to build ODBC Crystal reports combining data from Epicor and Open4. Thanks Marianne, I had forgotten ODBC reports were possible; I didn't have to use External BAQs. I also wanted to thank Bruce Ordway; I re-discovered your document on DBVisualizer in the files section that gave me a nudge in the right direction on how to work with views. I still have some work to do with setting up ODBC for HR and would still like to figure out why I can't get the external BAQs to work. With what I learned (and re-learned) this week I think I can get it done. Now I'm off for a 3-day weekend...thanks group for all the help I've found here!!

Sue
2
Is it possible to use SQL views created in DB Visualizer or SQuirreL in an Epicor (9.05.701) external BAQ? I've often been asked by HR to create reports that combine info from Epicor and Open4. We use Epicor payroll and Open4 HR. I can get the data I want from Open4 thru both DB Visualizer and SQuirreL, but I'm not sure if I can  get that data to Epicor. We're Progress, not SQL.

Sue


3

Hi Sue,


In a External BAQ, I have seen Views come across at least in 9.05.702A which is the version I am on. 

You need to

1) Create a System OBDC DSN on you Application Server that is hosting Epicor 9 Application for the Open4 database

2)  in Epicor 9 BAQ window, Select New/External BAQ

3) Supply the DSN you created in step 1 and add the connection criteria

4) test Connection

5) on the Phrase Builder tab - select a table from your external connection

6) on the Display Table - select your fields

7) on the Analyze Tab - test - you should get results


As you can tell though, you cannot add any Epicor 9 tables to this BAQ.

you will need to build the HR reports as Dashboard Reports. This will allow you to

link more than one BAQ together to pass to the correct xml data to Crystal reports. The

report gets embedded in the Dashboard (or you can set a link in the Dashboard to it).


Your other option is to build the Crystal reports outside of Epicor using ODBC for both the Epicor tables and Open4 tables. You can then add the Crystal reports to Epicor's menu.  Epicor does not necessarily support these ODBC reports but it is doable.


If you have more questions, you can contact me directly if you wish

Marianne Gizzi

Estes Group

mgizzi@...



4
Hi Marianne,

I think either I'm trying to do something that can't be done with DB Visualiser (free version) or I really need to brush up on my  SQL basics/terms, either of which is entirely possible. I can connect to, view data, and write SQL queries in Open4 and mfgsys databases with both DB Visualiser and SQuirreL so I think my OBDC connections are OK. I can set up and use a system DSN in external BAQ and get "Connection Successful" messages. I can create a new external BAQ and test the connection successfully. Then I start getting errors such as this:

Error occurred while accessing component property/method: EXECUTE.
[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Table/View/Synonym not found (7519)
Error code: 0x80020009 getMsSqlTableList bo/BAQExtODBCSearch/BAQExtODBCSearch.p (5890)
Invalid component-handle referenced while processing method/statement: RecordCount.

getMsSqlTableList bo/BAQExtODBCSearch/BAQExtODBCSearch.p (5884)

Thanks for steps, nice to know I was on the right track. I'm ready to set this project aside for another day. I'm missing something, probably something very basic that won't let me get from step 4 (successful connection) to step 5 (select table). A fresh start sometimes helps. Thanks again!

Sue