Revenue, Cost, Gross Profit, Margin Report

[theDeej]

I am in the process of creating a report that needs to look something like this:

CustomerName

Q1-03 Q2-03
Revenue
Apps 10000 2500
Svcs 30000 16000
Maint 20000 30000
Cost
Apps 9000 0
Svcs 13000 12000
Maint 11000 9000
Gross Profit (Rev - Cost)
Apps 1000 2500
Svcs 7000 4000
Maint 9000 21000
Margins (GP / Rev)
Apps 10% 100%
Svcs 23% 25%
Maint 45% 70%

I've successfully created a crosstab report containing revenue and cost as as well as the gross profit. I cannot figure out a way to include the margin percentages. Is there a way to do this?

I tested doing a manual cross tab (only doing one quarter), but it takes away the "dynamic-ness" of the report because everything has to be hard-coded (AND it so time consuming to create).

Have I explored all my options or have I missed something? Most reporting I've done to date has been operational, this is my first time doing financial type reporting via Crystal.

Thanks for any suggestions you might be able to offer.

I am using Crystal Reports 9.0 and SQL 2000. My table is as follows:

Cust# Date Type SubType Amount
1234 1/1/01 Cost Maint 20000
1235 1/1/01 Rev Svcs 18000


-Deej

 

[nagornyi]

I would suggest building the Gross Profit and Margin crosstabs as separate subreports. For those subreports different SQL is needed, in which aggregation (sum) of amount is done per Customer, Quater, Type and Subtype by proper groupping. For that SQL you will need a formula that would match Date to Quaer. That is, for 01/01/01 the formula returns Q1-01, for 12/12/03 it returns Q4-03. I beileve it is quite doable.

 

[theDeej]

Thank you for your input nagornyi. I was hoping to do this via Crystal - but perhaps turning back to old, reliable SQL for the calculation is the best method...

Thanks again.

-Deej