Jet 7.1.2 build 1830 Navision 4 sp3
Since a <> filter doesn't appear to work in jet ( you can't filter by whats not in the sort ) I decided to try filtering using an IF statement and the count function
=NL("Filter","Dimension Value","Code","DataSource=",$C$4,"Dimension Code","BRANCH","Code","IF(NL(""Count"",""Dimension Value Combination"",,""DataSource="",$C$4,""Dimension 2 Code"",""DIVISION"",""Dimension 2 Value Code"",$C$7,""Dimension 1 Code"",""BRANCH"",""Dimension 1 Value Code"", NF(""Code""))=0,NF(""Code"")","")
=NL("Rows","Dimension Value","Code","DataSource=",$C$4,"Dimension Code","BRANCH","Code",$E$5) brings back "VALUE" So sort isn't doing what I hoped it would
Am I trying to do the impossible ?
Date
Votes
3 comments
-
Jet Reports Historic Posts What you are pointing at is building a filter of "Dimension Value" Codes that dont have any combinations of a specified Dimension 2 Code/Value combination:
E5 = BRANCH E6 = DIVISION E7 = <Division Code> E8 = NP("Join",NP("Difference",NL("AllUnique","Dimension Value","Code","Dimension Code",$E$5),NL("AllUnique","Dimension Value Combination","Dimension 1 Value Code","Dimension 1 Code",$E$5,"Dimension 2 Code",$E$6,"Dimension 2 Value Code",$E$7)),"|") -
Jet Reports Historic Posts Now to make it real tricky what I really needed was a 3 dimemsion join
We use Division, Branch, Project
What I had to produce was a report that gave all the possible valid combinations
So a project may be valid for a division, the branch is valid for the division but the project is not valid for that branch
Oh and I have 17 divisions where branch numbers may be valid for one or all of them <S> and the projects can conceivably cross divisions as well fortunately Navision ( at least our setup ) is a global block by division and or branch or I'd lose what littel hair I have left
I've copied your suggestion out and will play with it . I've not had much luck trying to set up joins so this will help
Thanks -
Jet Reports Historic Posts Thanks again this definitely is a cleaner solution
The joins worked though it got a little tricky on the third one (to get valid project by branch) B13 is the Branch F6 is valid Projects by Division (see code below) E8 and E6 are Dimension types E& is a valid Division
F6 =NP("Join",NP("Difference",NL("AllUnique","Dimension Value","Code","Dimension Code",$E$8,"Company=",$D$3),NL("AllUnique","Dimension Value Combination","Dimension 2 Value Code","Dimension 1 Code",$E$6,"Dimension 2 Code",$E$8,"Dimension 1 Value Code",TEXT($E$7,"00"),"Company=",$D$3)),"|")
F13
=IF($F$6<>"",NP("Join",NP("Difference",NL("AllUnique","Dimension Value","Code","Dimension Code",$E$8,"Code",$F$6,"Company=",$D$3),NL("AllUnique","Dimension Value Combination","Dimension 2 Value Code","Dimension 1 Code",$E$5,"Dimension 2 Code",$E$8,"Dimension 1 Value Code",TEXT($B13,"000"),"Company=",$D$3)),"|"),"|")
G13
=NL("Rows",NP("Split",$F13,"|"))
Only issue I have is pulling "values" when there are no valid projects or branches for a division and the "@@"& cell creates an error when cell is expected to be an array . This may simply be a case of getting users to clean up the data as a division with no branches should probably not exist ( Hey I just report the Data <VBG>
Please sign in to leave a comment.