1 2 Previous Next 22 Replies Latest reply on Jun 30, 2009 3:12 PM by 657571

    Is there an inexpensive APEX report printer for invoices/checks/statements?

    690430
      I am considering using APEX for a billing/payments type system for a small company. BI Publisher is not an option for them due to cost. Has anyone used a free or low-cost product to successfully print detailed reports such as invoices, statements, checks, etc. from APEX?
        • 1. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
          dmcghan
          User,

          What is your name?

          Try searching "Jasper" in the forum. You will not find too much but enough to get started. There's also the Apache FOP option but that's limited. Jasper is more inline with BI Publisher. Learning curve? Of course there is - I'm just starting to explore it myself.

          I know Dietmar Aust plans to talk about this in this years ODTUG conference. Should be interesting.

          Good luck.

          Regards,
          Dan

          http://danielmcghan.us
          http://sourceforge.net/projects/tapigen
          1 person found this helpful
          • 2. Re: Is there an inexpensive APEX report printer for invoices/checks/stateme
            O_X
            Hi,

            I use Crystal Reports to do invoicing and late notices for a small electricity provider client. Im not a pro with oracle or apex but found the learning curve to use CR with my app was kinda easy after the setup and designing the reports.

            JP
            SFS Data Solutions
            1 person found this helpful
            • 3. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
              690430
              Dan,

              Thanks. I will check it out.

              Geoff

              PS You found me.
              • 4. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
                TexasApexDeveloper
                But crystal IS NOT cheap by any means... You are better off looking at pl/pdf if you want a cheap solution...

                Thank you,

                Tony Miller
                Webster, TX
                1 person found this helpful
                • 5. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
                  Martijnke
                  Geoff,


                  there are free alternatives available
                  have a look here : [pl_fpdf|http://reseau.erasme.org/PL-FPDF,1337]

                  what I have used for my invoice app is RTF
                  I made templates in ms word saved them as rtf
                  then put them in a clob
                  and used pl/sql to replace field names with field values
                  then sent the generated RTF to the clients browser

                  Kr
                  Martin
                  1 person found this helpful
                  • 6. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
                    463927
                    Hello Martin,

                    this:
                    Martijnke wrote:
                    ...... then put them in a clob
                    and used pl/sql to replace field names with field values
                    then sent the generated RTF to the clients browser
                    sounds interesting for me. Do you think its possible to elaborate a little bit
                    about this technique ?

                    Thanks

                    Regards
                    Andree
                    • 7. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
                      Martijnke
                      Andreé,

                      o no problem
                      1)
                      first make an invoice layout with field holders placed in between ##
                      e.g. Dear Mr #CNAME# find here your latest invoce and so no
                      just make it in wordpad, word or openoffice
                      and save it in an RTF file
                      I then copied the RTF in a CBLOB field using TOAD
                      2)
                      load this clob in clob variable in a pl/sql procedure
                      mix your RTF with real data, f.i. #CNAME# will be replaced
                      using pl/sql : replace('#CNAME# , rec.name); and so on for all fields
                      the end result is the invoice as rtf

                      the idea comes from this application from oracle called mailmerge
                      found here : [http://htmldb.oracle.com/pls/otn/f?p=18326:44:3650339304424939::::P44_ID:1682]
                      it's an apex app where I based my work on
                      have a look at it and feel free to ak more questions:

                      CREATE OR REPLACE PROCEDURE RIGHTSHOP.DOWNLOAD_INVOICE(p_id in number) AS
                          Lob_loc     CLOB;
                          i           integer := 0;
                          v_length    integer;
                          v_blob      BLOB;
                          v_tot       FACTUUR.TOT%type;
                          v_totexcl   FACTUUR.TOTEXCL%type;
                          v_totbtw    FACTUUR.TOTBTW%type;
                          v_docnr     FACTUUR.DOCNR%type;
                          v_datum     FACTUUR.DATUM%type;
                          v_klant_id  FACTUUR.KLANT_ID%type;
                          v_mime      varchar2(48) := 'application/msword';
                          v_file_name varchar2(2000);
                          cursor  cur_artikels(p_factuur in FACTUUR_ARTIKELS.FACTUUR_ID%type) is
                            select AANTAL, OMSCHRIJVING, BTW_CAT_ID, SERIENUMMER, NETTO_VERKOOPPRIJS
                                , (aantal * NETTO_VERKOOPPRIJS) netto_bedrag
                                , (select btw_waarde from btw_cats where id = btw_cat_id) btw         
                              from factuur_artikels
                             where factuur_id = p_factuur;
                          rec_firma   firma%rowtype;
                          str_gemeente varchar2(4000);
                          v_knaam     KLANTEN.NAAM%type;
                          v_kstraat   varchar2(4000);
                          v_kbtw      KLANTEN.BTW_NUMMER%type;
                      BEGIN
                          begin
                          select rtf
                            into lob_loc
                            from factuur_layouts
                           where id = (select flayout_id from firma);
                          exception
                            when others then
                              htp.p(' error during select '||sqlerrm);
                          end;
                      -- firma
                          select NAAM1, NAAM2, ADRES1, ADRES2, POSTCODE_ID, EMAIL, WEBSITE, BTW_NUMMER, TEL1, FAX, GSM, REKENING        
                            into rec_firma.NAAM1, rec_firma.NAAM2, rec_firma.ADRES1, rec_firma.ADRES2, rec_firma.POSTCODE_ID, rec_firma.EMAIL
                               , rec_firma.WEBSITE, rec_firma.BTW_NUMMER, rec_firma.TEL1, rec_firma.FAX, rec_firma.GSM, rec_firma.REKENING
                            from firma;
                          
                          select postcode || ' ' || gemeente
                            into str_gemeente
                            from postcodes
                           where id = rec_firma.postcode_id;
                          lob_loc := replace(lob_loc, '#FNAAM1#', rec_firma.naam1);
                          lob_loc := replace(lob_loc, '#FNAAM2#', rec_firma.naam2);
                          lob_loc := replace(lob_loc, '#FSTRAAT#', rec_firma.adres1 || ' ' ||  rec_firma.adres2);
                          lob_loc := replace(lob_loc, '#FGEMEENTE#',str_gemeente);
                          lob_loc := replace(lob_loc, '#FEMAIL#', rec_firma.email);
                          lob_loc := replace(lob_loc, '#FWEBSITE#', rec_firma.website);                
                          lob_loc := replace(lob_loc, '#FBTW#', rec_firma.btw_nummer);
                          lob_loc := replace(lob_loc, '#FREKENING#', rec_firma.rekening);
                      --factuur
                          select tot, totexcl, totbtw, docnr, datum, klant_id
                            into v_tot, v_totexcl, v_totbtw, v_docnr, v_datum, v_klant_id
                            from factuur
                           where id = p_id;
                          lob_loc := replace(lob_loc, '#BB#', to_char(v_totexcl, 'FM999G999G999G999G990D00'));
                          lob_loc := replace(lob_loc, '#TB#', to_char(v_totbtw, 'FM999G999G999G999G990D00'));
                          lob_loc := replace(lob_loc, '#TO#', to_char(v_tot, 'FM999G999G999G999G990D00'));
                          lob_loc := replace(lob_loc, '#FD#', to_char(v_datum, 'DD/MM/YYYY'));
                          lob_loc := replace(lob_loc, '#FN#', to_char(v_docnr));
                          lob_loc := replace(lob_loc, '#eenheid#', 'Aantal');
                          v_file_name := 'factuur' || v_docnr;
                      
                      -- klant
                          select naam, adres1 || ' ' || adres2 as straat
                               , (select postcode || ' ' || gemeente from postcodes where id = klanten.postcode_id) as gemeents
                               , btw_nummer 
                            into v_knaam, v_kstraat, str_gemeente, v_kbtw
                            from klanten
                           where id = v_klant_id;
                          lob_loc := replace(lob_loc, '#KNAAM#', v_knaam);
                          lob_loc := replace(lob_loc, '#KSTRAAT#', v_kstraat);
                          lob_loc := replace(lob_loc, '#KGEMEENTE#',str_gemeente);
                          lob_loc := replace(lob_loc, '#KBTW#', v_kbtw);
                      
                      -- factuur_artikels    
                          for vc_curr in cur_artikels(p_id) loop
                            i := i + 1;
                            lob_loc := replace(lob_loc, '#AR' || to_char(i) || '#', rpad(vc_curr.omschrijving, 120, ' '));
                            lob_loc := replace(lob_loc, '#AN' || to_char(i) || '#', to_char(vc_curr.aantal));
                            lob_loc := replace(lob_loc, '#NE' || to_char(i) || '#', to_char(vc_curr.netto_verkoopprijs, 'FM999G999G999G999G990D00'));
                            lob_loc := replace(lob_loc, '#BT' || to_char(i) || '#', to_char(vc_curr.btw) ||'%');
                            lob_loc := replace(lob_loc, '#BE' || to_char(i) || '#', to_char(vc_curr.netto_bedrag, 'FM999G999G999G999G990D00'));
                          end loop;    
                          
                          for j in i .. 9 loop
                            lob_loc := replace(lob_loc, '#AN' || to_char(j) || '#', null);
                            lob_loc := replace(lob_loc, '#AR' || to_char(j) || '#', lpad(' ', 120, ' '));
                            lob_loc := replace(lob_loc, '#NE' || to_char(j) || '#', null);
                            lob_loc := replace(lob_loc, '#BT' || to_char(j) || '#', null);
                            lob_loc := replace(lob_loc, '#BE' || to_char(j) || '#', null);
                          end loop;
                          v_length := length(lob_loc);
                          -- set up HTTP header
                          --
                          -- use an NVL around the mime type and
                          -- if it is a null set it to application/octect
                          -- application/octect may launch a download window from windows
                          v_mime := 'application/pdf';
                          owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );
                          -- set the size so the browser knows how much to download
                          htp.p('Content-length: ' || v_length);    
                          if v_mime != 'text/html' then
                              -- the filename will be used by the browser if the users does a save as
                              --htp.p('Content-Disposition: attachment; filename="' || v_file_name || '"');
                              htp.p('Content-Disposition: filename="' || v_file_name || '"');
                          end if;
                          -- close the headers
                          owa_util.http_header_close;
                          -- download the BLOB
                          v_blob := c2b(lob_loc);    
                          --wpg_docload.download_file( Lob_loc );
                          wpg_docload.download_file(v_blob);
                          /*update factuur
                             set printed = 1
                           where id = p_id;
                          APEX_UTIL.SET_SESSION_STATE('P9_PRINTED', '1');
                          */ 
                      exception
                        when others then
                          htp.p('other error : ' || sqlerrm);
                      END;
                      /
                      1 person found this helpful
                      • 9. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
                        692007
                        Andree,

                        I would seriously consider BIRT (completely free) running on Tomcat. We use Apex and BIRT in our applications at present and it works flawlessly. It has a great report designer and the quality of the reports are outstanding. It also has the ability to output reports in PDF, MS Word, Excel and Powerpoint.

                        Give it a shot, you'd be pleasantly surprised.
                        • 10. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
                          Binepal
                          Vis Naidu,
                          do you have documentation to integrate it with XE?
                          Thanks
                          • 11. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
                            692007
                            binepal,

                            There is a lot of documentation available, you just have to dig around for it. However feel free to email me and I will share with you whatever knowledge I have on setting up the
                            XE/Apex/Tomcat & Birt environment.

                            In a nutshell you will be serving up Apex pages on port 8080 (either via the Embedded PL/SQL Gateway or Oracle HTTP Server). You will then run Tomcat with BIRT deployed on any other port you desire, we chose port 8180. Finally it is a matter of setting up a URL in Apex to call the BIRT reports on Tomcat. If you run Tomcat as a service there is nothing visible on the server that makes the integration of these technologies look clunky, it all works seamlessly.

                            Start off by downloading BIRT RCP Designer 2.3.2 (latest stbale version). After familiarising yourself with BIRT via the designer you will need to download the BIRT Runtime and Tomcat 5.5.12 (I know it works with this version) to set up the runtime environment.

                            Contact me as soon as you've done the above or sooner if you need to.

                            Good luck.

                            Vis Naidu
                            • 12. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
                              Binepal
                              Vis Naidu,
                              i think i got birt working,
                              i downloaded birt-runtime-2_3_2 and after expanding this there was a war+ file which i deployed under tomact
                              now when i access
                              http://myservername.net:8089/birt/ i get a page which displayes

                              BIRT viewer has been installed.
                              Thank you for your choosing BIRT (Business Intelligence Reporting Tool).
                              Viewer Version : 2.3.2
                              Engine Version: 2.3.2
                              JRE version: 1.5.0_17
                              View Example

                              when i click on View Example* it shows me a page with a message

                              Congratulations!+

                              If you can see this report, it means that the BIRT viewer is installed correctly.+

                              Sample Parameter: my parameter+

                              Apr 12, 2009 11:41 PM+

                              now how can i create reports and access these report from APEX. Do i need to install any thing else?
                              tia
                              binepal
                              • 13. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
                                692007
                                Yoy need to download and install the BIRT RCP Designer 2.3.2.
                                Copy the Oracle jdbc driver (ojdbc14.jar) to the C:\birt-rcp-report-designer-2_3_0\plugins\org.eclipse.birt.report.data.oda.jdbc_2.3.2.vxxxxxxxx\drivers folder.

                                Create a blank report and setup your Data Source by following the wizard i.e. Driver Class = oracle.jdbc.OracleDriver (v10.2),
                                Driver URL = jdbc:oracle:thin:username/password@localhost:1521:XE. Once that is done you're ready to create a query (Data Set)to use in your report. Start off by creating a simple query without any input parameters. Once that is done drag the query onto the report canvas and preview it. Play around with the formatting, borders, fonts etc. to get a feel for what's on offer.

                                Please persevere with this as I believe it is a superb solution for reporting. The initial learning curve is steep but you will thereafter have in your development armoury an invaluable reporting solution.

                                Good Luck.

                                Vis Naidu
                                • 14. Re: Is there an inexpensive APEX report printer for invoices/checks/statements?
                                  Binepal
                                  Vis Naidu,
                                  I downloaded BIRT RCP Designer 2.3.2 and have followed your direction for creating data source and report. I can view the output of report in pdf. Now how do i use APEX to utilize this report.
                                  tia
                                  1 2 Previous Next