October 1, 2013 at 11:12 am
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 Year-Over-Year 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.Value-1
'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)
)
)
October 1, 2013 at 12:41 pm
Update: I am getting the following error now
Warning1[rsRuntimeErrorInExpression] The Value expression for the field ‘LYTD_EndDate’ contains an error: Argument 'DateValue' cannot be converted to type 'Date'.
October 1, 2013 at 1:05 pm
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 3-day 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)
)
)
)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply