我使用此自定义代码在SSRS中设置一个称为TermCodes的参数:
Public Function setTermDates (ByVal academicYear as String) as String
dim results as String
dim startYear as String = Mid(academicYear, 3, 2) 'Line 70
dim endYear as String = Mid(academicYear, 8, 2)
results = chr(39) & "FS" & startYear & chr(39) & ", " & chr(39) & "SS" & endYear & chr(39) & ", " & chr(39) & "US" & endYear & chr(39)
return results
End Function
@TermCodes的值是“ FS13”,“ SS14”,“ US14”。我正在尝试使用此参数的SQL是:
select
min(StartDate1) FallStartDate,
min(EndDate1) FallEndDate,
min(StartDate2) SpringStartDate,
min(EndDate2) SpringEndDate,
min(StartDate3) SummerStartDate,
min(EndDate3) SummerEndDate
from (
select
Start_Date, End_Date,
'StartDate' + cast(row_number() over(order by Start_Date) as char(1)) StartDates,
'EndDate' + cast(row_number() over(order by Start_Date) as char(1)) EndDates
from Term
where Term_Code in (@TermCodes)
) t
pivot (
max(Start_Date)
for StartDates in(StartDate1,StartDate2,StartDate3)
) sd
pivot (
max(End_Date)
for EndDates in(EndDate1,EndDate2,EndDate3)
) ed
问题在于SQL不返回任何内容。如果我在SQL Server Management Studio中键入此SQL并将@TermCodes替换为上述值,则它将起作用。为什么在SSRS中不起作用?
谢谢!
编辑:我也尝试过使用相同的结果来设置我的参数:
Public Function setTermDates (ByVal academicYear as String) as String()
dim results(0 to 2) as String
dim startYear as String = Left(academicYear, 4)
dim endYear as String = Right(academicYear, 4)
results(0) = "FS" + startYear
results(1) = "SS" + endYear
results(2) = "US" + endYear
return results
End Function
我不相信 这是人为错误。我查看了原始的自定义代码(放在“编辑”下),发现我使用的是整年而不是后两位。新的代码是:
Public Function setTermDates (ByVal academicYear as String) as String()
dim results(0 to 2) as String
dim startYear as String = Mid(academicYear, 3, 2)
dim endYear as String = Mid(academicYear, 8, 2)
results(0) = "FS" + startYear
results(1) = "SS" + endYear
results(2) = "US" + endYear
return results
End Function
我运行了报告,它按应有的方式工作。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句