In the G/L Account table I created a field with a G/L code of Hyperion (our Group reporting tool). A Hyperion G/L code can be assigned to several regular G/L Accounts.
I would like to retreive a list with unique Hyperion G/L codes by using NL(). Per unique code I would like to sum the balances of a number of Navision G/L Accounts.
I tried NL with the option "AllUnique" but this doesn't seem to work for me (returns only one record). Can anyone help me with this?
Thanks in advance!!!
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Try this Cell1 =NL("Filter", "G/L Account", "No." Filterfield ( one you created ) , "Hyperion")
Cell2 NP("Join", Cell1,"|") give you all accountsd with Hyperion in array)
Cell3 NL("Rows", NP("Split",Cell2,"|") gives you one row per account Now you have the acct so you can do sum from GLentry
Van -
Jet Reports Historic Posts I think perhaps this is a little easier than that. If you want to replicate rows for unique fields values, you can use this formula (assuming the field you created is called "Hyperion"):
C3:=NL("Rows","G/L Account","Hyperion")
Then to do your sum of all the G/L Accounts with the replicated Hyperion code value, you could do this:
D3:=NL("Sum","G/L Account","Balance","Hyperion",C3)
Note: If you are new to Jet Reports, you have to click the Report button for the rows function to replicate rows.
Regards, -
Jet Reports Historic Posts Thank you both for your replies. Unfortunately I can't test it at this moment because our network is down tonight due to maintenance.
I will test both options tomorrow and post the results.
Thanks again!
Best regards,
Gerard -
Jet Reports Historic Posts I performed some tests with my report and the solution from Van is exactly what I needed.
Thank you very much!!!
Best regards,
Gerard
Please sign in to leave a comment.