Grouping Formula

[Barock21]

Hello,

I am currently creating a report on Crystal 11 and I have created a bunch of an if then formulas. I ended up with 4 formulas:

Price 1, Price 2
Cost 1, Cost 2

What I did on Price 1 formula was (and this pretty much applies to the other formulas accordingly):

IF period = 1 THEN sales_price ELSE 0

The problem is, I need to group those formulas by price and cost. So that I will be able to sum up my total by sales or cost.

Price
Price 1 Price 2
100 200
0 50
Total 100 250

Cost
Cost 1 Cost 2
75 50
0 25
Total 75 75
Grand Total.....etc.

Any advice will be greatly appreciate it. Thx.

 

[Madawc]

Do you mean that each detail line has both a price and a cost? And that you want two listings, one showing each record with its price and the other showing each record with its costs?

In Crystal, you'd have to do this using a subreport, placed in the report header or footer. Show costs in the main report and prices in the subreport, or vice versa.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IanWaterman]

If price and cost are in the same Table/record then it will not be possible in Crystal alone.

You have two alternatives
Build a report showing say price and then a similarly structured subreport showing Cost and embed the cost subreport in the appropriate location.
or
Build a Union View which effectively splits the price and cost data into two separate rows. You will need to add an identifying column to show price or cost. Crystal can report off the view and you use the price/cost column to group report.

For speed of operation the latter will be best.

Ian

 

[lbass]

Please explain what database fields and tables you are working with, and show some sample data at the detail level.

-LB

 

[Barock21]

I'm currently working on 1 view - sales_history_view. Both fields - sales_price and sales_cost are from the same view.

Item Mo 1 Pr Mo 1 Co Mo 2 Pr Mo 2 Co
ABC123 100 50 150 80
XYZ123 0 0 200 120
DEF123 250 76 0 0
ZZZ123 0 0 0 0
ABC123 75 40 60 30

I need the cost and the price to be grouped separately rather than printed together like that. I know I can accomplish this using subreport but if I don't have that will be better. Please remember these Month 1 and Month 2 columns are formulas from the sales_price and sales_cost. Thanks all.

 

[lbass]

Well, a subreport would be simplest, or you could use a union all statement to combine the price and cost fields into one field and then add an identifier field that you could group on, something like:

select 'price' as type, table`.`price`,
`table`.`item`, `table`.`period`
from `table`
union all
select 'cost' as type,`,`table`.`cost`,
`table`.`item`, `table`.`period`
from `table`

Then you could group on "type".

-LB