Link to home
Start Free TrialLog in
Avatar of rocketdoctor
rocketdoctor

asked on

formulas in cross tab reports

I am trying to add a simple formula to get the average price of a product based on the orders and sales per day within a cross tab.

When I add the following formula  it appears to apply the same value to every column(date) even though the product and sales values for each date is changing.

This only seams to display properly in the totals column when I use

if sum(ProductOrders)<>0 then sum(TotalSales)/sum(ProductOrders)

however it then puts this values in all the columns because its summing all the data.  I am setting the field summary to be averaged.

is there a way to show the average price of orders for the day properly in each column and the totals column as well?   or do I have to do this in a Stored Procedure.

Avatar of frodoman
frodoman
Flag of United States of America image

What does your crosstab look like now - data & formulas?
Avatar of rocketdoctor
rocketdoctor

ASKER

My Cross tab has the breakdown of sales and orders by date and products IDs  some rows can have 0 values for the orders and the sales.   I need report average values which is just a the TotalSales/Totalorders for each day and the average of that for the day.

why doesn't the formula above work?
>>> why doesn't the formula above work

Because crosstabs don't react well to user formulas - there aren't a lot of places you can use them and they work as expected.  Generally speaking just dropping a formula into a crosstab does not work.

If you can layout a sample of what your crosstab looks like I'll try to offer some suggestions.  
I'll try.

I have a stored producedure that results in  ProductType,Product,Sales,Orders,Saledate  which it does by totalling orders and sales by the  Product Type, Product ID, Sale Date.


in the Crystal Report I am group the result set by ProductType

in the Cross Tab I  have
 the SaleDate summarized by Day in the Column Tab

i
oops did a premature submit

I have a stored producedure that results in  ProductType,Product,Sales,Orders,Saledate  which it does by totalling orders and sales by the  Product Type, Product ID, Sale Date.


in the Crystal Report I am group the result set by ProductType

in the Cross Tab I  have
 the SaleDate summarized by Day in the Column Tab
 The Sales and Orders in the the summarized fields
The ProductType in the Rows

I would also like to show the average sale price (Sales/orders) in the report.  This would be the average sale price for the Produt Type for each day.  
ASKER CERTIFIED SOLUTION
Avatar of frodoman
frodoman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the Weighted average was the trick this work great and now get the results I expected.

Thanks alot Frooman you have helped me from throwing Crystal reports out the window.