I’m opening this thread because I am starting putting my head deeper into Report Transformation.
I think that, to understand all the mechanisms of this reporting tool, I would like to start fully replicating Manager GLT report which is the mother of all reports.
Any help in this forum from anyone will be heavily appreciated.
I would like to involve into this discussion @Patch and everyone has a certain expertise in Report Transformation and, of course, @lubos.
This is my poor attempt at a GL report. I am almost certain that it could lose some unnecessary steps and it is ungodly slow, but it serves my purposes.
{% assign objType = "transactions" %}
{% assign trxs = [objType] | between: report.From, report.To %}
{% assign str_fields = [objType] | first | fields %}
{% assign arr_fields = str_fields | newline_to_br | replace: "<code>", "" | replace: "</code>", "" | replace: "<br/>", " "| split: " " %}
<table>
<tr style="background-color: #ddd;">
{% for i in arr_fields %}
{% unless i contains "TransactionLine" %}
<th style="border-width: 1px 0{% if forloop.last == true %}1px{% endif %} 1px 1px; padding: 5px; font-size: 90%;"><b>{{ i }}</b></th>
{% endunless %}
{% endfor %}
</tr>
<tr>
{% for trx in trxs %}
<tr>
{% for field in arr_fields %}
{% unless field contains "TransactionLine" %}
{% assign arr_field = trx.[field] %}
{% if field contains "Amount" or field contains "Qty" or field contains "Date" or field contains "Is" %}
<td style="border-width: 0 0{% if forloop.last == true %}1px{% endif %} 1px 1px; padding: 5px;">{{ arr_field }}</td>
{% elsif field contains "Transaction" or field contains "Invoice" %}
<td style="border-width: 0 0{% if forloop.last == true %}1px{% endif %} 1px 1px; padding: 5px;">{{ arr_field.Reference }}</td>
{% else %}
<td style="border-width: 0 0{% if forloop.last == true %}1px{% endif %} 1px 1px; padding: 5px;">{{ arr_field.Name }}</td>
{% endif %}
{% endunless %}
{% endfor %}
</tr>
{% endfor %}
</table>
If any anyone improves upon it, I would really appreciate sharing the modified version of it.
Thanks. The most difficult thing, I think, is to calculate the opening and closing balances.
Open balance is done by
{% Assign openingBalance = transactions | between: BusinessStartDate, report.From | group_by: account %}
Then a for loop to calculate the balance (starting balance) within the loop which cycles through the accounts. This maybe replaceable by a “balance:” filter in future versions of report transformations.
Ps
The above is all pseudo code. I have not tested it as I don’t believe I need the functionality and I’m not sure how this fits in to country specific localisation.
BusinessStartDate Needs to be set to the Business Start Date with a default of the start of the numbering system.
Thanks… Does anyone know how to list transaction type, ie “Sales Invoice”, “Purchase Invoice” etc etc?
I can only get reference number by enumerating {{ e.Transaction.Reference }}. I need to show “Purchase Invoice - 201” like in GLT.
I started working on a basic report.
I put the starting balances temporary in bold to see if they show up, but when I insert “group_by” all the bold part disappear. If I don’t group them I can see all transaction from starting period till the “from” date, so I think it is an issue of group_by.
Here is my current Report Transformation.
{% assign openingBalance = transactions | between: BusinessStartDate, report.From | group_by: "Account" %}
{% assign myTransactions = transactions | between: report.From, report.To %}
Secondly, does anyone know how to filter only Balace Sheet or P&L accounts like in custom reports?
The group by filter converts a array of transactions to an array of arrays of transactions, ie a group of arrays of transactions.
To display the results you have to cycle through both levels. The outer loop cycling through the group, the inner loop cycling through the transactions. For example
Unfortunately last time I tried after you use the group by filter, then the balance: and drill down no longer work, so have to be all calculated manually.
edit
group_by: GeneralLedgerAccount.Name is probably closer to valid code.
{% for account in openingBalance %}
{% for f in account %}
{{ f.Date }} {f.Amount }}
{% endfor %}
{% endfor %}
Thanks @Patch . I’m still not able to get it working.
{{ transactions | first | fields }}</br>
{% assign openingBalance = transactions | between: BusinessStartDate, report.From | group_by: GeneralLedgerAccount.Name %}
{% assign myTransactions = transactions | between: report.From, report.To %}
By the way I don’t need to drill down the lines of each account, I need only, for opening balance, the account name and the amount, ie the BS of the business.
It seems that Report Transformation is still not documented and so hard to implement. Does anyone know how to subtract a day to “report.From” date?
You shouldn’t do any coding inside the layout.
Your script should go into the script field and you should only use your final output in the layout.
Generally speaking, If you use a filtered array in the script e.g.
{% assign A = transactions | between: x, y %}
You can refer to {{ A }} in layout section after defining it in script. It will be a total linked to transactions.
Also, when you use a loop to calculate a custom total in the script e.g.
{% assign B = 0 %}
{% for i in transactions | between: x, y %}
{% if i.IsTaxTransaction %}
{% assign B = B | plus: i.Amount %}
{% endif %}
{% endfor %}
Then you can refer to {{ B }} in the layout and ir will be an unlinked number.
However, if you enumerate through an array using a loop, then you should have it formatted in HTML in the script and put nothing in the layout. As far as I know, you cannot enumerate an array in the layout section of the report transformations.
So as far as your code goes, I would put the whole thing in the script section and nothing in layout.
You will need to define BusinessStartDate
To get it working temporarily delete the group_by
Agree but note “A” is an array of transactions not a number total amount.
Agree
To leave it as an array use concat
You can. Just put the loop code in the first cell of the loop start and loop end lines.
You are correct hyperlink and balance filters are currently fragile but designed to work in the layout section. By fragile l mean after using concat or group_by then balance or hyperlink (drill down) no longer work.
I find the layout section simplified data presentation so like it.
That’s interesting, I need to try this out
My preference is to have a sub forum, perhaps not shown by default. Users could show it if so inclined.
That way most users would be hidden from it but all the information would be readily accessible for those who want to customise Manager
How about a github repository with documentation? Seems more natural to have it in a code review environment which also can hold longer documentation texts?
Who knows something about this?
I tried using where filter but it seems to do nothing
{% assign invTrx = transactions | where: "SalesInvoice" %}
But an if statement works fine
{% for i in transactions %}
{% if i.SalesInvoice %}
{{ i.Transaction.Reference }}{{i.Date}}{{i.Amount}}
{% endif %}
{% endfor %}
But I don’t think this is fool proof, because receipts and payments and journal entries could also refer to invoices in the lines.
There should be another property like {{i.Transaction.Something}} that we can use as a direct filter.
@lubos, is there a comand to query what’s under “Transaction” apart from “Reference”?