I'm fairly new. I have a nonglobal dimension called Project which is entered with some Invoices & Journal Entries.
I need a report by Project showing the data from G/L Entry table.
E.G. ABC company invoice for $1000 on 3/1/08 was charged to account 33000 and Project C123.
I know how to list the items by account, but I don't know how to filter, sort & subtotal by Project.
Any help would be appreciated.
Date
Votes
7 comments
-
Jet Reports Historic Posts This is a really great question - you aren't the first one to have this issue. And as crazy as it is, you can filter the G/L Entry table on your advanced dimensions even though they are not actual fields within the table. You can simply type in the name of the advanced dimension as the FilterField in your NL function. For instance, if you wanted to list all entries posted to a Project for a particular date and account, your formula would look something like this:
=NL("Rows","G/L Entry",,"Posting Date","3/1/08","G/L Account","33000","Project","C123")
Likewise, you can list out Project values as if it were a field from within the table like this:
=NL("Rows","G/L Entry","Project","Posting Date","3/1/08","G/L Account","33000")
I have also attached an example I created to help illustrate. I hope this is what you are looking for! -
Jet Reports Historic Posts New to me, very nice.
However, you're fairly free in the dimension names, so there could be a conflict of field Names I suspect…
Other than that, great functionality! Is it only the GL function that profits from this? -
Jet Reports Historic Posts Hi Jan,
In case there is a conflict between the name of a NAV field and the name of your Dimension, the NAV field has the highest priority. I think the Jet Reports PDF document has an example. Anyway, renaming the Dimension is all you need to do.
The GL does NOT have the option of Advanced dimensions - GL uses either both Global dimensions if you don't enter the View argument, or the up to 4 Dimensions if you enter the View argument.
The NL function is the function that has the Advanced dimension option - as you may see in the example The Kloser gave us. Please be aware that using the Advanced dimension affects the performance of your report/server considerably!
Advaced dimensions can be used for any standard document and ledger entry NAV table that has dimensions. There is an issue with a Budget ledger entry somewehre - I'm afraid I don't have the link right here.
If you have customized tables… if the dimensions linked to them have the same structure as standard NAV, then Advanced dimensions will recognize that too :) -
Jet Reports Historic Posts Thanks. I didn't know that you could just key in the Dimension Code without it being available in the Lookup list.
A lot of the other data still confused me, but I was able to use the important part.
One more step forward.
Thanks again! -
Jet Reports Historic Posts Moving forward. Now I need to group & subtotal.
First by G/L Account No, then by Project (advanced dimension).
All the data is from the G/L Entry table (plus Names from other tables).
I can't seem to get a good value in the Project field. -
Jet Reports Historic Posts Can you specify "not getting a good value"?
-
Jet Reports Historic Posts I've either gotten a blank in the Project field or a VALUE error.
Please sign in to leave a comment.