Calculate Profit on Report

[Guest]

I have a query with these fields:

CustomerID; ProductID; Type; Jan; Feb; Mar; etc…

“Type†can be either “Revenue†or “Cost†and in certain cases, there might
be revenue for a particular product, and no cost, or vice-versa

I’d like to create a report that looks like this:


Jan Feb Mar etc…
Customer1
Product1
Revenue
Profit (i.e. Rev less Cost)
Margin (Profit as a % of Rev
Product2
Revenue
Profit
Margin
Etc…


Note that I’d want there to be lines for Revenue, Profit, and Margin even if
a particular product had no revenue but only cost.

Can this be done?

Thanks,
TMore

 

[Duane Hookom]

Do you actually have fields with names of months? If so, you should consider
normalizing your table structure prior to creating any additional queries,
reports, or whatever.

 

[Guest]

Thanks for the quick reply.

Yes, I am pulling in a summary table from JD Edwards via ODBC that is
structured in that way.

However, I could also get the data into a normalized fashion without too
much trouble, i.e. a "Month" column instead of a field for each month. Would
that make it easier to create the report in question?

 

[Duane Hookom]

If you data is normalized, you don't have to perform a calculation 12 times
(once for each month).
It isn't real clear how you want to structure your report.

 

[Guest]

Sorry, my formatting didn't come through on the original post. Here's what I
wanted it to look like:

Jan Feb Mar etc…
Customer1
Product1
Revenue
Profit (i.e. Rev less Cost)
Margin (Profit as a % of Rev)
Product2
Revenue
Profit
Margin

Etc…

 

[Duane Hookom]

I would still prefer to work with the normalized records. There are several
examples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. You might want to
review the CrosstabReport.mdb.

 

[Guest]

Thanks, I will definitely take a look.

I would still prefer to work with the normalized records. There are several
examples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. You might want to
review the CrosstabReport.mdb.