Alternative to reports

[jack1955]

I missed out on 14 months of programming so when I was asked to add 3 new level breaks to a report, I went ahead and did just that. Several years ago, I didn't want to write any more reports if I did have to (or modify either). I found that the users would change their minds or want something different and I would spend all my time writing reports. I created some processes to output the data and let the users download or query the data on the AS/400. It worked well, but now I need a better solution. I'd like to know what others have done. Used IFS (or whatever it's called now), downloaded directly to PC, build multi-member files (please say you don't). Something good for me and the users.

 

[jaicee]

We've been knocking around with Crystal Reports. As a report writer, it's fairly easy to learn and use. Reports can be knocked out pretty quickly. Certainly quicker than coding. Modifications are also easy (usually) and quick.

The downside (although, it might just be my lack of experience) is that
[ol][li]it doesn't import the fields' text descriptions (at least i haven't been able to get that to happen with ODBC) and[/li]
[li]distribution of the reports seems to require a web server (at least if you want your users to be able to run the reports themselves).[/li][/ol]I still have much to do in regards to learning the package. So i may be missing things. However, it is a great way to design and maintain reports.

It also will output to a file as pdf, xls, doc, etc... and they can be e-mailed. There's a forum on Crystal here that can give you better info.

There may be other more iSeries specific packages out there. I just happened to have inherited it and found it pretty useful.

Hope this helps! Joseph R. Cattano
IS/Tech Support
Goldman Associates of NY

 

[jack1955]

Thanks. I was looking for a solution or solutions to get the data to the users, not create reports. A Crystal solution would require purchasing copies of Crystal. I was thinking of FTP, IFS, etc. The users has MS office suite on the desktop. They can use Excel or Access (or Word) to produce the reports or do the analysis.

 

[SuzieW]

Hi Jack

There is an article about One-Step AS/400 to Excel ODBC Transfer in a backissue of News/400 - August 99 (go to

http://www.iseriesnetwork.com,

search the archive for ODBC transfer) which sounds like it might be what you're after. But you have to be a professional member to read it! I haven't done it myself, but have seen it in action and it seems pretty good.

Good luck
Suzie

 

[Blobtech]

If you know anything about Windows programming, you can use the Client Access Object Library to run AS400 commands, and transfer data to the PC using SQL statements.

I've written an OCX which I use in VB, where I set various parameters that will run AS400 commands, download data and will either create and populate an Access database, or create an Excel spreadsheet.

 

[PeteJohnston]

You can use the CPYTOIMPF command to copy files with commas separating the fields then use FTP to move them to a network drive. If you add a .csv suffix to the file name when you copy it, it opens as an Excel spreadsheet when they double-click on it. This is Okay if you need to give them all of the fields but if you only need some of them, you might need a bit of RPG which does

Eval String = %TRIM(Field1) + ',' + %TRIM(Field2) ...

then write the string to a flat file.

Like Joseph I've never managed to get the field text using ODBC but if you want to be a smart-ass about this you can use the QDBRTVFD API to can get all of the info describing each field (name, text, type, length...) and write a first record with a comma-separated string holding the text.

 

[jack1955]

Thanks, PeteJohnson. I like your suggestions. How about naming conventions? I guess I could have folders by user profile, then subfolders by report name, then maybe individual documents by parms/date range. Yeah, that might just do it.

Cool!