SQLTeam.com | Weblogs | Forums

Ssrs 2008 displays field value incorrectly


#1

In a new ssrs 2008 report that I am working on the following sql works correctly in sql management studio:

select convert(char(08),convert(char(04),(TC.endYear - 1)) + '-' + substring(convert(char(04),TC.endYear),3,2)) as 'Transcript School Year' from [test].[dbo].[TranscriptCourse] TC

The results display like '2015-16' which is what I want.

However from the SSRS report I obtain the value of either 2016 or #ERROR depending on how many columns I displayed. For the run I am going the endYear value does equal 2016. Thus can you tell me how to change the ssrs 2008 report so that the value of '2015-16' is displayed?


#2

If you are getting TC.endYear as a one of the columns in your dataset, and and if your column name is endYear, use an expression like this (or something similar to it)

=CStr(Fields.endYear.Value-1)+"-"+Right(CStr(Fields.endYear.Value),2)

If you are doing the conversion and formatting the string in SQL and returning that as a column in your data set, and if you are using that column to display, I don't know why you are getting an error. You should not.


#3

Thanks your answer worked!