Hi,
I have simple/stupid question. Can somebody please explain what the significance of the"Rows = X" (X = any value 1 through 10)? I noticed many of the grouping example reports use this function. How do I know which numeric value to use if I am creating a grouping report? For example if I am grouping my income statement by revenues and cogs, but revenues has 4 different accounts and cogs has 6 different accounts then what value should I use for X in my NL function Rows = X of each category?
I read the brief section in NAV help on it, but it wasn't much help.
Thanks
Date
Votes
1 comment
-
Jet Reports Historic Posts The =NL("Rows=X", function refers to the # of rows Excel will skip before returning the next result from that NL function. Allow me to explain futher…
In your example, it looks like you're wanting to run a report showing you a list of all of your "Revenues", and then within each "Revenue", list all the "COGS"? You can do this by grouping, but in order for your NL formulas to run properly, you need to group your "COGS" NL formula within your "Revenue" formula. The reason grouping is used is because you cannot use two =NL("Rows", funtions on the same row in Excel. Since you need to list your Revenue's in rows, and your Cogs in rows, your formula's need to be on different lines.
Assume you're first NL function is in cell C10.
=NL("Rows","Chart of Accounts","Revenue", and any filters, etc).
In cell D10 (directly right of the NL formula), place "=C10".
Then go down once cell to D11 and put in the formula "=D10",
and lastly, go down one more cell to D12, and put in the formula "=D11". (this basically stair-steps your result down and over so you can use your next "rows" formula)
Now, in cell E12, place your other NL formula pulling in your COGS. The trick here is to add the filter to your formula to only show you the COGS that are included in the Revenue from the previous NL function.
=NL("Rows","Chart of Accounts","COGS","Revenue",D12)
Ok, now we're almost done. This is where the "Rows=X" comes into play. On your first NL function, you need to add "=X". That "X" value needs to be the number of rows down from your first "NL rows" formula, to your next. Basically, tell it how many rows to include in your Group. In this case it is 3. You include the row you're on, then two more rows down is your other NL formula with "Rows" in it. So if you add the "Rows=3" to your first NL Formula, when you run your report, it will list all of your Revenues, then within each revenue, it will run your COGS. Give it a try on your report and let me know if this returns the results you're looking for. If you have any questions or problems, let me know.
I hope this all makes sense.
Scott
Hammond, Indiana
Please sign in to leave a comment.