Hi all,
I am trying to create a report, that will show the sum, for each combination of Bus. Posting Group & Prod. Posting Group, for the active G/L accounts in a period (for reconciliation purposes). The way I have done this is by:
1. Through NL(Rows..) I extract all the G/L accounts with movement within the specied daterange. This works just fine.
2. NF(..) to get the wanted titles afterwards.
In my first column related to the issue, I have used this formula in I6 : "=NL("Rows";"G/L entry";"Bus. Posting Group";"Posting Date";datefilter;"G/L account no";H6;"Prod. Posting Group";"<>@@")"
datefilter = my set Date filter, and H6 = the NF function to get my account number. This part of the code works really well (any optimization is welcome).
Next though, in J6, I have used the following : "=NL("Rows";"G/L entry";"Prod. Posting Group";"Posting Date";datefilter;"G/L account no.";H6;"Bus. Posting Group";I6)"
This code works almost precisely how I want it to. The issue arises when we have done direct posting to the G/L account, and there aren't any Bus.- or Prod. Posting Groups applied to the account. Cell I6 will return blank (of course), which is ok. But the function in J6 will give #VAlUE, and the error message "Empty filters not allowed". G/L accounts where posting groups are applied, the code works perfectly.
Does anyone have a smart way of handling this? Usually I would simply use an IF function to test for empty cells, but the ROWS and COLUMN Functions don't work inside IF functions to my knowledge. Can i in the J(Fx) assistant 'test' for blank values instead?
I hope this makes sense. There are other factors in this report that are dependant on this issue - otherwise it works just fine.
/Kenneth
Date
Votes
4 comments
-
Jet Reports Historic Posts You can put IF statements inside the formula if that helps?
"=NL("Rows";"G/L entry";"Prod. Posting Group";"Posting Date";datefilter;"G/L account no.";H6;"Bus. Posting Group";"=IF(I6="""",""<>@@"",I6)")"
I couldn't quite tell from you example, but you're not really supposed to have multiple G/L rows on the same row, the behaviour can get a bit weird. That's what the grouping reports with Rows=3 or whatever are usually used for. -
Jet Reports Historic Posts Actually, I have not tried putting the IF statement inside the repeater - that might help. I will give that a go.
Yes I know. Repeaters should as a rule of thumb have their own rows and colums. But there are instances where it works allright. I have by accident broken a couple of reports by putting rows in rows :P .. but this works 100% as intended. Except for the issue with the empty filter.
Thanks for the idea. I will give that a go.
/Kemad -
Jet Reports Historic Posts I have been toying a bit with the IF function within the ROWS instead now. And it dawned on me, I may have to clarify something.
;"Bus. Posting Group";"=IF(I6="""",""<>@@"",I6)")" - is not working. Quite logically so if I think about it.
In my example, if I have 2 bus. posting groups, and 2 prod. posting groups, and they are all used, the code will automatically put
A B C
DK : 25 : NL(SUM..)
DK : 0 : NL(SUM..)
EU : 25 : NL(SUM..)
EU : 0 : NL(SUM..)
Both Column A and B are NL(ROWS) as mentioned earlier, but column B uses A as a filter. This is how I get the full combination of used Bus- and Prod. Posting Groups in the period. But if neither Bus. Posting Groups are used, Column B will give an error. The IF function placed like suggested will, regardless of the result in the IF function, enable a filter for Bus. Posting Group - and return an Error. You cannot have a Prod. Posting Group without Bus. Posting Group, so systematically doing a filter for "" will always fail. Ideally I would like an IF function before the Rows, but JET doesn't allow that.
I have been considering a different route instead. I have tried typing my second ROWS function, column B, as follows:
=NL("Rows";"G/L entry";IF(I6="";"";"Prod. Posting Group");"Posting Date";datefilter;"G/L account no.";H6;IF(I6="";"";"Bus. Posting Group");IF(I6="";"";I6))
These triple IF Formulas almost solve it. It tests my function and return "" as the field - so it produces a key instead. It also doesn't give error on the filter (as they are both "" after the test). My next step is trying to return a Text string instead. I thought I could do IF(I6="";{"test"};"Prod. Posting Group") , but it still registers the Test as a field instead of text string.
My compromise is, as I can't do the IF function AHEAD of the repeater, I can't in effect ignore the cell completely unfortunately. But if I can get a text result instead, I can use the Hide+? function to hide all rows with that Value, which will work almost as effectively.
This report is killing me. In effect it works - this is simply a question of how to make it better or neater to create the perfect posting reconciliation report :) The same will be used for VAT. -
Jet Reports Historic Posts Not meant to spam. But quick addendum.
I actually got a work around for this myself now. I wrote the Bus. Posting Group code as :
=NL("Rows";"G/L entry";IF(I6="";"Bus. Posting Group";"Prod. Posting Group");"Posting Date";datefilter;"G/L account no.";H6;IF(I6="";"";"Prod. Posting Group");IF(I6="";"";I6))
Basically - if I6 is empty, call the same criteria as in I6 (bus. posting group), and in the filter use IF in both filterfield and filtervalue to ignore the last value, if I6 = "" also.
It is by far not the prettiest code - so optimization is welcome! Can also upload a preliminary report if anyone is interested in it :) Just take note, my original report is in Danish, os the code is translated for assistance purposes on this board.
Please sign in to leave a comment.