0

Using the results from rows replication used in another rows replicator

Bob Thorp

June 29, 2019 11:38

Answered

Follow

I’m working on a budget report and need to pull the active global dimensions 2 (Budgeting code) code values from two different tables (Version Line Detail & G/L Entry) to populate rows functions in my calculated rows. 

My original formula (K47 below) only brought in the “Budget code” from the budget module table Version Line Detail.

In K47 =NL("Rows","Version Line Detail","Budgeting Code","0",$D$13,"Date",$D$12,"Fund No.",$D$14,"Department Code",$I30,"Budgeting Code",$D$15,"G/L Account No.",$F30,"Agreement Code","*")

So I wrote these two formulas (used in K25-K38) which isolate the needed code values from the two tables but I can’t figure out how to rewrite the original formula to use the new budgeting code values in the rows function.

=NL("Rows","Version Line Detail","Budgeting Code","0",$D$13,"Date",$D$12,"Fund No.",$D$14,"Department Code",$D17,"Budgeting Code",$D$15,"G/L Account No.",$F25,"Agreement Code","*")

=NL("Rows","G/L Entry","Budgeting Code","0",$D$13,"Posting Date",$D$12,"Fund No.",$D$14,"Department Code",$D17,"Budgeting Code",$D$15,"G/L Account No.",$F26,"Agreement Code","*")

I tried TexJoin(“}”,,K25:26) (in E40 below) on the results of the two formulas below, but since the row replication happens prior to textjoin function, the report fails.  Is it possible to write a formula to use the rows results shown in k25-k38 in another rows replication function in row 47?

Run mode

Design mode

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Harry Lewis July 01, 2019 14:02

  Have you considered using an NL(Filter) function to gather & store the Budget codes and then referencing that from within your NL(Rows) function?

• 0
  
  Bob Thorp July 01, 2019 14:22

  Hi Harry,  I meant to go in and mark this as solved.  Yes, I moved the rows formulas and textjoin (rows 25-28 in the second screenshot to start in row 31 which is inside a rows formula in C30.  The report works fine now.  Just took me a while to get it right.  

  One question though.  My remote desktop session is on a 2012 R2 server which operates on Office Pro Plus 2016 but does not have the latest Excel functions (i.e. does not include textjoin).  How would you dynamically, create a filter for K25-K38 without using textjoin?

• 0
  
  Harry Lewis July 01, 2019 14:48

  Hi Bob -

  I would use the Jet Reports NP("Join") function to created a delimited list.

  Information about using that function can be found in the NP Function Reference and the Array Calculations article.

   

• 0
  
  Bob Thorp July 08, 2019 13:32

  Hi Harry,

  I read the article and attempted to use the NP(Join function, in this formula =NP("Join",E27:E29,"|") where rows formulas give the following in cells E27 & E28.  

  E27 = B3379CN, E28 = B0681CN, E29 is blank.

  The result for the NP Join function is B3379CN|B0681CN| with the | after the last code which causing the following formuals to return #VALUE!.  How can I enter the formula so that it renders properly?

   

  Edited by Bob Thorp July 08, 2019 13:33
• 0
  
  Harry Lewis July 15, 2019 17:48

  Hi Bob -

  If the issue is ONLY with the last character, you can simply use Excel's LEFT and LEN functions to remove the last pipe symbol.

  Otherwise, if you have EMBEDDED blanks in your string:

  

   

  You could use Excel SUBSTITUTE function to remove the extra pipes:

  =SUBSTITUTE(F31,"||",|")

   

  If, however, there can be multiple blanks (in a row) in your data (thus resulting in multiple consecutive pipes)...

  

   

  I would go about the entire process in a different manner.

  I would build the list as I go:

  

  So that, when I run the report, I get my list:

  

• 0
  
  Bob Thorp July 19, 2019 21:01

  Thanks Harry, I replaced the NP(Join with your formulas =IF(E31<>"",IF(G30="",E31,G30&"|"&E31),G30),  =OFFSET(G32,-1,0), which is working great.