I have had a request from one of our branches to obtain the following information. Can you please advise which GridView report would be best able to extract this for me?

Customer #
Customer Name
Address 1
Address 2
Address 3
Address 4
Postal Code
Telephone
Contact
Sales each customer for Fiscal Year Ending 2008
Sales each customer for Fiscal Year Ending 2009

I was able to extract a report showing each customer's sales, but it ended up extremely cumbersome. It showed each document (invoice, credit note, cash application etc) as a line item, making the end report way too large to print. All we really need is the above info, for the two year periods.

Thanks for any help you are able to provide.

Hi Barb,

Take the report you have created, then 'Set Sort Order' by Customer #. Then click the Summarize button on the toolbar (the sigma Greek character) to summarize all the records, so now you have one row for each customer, with the Sales sub-totalled by customer.

In order to get two columns that show yearly summaries, you need to create 2 calculated columns that test the date of the transaction and if it is within the desired date range, then output the amount, otherwise output 0.

EG to list all sales for 2009:
IF (YEARL({Date}) = 2009 , {Amount} , 0 )

If your fiscal year does not end on Dec 31, then you can't use the simple year function, but define the start and end dates:

IF (
AND( {Date}>=Date(2008,07,01) , {Date}<=Date(2009,06,30) )
,
{Amount}
,
0
)