Report Direct Download to Excel

[jojochan]

Hi, I am a new user of JDE OneWorld. Currently our company users are experiencing lots of manual work for report printing. Basically they need to download the report output to Excel for further calculations. However, the standard reports have page breaks, page headers, and lots of blank lines in between the details. So the users have to manually delete hundreds or thousands of blank lines, and manually re-align the columns one by one after downloading the output to Excel. This is very very tedious and waste of effort!

The truly Excel ready format should be a delimited report (by TAB or other delimiters) with only one header row and all detail rows without any blank lines in between, e.g.:

ACCOUNT|DESCRIPTION|CURRENCY|AMOUNT
123|Desc 123|USD|888.89
334|Desc 334|USD|23499.00
...........

Then users can just open this delimited output in Excel and all columns will be laid out automatically.

I have the following questions:

1. How difficult is it to re-write the standard reports in Excel-ready formats such as the above? Can this be accomplished using the standard report writer tool, with each row having variable (unlimited) length depending on the data?

2. If we don't want to seek help from IT, is there any easy way end-users can download their reports to Excel and use them readily in spreadsheet format, without need to delete blank lines and re-formatting columns etc.?

Thanks so much!
Jojo

 

[Peter_Newcomb]

As an alternative we have been downloading table and business view data from OneWorld into Excel through MS Query and the OneWorld ODA Drivers. Assuming you know what information you want from where in the system this may be an alternative for what you need. Whoever builds the Spreadsheets needs to be comfortable with the data, but by adding a few variables you can setup recycleable/reusable queries. We have setup 5-10 general spreadsheet for pulling GL details, address book information, sales order details and the like. This has worked well for adhoc reports and data analysis. Let me know if you want anymore details.

 

[davepower]

The easiest way to format the data in excel would be to change the base report in report writer-- not a hard thing to do if you have report writer skills / access. Two things that I would suggest would be to remove page headers and page footers and replace with report headers and report footers-- therefore columns would be continuous when exporting in CSV. Second, just to be sure that I could delimited the report properly, I would add columns of pipes (‘ | ‘) between data columns and then use Excel’s ‘text to columns’ function with a pipe set as a the delimiter.

Alternatively, as I see pnewcomb suggested, would be use of ODBC in the Excel/Access that would give direct table lookup possibilities-- I use ODBC every day, but understanding table relationships is strongly recommended for performing ODBC lookup queries-- start small and work your way up.

The final alternative I can come up would be to use VBA to do all of your redundant formatting-- probably the easiest non ‘IT’ approach.

dp

 

[SidoConsulting]

You can also create a new version, go to the print properties and select the out put for CSV. If RDA does not suggest to reset spacing , you will need to ensure that spacing is set to 52 (Layout in RDA Menu). Then simply ensure that there is only 1 field per space (this space matched to excel spread sheet). You can shorten the fields to fit into the space, as they will print completely in the spreadsheet. Any level breaks I would make invisible as any totalling can be accomplished on the spreadsheet. Try it, it's a simple solution that works good.

 

[sumsoftware]

You should try DAS at www.dominionsoftusa.com
Thanks
----- Original Message -----
From: "SidoConsulting" <[email protected]>
To: <[email protected]>
Sent: Friday, December 12, 2003 11:25 AM
Subject: Re: Report Direct Download to Excel


the out put for CSV. If RDA does not suggest to reset spacing , you will
need to ensure that spacing is set to 52 (Layout in RDA Menu). Then simply
ensure that there is only 1 field per space (this space matched to excel
spread sheet). You can shorten the fields to fit into the space, as they
will print completely in the spreadsheet. Any level breaks I would make
invisible as any totalling can be accomplished on the spreadsheet. Try it,
it's a simple solution that works good.
Crystal.
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=Apps&Number=65340
messages, login to http://www.jdelist.com/forums, click Control Panel, then
click Edit by "Subscribe / Unsubscribe from receiving board posts by email,
change message notifications, etc." and adjust your subscription
preferences. JDEList is not affiliated with JDEdwards®

 

[mrgphd]

I had to produce excel tables with only some of the details of standard reports.

I made it through the use of RDA and functions OpenFlatFile, WriteOneLine, CloseFlatFile. In Do Section of the report's main section - or in the footer, header, wherever they are - put the RV values containing interesting numbers into a szOutputString_APTA dd (quite long string), values separated by tabs. Tab is tricky: had to copy&paste it into a cSeparator_CHAR from notepad.exe to be able to concat it to the APTA. Open the file, set tab and write custom excel header in Init Section, close the file in eg. After Last Object.

You can modify the report's procopt as well and add a new tab with file write options (eg. OWTRFL for [y]/[n] to write the file and a DSC1 for filepath).

I have only one question: from interactive forms one can run executables (run output.xls and Excel comes up) async, but in reports I only found a sync function thus excel must be closed first to get the pdf document. Is there a way to launch an app and not to be obliged to close it for the report to run on?

 

[KSmithCPA]

Depends on the report you are trying to export into excel...

Simple reports with only a few sections probably will be pretty easy to change. (Note: you probably want to make a copy of the report, and make your changes to the copy.) More complex reports could be tricky. From the example data you provided, the simplest thing would be to write a custom report to extract the data you need.

Another idea is to use the ODBC driver provided by JDE to extract the data directly from the database into excel. Then you can simply refresh the spreadsheet whenever you need current data.

Peace

 

[got_to_love_jde]

Hi Ken,
I have a question for you. Would that be World Peace or Inner Peace you are referring to ? Or some other kind of Peace ?
I know I could use a Piece some days.
Regards,
Dave

 

[JJWeer]

If there's an UBE that contains the data you want, while submitting it make sure to flag the CSV option.
Upload this into Excel, it will automatically use the import wizard to help format in in Excel.

To get rid of page headers and so forth, you can use the sort option in excel to sort the data in a column that contains data you want to keep. All page and report header crap is now either at the bottom or top of the excel sheet, so that you can easily get rid of that.

JJ

 

[CCarpenter]

Would this ODBC driver be available in WORLD or only ONE WORLD? We have an AS400 Query Report (or file) that I need to automatically get to EXCEL format with no intervention.

 

[DBohner-(db)]

You can configure the ODBC Client in Client Access to connect to your AS/400 db. Due to security issues, you want to limit access and make sure that it is 'really' something you want to do.

Daniel

 

[pcaenaro]

Hi,

Although I did not use it (I work for a One World client), I know that JDEDirect has a World version.

Cheers

Philippe

 

[mfletcher]

For several users we create bare bones data csv reports with no headers or blank lines. However, what has worked very well for us is the use of the ODBC acquired data directly into an Excel PivotTable. The report is formatted and summarized with drilldowns and settings which allow the data to be automatically refreshed everytime the report is opened. All of our top executives have several key indicator reports on their desktop which they can open at will.

 

[jgvargas]

Quizás se mi respuesta un poco tarde para darle solución a tus problema.
Por lo que entendí es que Generas un UBE y lo generas en el formato de CSV.

Cuando generas un UBE y los campos físicos en el reporte se encuentran encimados o el valor del contenido del campo es mas grande del espacio estimado en el Diseño del reporte, provoca que al momento de la exportación esto abra espacios o celdas en Excel.

Para darle solución a este problema acudo al siguiente procedimiento:
1.- Creo una nueva versión del reporte (especial para UBE's en CSV).
2.- Dejo mas grande los espacios entre cada campo en el diseñador de reportes (No encimo campos).
3.- Alinear perfectamente los campos, ya que un pequeño desnivel en alguno de los campos, provoca que se cree un nuevo renglón en Excel.
4.- Puedes especificar el tamaño de la hoja un poco mas grande ( no existen perdida de datos al momento generar el UBE en CSV)
5.- También puedes Formatear la sección con un tamaño de letra mas pequeño que el actual.(debes de considerar los Puntos, 1, 2 y 3, de este procedimiento).

Espero que esto te ayude.
Saludos desde León, Gto, México.