Hello,

I am trying to set up a report to compare the year to year revenue generated by our customers for each period and ytd. So far I've set up the main report with the customer field. I then created two sub-reports, one for the current year and one for the previous year and linked them to the main report by customer name. I've grouped the sub-reports by customer name and set it up so only the total for each customer shows up on the main report. Using was the only way I could figure out how to do this.

I'm having trouble with two areas. First, if the the customer has no revenue for that accounts in both years, I want to eliminate them from the report. Second, I can't figure out how to get the total for each year on the last page.

Any help/ideas are appreciated.

Ryan

Hello Ryan:
Let me suggest a different approach. Base the report on the transaction table with a link to the Customer table. Sort and group by Customer code. Filter the report to transactions in the past 2 years . Create a separate formula calculation for each period you want to see on the report (using IF). This approach solves both of your dilemmas and will likely run faster too.

Hi Bruce,

I've redid the report and I'm really close. I need some help with the IF statements in the formulas. I've set up a Parameter field called Date that enables the user to enter two ranges of dates for comparison (ex. 01/01/2012-31/01/2012 and 01/01/2011-31/01/2011). I also have a Parameter field to select the revenue account. I have two formulas set up, one for each date range so if the account is 0 it won't print the company name. I am am having trouble separating the Date ranges so that one formula does the current year figures and one formula does the previous years figures.

Any suggestions?

Thanks,

Ryan

Ryan:
Do you have 2 date parameters? Are the date parameters defined as ranges? Are you calculating 2 totals for each Customer: Current Year and Last Year?

Assuming you have used range parameters, the formula to calculate This Year would be something like:
If [Transaction date] in [This Year Range Parameter] then [Transaction amount] else 0

The actual fieldnames would vary depending on which tables you are using.

Thanks Bruce I'll give it a try.