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.

rocketdoctorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
frodomanConnect With a Mentor Commented:
I believe what you want to do is simply create a formula "AvgFormula" as: {sales}/{orders}

Drop this formula into the summarized fields section of your crosstab.  Now click to select it and click the "Change Summary" button.  Select "Average" instead of "Sum".  This should give you a crosstab expert that has:

Columns: {datefield}
Rows: {typefield}
Summarized: Sum of Orders, Sum of Sales, Avg of @AvgFormula

And the output would look like this:

                Day1            Day2              Total
Type1          5                 10               15
                  1000             1000           2000
                  200               100             150


In the above example, the 150 is the average of 200 & 100 instead of the true average of 133.33.  To get around this, rt-click on the value in your crosstab (directly on the 150 in this case) and select "Edit Summary".  From the dropdown that appears change "Average" to "Weighted Average With" and select the 'orders' value from the next dropdown.  This will change to reflect the true average in the total and give you something like this:

                Day1            Day2              Total
Type1          5                 10               15
                  1000             1000           2000
                  200               100             133.33

Hope that helps.

frodoman
0
 
frodomanCommented:
What does your crosstab look like now - data & formulas?
0
 
rocketdoctorAuthor Commented:
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?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
frodomanCommented:
>>> 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.  
0
 
rocketdoctorAuthor Commented:
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
0
 
rocketdoctorAuthor Commented:
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.  
0
 
rocketdoctorAuthor Commented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.