Crystal Reports - Get value from linked table conditionally

Question

I'm new to Crystal and trying to figure out how to bring in a value from a table with multiple matches without creating duplicate rows.

I have a table of inventory items, and each inventory item has several matches in a costs table. For example inventory item 1234 may have a match for a labor cost, a material cost, and an overhead cost.

What I'd like to do is to have a single row for the inventory item, a column for it's labor cost, a column for it's material cost, etc.

So far the best I've been able to do is to create a formula field for the labor cost and use a conditional to show the cost if it's labor cost type id, but that just resulted in multiple rows where only one showed the cost and the rest showed zero.

What I would probably do in SQL (right or wrong) would be to create common table expressions for each type with the item id and the cost so each CTE would have only one cost value, then link those CTEs to the main query.

I know this is probably a really basic thing to do, but I couldn't seem to find any answers with Google. I'm happy to read over a resource if someone could just point me in the right direction.

Thanks in advance.

Answer 1

Add the Cost table several times to the report, once per each cost type. This will create aliases that you can name based on the intended type.

Do the join on item ID but also add a record selection criterion for each alias to restrict it to only records of the desired cost type.

There are a couple of other options but the solution above is the cleanest approach.

Answer 2

I ended up accomplishing this by creating sub reports for each of the values that I was wanting to see, linking them on the item id, and filtering each report on the cost type. If there's a better way I'd love to know.