Reporting Strategy for E1

Hello,

Just want to check what is being used to get reports out of E1. RDA is slow by nature and we have been looking at various tools to fit into our reporting strategies.

We use GL Inquiry for Financial, but it limits us to few tables. We not only want reports from all the E1 table but also from other product that are outside E1 in SQL databases

Is any one using SQL server Reporting services to do reports. We use it and I was wondering if people have some comments on that.

Thanks

We use Easy Reporting for the Accountants and SQL Server Reporting for other reports, so far we like the solution, although we had to make a function to convert the dates to something meaningful...

Nick - there are a ton of alternatives out there, depending on the flavor of functionality and type of platform you're on. Its like asking "what ERP system should I use" !

Reporting directly against the database is certainly one option - but you have to interpret the data (julian dates and data dictionary display decimals). Fine for doing the occasional scheduled report, but not good when you want to put this out to users.

Instead, you need to look at a data connector - either with a product like the GL Companies "Really Realtime" - or with an ODA connector like JDEDirect (a frequent advertiser here). For enterprise reporting providing you're on 9.0 - you need to evaluate OBIEE from Oracle - and there are plenty of other flavors in between.

My recommendation is to talk to a number of vendors, get quotes and demonstrations and evaluate what is required for YOUR company. Not much advice, I'm sure, but its about as accurate as I can provide given the vast number of providers out there !

Malcom,

We have all those function already in place. This is meant to give it to end user for their reporting. How has been the impression so far. Do you think, it can be given to end user without much knowledge of SQL etc

JOhn,

Thanks for your response. I think I did not put my question correctly.

My question was more seeking input interms of expereince people had with SSRS as a reporting tool?

We are using GL Companies "Really Realtime" . Unfortunately it has too many flaws. It only exposes very specific table and you cannot add any custom table you might want to include. Performance is horrendous. It brought our production server down couple fo time. Hence, now we monitor its performance,... list goes on

ODA is a good tool, But as I said I am looking beyond E1. Obviously in this tough ecnomy we are carefull about spending money ( if any).



Thanks

SSRS is the best option to integrate different SQL Server databases and view the reports. It can be given to end users who do not have knowledge of SQL coding. It takes little time in development of each report based on users requirement.

Also with the help of SSAS it improves the performance with faster response time. We have been using SSRS, SSAS & SSIS tool extensively.

Hi John,

I have read through all the posts in this thread and I'd like to comment on your request for information. My company, InSync Technologies LLC, focuses on a complete set of BI Views and software for JD Edwards; however, I am not replying to sell you any of it, I am rather replying to your request for information about SSRS as a reporting platform. As we have based many of our solutions on SSRS, SSIS and SSAS plus a few third party packages, we have extensive knowledge of SSRS as an enterprise reporting tool.

I understand your frustration with many of the third party companies, and the ODA views which are limited and can be frustrating when users ask for information outside of the prepackaged views. If you take a little time you could create a Data Warehouse utilizing an OLE connection to SQL Server and through the ETL (Extract, Transform and Load) process you can transform the various fields that require special handling, ie: julian date fields, decimals places, and some of the weird leading spaces. Also creating a few new tables that extract category code values and order types, etc.. will result in a database that will be easy to use by either developers or power users and then they can access and build their own reports using your new warehouse files.

The process isn't difficult, it just takes a bit of time mapping out the ETL Warehouses, but once accomplished you can keep them updated with SSIS (at whatever interval you like scheduling the jobs in SQL Server Agent) in a separate database and even on a separate server if you have one available. None of this takes any really unique skills if you understand the basic data structures in JDE.

"jdeora" points out that you might want to also consider using SSAS (Analysis Services) to build OLAP Cubes and source these in your reports. Again, we offer a wide array of prebuilt Cubes for the larger files in JDE which are usually files like the F0911, F4111, the F42119, etc.. and trying to access these files may cause performance issues with any reporting tools unless you build a set of predefined indexes on the data warehouse, which is possible but SSAS is usually a much better alternative.

With all this stated SSRS is a wonderful platform in its ease of use and ability to distribute reports to a wide number of users across an Intranet or even via the Internet with no additional licenses or costs involved. We utilize SSRS as one of our standard bases of deployment, along with the other Microsoft Tools available in SQL Server.

You should be able to satisfy any of your reporting needs by following a path similar to those I have listed and if you have any questions, I'd be happy to answer them as best I can.

If you are looking for a low cost solution already built you can take a look at our offering, but it sounds like you are trying to conserve costs which is understandable in this market.

sincerely,
Paul Lynch
InSync Technologies LLC
http://www.bi4jde.com
http://www.insynctec.com

I guess the only downside of using SQL reports is the historical inability to leverage JDE code (and the inherent difficulty rewriting JDE code in SQL). - It may be necessary in reports to call JDE BSFN's to either read some calculated data from or to trigger some action / write some data back to JDE. And so historically you would have to resort to using RDA to do this.

Which is of course why we undertook to develop these solutions:

http://www.everestsoftint.com/index.asp?a=browse&p=ESI%20JDE%20Stored%20Procedure

I hope these will make SQL reporting more attractive, because now you can do _everything_ in SQL, including calls back into JDE from SQL code, including Table Triggers.

This will also enable Crystal and any BI Reporting integration into JDE - almost any reporting tool can call stored procedures...

We have a number of power users who use MS Access to satisfy their desire to report in an ad-hoc manner. The graphical nature of the query builder makes it easy for them, although they still have to have an idea where to look for their data and a slight knowledge of decimal issues. SQL reporting has a very similar graphical tool, and can import reports from access with relaitve ease...

We had to change the permissions on the database as by default *PUBLIC has write access, and I need users modifying data directly like I need holes drilled in my knee-caps!

The other thing we encourage is greater use of the grids, from real-time checking through to exports directly from JDE into excel for further manipulation.

One of our people even links excel into the data and graphs from there, although he had to turn off auto calculate...

For a no-cost solution, with a small amount of internal end-user training, it seems to work well and satisify most needs.

Hello Nick,

I have no direct experience with SSRS, we use Crystal extensively here. We're also Oracle - not SQL Server.

However - I can tell you from a performance viewpoint that going directly against the database using your reporting tool is much much quicker than any JDE solution. You have these issues to deal with though:

1. Date fields
2. Decimal places
3. Weird column names (ABALPH)
4. Data Security

You can solve these by creating your own "Reporting Views" in the database that:
- convert date fields to a normal date data type
- sets the numeric data fields to the right number of decimal places
- uses meaningful field names rather than DD aliases
- Sets DB permissions on who can access the database view

You can create the views manually (very painstaking process) or write a SQL script that "auto-generates" your DB views for you using the meta data inside JDE's DD and Table definitions.

Just another way to go thats worked very well for us here.

Regards,