Was also thinking in the line of dotproject or openproject.

Will have a look at your suggestion.

Regards

I thought that if you setup the project as a branch of the client that it might work, but you can not draw reports for the branch, only for the main company.

I also thought that you could setup each project with that client as a separate client, but then it becomes a nightmare to keep track of the cost items for that project.

Seems to be a hornet's nest like you say.

I am thinking of using an open source project management software for the tracking and costing of the project and then monthly bring in the total cost before invoicing from FA.

Regards

You have literally opened a can of worms here. Hope FA 2.4 addresses it better. Thanks for stirring up the proverbial hornet's nest and lets see what good comes of it.

You could test out the rep101.php with dimensions and report your findings. I do not see the use of any Dimensions in it.

Your new.zip change works great accept for the Total Balance.

Is there a way that one could have a sub balance for the dimension and then the total balance below?

Would that be the same if one would use dimensions to filter in Customer Balances report Report101.pdf

The attachment is from the demo CoA and it does not illustrate your case and is here only for there report format.

This is probably why the dimensions were not included in this report!

At the bottom of the reports where it reads: "Total Balance" it is the total balance for all the dimensions relating to that on customer, instead of the Total for that one selected dimension only.

The reports is providing the data require as per your zip file above.

When a dimension is selected for the report for that customer, it lists the correct entries in each line.

The Total at the bottom of the Statement report is the Total for all the dimensions for that customer, not just the total for specific Dimension selected.

I do not know if the above would fix the issue? It seems to the same as the info in the attached zip folder above.

regards

Wynand

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.

It would also be great if we could save the statement as Excel.

Let me start by thanking you for your input.

Have replaced the files. The report does give the info required, but there are two issues that I can pick up:

1. The dimension Name is not displayed anywhere on the Statement
2. The dimension total is the same as the total outstanding for all the dimensions on that particular client and not just for the invoices outstanding for the specific dimension.

For example:

Dimension A and Dimension B is linked to the same customer. Drawing a reports then on the customer with no dimension selected gives you the answer for the total outstanding for A+B.

when selecting Dimension A, the Invoices and amounts listed in the detail matches the dimension, but the total for the Statement is still A+B instead of the total only for A

Hopes this makes sense, but it gives a great platform to work from.

Regards

After backing up the files, overwrite the files in the reporting folder with the ones in the attachment and see if it is what you want.

rep108.php in it's function getTransactions() has an sql statement that refers to just 1 table where the fields can be written without the table name prefix.

I think that the statement report where invoices and payments are listed could work if you can search for a specific dimension attached to that Customer.  The customer balances report can also work.

It seems that all the posts regarding tags deals with income statements only, and will not provide me with a list of invoices that the dimension was attached to.

when for example I have a dimension A and dimension B on Level1 and I need to have a list of invoices for list be, it is not possible.

I do not see how using tags will provide me with that information if the only place where tags can be used is in the Income Statement report.