Hi all,
Two more questions:
1. For a new report I should calculate the balance of all accounts which have the same consolidation account. For example I should have the sum of the balances for the cash accounts 10201, 10202, 10203, 10204 and 10205 (all with consolidation accounts 10200) in one cell. If I choose the General Ledger Entries, I cannot filter the consolidation account. What I want to have is somethin like this:
=NL("Summe";"GLEntry";"Amount";"Company=";C7;"Posting Date";F8;"Consolidation Account.";C5)
2. The report should take the balances of four different companies. If I create a GL or NL formula I just have the possibiliti to choose one company. Is it possible to select more than one company in one formula or did I have to do it like this:
=NL("Summe";"GLEntry";"Amount";"Company=";C7;"Posting Date";F8;"Account No.";C5)&NL("Summe";"GLEntry";"Amount";"Company=";C8;"Posting Date";F8;"Account No.";C5)
Thanks for your help!
Viki
Date
Votes
10 comments
-
Jet Reports Historic Posts Official comment Hi Viki,
Hmmm, I'm not exactly sure what is going on without looking at your database and working with it. I think you could actually probably get the data you want all from the G/L Account table though with something like this:
=NL("Sum";"G/L Account";"Net Change";"Company=";B3;"Date Filter";B4;"Consol. Debit Acc.";B6)
It's also possible that the field you want is the "Consol. Credit Acc." instead of the "Consol. Debit Acc." which would make the formula:
=NL("Sum";"G/L Account";"Net Change";"Company=";B3;"Date Filter";B4;"Consol. Credit Acc.";B6)
Do one of these work for you?
Regards,
Hughes -
Jet Reports Historic Posts 1. Why is 10200 the consolidation account for 10201 etc? I assume it is because you decided to use the first three digits of every account number. It is not available in the general ledger as well.
You can use excel functions to check only for the left 3 digits of the account number.NL("Summe";"GLEntry";"Amount";"Company=";C7;"Posting Date";F8;"=LEFT(NF(;""Account"");3)&""00""";C5)BTW the double quotes are mandatory!
2. Never tried it myself, but shouldn't standard Nav filtering work here as well?NL("Summe";"GLEntry";"Amount";"Company=";C7&"|"&C8;"Posting Date";F8;"Account No.";C5)Maybe it is not possible because of the way the companies are separated in Nav, but it is worth a try. If not, your way of adding every company apart is the right way to do it.
rmw -
Jet Reports Historic Posts Thanks for your help!
All bank accounts are consolidated into acc. 10200, all s-t receivables into 11100 etc. The consolidation account is a summary of group of similar accounts. Further the consolidation accounts are the same in all our companies. That's why I want select the consolidation account and not the GL Account.
Your formula (C4&"|"&C5) doesn't work. But I think this shouldn't be the problem. The biger problem for me is the matter with consolidation accounts!
Regards,
Bruno -
Jet Reports Historic Posts Hi Viki,
You do have to use a different NL or GL function for each company.
Are your consolidation accounts the same as the standard Nav Totaling accounts or are they something custom in your database?
This seems like maybe a silly thing to ask, but if the consolidation account consolidates all the other accounts, why can't you just get the balance of the consolidation account itself? Why do you need to go to all the other accounts that it is consolidating and sum them?
Regards,
Hughes -
Jet Reports Historic Posts I dont know if the consolidation account is a standard Navision field. If I open the card of one of my accounts, I've a register "Consolidation" in which I can set a debit and a credit consol. account.
Caus the consolidation accounts are the same in every of our companies (p.e. Bankaccount in company A is 10203, consolidated in 10200 / bankaccount in company B is 10201, consolidated in 10200), i want to select this account and not each single accounts. It's more easier to choose one consolidation account for all companies then select all single accounts of each company.
It's not a total account.
If' it's in the standard, you can find it in the schedule GLAccount –> Consol. debit account and Consol. credit account
Thanks for your help!
Viki -
Jet Reports Historic Posts Hi Viki,
It looks like the consolidation accounts are part of standard Nav. I went to the G/L Account Card as you suggested and used the Nav Zoom feature (Tools\Zoom) to find the "Consol. Debit Acc." and "Consol. Credit Acc." fields which are on the G/L Account table. Since this field is not on the G/L Entry table, you will have to use something like a Link= or an NL(Filter) to link from the G/L Entry table to the G/L Account table. Try something like this:=NL("Summe";"G/L Entry";"Amount";"Company=";C7;"Posting Date";F8;"Link=";"G/L Account";"No.";"=G/L Account No.";"Consol. Debit Acc.";C5)
I'm not sure if you need to use the "Consol. Debit Acc." or the "Consol. Credit Acc." field, so you might have to modify it slightly. Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Thanks for this!
I checked this formula in my Excel, but it dosen't work. Here is the formula I've entered into Excel (it's in German)
=NL("Summe";"Fibuposten";"Betrag";"Mandant=";B3;"Buchungsdatum";B4;"Link=";"Fibukonto";"Nr.";"=Fibukontonr.";"Konsol. Sollkonto";B6)
What's wrong? I get a blance of 0.00.
I don't understand the the part "Link=";"Fibukonto";"Nr.";"=Fibukontonr.";"Konsol. Sollkonto";B6. That's why I'm not sure what I've to change!
Thanks for your help!
Viki -
Jet Reports Historic Posts Hi Viki,
So sorry I don't speak German but hopefully I can still explain how the Link= works so that you can fix the formula. The formula you have is this:
=NL("Summe";"Fibuposten";"Betrag";"Mandant=";B3;"Buchungsdatum";B4;"Link=";"Fibukonto";"Nr.";"=Fibukontonr.";"Konsol. Sollkonto";B6)
So in this formula, we are getting the sum of the Betrag field from the Fibuposten table where Mandant and Buchengsdatum have certain values, which I'm sure you understand. Then as an extra filter, we're linking from the Fibuposten table to the Fibukonto table where the Nr. field on the Fibukonto table is equal to the Fibukontonr. field on the Fibuposten table and where the Konsol. Sollkonto field on the Fibukonto table is filtered by the value in B6. So as an example, say that the Fibuposten table has the following values:Fibukontonr. Betrag 1 10 2 15 3 20
And the Fibukonto table has the following values:Nr. Konsol. Sollkonto 1 100 2 120 3 200
And say the value in B6 was "<150". Then our formula would only return 25 (from 10 + 15) since the entry in the Fibukonto table for Nr. 3 has a value for Konsol. Sollkonto that is greater than 150.
So I'm not sure exactly how this formula is going wrong for you. Perhaps one of the filters is wrong or the field Fibukontonr. does not really exist on the Fibuposten table. Does this make sense?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
thanks for your explanation. I think I understand what the formula means. But I still get a wrong value (I get 0.00). I try to translate my formula into english. Probably you find then a mistake:
=NL("Sum";"G/L Entry";"Amount";"Company=";B3;"Posting Date";B4;"Link=";"G/L Account";"No.";"=G/L account No.";"Consol. Debit Acc.";B6)
B3 = One of our companies we have in Navision
B4 = 01.01.2009..31.12.2009
B6 = 10200
So I want to have the sum of all entries of the company mentioned in B3 with posting date 2009. This is the first part of the formula. Then I want to make a link, for that Jet only take the sum of all accounts, which have the consol debit acc. 10200 in the list G/L account. As I've mentioned above I get 0.00 with this formula which is wrong!
Regards,
Viki -
Jet Reports Historic Posts Hi Hughes
You're my hero :D !!!
Thanks for your help! It works with your formula! That's exactly what I want.
Regards,
Viki
Please sign in to leave a comment.