I am using the NL("Columns"…) function for 18 colmuns successfully, but want to display the columns in descending order (most recent on the left, least recent on the right).
I know how to sort rows, but columns? The boss wants it this way…
TIA! -John
Date
Votes
7 comments
-
Jet Reports Historic Posts Official comment Yes, and it worked but with a kink. I am obtaining the columns using:
=NL("Columns","Date","Period End","Period Type","Month",Period)
and it works great, but sorts ascending (Feb, Mar, Apr, etc.). I then tried your suggection:
=NL("Columns","Date","Period Start","Period Type","Month","Period Start",Period,"-Posting Date","*")
but got a "$VALUE!" and when I ran it anyway, it returned "Invalid Field 'Posting Date'. "
BUT, I edited the field to "-3 Posting Date" and it worked! (I tried without the "3" but it failed)
As a side note, I started this report from a "Report Player" sample, and it calls the Posting Date field "3 Posting Date". When I make a report from scratch, the field is just "Posting Date". Any idea why, and is it a pitfall?
John -
Jet Reports Historic Posts Hi John,
To sort an NL function (either rows or columns), you add a + (for ascending) or a - (for descending) in front of the filter field by which you want to sort. So for example, say you were replicating columns from the Item Ledger Entry table and wanted to sort descending by posting date. Your formula might look like this:=NL("Columns","Item Ledger Entry",,"-Posting Date","*")
Does this make sense?
Regards,
Hughes -
Jet Reports Historic Posts Hi John,
Posting Date isn't a field on the Date table. You have to actually sort by a valid field on the table you're using. By specifying the 3, you were telling Jet to sort by field 3 which on the Date table is the "Period End" field. I assume you are actually wanting to sort by the Period Start field although the results would be the same for Period Start or Period End. So you could change your formula to this:=NL("Columns","Date","Period Start","Period Type","Month","-Period Start",Period)
Putting the - in front of the Period Start field causes it to sort descending by that field. Does that make sense?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
Yes, it makes sense- I didn't notice the Table being used in the function was "Date" doh!
Thanks again!
-John -
Jet Reports Historic Posts Hi guys
Can anyone help me please. I have just started using Jet reports.
I have a list of products using NL "rows" and next to each item I have a field to indicate whether this product has been discontinued by our supplier.
Then in columns next to it i want to list our substitutes we have listed in navision, which I have done successfully usin NL"Columns"
However, the tricky thing is I need to put next to each item whether it is discontinued or not.
Some products have more than 1 replacement so it looks like this
Item A, Is Item A discontinued, Replacement A, Replacement B, Is replacement A discontinued, Is replacement B discontinued
Whe I want it to look like
Item A, Is Item A discontinued, Replacement A, Is replacement A discontinued,Replacement B, Is replacement B discontinued -
Jet Reports Historic Posts Hi JWHITE,
You should have started a new thread I believe.
If the Item is discontinued, I would have "blocked" it in the Item Table.
If that's the case, just pull in the "Blocked" field from the Item table into your report (I'd put it before the columns-not clear why you need columns in the report as you described).
HTH! -John -
Jet Reports Historic Posts Hi Jon
It is for tidying up our catalogue/website
Page No. Item No. Description Vendor Cost Disccontinued Substitutes
Theres are the headings
But I wanted a column after to say whether the substitute is discontinued. but when I have more that 1 substitute item it would list all subs like this
Page No. Item No. Description Vendor Cost Disccontinued Substitutes Substitutes Substitutes Disc Disc Disc
1 1 x x £1 yes 2 3 4 Yes yes no
Rather than having which I want
Subtitute Discontinued Substitute Discontinued Substitute Discontinued
2 Yes 3 Yes 4 No
Please sign in to leave a comment.