You’re offline. This is a read only version of the page.
220
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)
)
)
*This post is locked for comments
Use this expression to find out if the date is in a leap year. If the result equals 366 days, then the year is a leap year, otherwise it isn't. It is simple and works for our reports.
Share
Report
All responses (
Answers (
=IIF(DateDiff(DateInterval.Day,
DateValue("January 1, " & Year(Fields!new_enddateValue.Value)),
DateValue("January 1, " & Year(Fields!new_enddateValue.Value)+1)
) = 366, "Leap Year", "Not Leap Year")
Thank you Sanjay. That would work, except that in the SSRS report the date is not always the same year as GetDate(). Therefore it has to be for a @StartDate and @EndDate type of range. When making the report, I created a Aux. Calendar table to populate all the days for the last ten and next thirty years and I guess I could put a marker in that table; but I would still need a SSRS Expression to tell if the current @EndDate was in a leap year and then make the LastYearEndDate the 28th or 29th accordingly. The entire purpose of the report is to show four time periods; the current (start to end)/ the last year current(last year start to end)/the current year to endDate/ and the last year- year to endDate. In each case I have to assume the report can be run in a Feb. of a leap or non-leap year and need to look back into a leap or non-leap year.
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)
)
)
)
select YEAR(getdate()), case lyflag when 0 then 'Not leap year' else 'Leap year' end yeartype from (
select
case
when year(GETDATE())%400=0 then 1
when year(GETDATE())%100=0 then 0
when year(GETDATE())%4=0 then 1
else 0
end as lyflag) as t
Update:
I am not getting the following error message
Warning 1 [rsRuntimeErrorInExpression] The Value expression for the field ‘LYTD_EndDate’ contains an error: Argument 'DateValue' cannot be converted to type 'Date'.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156