Hi
I want to be able to group a report by shortcut dimension values in NAV, but only for certain customers. Other customers, must simply be presented, with no grouping, even if there were dimension values on the transactions.
The report should recognize, based on an IF statement, whether the customer number meets the criteria and give values from the Dimension value table:
IF(Customer No.= XXXX, NL("Rows=4","Dimension Value","Code","Dimension Code","REGION"),""). However, I know there are two values in the table, but the result of this that it returns always only the first value of the two in the table…. why is that?
The other issue is that the customers who meet the criteria also have entries with blank dimension values, and the result should also assist in returning the 'blank' dimension transactions to the report.
Any help would be appreciated.
Date
Votes
3 comments
-
Jet Reports Historic Posts So the reason you only get the first customer number in this case is because you cannot embed a replicator function like NL(Rows) inside another Excel function. NL(Rows) must be by itself in a cell. I think you could do this much easier like this following.
First we want to get all the customers grouped by region who meet our criteria like this:
C3: =NL("Rows=4","Customer","REGION","No.",XXXX)
D4: =NL("Rows=3","Customer",,"REGION",C3)
Then you want to get the other customers who do not meet the criteria and who are not grouped by region. There are a couple ways to do this. The easiest and quickest is to just create a second filtering criteria which is the opposite of the original criteria. So if XXXX was "10000|50000|75000" then YYYY would be "<>10000&<>50000&<>75000".
C8: =NL("Rows=3","Customer",,"No.",YYYY)
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hugh
Could he get away with this ?
C2 NL ( Filter for value he wants to group on bringing back No.
C3: =NL(IF Customer =c2, (NL(""Rows=4"",""Customer"",""REGION"",""No."",XXXX),(NL(""Rows=3"",""Customer"","""No."" XXXXX) -
Jet Reports Historic Posts No, when Jet Reports is searching for replicators in a report, it looks specifically for =NL("Rows" and the equivalent translations. So putting an Excel IF function or anything else in the first argument of the NL function will prevent the formula from being replicated. You can do something like this:
=NL("Rows",IF([Some Criteria],"Customer",{""}),"No.","Name","A*")
This would only replicate if [Some Criteria] is true. If it's false, then an array with a single empty string becomes the table argument of the NL function, which is basically equivalent to not replicating (technically it is replicated but it is only 1 value so nothing is expanded). However, =NL("Rows" must be the first part of the function or it will never be replicated.
Regards,
Hughes
Please sign in to leave a comment.