We help IT Professionals succeed at work.

Date Range parameter and prior month data required.

771 Views
Last Modified: 2008-07-16
Hello,
I need to edit an existing report that includes sales and tons sold based on a record selector defined by a parameter DateRange.  The current report runs like a champ.  Now, I am being asked to write reports that will allow the existing data and then include 1 month prior.  A second report 2 include quarters and a third to include 6 mos.

First things first, I have a perfectly good working report based on a date range and I want to still include that data and then just add 1 prior month.  I have tried DateAdd and LastFullMonth, but both seem to run me around in circles.  They complain that a Date Range is required and when I change it to DateRange the complaint is that a date/time is required.  Also, with LastFullMonth I receive a Boolean error.

Is there an easier way to do this, sure seems like there should be?
Should I just define one parameter of Date; however, when I do this I lose the current data?

Much Appreciation,
Beth




Comment
Watch Question

CERTIFIED EXPERT

Commented:
This sounds somewhat peculiar to me.

You are saying that the user is to select a date range but that the report is to be based on a date range that you derive from what they have selected.  Why don't they just specify the range they want in the first place?

Author

Commented:
I agree, but my boss wants to select a range and then get that range and 1 month prior.
You know bosses.  ;-)

Anyway, figured something out.  I was using the record selector to just pull the data in the range which is why I was not getting the prior month's data.  So I have removed the record selector to include all data (which is a ton BTW).  Now I must conditionally suppress what I do not want, right?

It just seems like there should be a built in command to make this much easier.  Any other ideas?

Thanks,
Beth
CERTIFIED EXPERT

Commented:
So you are saying that if the user specifies a range of say:
17 Mar 2008 to 24 Jun 2008
then you really want the range to be:
17 Feb 2008 to 24 June 2008?

Author

Commented:
Kind of...
The user will specify 1 month, let's say 1 Jun 2008 to 30 Jun 2008.

On the report, I will need the following:

Jun 2008 Tons sold, Jun 2008 Sales $ sold AND May 2008 Tons sold and May 2008 Sales $ sold.  Then a variance of each.

Thx,
B

Top Expert 2008

Commented:
could create a formula using datediff then use ur (date parameter - 1). and use ur formula in your record selector instead of your parameter

Author

Commented:
Can you elaborate?  I am having a little trouble with the DateDiff.
The problem seems to be with the first line.

Would it look like this:
If DateDiff ('m',Minimum({?Date Range}),Maximum({?Date Range}))-1 and

{SalesTable.SalesType} = 3 then {SalesTable.SalesPrice} else -{SalesTable.SalesPrice}
Because this gives me a boolean error.

Thx


Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Not sure what that formula is trying to do.  The datediff needs to be compared to somevalue so it returns a boolean.

For the select formula
{YourDateFIeld} in DateAdd('m',-1,Minimom({?DateRange})) to Maximum({?DateRange})

mlmcc
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
How is that different from what I provided?

It seems my example of using the DateAdd led you to the solution.  You never mentioned having multiple formulas.

mlmcc

Author

Commented:
It is not different; however, I already the DateAdd in one of the formulas.  It was not in all of the prior month formulas as needed.  Additionally, I did not have both the current month and prior month defined in the record selector.

I did mention 6 calculated requirements in my 3rd posting.  I did not use the term "formula", but instead made an assumption that anything calculated would generally require a formula.  Furthermore, I figured out that I needed the DateAdd in ALL formulas that would require prior month's data.  Additionally, it was on my third posting that I realized I was only pulling in the current month's data.  So my problem was entirely fixed before you ever posted.  

Wish you could have shown up earlier so I hadn't wasted time on DateDiff, though.
I searched and found the definition of DateDiff here: http://www.codefixer.com/tutorials/datediff_dateadd_datepart_vbscript_functions.asp

This confirmed I was using DateAdd correctly and did not need DateDiff, this prompted me look back at all my formulas which ultimately led me to fix my own issue.

Now I have some experience with DateDiff, though.  Let's face it , it's never wasted time if you learn something new.

Have a good one and hope to see you around.  Thanks for trying to help.

Ciao,
Beth



Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.