Skip to Content
author's profile photo Former Member
Former Member

Compare two datasets in crosstab by showing percentage of sales

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%

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Aug 30, 2011 at 06:00 PM
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 30, 2011 at 07:26 PM

    Hi Joshua,

    assuming that you have linked your two tables and built a crosstab report:

    In the summary data field you summarize 2 fields

    1. your SUm of Business Cost

    2. your Sum of sales. -> Edit that summary to show % of the Table field you want to summarize and select by column

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 11, 2011 at 05:57 AM

    Not possible. CR is a relational reporting tool. Use a Subreport for your second data source then you may be able to show side by side.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.