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(Produc tOrders)
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.
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(Produc
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.
What does your crosstab look like now - data & formulas?
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?
>>> 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.
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.
ASKER
I'll try.
I have a stored producedure that results in ProductType,Product,Sales, Orders,Sal edate 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
I have a stored producedure that results in ProductType,Product,Sales,
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
ASKER
oops did a premature submit
I have a stored producedure that results in ProductType,Product,Sales, Orders,Sal edate 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.
I have a stored producedure that results in ProductType,Product,Sales,
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks alot Frooman you have helped me from throwing Crystal reports out the window.