I'm creating another report and have run into the following issue: Two of the fields I'm using are INV TOTAL - YTD and TOTAL DOLLAR VALUE (of the last invoice). Let's say, I have a customer called WINNER. In this report, I have 10 lines for this customer that all show $11,760 in the INV TOTAL -YTD column. If I select the INV TOTAL - YTD field using the SET SORT ORDER and then click on the SUMMARIZE button, GridView properly summarizes it by displaying $11,760 as the total sum.
However, if I set the search order any other column (e.g. the TOTAL ORDER VALUE column) and click on the SUMMARIZE button, the INV TOTAL - YTD column suddenly adds up those $11,760 however many times it appears in the list in this case 10 times) and I end up with a sum of $117,600 - which is obviously wrong.
How do I get around or fix this? In my opinion, GridView should 'know' that any YTD, LY, PTD like column should only count once.

Another question along those same lines is this: Is there a way to say I want to get (sub)totals for certain columns and not for others? Would I have to use Crystal reports for something like that?

Thanks much!

Andre

Hello Andre,

The problem is that the master file (which contains the YTD figures) is being read (and totalled) for each transaction. I think you'll find that Average on those columns will result in the correct total being displayed.

BTW - Summary figures are shown for those columns that have totals (or averages) set for the column. If you don't want them summarized (or the numbers aren't meaningful) then turn off the column total.

Also, as an aside, when I here someone talking about various subtotals and totals, I'd normally recommend Crystal over GridView. GridView is designed to handle fast, on screen inquiries, where the numbers are moe important than the presentation.

Thanks, Andrew. I think this is another report we'll have to recreate in Crystal Reports. The 'Average' suggestion won't really work for us since we'll have mutiple customers listed in this report - each with different YTD numbers. If I select to display the average for that column, it's going to give me the average for all the customer's YTD figures. We don't have any use for that number.
I guess you're correct: gridView is good for a quick and dirty look at the numbers. For more sophisticated reports, Crystal is probably the way to go.

thanks again.

Andre

Hi Andre

Actually Andrew's idea about using Average will work - give it a try and you'll see. It computes the average per customer (or whatever field you summarize on), not the average for all customers.

Steve

Hi Steve:

It still wouldn't work. I may have a customer called ABC with 2 orders (e.g. $2000 and $2400) and 4 line items each. If I were to summarize by customer, I would get an average of $2200 and not the total dollar amount ($4400) for that customer.
If I were to summarize by order number, I would get the correct average numbers ($2000 & $2400), but then I would still have to manually add up both to get the total for that particular customer. Does this make sense?

Andre

Hello Andre,

I was suggesting you select "Average" for the YTD sales column and "Total" for the Order Amount column.

Make sense?

Hi Andrew:

Thanks! I thought that setting (under EDIT, DEFAULTS) applies to the entire view. How do I set this for each individual column?

Andre

Hi Andre,

Right Mouse click on the column heading and select the summary operation you want on that column (Total, Min, Max, Average).

Got it. Thanks!

Andre

Andrew: Is it possible to upload screenshots? I followed your suggestion and set the YTD column to display the 'average'. That part works. However, I can't use 'Total' for the 'Total Order/Dollar Value' column because that adds up several lines - all with the same dollar amount, all with the same order number. So, instead of showing me '$1603.40' for this customer and his one order number, it shows me six times $1603.40 and a total of $9620.40. The order number is the same for each one of the $1603.40 lines.
I can't change this to display the 'average' amount either because I have customers with more than one order. In that case, I may have five lines with, say, $1000 as the Total Order Value for Order # 100 and another 6 lines with $1500 as the Total Order Value for Order # 101 (all just examples). If I were to use average in that example, it would give me the average of those two order amounts - not the sum of the two. if I were to select 'Total', it would multiply $1000 by 5, mulitply $1500 by 6 and give me that sum of that. Neither is what I want/need.

Anyway, a screenshot would maybe better show what I'm trying to say. Please let me know. Thx,

Andre

Hi Andre,

It is possible to Set Sort Order and Summarize by more than one column. Perhaps that might be useful for getting Totals/Averages on the lines you want (ie by customer or by document). If you summarize by customer and document number and set the Summary method to Average for all fields that are 'header' information like 'Total Order/Dollar Value', you are guaranteed to get the exact amount for the document on the 'summary by document' sub-total row. You would get a different value for the customer that has multiple orders, as you indicated earlier.

To do this, first make sure your View is not summarized. Then multi-select columns (say Customer and Document #) and then choose Set Sort Order. Sorting will be done from left-to-right of sorted columns, so the left-most column chosen to sort by will be the primary sort.

In following this thread where you desire particular information in the Sub-total lines, it may be that Crystal is the better reporting tool in some cases that GridView. When you start wanting to manipulate the sub-total or grand total lines, GridView isn't the best because I believe it's best usage is to be detail-driven and provide simpler summary operations.

Hello Andre,

OK - I'm lost.

Are you trying to do Customer by Product Sales? If so, then the place to report from is SalesAnalysis.

Can you give me the column headings of your report?

In thinking about this, you probably should not be using fields such as Total Order/Dollar Value for the summary operations you currently are doing. If you have detail in your View (item number, etc), then you really should be using fields like Extended Price, Extended Order Price and using the Total summary operation (not average). Because the sum of the details should be equal to the total (and if not, an integrity error would exist in your data), you should get the same number in the Total by Document # row as the Total Order/Dollar Value.

And also as Andrew posted above, SalesAnalysis keeps track of all sales (invoices and credit notes) at a detail level, making all of this fit nicer with GridView.

This may actually work. I was able to select the two columns you had suggested and the numbers look right. I'm going to look into it some more, run the Reportmaster report and compare the two... Thanks for the suggestion!
In the meantime, here is the report definition from Reportmaster:

- The columns in this report from left to right are:
GROUP
REP
CUSTOMER
NAME
LY-YTD
CY-YTD
DIFFERENCE
LAST ORDER

- The report uses the S/A Transactions table
- Fields selected from that table are:
CUSTOMER
GROUP (Formula is characters 1 & 2 of the SALESPERSON field)
REP (Formula is characters 1-3 of the SALESPERSON field)
LY-YTD (Formula: IF D<=E-365 THEN A (A=Amount,D=date,E=End Date:))
CY-YTD (Formula: IF D>=S and D<=E then A (A=Amount, D=Date, S=Start Date:, E=End Date)
DIFFERENCE (Formula: ID D<=E-365 then -A else D>=S and D<=E then A (A=Amount, D=Date, S=Start Date:, E=End date:))

I believe the 'NAME' and '-DATE' fields are being pulled from the AR CUSTOMER table. That table is listed under RELATED DATA.

Under REPORT SELECTION for this report, the following formula is listed:

D>=S-365 and D<=E and I[1~3]<>"MIS"
D = Date
S = Start Date:
E = End Date:
I = Item

When I select PRINT REPORT, I get asked for the Start and End Date and then the report runs.

If all this is in Sales Analysis, then I would be happy to use that instead of GridView or Crystal. I'm not an ACCPAC or ADAGIO expert by any means. I'm simply trying to get all those Reportmaster reports I inherited ported overt to either GridView, Adagio or something else that's not DOS based.;-)

Cheers,

Andre