Solved

# formulas in cross tab reports

Posted on 2004-04-24
843 Views
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.

0
Question by:rocketdoctor
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3

LVL 42

Expert Comment

ID: 10919511
What does your crosstab look like now - data & formulas?
0

Author Comment

ID: 10920764
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

LVL 42

Expert Comment

ID: 10920981
>>> 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

Author Comment

ID: 10921370
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

Author Comment

ID: 10921475
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

LVL 42

Accepted Solution

frodoman earned 500 total points
ID: 10921823
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

Author Comment

ID: 10923099
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
###### Suggested Courses
Course of the Month6 days, 19 hours left to enroll