0

Work arounds for unsupported "Sheets=N"?

Jet Reports Historic Posts

February 07, 2008 17:40

None

Follow

HI

Any ideas for attaining the "Columns=N" concept in creating nested Sheets?

For Example: from an array(DIVISION1|DIVISION2) a Sheet is created, then based on that Sheet, and a new array(Cost Center01|Cost Center02), a new set of sub-Sheets are created. The result would be Sheets in the following order:

DIVISION1
Cost Center 01
Cost Center 05
DIVISION2
Cost Center 02
Cost Center 04

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts February 11, 2008 09:20

  Hi Stevek
  
  Like "Rows=N" and "Columns=N", "Sheets=N" is not supported in NL funcution of Jet Reports.
  
  Thanks and Regards
  Gowri shanker

• 0
  
  Jet Reports Historic Posts February 11, 2008 16:26

  Thanks for your response, but I know this - thus the "work around" in the subject header.
  
  I am a very impressed and a supportive promoter of JetReports in the user community. And this sheet control would crank up the product.
  
  Currently, I see that I need to create 23 different workbooks in order to create the nesting of divisions and cost centers. Instead, I just want one workbook that would do the same.
  
  Can a macro be called by JetReports? Maybe the macro would read a Jet populated worksheet and create the nested divisions and cost centers.

• 0
  
  Jet Reports Historic Posts February 11, 2008 18:20

  Hi SteveK,
  
  Although it's a complete solution yet, here's my 2 cents. I hope this will help you or others to find a solution together.
  
  If you could create an array in e.g. D4 that says DIV 1 - CC 1|DIV 1 - CC 5|DIV 2 - CC 2|DIV 2 - CC 4
  
  you could enter =NL("Sheets";NP("Split";$D$4;"|")) in another cell to generate sheets for you.
  
  Thus you would have a division - cost center sheet. The next step would be to break this string in a division and a cost center, but that's not the difficult part is it?
  
  Would that work for you? Then we'd only have to find out on how we can combine both values into 1 single array.

• 0
  
  Jet Reports Historic Posts February 11, 2008 18:54

  Thank you "hansfousert"
  
  If I setup an intermediate worksheet that would create the array as noted e.g. D4, then D4 would be a read for the sheet command. I think that I can make this work. All that I would have to worry about then, would be the character limitation within a cell. This too is manageable.
  
  AGAIN, THANK YOU

• 0
  
  Jet Reports Historic Posts February 11, 2008 20:41

  What you could do to generate the array…
  
  In D4: NL("Rows";TableX;DivisionField)
  In E4: NL("Rows";TableY;CostCenterField;DivisionField;D4)
  In F4: =D4&"-"&E4
  
  Then you will have an array of all possible combinations of Divisions and their Cost centers, won't you?
  
  I tried =NL("Sheets";F4:F5) but for some reason this does not work. Maybe the Jet team can shine a light on this?
  
  So close… but no cigar yet!

• 0
  
  Jet Reports Historic Posts February 12, 2008 14:39

  Hi,
  
  The following formula should work. Tested it on a Cronus DB so you'll have to change the field- and tablenames.
  
  =NL("Sheets";"Item";"=NF(;""Gen. Prod. Posting Group"")&"" - ""&NF(;""Inventory Posting Group"")")
  
  This will give you a sheet for every combination of GPPG and IPG present in the Item table.
  
  Good luck with it !!