Solved

formulas in cross tab reports

Posted on 2004-04-24
7
833 Views
Last Modified: 2008-03-10
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
Comment
Question by:rocketdoctor
  • 4
  • 3
7 Comments
 
LVL 42

Expert Comment

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

Author Comment

by:rocketdoctor
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

by:frodoman
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:rocketdoctor
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

by:rocketdoctor
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

by:
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

by:rocketdoctor
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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 …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now