Hi,
I am making a report in which I want to create a lookup where I can choose
if the Customers are presented in rows or sheets (the lookup is already created and works fine).
The WHAT-Argument in the Wizard is a cell reference to a line were I have the information
rows=8 or sheets.
If i start the report there is no error, but only 1 Customer appears (there are more who fit the filters). When i write
manually that he should take rows or sheets everything works fine.
Here what i Have written:
=NL(Optionen!$D$12;"Debitor";"Nr.";"Nr.";NL("Union";NL("AllUnique";"Debitor";"Nr.";"Verkauf (MW)";">0";"Nr.";Optionen!$D$4);NL("AllUnique";"Debitor";"Nr.";"Nicht fakt. Lieferungen";">0";"Nr.";Optionen!$D$4));"Debitorenbuchungsgruppe";Optionen!$D$5)
Optionen!$D$12 contains:
=WENN($D$9="Ja";"Sheets";WENN($D$9="Nein";"rows=8";"sheets"))
Maybe someone can help (sorry for my bad english)
Date
Votes
10 comments
-
Jet Reports Historic Posts There isn't really a "good" way of doing this.
The best way methinks is an "If" formula.
Otherwise, if this is your formula:=NL($C$3;"Item";"No.")
In the adress bar, selecting $C$3 and pressing F9 will replace it with the value rather than the formula.
I'd go with an "If" if you where you… -
Jet Reports Historic Posts THX for the answer, I'll give it a try.
-
Jet Reports Historic Posts Hi Jan,
Did you try your solution, 'cause I can't get it to work. Maybe you could attach an example report?
Hi Goblin187,
It would be a nice feature to have in a report - let's put it on our wish list. I tried your report and it didn't work for me either (NAV 5, Excel 2007, Jet 7).
I made a work around though - it is in the attachment. Not a pretty one, but it works. Here's the concept:
- 1 sheet with an NL that generates Rows;
- 1 sheet with an NL that generates Sheets;
- on both sheets is an IF that result in HideSheet
- for the Rows sheet if Sheets is selected
- for the Sheets sheets if Rows is selected
Does that make sense so far?
The not so nice thing about it is that a generates both Rows and Sheets sheets, before hiding the one(s) you didn't select.
So, I'm very curious for a more advanced solution!
(p.s. Ihr English ist doch sehr gut, Goblin187! Wir haben alles verstanden und hoffen dass Sie jetzt weiter konnen! Schone Grusse aus Holland) -
Jet Reports Historic Posts I was thinking about the exact same thing, but I don't really like the "conditional hide" for the reason you specified (performance :/).
I tried the IF and it didn't work (returns the same thing: 1 row)… I'm quite suprised about that. -
Jet Reports Historic Posts Very nice idea, though.
Thanks for your effort. I'll try it but I guess it is not worth
the performance loss. With Cronus AG it's no problem but
with a far bigger database it could get difficult.
Hope they fix it in later versions.
In NAV Reports it is more or less a standard feature.
Very sad that it won't work the way i hoped it would. -
Jet Reports Historic Posts Hi Jet Reporters,
You can do this. You will still need 2 formulas, one for NL("Rows") and the other for NL("Sheets"), but you need to control when the NL("Rows") executes, and when the NL("Sheets") executes. For example, Let us say that cell E3 contains the word "Rows". Then in cell E7 I put=NL("Rows",IF($E$3="Rows","Item",{""}),"No.")and in cell F5 I put=NL("Sheets",IF($E$3="Sheets","Item",{"Report"}),"No.")Now, if cell E3 contains "Rows", I get rows on my report, and if it contains "Sheets", I get sheets.
Here is an example workbook
rowsVSsheetsExample.xlsx -
Jet Reports Historic Posts There's always another trick, isn't it? :) Cool! But… why does it work?
What I tried is
- IF (CellRef="Rows";NL("Rows"…);"NL("Sheets"…))
- NL (IF (CellRef="Rows";"Rows";"Sheets")…)
So, now I'm wondering why these functions are not executed as we expected them to. How does the Jet Engine interpret the NL functions?
And another question is about the { } brackets you included in the IF part of your function. Why do they have to be there? -
Jet Reports Historic Posts I was hoping I wouldn't have to explain, but if I do, I do.
First, { } is how we tell Jet Reports that we are working with an array of values. For Example;=NL("Rows",{"Now","is","the","time","for","all","good","men"})will give you rows with each word in a cell. We see this also used when using the NL("Lookup"). For Example=NL("Lookup",{"Yes","No"},"Select Yes or No")provides you with a lookup with 2 choices, Yes and No, which you can then select one of the values.
Now, why did we write the formulas the way we did=NL("Rows",IF($E$3="Rows","Item",{""}),"No.")First, we always need the "what" parameter in an NL() formula. In this case we want either a "Rows" or a "Sheets". However, we can't use an If function at this point as Jet Reports searches for the "whats" of all NL() functions first. So, we have to control if the "Rows" or "Sheets" function actually does anything.IF($E$3="Rows","Item",{""})says, if cell E3 equals "Rows", then use the Item table, otherwise, use an array of values where the only value in the array is nothing (or Null). This prevents the NL("Rows") from doing anything if E3 is not equal to "Rows".
Does this answer your question? -
Jet Reports Historic Posts Yes, that helps :)
So, the Jet Engine only works with a fixed What value only. It does not accept any functions like a cell reference or an IF. Nor can you use an NL Replicator function inside another function.
All other arguments in the NL function do accept functions and cell references.
I will surely add that to my documentation :)
Thank you Sherman. -
Jet Reports Historic Posts Very interesting post this one, thanks Sherman!
Regards,
Jan
Please sign in to leave a comment.