IBM i Global

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
#Power
 View Only
Back to discussions
Expand all | Collapse all

Generating Reports to PDF

  • 1.  Generating Reports to PDF

    Posted Wed February 23, 2022 09:40 AM
    I am fairly new to IBMi. I switched over from Visual Foxpro (I know that is still old school) where we had a very easy to use wysiwyg report generator to print reports or save to PDF. My shop previously used printer files then converted to PDF. I know RDi has an option but it is not very intuitive.

    We are currently passing the data to node.js programs that generate a pdf, but it is taking my team days or weeks to program reports that would have taken me just hours with my old Foxpro tool.  What am I missing?  There must be some report generating tool for IBM i that I am not aware of.

    ------------------------------
    Lori Austin
    ------------------------------


  • 2.  RE: Generating Reports to PDF

    Posted Wed February 23, 2022 11:57 AM
    cpysplf is your friend, you can convert a spool file into PDF and then you can email it or save it in IFS or network folder.

    Thank You Jose Enoc Perez





  • 3.  RE: Generating Reports to PDF

    Posted Thu February 24, 2022 01:11 PM
    Hi Lori,

    The easiest way is if you have a printer file.  Below is one of my CRTPRTF.  Notice DEVTYPE, TOSTMF, WSCST.

    CRTPRTF FILE(objlib-name/prtf-name)
    SRCFILE(srclib-name/QDDSSRC)
    SRCMBR(prtf-name)
    DEVTYPE(*AFPDS)
    PAGESIZE(8.5 11 *UOM)
    LPI(8)
    CPI(16.7)
    FRONTMGN(0.5 0.5)
    OVRFLW(55)
    UOM(*INCH)
    TOSTMF('/path/Name of report goes here.PDF')
    WSCST(*PDF)
    REPLACE(*YES)

    If the printer file will be used for both printing and creating PDFs then you can use OVRPRTF at run time with the same parameters.

    If you want to convert an existing spooled file to a PDF, you can use SQL:

    SELECT job_name,
    spooled_file_name,
    file_number,
    systools.generate_pdf(
    job_name => job_name,
    spooled_file_name => spooled_file_name,
    spooled_file_number => file_number,
    path_name => '/home/GLENN/' CONCAT REGEXP_REPLACE(job_name, '/', '_') CONCAT '_' CONCAT spooled_file_name CONCAT '_'
    CONCAT file_number) AS "PDF Created?",
    '/home/GLENN/' CONCAT REGEXP_REPLACE(job_name, '/', '_') CONCAT '_' CONCAT spooled_file_name CONCAT '_' CONCAT file_number
    CONCAT '.pdf' AS pdf_path
    FROM qsys2.output_queue_entries_basic
    WHERE job_name = '942614/COMPOPR/ETHA11'
    AND spooled_file_name = 'QPJOBLOG';

    -- Alternate method.
    VALUES
    SYSTOOLS.GENERATE_PDF(
    JOB_NAME => '942614/COMPOPR/ETHA11',
    SPOOLED_FILE_NAME => 'QPJOBLOG',
    SPOOLED_FILE_NUMBER => 77,
    PATH_NAME => '/home/GLENN/942614_COMPOPR_ETHA11_QPJOBLOG_77');

    There are lots of good articles:
    https://gist.github.com/forstie/3805d74a06d3349a00deef704e40b219
    https://gist.githubusercontent.com/forstie/3805d74a06d3349a00deef704e40b219/raw/32d4943aeecd87deef9cd0cf9feac78fe66e51f8/generate%2520pdf.sql
    https://www.ibm.com/docs/en/i/7.3?topic=services-generate-pdf-scalar-function

    Lastly, there is the old method of using CPYSPLF:
    CPYSPLF FILE(QPJOBLOG)
    TOFILE(*TOSTMF)
    JOB(942614/COMPOPR/ETHA11)
    SPLNBR(*LAST)
    TOSTMF('/HOME/GLENN/942614_COMPOPR_ETHA11_QPJOBLOG_77.PDF')
    WSCST(*PDF)
    STMFOPT(*REPLACE)

    ------------------------------
    Glenn Gundermann
    Glenn Gundermann
    Thornhill ON
    (416) 317-3144
    ------------------------------



  • 4.  RE: Generating Reports to PDF

    Posted Thu February 24, 2022 01:12 PM
    I have written instructions on how to do it -> https://www.rpgpgm.com/2014/03/creating-pdf-files-from-spool-files.html

    ------------------------------
    Simon Hutchinson
    ------------------------------



  • 5.  RE: Generating Reports to PDF

    Posted Thu February 24, 2022 01:14 PM

    There are a number of options available to you.  I know of people using Crystal reports and also Jasper.  There are also native tools available from companies such as NGS (www.ngsi.com/company/index.html).  

    Another option would be something like Valence from CNX (https://www.cnxcorp.com) which can do everything from simply SQL based reporting with PDF and Excel output options, to full blown applications. There are also a number of open-source and/or low-cost reporting tools that will run natively on IBM i. Since we use PHP alongside RPG and SQL on our systems we have used PHP Grid (PHPGrid.com) for this purpose. It is very low cost and works well but does not have a graphic design option - it just builds reports very easily from SQL statements.

    Now with node, python, PHP and other languages supported natively you can use pretty much any tool you like. For that matter since there is now an excellent multi-platform ODBC driver available you could probably use tools like Foxpro still and access the data via ODBC.



    ------------------------------
    Jon Paris
    ------------------------------



  • 6.  RE: Generating Reports to PDF

    Posted Fri February 25, 2022 09:03 AM
    Thanks, Jon. This is exactly what I was looking for. 

    Lori Austin




    Original Message


  • 7.  RE: Generating Reports to PDF

    Posted Fri February 25, 2022 02:00 PM
    Glad to be of help.

    ------------------------------
    Jon Paris
    ------------------------------

    Original Message


  • 8.  RE: Generating Reports to PDF

    Posted Mon February 28, 2022 09:42 AM
    Have you looked at Db2 Web Query (you might already own the Express Edition). Very easy to quickly generate a report over, say, an SQL statement (or set up a dataset representation of your file structures) and then choose output (or let users choose output at run time, e.g., excel, excel pivot tables, PDF, HTML, "Active" report HTML.
    http://ibm.biz/db2webqueryi  and I can also look up if you already own it or not (reach out to me at QU2@us.ibm.com and provide your s/n).

    ------------------------------
    Doug Mack
    ------------------------------



  • 9.  RE: Generating Reports to PDF

    Posted Mon February 28, 2022 02:35 PM

    In addition to Jon Paris' answer (lots of solutions relying on Open Source technologies :)), have you considered DB2 Web Query for this?

    DB2 Web Query offers a WYSIWYG interface to build graphs or table reports, with HTML, PDF, Excel outputs.

    More informations on : https://www.ibm.com/support/pages/node/665181 




    ------------------------------
    Gautier DUMAS
    ------------------------------



  • 10.  RE: Generating Reports to PDF

    Posted Mon February 28, 2022 07:07 PM

    Hi Lori,

    In case it's helpful, we at Profound Logic are in the process of adding PDF support to our Profound.js product.  

    We also offer a service to convert RPG O-spec style reports to use this new PDF support in Node.js, if you happen to have existing RPG programs, this could be useful to you.

    If you're interested, please reach out to us!



    ------------------------------
    Scott Klement
    Director
    Profound Logic Software
    Oak Creek WI
    ------------------------------



  • 11.  RE: Generating Reports to PDF

    Posted Wed April 27, 2022 12:25 PM
    Hi Lori, 

    I used to program is Foxpro. Too bad it's on the downturn. 

    Jasper Report Server is probably the best option because it has a visual designer and you can design everything from reports to pixel perfect business documents. 

    You don't want to had code reports in RPG. Ugh !!

    Check it out:
    https://community.jaspersoft.com/project/jasperreports-server

    However as mentioned if you do have IBMi spool files, CPYSPLF to PDF is your best option.

    ------------------------------
    Richard Schoen
    ------------------------------



  • 12.  RE: Generating Reports to PDF

    Posted Wed April 27, 2022 01:20 PM

    Try the Eclipse BIRT project (B.I. Reporting Tool). Version 4.8.0 runs inside RDi

     

    The current version is 4.9.0, and you can download the standalone designer from here:

     

    https://eclipse.org/birt

     

    You have to download the JDBC driver from the JTOpen project and follow the instructions when creating a new report.

     

    https://sourceforge.net/projects/jt400/

     

     

    Best regards,

     

    Wim Jongman, CTO

    +31(0)622371297

     

     

    remainsoftware-logo

    Embrace Change. Remain In Control.

     

    www.remainsoftware.com

    See our video to learn more about Remain.

     

     




    Original Message


  • 13.  RE: Generating Reports to PDF

    Posted Thu April 28, 2022 12:21 PM
    Yeah BIRT is also interesting.  

    I wonder if any of the Jasper Plugins work in RDI ? 


    ------------------------------
    Richard Schoen
    ------------------------------

    Original Message


Related Content