How to format this data in Crystal Reports?

[mgakhar]

Hi,
Im querying the Bill of Materials (BOM) table in my db which basically stores what component is used to make a product and how many of these components are needed to make this product. For e.g. To make 1 car I need 4 tires. However, a particular product can be used to make multiple products. For e.g. the same tire goes to make a Bike as well.

So my table contains data in the following format -
Main Item Component Qty
Car Tire 4
Car Doors 4
Bike Tire 2

This format is also the way my query returns the data. The where clause contains a range of 'Main Items'.

However, I need to display the data in a different format -

Component Car Bike
Tire 4 2
Doors 4 0

Any ideas on how I could achieve this in Crystal Reports if at all this can be done in CR?

Im using CR version 10.

Thanks in advance,
MG.

 

[rrgkanth]

Hi,

I am not sure about CR 10, because I have CR xi. But I am sure, there must be a similar method of doing this.

What you need is a cross tab.

Insert->Cross-tab and drop in your report header.

Right click and go to cross-tab expert.

Select Main Item for columns, Component for rows and Qty for Summarized fields.

Go to the customize style tab: Check supress row grand totals and check suppress column grand totals.

I think you would get what youre asking for.

~rrgkanth.

 

[vngpal]

You can do this way.

First create a fromula for each main item like @CarQty and
do as follows.
If {table.mainitem} ="Car" then
{table.Qty}
Similarly create fro each main item.

Create a group by component.
Place the formulas fields in the detail section and place the sbutotal in the group footer. Then hide the detail section.
You can get thedesired result.

vngpal

 

[mgakhar]

rrgkanth - thanks for your solution ... works great ... but I now realize that cross tabs is good when it comes to provide summary report.

However, I now want to expand this further. So basically after listing all the main items, I need to display some detail information regarding the components. For eg. Supplier, Cost Price for Tires and Doors.


so that my output looks something like this -

Component Car Bike Supplier Price
Tire 4 2 ABC 200
Doors 4 0 XYZ 1000


vngpal - I didnt understand your solution. The number of main items can be different everytime I run the report. In effect the number of main items is dynamic. So how do I know how many formula's to create?


thanks again for all your help !!

MG.

 

[lbass]

You could insert supplier and price as second and third row fields, leaving the main item as the column field, and count of item as the summary. If there is one supplier and price per component, then this should work fine, although the display will be component->supplier->price and then the breakout of main items.

-LB

 

[mgakhar]

LB,
It doesnt work ... for every new field that I add to the row, there is a corresponding total row also. And the format is all messed up.

MG.

 

[lbass]

You can go to the customize style tab within the crosstab expert and suppress the subtotals--highlight each row field and check "suppress subtotals". Please show a sample of the results after that so that we can see what you mean by "messed up". Is there more than one supplier and/or more than one price per component?

-LB

 

[rrgkanth]

MG,

How does ur actual table look? That might give a different solution.

The cross tab will not look the way you want it to.

 

[mgakhar]

RRG -
My output should look like this -

Component Car Cycle Bike Supplier Price
Tire 4 2 2 ABC 200
Doors 4 0 0 XYZ 1000
Engine 1 0 1 PPP 5000

However, the data that I get from my query looks like this -

Main Item Component Qty Supplier Price
Car Tire 4 ABC 200
Car Doors 4 XYZ 1000
Car Engine 1 PPP 5000
Bike Tire 2 ABC 200
Bike Engine 1 PPP 5000
Cycle Tire 2 ABC 200

Thanks,
Manish.

 

[lbass]

Component Supplier Price Car Cycle Bike
Tire ABC 200 4 2 2
Doors XYZ 1000 4 0 0
Engine PPP 5000 1 0 1

If you followed my suggestion, you would get the result above--if there is only one supplier and one price per component.

-LB

 

[mgakhar]

Hi LB,
You are right - I didnt read your post regarding suppressing the subtotals. Now it works great. One small problem though (Im pretty sure Im going to many of these small problem) - The new rows that I added for example the Supplier and Price columns, how do I create header Labels for these columns. Infact even for Component. I've not been able to figure out how to get the header labels for these columns.

thanks again,

MG.

 

[lbass]

I think the best you can do is one of two things:

1-Add a text box that contains the three headings. However, this will not repeat on subsequent pages.

2-Create a formula:

whilereadingrecords;
"Component "+"Supplier "+"Price"

Add this as your topmost row field. This will result in an outer field (on the left) that will display like:

Component
Supplier
Price

You could add text, as in:

Component (Col 1)
Supplier (Col 2)
Price (Col 3)

Or you could rotate the text 90 degrees so that it displays vertically. None are perfect solutions.

-LB