Sage 100

Sales reporting. I need to create a Crystal report of year over year sales by period by item I can burst into Excel sheets using Visual Cut by Salesperson. The rep is assigned at invoice data entry so will use AR Invoice History. I could do this with sub reports but since the end goal is Excel, that's not going work. I'm thinking of summarizing data in Access then pointing Crystal to the Access database. Will this work and does this make sense?

Access will work, and may be best option if you need quickly. IMHO Power Query or Power BI in Excel would be better choices, if you have time. Power Query has a faster learning curve, where are Power BI requires a bit more.

Since I need to email these Excel sheets to each rep, and as I was planning on using Visual Cut to do that, I'm going to go with what I know. I'm reassured that this is not nuts though.

Not nuts :) Just a lot of data!

Have you considered using the Excel Power Query and Power Pivot functions? It's a lot like combining Access built right into Excel. If you are unfamiliar with these features I can post some links introducing it.

I'd love to learn Excel Power Query but, for this project, I have to run this off of a Crystal report to handle bursting and emailing. Also, client has Office 2007 (I know!) Since these are once a month Reports, speed is not an issue. Am I missing something obvious?

I assume you are already used to working with Visual Cut, then. Another tool is using logicity (https://www.logicitysuite.com/) which is a Crystal Reports scheduler and export /email function. For example, you could create a crystal report with parameters for then create different logicity jobs to run the crystal based on the parameter and email it. It works with windows task scheduler to automate running it. There is a free version, then a paid version (think about $85/user) with more functionality. It's always good to have extra tools avaiable :)

Formula for ThisYearAmt and LastYearAmt (these are your values). Formulas for year and month (columns). Pivot table / cross tab (within a group section) to break things down the way you want. The problem with this strategy is that months without activity will not show up... but it's not a difficult report to make.

To get all dates to show, I sometimes create a separate date table and link the data to it with a left outer join. Then the date table (Year/Mo) becomes the column in the crosstab. It's been a few years since I did that, but it has worked in the past. I've even created a UTD date table for this.