programatically logging into EnterpriseOne from Crystal Reports

Rob Woods

Member
I'm having some trouble connecting to EnterpriseOne with Crystal Reports. I have written many Crystal report applications, before, but I can not connect to the Enterprise One system.

I have created a web application that uses the Crystal reports view from Visual Studio 2003 to print reports. I am able to print reports made directly against the JDE tables with no issues, but I can not connect to the EnterpriseOne system.

I am ussing the following code to connect to the database with Crystal Reports:

Dim obj As New CrystalDecisions.CrystalReports.Engine.ReportDocument
obj.Load(Server.MapPath("ParameterTest.rpt"))


Me.CrystalReportViewer1.ReportSource = obj

Me.CrystalReportViewer1.LogOnInfo(0).ConnectionInfo.DatabaseName = "JDEDB"
Me.CrystalReportViewer1.LogOnInfo(0).ConnectionInfo.Password = "password"
Me.CrystalReportViewer1.LogOnInfo(0).ConnectionInfo.ServerName = "Server12"
Me.CrystalReportViewer1.LogOnInfo(0).ConnectionInfo.UserID = "sa"


Can anyone help?

--------------------
Robert Woods
 
O you spammer you.

Please do not cross-post into multiple forums. I replied to this same post in another forum (you should have only posted in the Crystal forum).
 
The JDE configuration is on a Windows 2003 Server with SQL Server 2000 for the database. Current version is E810.

I am attempting to build a web application that will allow me to view one of the reports created with Crystal reports 10 for PeopleSoft. I have a Visual Studio.NEt 2003 web application with at crystal report view on a page. I am able to connect to standard sql databases with the above code, but not to EnterpriseOne.

I have tried setting the login information for each table, and for the view control separately. I believe that my problem is in the actual connection method to EnterpriseOne

I am primarily a .NET developer, and have very limited exposure to PeopleSoft/JDE. Please let me know if you need more information.

Thanks.
 
Rob,

if you are going straight at the SQL Server database then you should be able to connect fine. If instead you are trying to access the Enterprise data through the JDE connectors then, yeah, I'd believe you'd have problems.

My advice would be to go straight at the SQL database. You'll need to handle the following issues though:

1. Table and column naming. I.T. people after much use may become comfortable with the brief acronyms such as PRODDTA.F4101.IMITM when referring to the Item Master table's Short Item Number. We don't expect our end-users to have to deal with that though.

2. Date Fields. JDE uses a Julian Date format which stores dates as a number - not as a date. It's not obvious that 100129 represents April 8, 2000.

3. Decimal Places. All numeric fields are written without decimal places, so if you query the database directly a Line Item number will be returned as 1000 instead of 1.000.

To deal with these issues we wrote SQL scripts that:

1. Created views and synonyms for specific tables so that F4101 appears as "Item Master F4101" and IMITM appears as "Short_Item_Number_IMITM". The script pulls this information from the Object Librarian and Data Dictionary tables so the only thing we needed to do was to edit some of the names.

2. We dealt with dates in the same script that generates the views. It looks at the Data Dictionary Data Type and if the column is a Date data type then a custom Database Function is used to convert the JDE number into an Oracle Date. From Crystal (or Access or MSQuery ...) it looks like a normal date field now.

3. Numeric fields with decimal places were dealt with similiarly. If decimals exist then the database value was divided by 10 to the 'x' power.
 
Thanks,
I was afraid you would say something like that.

My plan in the event I was unable to connect to EnterpriseOne directory was to do exactly as you suggest. As it's going to be the JDE Developers who are writing the reports, I think we will be ok. Luckily we only have one report in existence using the Crystal Reports for PeopleSoft so it will be easy enough to convert the one report.

As for the data issues, that's not a problem, I plan to write stored procedures for the report queries, and have them deal with the date and other issues with sql server functions.

Thanks for the time.
 
Hi Rob,

I'm curious to know why you aren't using the OneWorld ODA DSN for your reporting...

We've been quite successful with it so far, both with 7332 and 7334 (ERP 8.0).

We're still on SQL 2000 and Win 2000, but I don't think that will have much of a bearing when using the ODA route...

Karim
 
Back
Top