August 15, 2012 at 11:32 am
Hi
I have 3 parameters in my report:
FinancialYr in this text format: '2011/12', '2012/13' etc
FromDate in Date format (UK dd/mm/yyyy)
ToDate in Date format (UK dd/mm/yyyy)
I have been trying all day to come up with an expression which will display if any of the following scenarios play out.
1
August 15, 2012 at 11:45 am
What are your scenarios?
August 15, 2012 at 11:49 am
Not sure entirely what you're asking, since the post seems incomplete, but it's looking like a typical "you need a Calendar table" type question. In a Calendar table, you would have dates stored in either DateTime or Date datatype, and then have a separate column to indicate Fiscal Year for each. It then becomes trivial to query that kind of data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 15, 2012 at 11:58 am
Sorry my previous post was incomplete (pressed some key combination that submitted).
Here's what I meant to say:
Hi
I have 3 parameters in my report:
[FinancialYr] in text format: '2011/12', '2012/13' etc
[FromDate] in Date format (UK format: dd/mm/yyyy)
[ToDate] in Date format (UK format: dd/mm/yyyy)
I have been trying all day to come up with an expression which will display an error message if any of the following scenarios play out (the expression will be placed in the visibility property of the text box and display the message if any of the following are 'True'.
1 If the last 2 numerals of the 'Year' part of [FinancialYr] (12 or 13) is less than the last 2 numerals of the Year part of [FromDate]
2 If the last 2 numerals of the 'Year' part of [FinancialYr] (12 or 13) is more than the last 2 numerals of the Year part of [FromDate]
3 If the last 2 numerals of the 'Year' part of [FinancialYr] (12 or 13) is less than the last 2 numerals of the Year part of [ToDate]
4 If the last 2 numerals of the 'Year' part of [FinancialYr] (12 or 13) is more than the last 2 numerals of the Year part of [ToDate]
5 [FromDate] > [ToDate]
6 [ToDate] < [FromDate]
I've managed to find a solution to 5 above so far:
=IIF(Parameters!FromDate.Value>Parameters!ToDate.Value,False,True)
but have sort of ground to a halt when trying to compare the last 2 numerals of the 'Year' part of [FinancialYr] - 12 or 13 which are text with the last 2 numerals of either date field. Which has brought me here.
Can anyone help out please?
Many thanks in advance.
August 15, 2012 at 12:01 pm
Canazei65 (8/15/2012)
HiI have 3 parameters in my report:
FinancialYr in this text format: '2011/12', '2012/13' etc
FromDate in Date format (UK dd/mm/yyyy)
ToDate in Date format (UK dd/mm/yyyy)
I have been trying all day to come up with an expression which will display if any of the following scenarios play out.
1
For the financial years, what is the starting Month/Year and the ending Month/Year. For example: Dec 2011 - Nov 2012 might be the financial year for a US company for their Fiscal Year 2012 (and actually, it is).
You show 2011/12, what is the starting and ending month/year for this financial year?
August 15, 2012 at 12:33 pm
Canazei65 (8/15/2012)
Sorry my previous post was incomplete (pressed some key combination that submitted).Here's what I meant to say:
Hi
I have 3 parameters in my report:
[FinancialYr] in text format: '2011/12', '2012/13' etc
[FromDate] in Date format (UK format: dd/mm/yyyy)
[ToDate] in Date format (UK format: dd/mm/yyyy)
I have been trying all day to come up with an expression which will display an error message if any of the following scenarios play out (the expression will be placed in the visibility property of the text box and display the message if any of the following are 'True'.
1 If the last 2 numerals of the 'Year' part of [FinancialYr] (12 or 13) is less than the last 2 numerals of the Year part of [FromDate]
2 If the last 2 numerals of the 'Year' part of [FinancialYr] (12 or 13) is more than the last 2 numerals of the Year part of [FromDate]
3 If the last 2 numerals of the 'Year' part of [FinancialYr] (12 or 13) is less than the last 2 numerals of the Year part of [ToDate]
4 If the last 2 numerals of the 'Year' part of [FinancialYr] (12 or 13) is more than the last 2 numerals of the Year part of [ToDate]
5 [FromDate] > [ToDate]
6 [ToDate] < [FromDate]
I've managed to find a solution to 5 above so far:
=IIF(Parameters!FromDate.Value>Parameters!ToDate.Value,False,True)
but have sort of ground to a halt when trying to compare the last 2 numerals of the 'Year' part of [FinancialYr] - 12 or 13 which are text with the last 2 numerals of either date field. Which has brought me here.
Can anyone help out please?
Many thanks in advance.
A couple of your rules are redundant. For example, you don't have to check for FromDate > ToDate and ToDate < FromDate, because both of those conditions are identical. Likewise, you don't have to check that the last digits of your string parameter is not greater than and not less than the years in your date parameters; you can simply check for them not being equal.
In other words, rules 1 and 2 are the same rule; rules 3 and 4 are the same rule, and rule 5 and 6 are the same rule.
Assuming you're using the standard Jan-Dec fiscal year, this should work:
=IIF(
Parameters!fromDate.Value > Parameters!toDate.Value
OR CInt(Right(Parameters!FinancialYr.Value, 2)) <> Right(CStr(Year(Parameters!toDate.Value)), 2)
OR CInt(Right(Parameters!FinancialYr.Value, 2)) <> Right(CStr(Year(Parameters!toDate.Value)), 2)
, False, True)
August 15, 2012 at 1:10 pm
Hi
Thanks for the replies - I shall take a look now. However, I should have made it clear in my post the financial year in the UK runs from 1st April - 31 March each year. Hence the reason why it's recorded 2011/12 etc.
Thanks again.
August 15, 2012 at 2:05 pm
Canazei65 (8/15/2012)
HiThanks for the replies - I shall take a look now. However, I should have made it clear in my post the financial year in the UK runs from 1st April - 31 March each year. Hence the reason why it's recorded 2011/12 etc.
Thanks again.
Well, one way to do this is:
=iif(
Parameters!fromDate.Value >= DateAdd(DateInterval.Year, -1, CDate("4/1/"+right(Parameters!financialYr.Value,2)))
AND Parameters!fromDate.Value <= CDate("3/30/"+right(Parameters!financialYr.Value,2))
AND Parameters!toDate.Value >= DateAdd(DateInterval.Year, -1, CDate("4/1/"+right(Parameters!financialYr.Value,2)))
AND Parameters!toDate.Value <= CDate("3/30/"+right(Parameters!financialYr.Value,2))
AND Parameters!toDate.Value > Parameters!fromDate.Value
,"False", "True")
So what this is doing is finding the start date and end date of whatever FY you enter, checking to see that both of your dates fall between those start and end dates, and also checking to see that "from" is less than "to".
August 15, 2012 at 4:33 pm
Thank you cphite, that's brilliant. I'll implement when I get to work tomorrow and let you know how it goes.
I have to admit, I didn't know about the cdate and related functions, but I do now.
Have a great evening.
August 16, 2012 at 2:40 pm
Hi
I managed to get things to work with slightly amended code as follows:
=iif (Parameters!From.Value > CDate("31/3/"+right(Parameters!FinancialYear.Value,2))
or (Parameters!From.Value < DateAdd(DateInterval.Year, -1, CDate("1/4/"+right(Parameters!FinancialYear.Value,2)))
or Parameters!From.Value > Parameters!To.Value)
or Parameters!To.Value > CDate("31/3/"+right(Parameters!FinancialYear.Value,2))
or (Parameters!To.Value < DateAdd(DateInterval.Year, -1, CDate("1/4/"+right(Parameters!FinancialYear.Value,2)))),False, True)
Anyway, I just thought I'd post it and thanks for the help.
Best wishes.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply