Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CaliDavid
Starting Member
9 Posts |
 Posted - 2008-03-21 : 03:42:20
|
| Hi, this is a simple question:I have a field called school_year that has 11 values in it as follows: 0 2000-2001 1 2001-2002 2 2002-2003 3 2003-2004 4 2004-2005 5 2005-2006 6 2006-2007 7 2007-2008 8 2008-2009 10 2009-2010When I query this field it returns a value between 0-10 . In my report I need to display this as a text(example: "2006-2007" if 6 was returned). Currently I'm using a Switch function. I was wondering is there is an easier way to do this using a lookup table or something like that.Thanks.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-23 : 12:30:53
|
| What determines the value to be displayed? or do u want to show all values one beneath the other? |
 |
|
|
CaliDavid
Starting Member
9 Posts |
Posted - 2008-03-28 : 18:53:00
|
| When I use the expression =Field!School_Year.value my report shows: 8 for 2007-2008 school year, I want to show the text "2007-2008". Currently I'm using a switch as follows:=Switch(Fields!School_Year.Value=1,"2000-2001",2,"2002-2003",3,"2004-2005") etc...I was wondering if I could enter the text 2000-2001 , 2001-2002, etc. in a table and point to it with the return value fromFields!School_Year.Value, and get the corresponding text.Thanks.. |
|
|
|
jhermiz
3564 Posts |
Posted - 2008-03-28 : 22:31:58
|
quote:
Originally posted by CaliDavid
When I use the expression =Field!School_Year.value my report shows: 8 for 2007-2008 school year, I want to show the text "2007-2008". Currently I'm using a switch as follows:=Switch(Fields!School_Year.Value=1,"2000-2001",2,"2002-2003",3,"2004-2005") etc...I was wondering if I could enter the text 2000-2001 , 2001-2002, etc. in a table and point to it with the return value fromFields!School_Year.Value, and get the corresponding text.Thanks..
Man no offense but that is bad design, sounds like you are using SQL Server as if it were access. There is no need to have a field that stores 0 and a year range in one field. Even if these are seperate fields you really dont need the single digit as your ranges are unique varchar. But if you insist on using this design if there are 2 fields one for the auto number and one for the range just pull back both fields.If it is one field I'd advise seperating this into two fields, or please jsut get rid of the single digit field and use your range as the key.No point in having those 2 fields those, one single field, namely the year range serves as your key.Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
CaliDavid
Starting Member
9 Posts |
Posted - 2008-03-31 : 15:07:53
|
| You're absolutely right. Unfortunately this is not my database. I'm just writing reports for them. Thanks.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-01 : 10:12:46
|
| Or you could simple write a case statement in your sql query statement/stored procedure that provides data for dataset to include the new value to be displayed and show it in report. |
|
|
|
|
|
|
|
|
Displaying Text from a Table: