geek-goddess
asked on
Date Range parameter and prior month data required.
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
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
ASKER
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
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
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?
17 Mar 2008 to 24 Jun 2008
then you really want the range to be:
17 Feb 2008 to 24 June 2008?
ASKER
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
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
could create a formula using datediff then use ur (date parameter - 1). and use ur formula in your record selector instead of your parameter
ASKER
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
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
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({?D
mlmcc
For the select formula
{YourDateFIeld} in DateAdd('m',-1,Minimom({?D
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
It seems my example of using the DateAdd led you to the solution. You never mentioned having multiple formulas.
mlmcc
ASKER
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
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
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?