0

call same function no of times

Jet Reports Historic Posts

April 08, 2009 11:27

None

Follow

Hi,
I want creating a report where for each items sold for a particular period, I want to get the RM Cost.
For one Item "X" needs to calculate bom lines items unit cost and then multiply with qty and then final total value is to be shown.
It can be better understood with the attached report.

• run same function.xls

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Jet Reports Historic Posts April 10, 2009 07:10

  Hi,
  Is there no one to attend ?

• 0
  
  Jet Reports Historic Posts April 13, 2009 16:00

  Firstly, on the left hand side, where you have X, Y, Z, AA, replace that with a NL Rows function
  
  C5 = nl("Rows",{"x","y","z","aa"})
  
  In D6, put the table calcs you have that build the table, that starts in I8 on your sheet. Put 'hide' at the begining of those rows.
  
  Lets presume that you have 1 heading row, 1 formula row, a blank then a sum() to get your total of 330 (4 in total) (all with hide)
  
  Back in C5, change it to :
  C5 = nl("Rows=4",{"x","y","z","aa"})
  C6 = your sum() formula.
  
  See attached to get you started. I think there is a better way, however when I was getting started, this is the tricks I used. Good luck

  • run same function.xls
• 0
  
  Jet Reports Historic Posts April 14, 2009 05:57

  Hi,
  Thanks for your support.
  
  I want to have the report without generating supporting calculations rows. The calculation rows should be like a function it should receive the input value and calculate the rows and give the final value to main nl() row. calculation rows function can be on other sheet or another excel file which can run and give output value to main nl(). I don't know but if it can do by using macros or not. Pl. try to get the report to work as this.

• 0
  
  Jet Reports Historic Posts April 14, 2009 13:02

  I prefer to keep my Jet Reports as simple as possible - I'll often use vlookups but I have not yet resorted to macros. Its a personal choice but find it keeps things consistent.
  
  We may be able to find a middle ground if you use this formula:
  
  =NL("Sum","Table","=NF(,""Field 1"")*NF(,""Field 2"")","Filter1","FilterValue1", etc)
  
  Where field1 would be Qty and field 2 would be cost.

• 0
  
  Jet Reports Historic Posts April 15, 2009 05:30

  Hi,
  
  Thanks for your support.
  By using the function given by you will result wrong values. Correct value will be as in cell E6, but the result will be given as cell D8.
  
  More over the qty for field1 is from bom line table and cost for field2 should be from item table (unit cost of bom line's item).
  
  Pl. recheck and give you support. Pl. correct me if Iam wrong any where.

  • sample.xls
• 0
  
  Jet Reports Historic Posts April 15, 2009 13:42

  In the case of the field being from seperate tables, I'm afraid my knowledge doesn't go any further than what I've shown you here. My advice is to use the method I illistrated first. Unless you have problems with the number of rows reaching Excel's limits, there's no real issue to using this technique that I see.
  
  If that doesn't work for you, use a seperate sheet and throw in some vlookups, however in cases where I have done this, it turns the report into a support nightmare, where making one small change to the report requires changes to all sheets, rather than just a couple of formulas.
  
  Some of the seasoned veterans on this forum may have other ideas for you. The official Jet Support team would likely be able to assist you if you still wish to persue further options for your report.

• 0
  
  Jet Reports Historic Posts April 17, 2009 07:14

  Hi,
  
  Thanks for your support.
  By using the data to retrieve in another sheet or the same sheet it grows up the data as my report period (for 1 year date) so I want option of using same function no. of times.