I am sure the answer for this is somewhere on these forums but I must not be using the correct terms. I think my biggest problems come because I used Crystal reports for many years and I still think in those terms. But I am sure there is nothing I did in Crystal that can't be done easily in webfocus, so here is a simple version of my scenario:

2 tables in a SQL Server with a parent/child relationship based on INVOICEID:

INVOICE

INVOICEID
INVOICEDATE
INVOICETOTAL
MERCHANTFEE

INVOICEITEM

INVOICEITEMID
INVOICEID
ITEMAMOUNT
ITEMWHOLESALE

I have a view in SQL server that combines those two tables, which means the fields from the INVOICE table are repeated for each INVOICEITEM record. This means that when I sum ITEMAMOUNT and ITEMWHOLESALE, the totals are correct, but when I sum INVOICETOTAL or MERCHANTFEE the total or too high.

In Crystal I was able to group by MONTH and then INVOICEID, put INVOICETOTAL and MERCHANTFEE in the INVOICEID group header next to the SUM of ITEMAMOUNT and ITEMWHOLESALE, then hide both the detail and the entire INVOICEID group, and only display the MONTH group data, which included the correct SUM of INVOICETOTAL and MERCHANTFEE from the group headers, and also the SUM of ITEMAMOUNT and ITEMWHOLESALE from the detail.

Hopefully my description makes sense to someone here. I know that there are many ways to approach this - I have tried a lot of things - but my tables are large so I need the join to be handled by SQL server.

What result (and what SQL code) does Focus generate for a multiverb request along these lines? --

SUM INVOICETOTAL BY INVOICEID
SUM ITEMAMOUNT   BY INVOICEID BY INVOICEITEMID
ON TABLE HOLD FORMAT FOCUS

JG,

That code will return a correct sum for ITEMAMOUNT but the SUM of INVOICETOTAL will be INVOICETOTAL multiplied by the number of invoiceitem records.

OK - here is another approach and the problem I have with this approach:

SUM
MAX.INVOICETOTAL
MAX.MERCHANTFEE
ITEMAMOUNT
ITEMWHOLESALE
BY INVOICEDATE
BY INVOICEID

This approach gives me the correct data, but I need a summary report and do not want to see every invoice on this report.

I only need the daily (or monthly) totals, but when I add subotals and take out BY INVOICEID the data is wrong, or if I NOPRINT the columns, then the subtotals and grand totals are gone - the lines for the subtotals appear but with no columns.

Tom,

If you can have Focus do the JOIN your problem would disappear because then your INVOICE data would be used once for every invoice.
If this is not feasible, then maybe you can

  
SUM 
MAX.INVOICETOTAL
MAX.MERCHANTFEE
ITEMAMOUNT
ITEMWHOLESALE
BY INVOICEDATE
BY INVOICEID
ON TABLE HOLD
END
TABLE FILE HOLD
SUM 
INVOICETOTAL
MERCHANTFEE
ITEMAMOUNT
ITEMWHOLESALE
BY INVOICEDATE
...

Thanks Danny - you have pinpointed the issues.

I guess I did not mention that when I let Webfocus handle the join, the performance was terrible whenever anyone choose more than about a week of data because of the amount of data. That is why I am trying to push the join to SQL server. Ideally I would push the aggregation to SQL server as well, but there are several calculations that have to be done at the invoice level which I need to keep in Webfocus.

2) Your solution of creating a HOLD file would definitely work fine for this. FOr some reason I have an irrational aversion to creating HOLD files and I need to get over that - I have rarely needed then in other reporting tools, but I know that the tool itself was doing that in the background for me.

In this case, your solution is exactly what I needed - load the detail in a hold file with the calculations I need at the invoice level, and then summarize using that data. If there is no way to do that in a single step by just 'hiding' or supressing the details and showing only the the summary data, then I will use this solution.

Tom,
Welcome to the world of Focus. You will see that HOLD files, SAVE files are the everyday fare. Use them well as they have lots of power.
The other hugely powerful tool in WF is the Dialog Manager. Understand that and you will never look back!

Tom,

You mention that you have large amounts of data, don't forget to utilise RECORDLIMIT and / or READLIMIT during testing to speed up your turn around rate between runs. Also, XRETRIEVAL can be very useful in trapping the SQL to determine how the join is being handled.

T