Skip to Content
0
Former Member
Aug 30, 2011 at 05:53 PM

Compare two datasets in crosstab by showing percentage of sales

53 Views

I am wanting to compare two datasets in a crosstab chart.

Basically I have a sales table and a business costs table.

My business costs table fields look like this:

Category

CostDate

CategoryCost

My sales tabel looks like this:

PartNumber

SalesDate

SalesAmount

In my crosstab I want to group my rows by Category and my columns by Month.

So that in my summaries I can show sum of category cost and the percent of sales for that particular month. I can do everything except show percent of sales for that specific month. My sales table has a SalesDate that I will be using to group off of.

Any ideas on how to do this, I am not sure where to even start. I am still new with crystal reports.

my sales data

PartNumber SalesDate SalesAmount

Part1 1/1/2011 $1.00

Part2 1/1/2011 $1.00

Part3 1/1/2011 $1.00

Part4 1/1/2011 $1.00

Part5 1/1/2011 $1.00

Part6 1/1/2011 $1.00

Part7 2/1/2011 $1.00

Part8 2/1/2011 $1.00

Part9 2/1/2011 $1.00

Part10 2/1/2011 $1.00

Part11 2/1/2011 $1.00

Part12 2/1/2011 $1.00

Part13 2/1/2011 $1.00

My business cost data

Category CostDate CategoryCost

Office 1/1/2011 $1.00

Office 2/1/2011 $1.00

Warehouse 1/1/2011 $1.00

Warehouse 1/15/2011 $1.00

Warehouse 2/1/2011 $1.00

Quality 1/1/2011 $1.00

Quality 2/1/2011 $1.00

Quality 2/15/2011 $1.00

What I want my crosstab to look like

Total | 1/2011 | 2/2011

Business cost | % of Sales | Business cost | % of Sales | Business cost | % of Sales

Total | $8.00 | 62% | $4.00 | 67% | $4.00 | 57%

Office | $2.00 | 15% | $1.00 | 17% | $1.00 | 14%

Quality | $3.00 | 23% | $1.00 | 17% | $2.00 | 29%

Warehouse | $3.00 | 23% | $2.00 | 33% | $1.00 | 14%