I would like to create a custom report to show income by currency for a specified period. Thanks to some help from @lubos in a similar topic of mine, I feel like I am pretty close:
Amounts are tax inclusive, whereas amounts in the built-in Profit and Loss Statement report that I’m cross-checking against are tax exclusive.
Some background: We are allowed to trade in ZWL and USD. Profit and loss are calculated in our base currency (ZWL), but taxes are to be paid in both currencies according to the proportion of income generated from each. So, I have the built-in Profit and Loss Statement report that I can use for the assessment of profit and loss, but this doesn’t show the currency break-down, which is where this custom report comes in.
You can see in my report definition above I have included both AccountAmount and Amount. This allows me to see the amount in the currency of the transaction, and also the base currency equivalent amount.
So, attempting to solve problem number 1 above I tried:
removing SalesInvoiceAsTransaction and replacing it with Payment or Receiptis empty (after seeing payments and receipts appearing). This then means that credit notes and journal entries are included. But it’s still not a complete picture of income, and the totals don’t match the sum of Inventory - sales and Sales from the built-in Profit and Loss Statement report.
using Where …General Ledger AccountIsProfitAndLossAccountis checked and multiple exclusions to try to get rid of anything that isn’t income.
But in the resulting report, the SalesTaxAmount, Tax Amount, and TaxComponent columns are empty, and the TaxExclusiveSalesAmount column shows the total tax-inclusive amount for the transactions (with a minus sign in front).
So, I’m very confused, and would welcome any guidance towards creating the report I want, if it’s possible.
Have you tried going back to the design of your chart of accounts and creating different income accounts for ZWL and USD sales? Then you could design simpler custom reports that looked at transactions posted to the respective accounts.
@GrahamvdR if you append " (USD)" or " (ZWL)" to the respective Manager accounts then you could use the filter Where General Ledge Account Name contains " (USD)"
By the way, the complexity of your “Where” search is probably related to all search “Where” terms must be met (search term 1 “and” search term 2 “and” …). Manager does not support search conditions 1 “or” search conditions 2
The work around is to put a common but unique string in the account names as described above. If you also want another group of accounts, adding a punctuation character is useful such as “.”
Thanks @Tut. That sounds like a good plan, and seems obvious now that you mention it. I think it could help me in other ways too, and might even mean I don’t need custom reports at all.
Yes, I had hoped to do an “or” filter to include just the accounts I wanted (sales invoices or credit notes or journal entries…), but because the lines behave as “and” criteria I had to rather specify all the accounts I didn’t want, as shown in my second screenshot.
Thanks, this hadn’t occurred to me.
I don’t understand this. Would you mind elaborating, please?
If I do this, how do I assign sales of inventory and non-inventory items to the right account? It looks like the account is linked directly to the item, so once I’ve chosen the item I can’t change the account when I’m creating a sales invoice. I know I can specify for each item if I want it to use a custom income account, but does that mean I have to have duplicates of all items? I have approximately 700 inventory items now, and this number will keep growing, so I don’t think having duplicates is a viable option. Also, I don’t know how that would work with purchasing and stock tracking.
To clarify: when customers make a purchase, they can choose which currency they want to use. They could even use a combination of the two. This means I have a ZWL and a USD customer in Manager for most of my real customers, and when they tell me how they’d like to pay I choose the appropriate one for the transaction.
This topic clarifies that custom income accounts for inventory items are defined for the items and are not selectable when creating a sales invoice. So I don’t see how I can use multiple custom income accounts efficiently in my scenario. I’d be happy to be proven wrong.
Punctuation characters are typically not use in account names.
Adding a punctuation character to a group of account names enables you to then find transactions a group of accounts, simulating the “or” operator. For example with the following account names:
Red
Orange.
Yellow;
Green.
Blue.
Indigo;
Violet;
Enables searching for transactions in accounts Orange or Green or Blue by Where General Ledge Account Name contains "."
And we can select transaction in accounts Yellow or Indigo or Violet by Where General Ledge Account Name contains ";"
