Unfortunately, this report has not been coded for excel and hence the choice is not there in the menu.
The simulation of the sql in the function getTransactions() in reporting/rep108.php using the en_US-demo.sql Chart of Accounts is illustrated in the attachment herein - it contains the raw table data and the sql results which includes the CUSTCREDIT entries as well. The Simulation SQL is:
SET @date_due := '2014-09-23';
SET @date_now := '2014-07-25';
SET @debtor_no := 3;
SET @dimension_id := 2; -- Dimension 1
-- SET @dimension2_id := 2; -- Dimension 2
-- Taken from defines in includes/types.inc
-- ST_SALESINVOICE = 10
-- ST_CUSTCREDIT = 11
-- ST_CUSTDELIVERY = 13
SELECT dt.*,
(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) AS TotalAmount, alloc AS Allocated,
((TYPE = 10) AND due_date < @date_now) AS OverDue
FROM 1_debtor_trans dt
WHERE tran_date <= @date_due
AND debtor_no = @debtor_no
AND TYPE <> 13
AND ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) > 1e-6
-- if (!$show_also_allocated)
-- AND ABS(ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) - alloc) > 1e-6
-- Dimension 1
AND dimension_id = @dimension_id
-- Dimension 2
-- AND dimension2_id = @dimension2_id
ORDER BY tran_date;
FLOAT_COMP_DELTA defined as a FA constant in PHP is used instead of 1e-6 in some places.
You may use the above in any MySQL client and execute them all on your database and check the results.
I see no reason why your output does not conform to what is expected. Please clear your report and js cache and try it again and use appropriate values for the constants defined in the code above on your database and verify the results to see if it matches your output.
The dimension name comes from another table and you will have to alter the sql to join it to that table like:
SELECT ....., d1.name AS D1Name
-- Dimension 2 if used
-- , d2. name A D2Name
FROM 1_debtor_trans dt
LEFT JOIN 1_dimensions d1 ON (dt.dimension_id=d1.id)
-- Dimension 2 if used
-- LEFT JOIN 1_dimensions d2 ON (dt.dimension2_id=d2.id)
WHERE ......
ORDER BY tran_date;
Then use the D1Name (and D2Name if appropriate) as desired in the report. Experiment with INNER JOIN instead of LEFT JOIN for the first or both instances as desired.
Post's attachmentsrep108_simulation.png 135.1 kb, file has never been downloaded.
You don't have the permssions to download the attachments of this post.
