Alpha Video Training
Results 1 to 4 of 4

Thread: Print FreeForm report in Group Footer of another Freeform report

  1. #1
    "Certified" Alphaholic
    Real Name
    Jaime Ben David
    Join Date
    Jan 2011
    Location
    Karkur, Israel
    Posts
    1,018

    Default Print FreeForm report in Group Footer of another Freeform report

    Hi
    Backend is MariaDB
    I am trying to print a basic "Invoice Receipt" document. It is an invoice-receipt document from the invoice-receipt tables (primary key InvoiceReceiptID), which has, for each one, a list of products sold, saved in InvoiceReceiptProducts, and a list of payments for each invoice-receipt, saved in InvoiceReceiptPayments.

    The only way i found in Alpha to print the document with the list of products is by basing the SQL Datasource on InvoiceReceiptProducts, then grouping the report by InvoiceReceiptID. The report is filtered by whatInvoiceReceiptID to output just one document.

    I also have an InvoiceReceiptPayments report (also filtered by InvoiceReceiptID) that i would like to embed in the group footer of the main report, alas, linked reports have to be layout reports ( the InvoiceReceiptPayments report is freeform and has many conditional areas ), and the sub-report option does not seem suitable ( the documentation for sub-reports is ambiguous to say the least) particularly since, contrarily to the documentation, i didn't find a way to create a one-to-many relationship between the tables.

    Has any of you an idea on how to achieve the above? I am surprised it's so difficult to implement such a basic reporting feature.

    Many thanks
    Jaime

  2. #2
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,764

    Default Re: Print FreeForm report in Group Footer of another Freeform report

    For this I would create the list of receipts as an HTML table and then use printhtml to print the table.

    I use a function to go through the records and create an HTML table that I then print directly from the report.

    But to do that you need to know how to write the SQL function. If that option will not work for you, then hopefully someone else will chime in.

  3. #3
    "Certified" Alphaholic
    Real Name
    Jaime Ben David
    Join Date
    Jan 2011
    Location
    Karkur, Israel
    Posts
    1,018

    Default Re: Print FreeForm report in Group Footer of another Freeform report

    Thanks Larry
    How do you construct the html table, and at what stage?

  4. #4
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,764

    Default Re: Print FreeForm report in Group Footer of another Freeform report

    Below is a typical function that I would write. This one create a table of installment payments.

    Then you call it as part of a select statement:

    select myfunction(myid) as installment_table

    The function is based on using a cursor. A cursor allows you to loop through records in a SQL table.


    CREATE FUNCTION `Installment_Table`(tTrip_ID int) RETURNS varchar(2500)
    READS SQL DATA
    BEGIN
    declare tTable varchar(5000) default '<TABLE border="1" cellpadding="3" cellspacing="2" width="100%"><TBODY><TR><TD><B>Date Due</B></TD><TD align="Right"><B>Amount</B></TD></TR>' ;
    declare tRow varchar(1000) default '' ;
    declare tFinal_Row varchar(1000) default '' ;
    declare tFirst_Row varchar(1000) default '' ;
    declare tFor varchar(25) ;
    declare tFor_Last varchar(25) ;
    declare tI int default 1 ;
    DECLARE done INT DEFAULT 0;
    declare tAdultCount int default 99;

    declare tRowCount int default (select count(Trip_ID) FROM installment_schedule AS Inst
    WHERE Inst.Trip_ID = tTrip_ID) ;

    declare row_cursor CURSOR FOR

    SELECT
    Inst.Installment_for,
    concat('<TR><TD>Upon Registration</TD><TD align="Right">$',
    Inst.Installment_Amount, '</TD></TR>') as First_Row,
    concat('<TR><TD>', date_format(Inst.Installment_Date, '%a %b %D, %Y'), '</TD><TD align="Right">$',
    Inst.Installment_Amount, '</TD></TR>') as Installment_Row,
    concat('<TR><TD>', date_format(Inst.Installment_Date, '%a %b %D, %Y'), '</TD><TD align="Right">$Balance</TD></TR>') as Final_Row
    FROM
    installment_schedule AS Inst
    WHERE
    Inst.Trip_ID = tTrip_ID
    ORDER BY
    Inst.Installment_For, Inst.Installment_Date ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN row_cursor ;

    FETCH row_cursor INTO tFor, tFirst_Row, tRow, tFinal_Row;

    if tFor <> 'Everyone' then
    SET tTable = CONCAT(tTable, '<TR><TD><b>', tFor, '</b></TD><TD></TD></TR>') ;
    SET tAdultCount = (select count(Trip_ID) FROM installment_schedule AS Inst
    WHERE Inst.Trip_ID = tTrip_ID and inst.Installment_for = tFor) ;
    end if ;

    if tRowCount = 1 then
    set done = 1 ;
    end if ;

    SET tTable = CONCAT(tTable, tFirst_Row) ;

    set tFor_Last = tFor ;

    repeat

    if not done then

    set ti = ti + 1 ;

    FETCH row_cursor INTO tFor, tFirst_Row, tRow, tFinal_Row;

    if not done then

    if tFor <> tFor_Last then
    SET tTable = CONCAT(tTable, '<TR><TD><b>', tFor, '</b></TD><TD></TD></TR>') ;
    end if ;

    IF ti >= tRowCount then

    SET tTable = concat(tTable, tFinal_Row, '</TBODY></TABLE>') ;

    ELSE
    if ti = tAdultCount then
    SET tTable = concat(tTable, tFinal_Row) ;
    else
    SET tTable = concat(tTable, tRow) ;
    end if ;
    END IF ;

    end if ;
    end if ;

    set tFor_Last = tFor ;

    until done end repeat ;

    CLOSE row_cursor ;

    RETURN tTable;

    END

Similar Threads

  1. Calculated Fields Dropped in Embeded Freeform Report
    By jackp_MDB in forum Mobile & Browser Applications
    Replies: 0
    Last Post: 05-31-2016, 06:30 PM
  2. Freeform report text won't show if any styling is selected
    By lilmofo812 in forum Mobile & Browser Applications
    Replies: 1
    Last Post: 04-12-2016, 01:05 PM
  3. Freeform Report on SQL view
    By Peter.Greulich in forum Mobile & Browser Applications
    Replies: 11
    Last Post: 05-30-2014, 07:42 AM
  4. How can I add freeform text to a report at print time?
    By alex in forum Alpha Five Version 6
    Replies: 3
    Last Post: 09-08-2006, 02:42 AM
  5. Report Group Footer print position
    By Mike Vance in forum Alpha Five Version 7
    Replies: 5
    Last Post: 05-19-2006, 10:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •