Multiple records displayed in Crystal Reports 9

[wcp]

I'm currently trying to design a report for our estimating package at work. We're almost there, but I'm running into 1 major problem. Our estimating system is setup in 3 "tiers" to speak of. Tier/level 1 is the overall estimate. Each estimate contains components, and each component contains segments. I need to provide component level pricing for a client (no big deal), but I need to list certain aspects of each segment inside that component above where the component price is listed.

Our table would look like this:

Segment-ID Component-ID Desc: Segment-Number
1 1 Prod1A 1
2 1 Prod1B 2
3 2 Prod2A 1
4 2 Prod2B 2
5 3 Prod3A 1
6 3 Prod3B 2
F 1 1 Prod1C 3

This table represents 1 Estimate. What I can currently do is pull segment-ID 2, 4, & 6 using a select case/maximum statement based on component-id, or 1,3,5 if the max statement is based on segment-id. What I need to do is list the price based on the component, no big deal, already have that. Then, list "Prod1A" carriage return "Prod 1B" while still in the grouping for the first component. I do no want to include components which start with "F", so the "F 1" would need to be ignored. There is other information to pull besides description, but if someone can get me started on how to do this, I'm fairly certain I can figure out the rest.

Thanks in advance!!!

WCP

 

[synapsevampire]

Eliminating the "F" values:

Report->Edit Selection Formula->Record

left({table.sg,1) <> "F"

Now you won't have any "F"'s in the report at all, so there's nothing to code for.

I have to admit that the rest of your post was a bit confusing and will likely lead to more posts, so I'll try to simplify the process a bit.

Try posting technical information:

Crystal version
Database/connectivity used
Example data
Expected output

I don't get why you'd pulll seg_id 2,4,6, seg_num makes sense as those are all 2's.

Hope this helps.

-k

 

[wcp]

Thank you for the help on the "F"'s. Okay, here's a shot at the technical info:

Crystal Reports 9
ODBC Connectivity into a Progress Database
The following is actual sample data yanked out of our database, minus many fields as there are on average 30-40 fields/table:

Segment Table:

Segment-ID Component-ID Desc: Segment-Number
1 1 Prod1A 1
2 1 Prod1B 2
3 2 Prod2A 1
4 2 Prod2B 2
5 3 Prod3A 1
6 3 Prod3B 2
F 1 1 Prod1C 3


Component Table:

Component-ID Qty Price
1 1,000 $100.00
2 2,000 $150.00
3 3,000 $175.00

Obviously, the tables are more complex, but this is a basic idea.

The expected output would be:

[Customer Info]

Group Header 2a: "Prod1A" - description from Segment table
"Prod1B" - same as above
Group Header 2b: $100.00 (pulled from the component table)


Group Header 2a: "Prod2A" - description from Segment table
"Prod2B" - same as above
Group Header 2b: $150.00 (pulled from the component table)


Group Header 2a: "Prod3A" - description from Segment table
"Prod3B" - same as above
Group Header 2b: $175.00 (pulled from the component table)

[Rest of Report]

This is the expected output inside Crystal reports (at least with the way it's layed out. I can get it to give me one of the descriptions, but I don't know how to get it to give me both descriptions.

 

[lbass]

Is there a reason you are placing these fields in the group #2 headers? I'm assuming that group #1 is the overall estimate and group #2 is on component. I think you could just use the deatil section for the segment table info ({segment.desc}), and then place {component.price} in the group #2 footer.

It would probably help if you explained your report structure overall.

-LB

 

There are some occasional Progress experts that pop in, perhaps they will have additional insights.

How are the tables joined? Your original post stated only one table, simplifying your posts will prevent accurate answers, not expedite things.

As LB brought up, it seems that you're displaying data at some unknown group level, but want the detail data.

Join the segment table to the component table using an equal join.

Group by Segment.Component-ID and place the fields in the detail and you should get something akin to the above results.

-k

 

[wcp]

Thanks for the info everyone. Thanks to lbass's answer, it works. I appologize for taking so long to get back with you, but I was having a lot of issues connecting to tek-tips for some reason. But thanks for everything though!!!