Solved

Placing values in an array to print at total or fiinal record time

Posted on 2009-05-12
66
249 Views
Last Modified: 2012-05-11
I woulld like to print a spreadsheet of values at the end of  processing.  I am reading the records from the inventory master table.  I select only certain items and want to print a spreadsheet of items that are in a predetermined order or format. All  I am printing are item, description and an inventory value. But I need them in a ceratin order which is not  based on sorting the Item id or desciption field. Can I load these values to an array and print upon reaching the final record.
0
Comment
Question by:fbhunt
  • 27
  • 19
  • 19
66 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 24364257
Are you doing the filtering of records in the database or are you doing it in code in the report?

mlmcc
0
 

Author Comment

by:fbhunt
ID: 24366236
I want to do it in the code of the report.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24367444
What is the order based on?

mlmcc
0
 

Author Comment

by:fbhunt
ID: 24368175
we have 4 different oil suppliers who have similiar products.  The purchasing agent has developed a spreadsheet to compare the products.  You cannot identify the order that they are on the report by code,id or description.  I was going th load them to an array and print the array at total time of the report or last record.  I would have an array for product ID, product description and product cost.
I.e
array id,1        array descr,1      array cost,1       array id,2      array descr,2   arrary cost,2
array id,3       arrary descr,3     array cost,3        array id,4      array descr,4   arrary cost,4
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24368702
The problem is, Crystal doesn't have an easy way to print an array.  The only way is convert the values to strings and concatenate them.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24370853
The problem seems to be getting the records in the correct order.  If you just load the fields into arrays and then output those, how is that any different from just outputting the records as you read them?  Do you have some way to sort the arrays into a different order?

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24370972
If they can be sorted then there is a way for Crystal to sort them.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24371283
That's what I was thinking.

 James
0
 

Author Comment

by:fbhunt
ID: 24372402
I would put the records in specific array locations and then print the array.  The products will not change in this report, so I could structure the array in the order I want the data to print.  I would make the company ID a group and then print the array at Group Fouter level.  Does this make sense and do you think it can be done.?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24375090
It can be done but the control over the output is poor.

If you can structure the array in the order you want (I assume you do this when you design the report) we might be able to use that array to produce an order.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24375197
Here is an idea

Add a formula to the report header
Global StringVar Array IdList := ['D','A','G','B'];   // Put the fields in the order you want the shown
''

Add a formula
Global StringVar Array IdList;
Local NumberVar i;

For i := 1 to UBound(IdList) do
    If {IdField} = IdList[i] then
         i;

use that formula for the firat sort

mlmcc
0
 

Author Comment

by:fbhunt
ID: 24375749
I am not sure what you mean.  If I put the fields in the array in the order I want them to print, I should not need to sort them.    For simplicity sake.  I  have 3 products as follows:
Item ID               Description                    Cost
IT01                  OIL PRODUCT 1             5.00
IIT02                  OIL PRODUCT 2            6.00
It03                   OIL PRODUCT 3            7.00
II want the spreadsheet to look like below
IT03      OIL Product  3       7.00        IT01   Oil Product 1     5.00       IT02 Oil Product 2     6.00

when i read the records I would put  IT03 in Array,1      IT01 in Array,2      IT02 in Array,3
then print the arrary at Group Footer which is company ID.  
Doew this make sense ?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24376267
Try this

Set the report to be multiple column
Display the section expert
Click the DETAIL section
Click the option for multiple columns
Click the new tab to set the columns
Set the width so you have 3 columns say 2"
Click the ACROSS THEN DOWN option

Add a formula to the report header
Global StringVar Array IdList := ['IT03','IT01,'IT02'];  
''

Add a formula
Global StringVar Array IdList;
Local NumberVar i;

For i := 1 to UBound(IdList) do
    If {ItemId} = IdList[i] then
         i;

filter the report to get only those 3 products

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24381030
mlmcc,

 You forgot to mention in that last post that he should be sorting by that second formula.


 fbhunt,

 > If I put the fields in the array in the order I want
 > them to print, I should not need to sort them.

 What mlmcc was suggesting (the same thing I was thinking) is not that you sort the array, but that you use the order that you would use in the array to sort the records on the report, instead of actually putting everything in arrays and printing the arrays.

 > when i read the records I would put  IT03 in Array,1
 >      IT01 in Array,2      IT02 in Array,3

 If you know that you want those items in that order, then you could, in very simple terms, just create a formula like the following and sort on that:

Select {Item ID}
 Case "IT03" :
  1
 Case "IT01" :
  2
 Case "IT02" :
  3
 Default :
  9


 That would sort the items in the order you wanted, without having to resort to storing every field for those items in arrays and trying to output those arrays at the end of a group or wherever.

 The "Default" entry at the end is just to put any unspecified items after the others.  Without that, the default would be 0 and you'd get any unspecified items first, which is also fine if that's what you want.  Of course if there aren't any unspecified items, then it doesn't matter.

 If you really want to use arrays, you have that option, but at this point I really see no need for that.

 One last note on arrays.  They're limited to 1000 elements, so if you're going to need to save more than 1000 values at once (more than 1000 items in a group or whatever), then you'll have to deal with that limitation.  Since you're planning on sorting these items manually, I've got to think that you don't have that many values to deal with, but I wanted to make sure you knew about this limitation, just in case.

 James
0
 

Author Comment

by:fbhunt
ID: 24545428
When I place the items in the array, how do I hold the values in the array until the end, to print at last record.?   I will have no more than about 50 items.
0
 

Author Comment

by:fbhunt
ID: 24549073
Has anyone seen my last post , about holding values in the array ?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24551910
To use an array you have to use the WhilePrintingRecords;

WhilePrintingRecords;
Global NumberVar Array IT01Cost;
Global NumberVar Array IT02Cost;
Global NumberVar Array IT03Cost;

Global SringVar Array IT01Description;
Global SringVar Array IT02Description;
Global SringVar Array IT03Description;

''

In the detail section to build the array

WhilePrintingRecords;
Global NumberVar Array IT01Cost;
Global NumberVar Array IT02Cost;
Global NumberVar Array IT03Cost;

Global SringVar Array IT01Description;
Global SringVar Array IT02Description;
Global SringVar Array IT03Description;

Local NumberVar Count;

If {ItemId} = 'IT01' then
(
    Count := UBound(IT01Cost) + 1;
    ReDim Preserve IT01Cost(Count);
    ReDim Preserve IT01Description(Count);
    IT01Cost[Count] := {CostField};
   IT01Description[Count] := {DescriptionField};
)
Else
   Similar code for IT02 and IT03


To display them you will have to convert the arrays to strings.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24552987
mlmcc,

 You forgot to mention that that first formula goes in the report header.

 Also, if you look back at his previous posts, he was talking about having one array for each field, not a separate set of arrays for each value in "Item ID".



 fbhunt,

 Using mlmcc's formulas as a starting point:

 Create a formula like the following and put it in the report header:

WhilePrintingRecords;
Global StringVar Array Item_ID;
Global StringVar Array Description;
Global NumberVar Array Cost;

Redim Item_ID [ 50 ];
Redim Description [ 50 ];
Redim Cost [ 50 ];

""


 Based on your statement that you'd have around 50 items or less, I started the arrays with 50 elements.

 Create a formula like the following and put it in the detail section:

WhilePrintingRecords;
Global StringVar Array Item_ID;
Global StringVar Array Description;
Global NumberVar Array Cost;

Local NumberVar i;

Select {table.Item ID}
 Case "IT01" :
  i := 2;
 Case "IT02" :
  i := 3;
 Case "IT03" :
  i := 1;

// We're specifying where each item will be put in the arrays.
// Fill in the value for i for the other "Item ID"s.

// The "Default" entry below is to handle any "Item ID" values you
// don't specify above.  The value (currently 99) should be something
// higher than the number of different "Item ID"s you expect to have.

// Note that if you have more than one undefined "Item ID" value, the
// arrays will end up with the values from the last one, since they'll
// all be dumped in that same slot (99).  If you expect to have more
// than one undefined "Item ID" and want to save them all, that can
// be done.  My understanding is that you plan to assign a position
// to every "Item ID", in which case there shouldn't be any undefined
// values, so I didn't bother to try to handle multiple undefined values.

 Default :
  i := 99;

if UBound (Item_ID) < i then
(
  Redim Preserve Item_ID [ i ];
  Redim Preserve Description [ i ];
  Redim Preserve Cost [ i ];
  "";   // CR doesn't like an if to end with a Redim
);

Item_ID [ i ] := {table.Item ID};
Cost [ i ] := {table.Cost};
Description [ i ] := {table.Description};

""


 That should save the fields in the arrays.

 The exact method for showing the values from those arrays on the report will depend on what you want the output to look like.  For testing purposes, just to confirm that the values have been stored in the arrays, you can create a simple formula like the following and put it in the report footer:

Item_ID [ 2 ] + "   " + CStr (Cost [ 2 ], "#.00") + "   " + Description [ 2 ]


 That will output the second element from each array, which should be the values for IT01.  Replace the 2 with another number to check other elements.


 FWIW, I still don't see why you can just sort the records based on the order you're using to put the items in the arrays.

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 24553004
Oops.  For my last formula, to display some of the values in the arrays, I forgot to include the array declarations.  Make that:

WhilePrintingRecords;
Global StringVar Array Item_ID;
Global StringVar Array Description;
Global NumberVar Array Cost;

Item_ID [ 2 ] + "   " + Description [ 2 ] + "   " + CStr (Cost [ 2 ], "#.00")


 In this version I also rearranged the fields and put Description before Cost.

 James
0
 

Author Comment

by:fbhunt
ID: 24554880
To answer your question about the sorting of the items.  There is no way to sort the items by id or description or for that matter any field within the item table.  We are comparing  products from multiple suppliers. Their ids and descriptions are a wide range. The end result is I want to put those items, side by side on a spreadsheet so that the purchasing agent can visually compare the items. I hope I explained the situation.
0
 
LVL 34

Expert Comment

by:James0628
ID: 24560877
But you are placing them in the order you want in the array by putting IT03 in the 1st position, IT01 in the 2nd, IT02 in the 3rd, etc.  Correct?

 If so, then you could use the exact same logic to sort the records on the report.  You'd create a formula like the following and sort the report on that formula:

Select {table.Item ID}
 Case "IT01" :
  2;
 Case "IT02" :
  3;
 Case "IT03" :
  1;

 etc., etc., etc.

 Default :
  99;


 Based on that formula, the report would show IT03, then IT01, then IT02.  If you want the report to show 3 items across the page, you can change it to a multi-column report.

 If I'm right and you know the order that you want the items to be in, then in addition to the formula idea above, there's also another way that you could handle that sort.  You could group the report on the "Item ID" field and use the "specified order" option for the group.  That would let you tell the report exactly how to sort the values.  It'd be a huge PITA, and I see absolutely no reason to use it here, but it is an option.

 James
0
 

Author Comment

by:fbhunt
ID: 24755316
I am attaching a spreadsheet to show the report as I want it to be.  the inventory items hardly ever change just the pricing.  The items are in the exact order they must be.   Can I run this in Crystal Standard Report and Print the Array of numbers at the end ?
CraftPricingComparison-Glenn.xls
0
 
LVL 34

Expert Comment

by:James0628
ID: 24763459
You have a very specific layout there.  For example, you have 4 sets of columns ("ExxonMobil", "*Shell", "Service Pro" and "Castrol") with a "Code", "Description" and "Cost" column in each set, and you start with 3 lines with specific codes in the 4 column sets, then a blank line, then 1 line of codes in the 4 column sets, then a blank line, then 3 lines with specific codes in column sets 1, 3 and 4, but nothing in column set 2 ("Shell"), and so on.  Reproducing that exact layout might be possible, but I think it would be a huge PITA.  In order to get the various blank lines and columns, I guess you're going to have to place every value in a specific array element based on the Code.

 Do you also need the yellow lines that you show in that spreadsheet?  I think trying to include those (assuming that it's possible) would complicate things even further (possibly quite a bit).

 FWIW, the layout in that spreadsheet is a lot more complicated than what was indicated by your previous messages (IOW, the problem appears to be a lot more complicated than I previously thought).

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 24763573
You know, it might be easier to create a report that simply lists the "Code" and "Cost", export that to a spreadsheet, and then have another spreadsheet with the layout you want that links to the spreadsheet exported from the report to get the Cost.  I've never done it, but I think Excel can do that.  If so, and if the additional steps involved (exporting the report and having the main spreadsheet get the values from the exported spreadsheet) aren't a problem, that would probably be a lot easier than trying to re-create that spreadsheet in CR.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24766362
I think the report could be built with either a cross tab or manually.  However the issue remains of how to relate a Shell product to  Mobil/Exxon to Castrol to Service Pro.  If you can resolve that issue then you can build the report.

You may need to add a column to the database for YourStockId which would relate the products.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24768949
> I think the report could be built with either a cross tab or manually.

 I'd like to see you do it.
 No.  Seriously.  I would.  I would be very impressed.  :-)

 The 4 sets of columns, with some column sets being blank on some lines, and the blank lines at irregular intervals (assuming that the blank lines are necessary) seem like real problems to me.  I can't think of any "reasonable" way to duplicate that layout.  All that I've been able to come up with is storing each column in an array, which might work, but probably wouldn't be easy (to say the least).  But maybe adding a YourStockId or whatever, like you're suggesting, would simplify things in ways that I'm not seeing.

 James
0
 

Author Comment

by:fbhunt
ID: 24771436
I am not worried about the blank or yellow lines , I just want to get then to the columns they belong.

Simply move the data to an array such as code to CODE , description to DESC and Cost to COST arrays.

Then print the arrays in the REPORT TOTAL SECTION
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24774340
Moving them to arrays, building a cross tab is relatively easy.  You need to tell me how to determine that
Exxon/Mobil 1234 is the same or competitive to Shell 4321, Catrol 7654 and Service Pro 9886.  If that requires manual intervention then it can't be done.  The cross reference between the products must be in the database.

A table like this could do it

ID      Mobil/Exxon ID       Shell ID      Castrol ID  Service Pro ID
1           1                              3               4                   7
2           3                               6              1                   6
3           6                                                2                   5


Or if you want groups add another column
Group    ID      Mobil/Exxon ID       Shell ID      Castrol ID  Service Pro ID
1            1           1                              3               4                   7
1           2            3                               6              1                   6
1           3            6                                                2                   5
2            4            2                              2                                    8

Without a table like that or changing the existing tables to have columns with the Group and ID this task can't be done.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24776684
 fbhunt,

 You've referred to a spreadsheet a number of times, in one way or another.  Are you planning on exporting this report to an Excel file?  I ask because, assuming that you can get the values in arrays, whether or not you plan to export the report to Excel may affect how you output those values.  In at least some cases, the simplest way to output an array as a column of values is to convert all of the values into one long, multi-line, string.  But that puts the whole column in one field, so if you export that to an Excel file, the whole column ends up in one cell in the Excel file.  If you're going to export the report to an Excel file, the values in the arrays really need to be output one at a time, which is doable, but generally more complicated.

 This will be a moot point if you can't get the values in the arrays to begin with, but assuming that you can, I wanted to go ahead and check on this detail, since it may affect how you get the values back out of the arrays.

 James
0
 

Author Comment

by:fbhunt
ID: 24785562
I am planning to export this to excel.  I guess I do not understand why I cannot have a 50 element array and if THe 1st  product I read from the table is the product I want in column 4 of the array then that is where I place it , THen if the 2nd product i read from the table is the 1st product on the array then I put that in position 1 of the array.  There is not a calculated way of comparing the products to determine there array position.  THey will always be in the predetermined position of the array. Those position will rarely if ever change.  IF they change I will modify the Crystal Report. I want to have a code,description and cost array. Load the array as  it reads the records and print those 3 arrays at the Report total.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24786445
Okay then you do have a way to get them related.

You could use an array for each company then print them with formulas

What does the table have for columns?

mlmcc
0
 

Author Comment

by:fbhunt
ID: 24786977
mimcc
you saw a copy of the excel spreadsheet I attached to my comments?  I have columns A thru L.
col a = code  * col b = Description * col c = Cost  col d= code etic...... to L
Column A - C is Exxon | Column D - F Shell |   Column G - J is Service Pro | Column J - L Castrol
COD,1= MO98HR03   DESC,1= Clean 5w20,COST,1 = Cost for Product
COD,2= SH5020100001   DESC,2= Formula 5W20 COST,2 = Cost for Product
(continue to put product in proper array position bas on excel layout)

Than print array at company change
How do I have the array values since they are formula fields retain their value till company change.?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:James0628
ID: 24790810
> I guess I do not understand why I cannot have a 50 element
 > array and if THe 1st  product I read from the table is the product
 > I want in column 4 of the array then that is where I place it , ...

 I think you can.  I don't know about mlmcc, but, personally, I was hoping that there was a better way to handle this than checking for every single code to determine which array element to use.  But I guess not.  I don't think it's really all that difficult, technically.  Just a lot of grunt work, as it were.

 I take it from your last comments that the report will be run for multiple companies and you want to produce this "table" for each company.  I assume that the report is grouped on the company?

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24790895
I saw the spreadsheet.  Where is the value that says which row the values go on?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24791021
I think it's all based on the Code.  MO98HR03 goes in the first set of columns (ExxonMobil) in line 1, SH5020100001 goes in the second set of columns (*Shell) in line 1, SP525S goes in the third set of columns (Service Pro) in line 2, and so on.  Glancing over the spreadsheet, all of the Code values appear to be unique, so I'm guessing that that's used to determine the position (line and column) of each item in the "table".

 Is that correct fbhunt?

 James
0
 

Author Comment

by:fbhunt
ID: 24792754
The value where the codes go would be determined within the Crystal Report . The code that is in column A, row 1 would always be the same code and would be in position 1 of the array(Code array).
The Description if column B, row 1 would be in position 1 of the Desc Array. The Cost in column C, row 1 would always be in position 1 of the Cost Array.  The code that is in column D, row 1 would always be the same code and would be in position 2 of the Code Array.
 
And so on.....................
0
 

Author Comment

by:fbhunt
ID: 24793250
Just an addendum to my last comment.  I vision a CR report with 12 values across each row just as in a spreadsheet.
Code,1  Descr,1  Cost,1   Code,2  Descr ,2  Cost,2  Code,3 Descr,3 Cost,3   Code,4  Descr,4 Cost,4
Code,5
Code,9
Code,13
and all the way to Code,49
I would then export this report into an Excel Spreadsheet.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24798671
As records are read from the database you can put them into the array as we showed you above.  The only issue would be where in the array.

What fields are in the table?
I assume you have something like

Company     ProductCode      ProductDescription      Price  

Is there a column for the array placement?

mlmcc
0
 

Author Comment

by:fbhunt
ID: 24802379
No there is not a array placement value in the table. I was going to test for that in the Crystal program.
Is that feasible?

How do I retain the values in the array until the last record is read.  I tested for a couple of codes and moved them into the proper array location but when I printed the array at group total (company change)nothing printed.
0
 

Author Comment

by:fbhunt
ID: 24809284
How do i retain the values in the array until the grouif footer total.  Using a formula field I would
New field is  ITEM[1]
if inv_mast_id = 'MO98HR03' then inv_mast_Id

New Field is DESC[1]
if inv_mast_id = 'MO98HR03 then inv_descr

New Field is COST[1}
if inv_mast_id = 'MO98HR03 then inv_cost

I would fill the arrays with the above logic but I tested for 1 item and printer the 1st position of all 3 arrays at group footer(company change)and nothing printed.   How many line can I print at group footer.
this would have to be 12 lines since i have 48 position in each array. 4 per row would have to print.



0
 
LVL 34

Expert Comment

by:James0628
ID: 24809705
If the values weren't there when you checked the array, my first guess would be that you were using global variables (the default type) and didn't declare/use them quite right.  Global variables are a bit "trickier" to use than the other types (local and shared), in that there are a couple of things you need to do, or you may not see the values you expect.  They are:

 1) Put a formula in the report header that declares the variables.  If you know the number of elements that you'll need in each array, you can use Redim here to set the size of each array.

 2) Use WhilePrintingRecords at the beginning of _every_ formula that uses those variables (including the formula mentioned above).


 Here's a little tip for item #1:
 If the formula is simply declaring the variables and the variables are arrays, then the last thing in the formula will probably be an array declaration/redimension, which CR does not allow (the result of a formula can not be an array).  To avoid that, you can just put "" (two quotes) at the end of the formula.  Then the result of the formula will just be an empty string, instead of an array, which avoids that error, and means that the formula won't produce any output, to speak of, on the report.


 Since you're using arrays, there is one other specific reason that you may not have seen the values that you put in the arrays.  You presumably used Redim at some point to specify how many elements were in each array.  Redim by itself will change the array size and erase its contents in the process, so if you put values in the arrays and then did a Redim, that would have wiped out the values.  Use Redim Preserve to resize an array and keep the current contents.


 You appear to be thinking of putting all of the codes (in all 4 columns) in one array, all of the descriptions in a second array, and all of the costs in a third array.  FWIW, it would probably simplify things a bit if you used a separate array for each column.  Something like ITEM1, ITEM2, ITEM3, ITEM4, DESC1, DESC2, etc.  It would simplify determining which spot in an array each value should be put in.  For example, if you determine that a record should go in the 3rd line in the 4th set of columns (and assuming that there are 12 lines per column), you could calculate the element ((3 * 12) + 3 = 39) in a single array, or just use the 3rd element in the 4th array (eg. ITEM4 [ 3 ]).  Personally, I think I'd prefer the separate arrays approach, but I don't see any compelling reason that you couldn't use one set of arrays as you described (and calculate the element number, as shown above).


 > How many line can I print at group footer.

 Basically, as many as you like, but you have to do it "manually".  By that I mean that CR has no mechanism to automatically produce multiple "lines" for a single group footer.  If you want to show your variables on 12 lines, you would probably have 12 group footer sections, with formulas in each one to show the corresponding array values.  For example, in the first group footer section, you'd have formulas to show ITEM [ 1 ], DESC [ 1 ] and COST [ 1 ], ITEM [ 13 ], DESC [ 13 ] and COST [ 13 ],  ITEM [ 25 ], DESC [ 25 ] and COST [ 25 ], and ITEM [ 37 ], DESC [ 37 ] and COST [ 37 ].  Then in the second group footer section, you'd have similar formulas for elements 2, 14, 26 and 38.  And so on.


 One last note:

 If you're using the arrays to get totals for a group, you will also need a formula somewhere (I'd put it in the group header section) that reset the arrays for the next group.  A Redim (without Preserve) will do that nicely.  You could probably use the same formula that you put in the report header, but you still need it in the report header too, so don't just move it from the report header to the group header.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24810885
The big thing is to use the WhilePRintingRecords line a stated above.

That phase in the report is the only pass that maintains values in variables outside the formula they are set.

mlmcc
0
 

Author Comment

by:fbhunt
ID: 24812633
Do i use this whileprintingreocords in the formula editot on a standard report ????

For instance
whileprintingrecords if inv_mast_id = 'MO98HR03" then inv_mast_id
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24814868
Yes.

It looks like you are using Crystal syntax in your formulas so that would be

whileprintingrecords;
if inv_mast_id = 'MO98HR03" then inv_mast_id

mlmcc
0
 

Author Comment

by:fbhunt
ID: 24819378
I tried defining the arrays as James stated.
I tried to setup a new formula called DEFINE (as follows)
whileprintingrecords;
Redim Item [50]    
(this gave me an error that a variable was expected and item was highlighted.

I also had a formula for the first item it was ITEM [1]
if {p21_view_inv_mast.item_id}= 'MO98HR03' Then {p21_view_inv_mast.item_id}

I placed the Item [1] in the group footer to test printing and I did not get anyhting to print.

0
 

Author Comment

by:fbhunt
ID: 24819494
I tried defining the arrays as James stated.
I tried to setup a new formula called DEFINE (as follows)
whileprintingrecords;
Redim Item [50]    
(this gave me an error that a variable was expected and item was highlighted.

I also had a formula for the first item it was ITEM [1]
if {p21_view_inv_mast.item_id}= 'MO98HR03' Then {p21_view_inv_mast.item_id}

I placed the Item [1] in the group footer to test printing and I did not get anyhting to print.

0
 

Author Comment

by:fbhunt
ID: 24819502
Oh by the way
I had a whileprintingrecords on the line above the item(1) formula
0
 
LVL 34

Expert Comment

by:James0628
ID: 24828491
You have to declare the array first, then use Redim.  Your error that a variable was expected was because you had not declared Item, so CR did not know what it was.

 Assuming that you're going to put string values in Item:

WhilePrintingRecords;
Global StringVar Array Item;
Redim Item [50];
""


 As I mentioned before, the "" at the end is partly so that the formula doesn't produce any visible output on the report, but also because you'll get an error if you have the Redim as the last thing, because Redim would produce the array as a result, and a formula can not produce an array result.


 > I also had a formula for the first item it was ITEM [1]
 > if {p21_view_inv_mast.item_id}= 'MO98HR03' Then {p21_view_inv_mast.item_id}
 >
 > I placed the Item [1] in the group footer to test printing and I did not get anyhting to print.

 I'm not sure what you're doing there.  That formula just says if item_id = MO98HR03, then show item_id.  It does not reference your Item array.  Did you maybe mean something like:

WhilePrintingRecords;
Global StringVar Array Item;

if {p21_view_inv_mast.item_id}= 'MO98HR03' Then
  Item [ 1 ] := {p21_view_inv_mast.item_id};


 That should put item_id in Item [ 1 ] when it's MO98HR03.

 Then you'd use something like the following to output that value:

WhilePrintingRecords;
Global StringVar Array Item;

Item [ 1 ]


 James
0
 

Author Comment

by:fbhunt
ID: 24828543
James is the declare and redim a formula(define as a new formula), that I put it in the report header section.
0
 

Author Comment

by:fbhunt
ID: 24828611
WhilePrintingRecords;
Global StringVar Array Item;

if {p21_view_inv_mast.item_id}= 'MO98HR03' Then
  Item [ 1 ] := {p21_view_inv_mast.item_id};

Note : I would do this statement for all 50 items.as above in the same formula

WIll I have to do this for every item as a different formula or can  they all be contained in on e formula called item?

Do I  do the whileprinting and global stringva array, once in a formula
0
 
LVL 34

Expert Comment

by:James0628
ID: 24828724
Yes, the formula with the Redim would be in the report header.  It declares the array _and_ sets the size of the array.  You don't need another Redim unless you want to change the size of the array (make it larger or smaller).  If you do another plain Redim, it will resize the array _and_ erase its contents.  If you want to change its size and preserve the current contents, use Redim Preserve instead.

 You need WhilePrintingRecords and the variable declaration (Global StringVar Array Item) in every formula that will use that array (either putting values into the array or getting them out of the array).

 You can put all of the values in all of the arrays in one formula.  Using your example, you can use something like the following:

WhilePrintingRecords;
Global StringVar Array Item;
Global StringVar Array Desc;
Global StringVar Array Cost;

if {p21_view_inv_mast.item_id} = 'MO98HR03' Then
(
  Item [ 1 ] := {p21_view_inv_mast.item_id};
  Desc [ 1 ] := {p21_view_inv_mast.desc field};
  Cost [ 1 ] := {p21_view_inv_mast.cost field}
)
else
if {p21_view_inv_mast.item_id} = 'second value' Then
(
  Item [ 2 ] := {p21_view_inv_mast.item_id};
  Desc [ 2 ] := {p21_view_inv_mast.desc field};
  Cost [ 2 ] := {p21_view_inv_mast.cost field}
)
else
if {p21_view_inv_mast.item_id} = 'third value' Then
(
  Item [ 3 ] := {p21_view_inv_mast.item_id};
  Desc [ 3 ] := {p21_view_inv_mast.desc field};
  Cost [ 3 ] := {p21_view_inv_mast.cost field}
)
else

 and so on, until

if {p21_view_inv_mast.item_id} = '48th value' Then
(
  Item [ 48 ] := {p21_view_inv_mast.item_id};
  Desc [ 48 ] := {p21_view_inv_mast.desc field};
  Cost [ 48 ] := {p21_view_inv_mast.cost field}
);



 You could simplify that by putting the subscript in a variable and setting the variable in your if's, then using the variable to fill the arrays.

WhilePrintingRecords;
Global StringVar Array Item;
Global StringVar Array Desc;
Global StringVar Array Cost;

Local NumberVar i;

if {p21_view_inv_mast.item_id} = 'MO98HR03' Then  i := 1
else
if {p21_view_inv_mast.item_id} = 'second value' Then  i := 2
else
if {p21_view_inv_mast.item_id} = 'third value' Then  i := 3
else

 and so on, until

if {p21_view_inv_mast.item_id} = '48th value' Then  i := 48;

Item [ i ] := {p21_view_inv_mast.item_id};
Desc [ i ] := {p21_view_inv_mast.desc field};
Cost [ i ] := {p21_view_inv_mast.cost field}



 Personally, when checking for a lot of different values, I use Select/Case instead of if/else.  Something like:

WhilePrintingRecords;
Global StringVar Array Item;
Global StringVar Array Desc;
Global StringVar Array Cost;

Local NumberVar i;

Select {p21_view_inv_mast.item_id}
 Case 'MO98HR03' :
  i := 1
 Case 'second value' :
  i := 2
 Case 'third value' :
  i := 3

 and so on, until

 Case '48th value' :
  i := 48;

Item [ i ] := {p21_view_inv_mast.item_id};
Desc [ i ] := {p21_view_inv_mast.desc field};
Cost [ i ] := {p21_view_inv_mast.cost field}



 You can use whichever form you prefer.  I don't know that it makes any difference to CR.  I suppose that there might be some kind of limit on the number of if/else's you can have (since they are nested), but I'm not aware of one and, if there is, I doubt that it would be a problem here.

 James
0
 

Author Comment

by:fbhunt
ID: 24828888
One final question I hope how do I set the formula to print at group footer (company id)

For instance I would want to print
item [1]  desc[1]  Cost [1]  item [2] desc [2]  cost [2]  item [3]  desc [3]   cost [3]   item [4]  desc [4]  cost [4]

and so on up to 50
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24829078
With the array being built, you would print it in the report footer rather than the company id footer.

To get the format you want requires 12 columns and a formula for each column

The formulas will be like

Name - ItemCol1
WhilePrintingRecords;
Global StringVar Array Item;
Local NumberVar Index;
Local StringVar OutputList;

Index := 1;

While Index <= UBound(Item) DO
(
    OutputList := OutputList & Item[Index] & chr(13);
    Index := Index + 4;
);
OutputList


Similarly for all 12 columns.

For the Item[2] column set index to 2
For the Item[3] column set index to 3
For the Item[4] column set index to 4

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24829096
You could have one formula combine all of those values (the first 4 elements from each array) into one string and output the whole line at once, but then they'd all be in one field on the report.  If you're exporting this to Excel, that one field would export to one cell in the Excel file, which is probably not what you want.  You presumably want each value in a separate cell, which means separate fields.

 The simplest thing is just to create a separate formula to output each element from each array.

WhilePrintingRecords;
Global StringVar Array Item;
Item [ 1 ]


WhilePrintingRecords;
Global StringVar Array Desc;
Desc [ 1 ]


WhilePrintingRecords;
Global StringVar Array Cost;
Cost [ 1 ]


WhilePrintingRecords;
Global StringVar Array Item;
Item [ 2 ]


WhilePrintingRecords;
Global StringVar Array Desc;
Desc [ 2 ]


WhilePrintingRecords;
Global StringVar Array Cost;
Cost [ 2 ]


 and so on.  If you've got 3 arrays with 50 elements in each array, that's 150 formulas.  Not pretty, and it won't be much fun :-), but it's simple.  Just a lot of "grunt work" to set up.

 Note that you'll presumably want to create a separate section for each line of values.  You could create one tall section and put all of the formulas in it in columns, but when you export to Excel, everything in the same section gets exported to one row.  If you put each line of values in a separate section, each line will export to a separate row in Excel.


 If you're doing this in a main report, as opposed to a subreport, then you could use a subreport to output the arrays.  You can't do that if the arrays are being built in a subreport, because a subreport can't contain a subreport.

 A subreport could read some table (basically anything with at least as many records as the number of lines you need to output your arrays) and then output one line of array values for each record, using the record number to determine which values to show.  If you're going to do this, you'll need to change the arrays from Global to Shared (ie. change the array declarations in _every_ formula).

 The formulas would be put in the detail section of the subreport and would look something like:

Shared StringVar Array Item;
Item [ ((RecordNumber - 1) * 4) + 1 ]


Shared StringVar Array Desc;
Desc [ ((RecordNumber - 1) * 4) + 1 ]


Shared StringVar Array Cost;
Cost [ ((RecordNumber - 1) * 4) + 1 ]


Shared StringVar Array Item;
Item [ ((RecordNumber - 1) * 4) + 2 ]


Shared StringVar Array Desc;
Desc [ ((RecordNumber - 1) * 4) + 2 ]


Shared StringVar Array Cost;
Cost [ ((RecordNumber - 1) * 4) + 2 ]


 Same thing for (+ 3) and  (+ 4).

 If you want to try the subreport idea and need any more details, let me know.

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 24829102
FYI, mlmcc's suggestion will output each column as a single field, which, again, will (I think) be exported to a single cell in Excel.  If you want each value in a separate cell, I think you'll need another approach, like one of the ones I suggested.

 James
0
 

Author Comment

by:fbhunt
ID: 24831710
This is now my item fornula and I put it in the detail section which I hide

whilePrintingRecords;
Global StringVar Array Item;
Global StringVar Array Desc;
Global NumberVar Array Cost;

Local NumberVar i;

Select {p21_view_inv_mast.item_id}
 Case 'MO98HR03' :
  i := 1
 Case 'SH502010001' :
  i := 2
 Case 'SP526S' :
  i := 3
 Case 'CA00456' :
  i := 4
 Case 'MO98HR05' :
  i := 5
 Case 'SH5020000001' :
  i := 6
 Case 'SP525S' :
  i := 7
 Case 'CA798700' :
  i := 8

Default :
  i := 48;

Item [ i ] := {p21_view_inv_mast.item_id};
Desc [ i ] := {p21_view_inv_mast.item_desc};
Cost [ i ] := {p21_view_inventory_supplier.cost}

0
 

Author Comment

by:fbhunt
ID: 24831723
this WhilePrintingRecords;
Global StringVar Array Item;
Item [ 1 ];

this is how I set my Printing Formulas ; Which I have in my report footers.  1 for each arrary element.
I tested the 1st 6 elements and nothing printed.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24832406
Try unhiding the formula in the detail section.

Need to make sure you are loading something.

mlmcc
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 24832412
You are correct James.  I didn't think about the final export to Excel.  The only way to get the Excel sheet to look right is to export each field.  I was concerned about concatenating them in a row since that could make aligning them very difficult.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24839350
Everything looks OK.  As mlmcc said, allow the formula in the detail section to show and see if the values show there.  Note that you'll only see the last value in the formula, which is cost in the formula you posted.  So, see if the cost shows in the details and, if you're not showing the Cost array at the end, add formulas for it.  Or rearrange the last few lines of the detail formula so that the array you want to check (Item, Desc or Cost) is the last one in the formula (ie. the one that will be output by the formula).

 James
0
 

Author Comment

by:fbhunt
ID: 24874689
I would like to thank mimcc and james0628 for a great job in helping me solve this array issue.  It took awhile and I ask alot of questions but both of them were very helpful.  Thanks again, great job
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 24875298
So, you were finally able to get the array values to show up?  Just out of curiosity, what was the problem?

 As for increasing the points (as mentioned in your close request), isn't there a button for that or something?  I've seen other people do it, so I'm guessing that there's something.  I don't post questions here on EE (I posted one, ages ago), so I'm not really familiar with that part of things.  :-)

 Well, OK.  Technically, I do post "questions".  I'm posting a few in this very message.  But you know what I mean.  :-)

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24891974
The option appears in a box just above or below the empty comment box.

mlmcc
0
 

Author Comment

by:fbhunt
ID: 24894722
Please increase value of solution.  Both experts work vey hard at reaching a solution
0
 

Author Comment

by:fbhunt
ID: 24894741
I need to make sure that mimcc and James receive 250 points for this solution. Than this solution can be closed
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now