Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Complex reporting need.. I need help! Post Reply Post New Topic
Author Message
DBK412
Newbie
Newbie


Joined: 06 Nov 2011
Online Status: Offline
Posts: 6
Quote DBK412 Replybullet Topic: Complex reporting need.. I need help!
     Posted: 06 Nov 2011 at 1:32pm
I'm new to crystal reports and have recently moved from crystal 9 to crystal XI. I am developing metrics around containers that are shipped out of our company daily. They vary in sizes per the container profile id. This information is stored as a text string in a "LineItem" table that is linked to the shipping table. The table field I'm pulling from is {lineitem.adddescrip} and the line items look like this:
The profile id is followed by the quantity of containers shipped x container size.

X28342 1x30 2x15 2x55
H50108 1x5 1x30 1x10
H50108 1x10 1x20
X99293 10x5 5x55

I need to be able to show the quantity of containers per size like this for each shipment:

7x55
2x30
1x20
2x15
2x10
11x5

I've come up with the following formula but only feel like I've scratched the surface or possibly am going in the wrong direction.

right({LineItem.adddescrip}, len({LineItem.adddescrip}) - InStr (1, {LineItem.adddescrip}, " "));


Any help is greatly appreciated.

Edited by DBK412 - 06 Nov 2011 at 1:37pm
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 08 Nov 2011 at 3:45am
if the values are to be displayed in one text field and is basically just pivoting the one string at a time into a column, why not try:
local stringvar keeper := mid({table.field}, instr({table.field}, " ") + 1);
replace(keeper, " ", chrw(13) + chrw(10))
 
then set the text object to can grow.
 
it's thought
HTH
IP IP Logged
DBK412
Newbie
Newbie


Joined: 06 Nov 2011
Online Status: Offline
Posts: 6
Quote DBK412 Replybullet Posted: 08 Nov 2011 at 2:33pm
This is great! Definitely he right direction!

Is there any way I can add the different sizes? They are pretty standard... 55...30...20...15..10..and 5.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 09 Nov 2011 at 11:41am
don't really understand your request...what do you mean by the different sizes?
IP IP Logged
DBK412
Newbie
Newbie


Joined: 06 Nov 2011
Online Status: Offline
Posts: 6
Quote DBK412 Replybullet Posted: 10 Nov 2011 at 2:19am
Each line item is a container profile "X58495" and "quantity" x "container size" (ex. 2x30 = 2 30 gallon containers). Now that I'm able to isolate the quantitys x container sizes, I was wondering if it's possible to consolidate the lists automatically as shown below

X28342 1x30 2x15 2x55
H50108 1x5 1x30 1x10
H50108 1x10 1x20
X99293 10x5 5x55

I need to be able to show the quantity of containers per size like this for each shipment:

7x55
2x30
1x20
2x15
2x10
11x5

We ship standard size containers (55 gal, 30 gal, 20 gal, 15 gal, 10 gal, and 5 gal).

Edited by DBK412 - 10 Nov 2011 at 2:20am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 10 Nov 2011 at 3:07am
ok, now I understand....much harder to do.
 
2 approaches come to mind:
1) create a shared variable which is a delimited string which would keep track of both size and quantity like: |1x30|3x55|, then you would search for the xYY and get the quantity (actually |30x1|55x3| would probably be easier)
 
2) use arrays. you could have a reference array of the standard sizes (this could be skipped as long as it is documented somewhere), then you just need to increment the correct array index.
 
for either of these methods to work, you would need to parse your string (splitting would probably be nice as you already have the code to split into an array) and update whichever method you are using.
 
finally, you will need to take the results and display them in whatever fashion you desire.
 
more work, but doable.
 
HTH
IP IP Logged
DBK412
Newbie
Newbie


Joined: 06 Nov 2011
Online Status: Offline
Posts: 6
Quote DBK412 Replybullet Posted: 10 Nov 2011 at 3:18am
I was actually trying to draw out a code to create shared variables, but now that you mention the arrays method, i'm definitely already half way there!

Thanks for your help!
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum