Crystal Report - 12 Month Sales Data

Good evening,

I have a sales report that I need to show gallons by month - I need it to roll for 12 months including the beginning date.  For example - the user would enter 1/1/2017 and it would report Jan - Dec for 2017.  I manually plug the formulas but I know there is a way to automate it.  I'm drawing a blank - can you help?  

I have attached a screen shot of the report.  

thanks.
Erin LealeConsultant/ControllerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
I don't see the screen shot.

Is your problem in selecting the data based on the selected date?
This selection formula will pull 12 months of data based on the entered date

{YourDateField} BETWEEN {?StartDate} AND DAteAdd("m", 12, {?StartDate})

Will they always enter the 1st of the month or can they select any date?
If they select any date do you want the data for 12 months from that date or from the first of the month?

mlmcc
Erin LealeConsultant/ControllerAuthor Commented:
Hi mlmcc,

By the date selected - the data should always return a full month for the range.  So the user could select 1/1/2017 as the start date and the data should return each month with the gallons.  They could also do 5/1/2016 as the start and I would need each month from May 2016 - April 2017 data.  The attachment is a Jpeg of the report.  I can attach the report if needed too.  This is using a SQL database.
Sales-12-month-rolling.jpg
Erin LealeConsultant/ControllerAuthor Commented:
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

mlmccCommented:
Do you need to drill into the details?

I assume you only need to see the 12 month summary

mlmcc
Erin LealeConsultant/ControllerAuthor Commented:
Summary only.  Thanks.
mlmccCommented:
I think this version does what you want.

You will need to point it back to your data through the SET DATA LOCATION screen.
The tables are named the same but I changed the field names slightly.

I also deleted your selection formula since my data didn't match your values.

The formulas I added/modified all start with a z so you can find them easily.

mlmcc
Sales.rpt
Sales.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Erin LealeConsultant/ControllerAuthor Commented:
This works perfectly - Thank you!!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.