

SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 05, 2014 12:36 PM
Points: 39,
Visits: 144


I have a report with the following objectives, purpose, and questions (below the generic code). The environment is SSRS on SQL 2008 R2 with Visual Studio 2008.
I cannot get the logic for returning a 28 or 29 for the end of February depending on if it is a leap year.
I have the code and each of its pieces below.
The error message that comes up is a generic #Error in the field. When I try to substitute in general language I get a [BC30201] expression expected error.
I cannot find the logical piece that is missing; please help.
This report used to be a working Crystal Report; but must be converted to SSRS.
'Generic Sample Code version: =IIF(
2 = 2 AND 'test 1 iif( 3=3,'test 2a 366,' true part 2a iif( 4=4,'test 2b 365,' true part 2b iif( 1=1, 'test 2c 366,' true part 2c 365 ' false part 2c ) ' false part 2b ) ' false part 2a ) = 366 AND 'test 2 5=5 , 'test 3 3000, ' true part main IIF( iif( 6=6, 'test 4 366, ' true part test 4 iif( 7=7, 'test 4a 365, ' true part test 4a iif( 8=8, 'test 4b 366, ' true part 4b 365 ' false part 4b ) ' false part 4a ) ' false part test 4 )=366 AND 'test 4 9=9, 'test 5 1000, ' true part test 5 2000 ' false part test 5 ) ' false part main )
'Objective: 'Give the GrossProfit for the same period for the dates between StartDate and EndDate for the prior year. 'Give the GrossProfit for the YTD period of the dates from Jan 01 of last year to the EndDate for the prior year.
'Purpose: 'Compare YearOverYear Gross for the same period for the current and past year. 'Compare YTD Gross for the same period for the current and past year.
'Questions to answer:
'What is the CurrentYear? The year of the StartDate =DatePart(DateInterval.Year,Parameters!StartDate.Value)
'What is LastYear? The CurrentYear 1 =Fields!CurrentYear.Value1
'What is the first day of the LastYear's month? =DateSerial(Fields!LastYear.Value,DatePart("m",Parameters!EndDate.Value),01)
'What is the last day of the LastYear's month? =DateAdd("d",1,DateAdd("m",1,Fields!LYFirstDayOfMonth.Value))
'What is the StartDate for last year? Use LastYear and 01/01 for Jan 01 =DateSerial(Fields!LastYear.Value,01,01)
'What is the EndDate for last year? '(it must be the same day as the EndDate only with last year's date; except for leap years in Feb.) 'If the CurrentYear is a leap year and the EndDate is in February and the EndDate is the end of the current month then the EndDate for last year is 28. 'If the CurrentYear is not a leap year and LastYear was a leap year and the EndDate is in February and the EndDate is the end of the current month then the EndDate for last year is 29. 'If the CurrentYear is not a leap year and LastYear is not a leap year then use the regular calculated last day of the month for all months.
The Code(s) From Crystal: CurrentYear: year ({?Ending Date})
LastYear: {@Current Year}1
AS_LYFirstDayOfMonth: Date ({@Last Year}, Month ({?Ending Date}), 01)
AS_LYLastDayOfMonth: dateadd("m", 1, {@AS_LYFirstDayOfMonth})1
This is the code from Crystal Reports that works:
StringVar LYEndMonth; StringVar LYEndDay; StringVar LYEndYear;
StringVar LYEndMonth := ToText(Month({?Ending Date})); StringVar LYEndDay := ToText( (IF Month({?Ending Date}) = 2 and (if Remainder({@Current Year},4)= 0 OR Remainder({@Current Year},400)= 0 THEN TRUE ELSE FALSE ) and day({?Ending Date}) = 29 then Day({@AS_LYLastDayOfMonth}) ELSE IF Month({?Ending Date}) = 2 and (if Remainder({@Last Year},4)= 0 OR Remainder({@Last Year},400)= 0 THEN TRUE ELSE FALSE ) and day({?Ending Date}) = 28 then Day({@AS_LYLastDayOfMonth}) ELSE Day({?Ending Date}))); StringVar LYEndYear := ToText({@Last Year});
Local DATEVAR LYEndDate := date(tonumber(LYEndYear),tonumber(LYEndMonth),tonumber(LYEndDay))
'!!!!!!!!!! code that won't work !!!!!!!!!!!!!
=IIF(
DatePart("m",Parameters!EndDate.Value) = 2 AND iif( cdbl(DatePart("y",Parameters!StartDate.Value)Mod 400)=0,366, iif(cdbl(DatePart("y",Parameters!StartDate.Value)Mod 100)=0,365, iif(cdbl(DatePart("y",Parameters!StartDate.Value)Mod 4)=0,366,365)) ) = 366 AND DatePart("d",Parameters!EndDate.Value) = 29 ,DatePart("d",Fields!LYLastDayOfMonth), IIF( iif( (cdbl(Fields!LastYear.Value)Mod 400)=0,366, iif((cdbl(Fields!LastYear.Value)Mod 100)=0,365, iif((cdbl(Fields!LastYear.Value)Mod 4)=0,366,365)) )=366 AND DatePart("d",Parameters!EndDate.Value) = 28,DatePart("d",Fields!LYLastDayOfMonth),DatePart("d",Parameters!EndDate) ) )




SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 05, 2014 12:36 PM
Points: 39,
Visits: 144


Update: I am getting the following error now Warning 1 [rsRuntimeErrorInExpression] The Value expression for the field ‘LYTD_EndDate’ contains an error: Argument 'DateValue' cannot be converted to type 'Date'.




SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 05, 2014 12:36 PM
Points: 39,
Visits: 144


Update and answer:
Update:
I was missing several .Value items and chose to use dateinterval.{day,month,year} instead of "m" or "d";
as well, instead of just returning an integer to plug into a data building field I used date serializer; Here is the working code, I hope somebody can get use out of my 3day torment.
=DateSerial(Fields!LastYear.Value,
DatePart(dateinterval.month,Parameters!EndDate.Value),
IIF(
DatePart(dateinterval.month,Parameters!EndDate.Value) = 2 AND
iif(
cdbl(DatePart(dateinterval.year,Parameters!StartDate.Value)Mod 400)=0,366,
iif(cdbl(DatePart(dateinterval.year,Parameters!StartDate.Value)Mod 100)=0,365,
iif(cdbl(DatePart(dateinterval.year,Parameters!StartDate.Value)Mod 4)=0,366,365))
) = 366 AND
DatePart(dateinterval.day,Parameters!EndDate.Value) = 29 ,DatePart(dateinterval.day,Fields!LYLastDayOfMonth.Value),
IIF(
iif(
(cdbl(Fields!LastYear.Value)Mod 400)=0,366,
iif((cdbl(Fields!LastYear.Value)Mod 100)=0,365,
iif((cdbl(Fields!LastYear.Value)Mod 4)=0,366,365))
)=366 AND
DatePart(dateinterval.day,Parameters!EndDate.Value) = 28,DatePart(dateinterval.day,Fields!LYLastDayOfMonth.Value),DatePart(dateinterval.day,Parameters!EndDate.Value)
)
)
)



