ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Reports: Printing to Excel, and distribution. Whats the best way?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reports: Printing to Excel, and distribution. Whats the best way?

    In my firm, for a number of years, we have naturally been moving away from paper reports, and Excel is the preferred means.
    We therefore tend to build extract files, rather than print spoolfiles.
    We have a number of monthly reporting jobs, which build extract files.
    Some of these extract files are based on other extract files.
    Im wondering what the best way of distributing reports are. We are faced with three options.

    1. As part of the monthly job, Let Iseries create an excel file, and FTP it to a location on the network.
    2. use Catapult to read a spoolfile/work file.
    3. Use microsoft reporting services (scheduled, or on demand)

    Pros of the above ...
    1. if monthly job fails, or there is a problem, no report gets distributed, so users know theres a problem
    2. Not sure if it can read extract files, but can read spool files, create excel files and distribute/email.
    3. More conventional means of data extraction, users have a URL and access reports as they see fit.

    Cons against above ...
    1. Always better to get Data Off database, rather than have database send it out (in my opinion).
    .. plus, no URL to pick up report.
    2. Very slow, resource heavy: need to design catapult reports. If job failed, there would be
    no way of knowing. Plus, no URL to pick up report.
    3. If job failed, there would be no way of knowing - extract file may hold last month's data.

    Anyone got any thoughts on this, or other ideas? Is there a conventional method everyone uses?

    Many thanks

  • #2
    Re: Reports: Printing to Excel, and distribution. Whats the best way?

    i use Scott's POI HSSF service program to generate the excel files & MMAIL to send the spreadsheet to designated recipients, trust me the users will let you know if something didn't come through. extra bonus is that both are FREE...
    I'm not anti-social, I just don't like people -Tommy Holden

    Comment


    • #3
      Re: Reports: Printing to Excel, and distribution. Whats the best way?

      I have written some RPG programs that use Scott's POI HSSF routines as well, and these are fairly easy to use once you understand how to use them. It isn't perfectly obvious to an old style RPG programmer like myself at first though.

      In many cases our users are happy with CSV files though, which we create either by building database files and using CPYTOIMPTF to create the CSV on the IFS (Old Style) or by writing the output directly to a stream file, which is very easy using RPG-Free (Newer Style).

      I see you have Catapult StateOfPlay, so you might also have Websmart like us? We also have some intranet programs that prompt the user and then create CSV files using Websmart ILE. That's very easy.

      I have also created XML output from some Websmart programs, outputting the raw code rather than using the POI HSSF routines.

      Mostly though, in our case the users are happy to take a CSV file and to format it themselves or to import it into their own reports.
      Poddys Rambles On

      Comment


      • #4
        Re: Reports: Printing to Excel, and distribution. Whats the best way?

        i wrote a wrapper around the HSSF service program that allows you to specify the input file to convert, output stream file, etc so it's a breeze. the only time that i use the service program directly in a program is if there's any special formatting, etc requirements involved.
        I'm not anti-social, I just don't like people -Tommy Holden

        Comment


        • #5
          Re: Reports: Printing to Excel, and distribution. Whats the best way?

          Websmart sounds really interfesting; however, Microsoft is the main focus of our department; hence, Reporting Services is more than likely the preferred option - unless theres good enough reasons to look at the alternative. I was interested in looking at the ftp from a technical perspective (ie better to take data, or give data). I agree, its better to give them raw data, and let users do their own formatting. However, on our reports, we need to stamp certain attributes, such as when the report was created, when the extract data was built, cut-off date used to build that extract date, etc; so I think plain CSV wont work. Think we need to stick to MSRS, and come up with some clever means of ascertaining on the report that the job which built the extract succeeded.

          OK thanks guys, thanks very much for all your help anyway.

          Comment


          • #6
            Re: Reports: Printing to Excel, and distribution. Whats the best way?

            POI/HSSF is the simplest way to write the data out - if you can properly understand it. The alternative is to manually create a sample spreadsheet, view the XML code and to write that out.

            If you need to confirm that the extract completed etc, you could either add a footer to the report to show audit/control totals etc, or you could write the content to a temporary file (database or stream), output the controls in the header rows, and then write/append the data that you stored in a temporary location.

            Hoping that made sense. I had to do something similar when they needed me to create a spreadsheet with 4 tabs. I had to store the content of tabs 2-4 in temporary files and then inserted them where necessary after I had finished reading the extracted data.
            Poddys Rambles On

            Comment

            Working...
            X