Is it possible to have a ROWS command inside an IF statement? I'd like to give the users the option of choosing between two versions of the report using report options and then using different field filters in my ROWS command based on the option.
For example, in the report options, the user would select between Report Style A and Report Style B. I was hoping to have a ROWS statement set like this:
=IF(Options!C2="A",NL("Rows","Item",,FilterFieldA1,FilterA1,FilterFieldA2,FilterA2,etc),NL("Rows","Item",,FilterFieldB1,FilterB1,FilterFieldb2,FilterB2,etc))
The problem is that when I put the ROWS command inside the IF statement, I only get the first line of the results…the rows don't get replicated.
Any ideas? Thanks very much.
Date
Votes
4 comments
-
Jet Reports Historic Posts Try
=NL("Rows",IF(Options!C2="A",("Item",,FilterFieldA1,FilterA1,FilterFieldA2,FilterA2,etc),("Item",,FilterFieldB1,FilterB1,FilterFieldb2,FilterB2,etc)))
You may need to play a bit - especailly with "'s but this should get you where you want to go -
Jet Reports Historic Posts Hey Van,
Thanks for your reply. You were right…your suggestion got me very close. To make it work, I had to add the "Filter" command inside the IF statement, and then I used a separate NL("First") command to get the current Item No. record. Below is my revision to your suggestion:
=NL("Rows",IF(Options!C2="A",("Filter","Item","No.",FilterFieldA1,FilterA1,FilterFieldA2,FilterA2,etc),("Filter","Item","No.",FilterFieldB1,FilterB1,FilterFieldb2,FilterB2,etc)))
I'm not sure if I did this in the most straightforward manner, but it yielded the correct results. Thanks very much for your help!! -
Jet Reports Historic Posts I know you all have marked this as solved but I am having the exact same problem but can't seem to make it work
I am trying=IF(LEFT($G4,3)="PCM",NL("Rows","Purch. Cr. Memo Line",,"Document No.",$G4),NL("Rows","Purch. Inv. Line",,"Document No.",$G4))And getting one row
but i can't seem to get either=NL("Rows",IF(LEFT($G4,3)="PCM",("Filter","Purch. Cr. Memo Line",,"Document No.",$G4),("Filter","Purch. Inv. Line",,"Document No.",$G4)))orNL("Rows",IF(LEFT($G4,3)="PCM",("Purch. Cr. Memo Line",,"Document No.",$G4),("Purch. Inv. Line",,"Document No.",$G4)))to work. excel just throws a generic formula error and wont let me leave the cell. -
Jet Reports Historic Posts Those 2 formulas are not working because you don't have the NL in there for the NL function. So you need to change them to something like this:
=NL("Rows",IF(LEFT($G4,3)="PCM",NL("Filter","Purch. Cr. Memo Line",,"Document No.",$G4),NL("Filter","Purch. Inv. Line",,"Document No.",$G4)))NL("Rows",IF(LEFT($G4,3)="PCM",NL("Purch. Cr. Memo Line",,"Document No.",$G4),NL("Purch. Inv. Line",,"Document No.",$G4)))
Regards,
Hughes
Please sign in to leave a comment.