Hello - I am building a basic summary list with a rows=4 nl function.
I built this so that it replicates a global dimension as the primary list and then the g/l account as the secondary list so that I can total expenses.
Something is wrong because the first block of replicated values is blank. After that, the report returns the values I was expecting. Just that first chunk is blank.
below is the primary list nl function.
=NL("Rows=4","G/L Entry","Global Dimension 3 Code","Award No.",$B$3,"Global Dimension 3 Code",$D$3)
My intention here is to replicate a list by global dimension 3 where I can select the award number and filter by the global dimension 3 in order to return only those dimensions associated with the costs I would like to total. This would then become the base for the secondary list that replicates the g/l account number and name for each primary list dimension.
Just confused as to why the first block of data in the report output would be blank.
I appreciate the help.
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hi,
You cannot filter by the same field twice, so you are correct that you need to combine the filters into one filter. with <> filters, you need to combine them with an & (AND) instead of an | (OR). Something like this should work:=NL("Rows=4","G/L Entry","Global Dimension 3 Code","Award No.","31672","Global Dimension 3 Code","<>''&<>"&$D$2&"L000")
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
I'm guessing the first block is blank because you have blank values for Global Dimension 3 Code in the G/L Entry table. That seems like the most obvious possibility. You could filter out blank values by prefixing your filter with <>''&. Maybe something like this:=NL("Rows=4","G/L Entry","Global Dimension 3 Code","Award No.",$B$3,"Global Dimension 3 Code","<>''"&$D$3)
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Thanks Hughes. I think this is a good start.
Now I'm encountering the issue where I need to filter the global dimension 3 value against two different criteria. Each of them seems to work individually but I need them to work together.
The first is:
"<>"&$D$2&"L000"
The second is the part you've added:
"<>' '"
So here is what my formula now looks like.
=NL("Rows=4","G/L Entry","Global Dimension 3 Code","Award No.","31672","Global Dimension 3 Code","<>"&$D$2&"L000","Global Dimension 3 Code","<>' '")
I believe I need to throw something together that changes the end of the NL from "Global Dimension 3 Code","<>"&$D$2&"L000","Global Dimension 3 Code","<>' '" to include a pipe.
Maybe something like "Global Dimension 3 Code","<>"&$D$2&"L000"|"<>' '"?
I know there is a particular way to handle special characters so I want to make sure I'm understanding how to handle these types of situations. -
Jet Reports Historic Posts Worked like a charm. Thanks Hughes.
Please sign in to leave a comment.