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?
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
0
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
0
Erin LealeConsultant/ControllerAuthor Commented:
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

mlmccCommented:
Do you need to drill into the details?

I assume you only need to see the 12 month summary

mlmcc
0
Erin LealeConsultant/ControllerAuthor Commented:
Summary only.  Thanks.
0
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
0

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!!!!!
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.