asked on

How to match MS SQL query value to a list of String Constants

Hello Experts,

I'm converting a series of payroll reports from IQ Objects (Informix) to MS SQL Reporting Services with SQL Server 2005. The SQL Query reads a data element representing a Department Number. I need to take this value and concatenate a string value representing the department number and name. For example, the returned value for my department is 15060. I want to display this in the report as 15060-MIS. I'm using MS Business Intelligence Studio as the development tool. Any Help with the source code is greatly appreciated.

MikeT

dqmq

Instead of:
Select DeptNo... from Yourtable

Try, something like:
Select DeptName + '-' + cast(DeptNo as Varchar) as DeptNo...
from yourtable inner join depttable on yourtable.deptno = depttable.deptno

mtaylor29902

ASKER

dqmg,

Your solution assumes that I have a "yourtable" to join to, which does take the possibilities in a different direction, but I am looking for something more benign. I'm new to MS SRS, but want to use something Static in the Expression Builder of the TextBox properties on the SRS Report. I have about 50 Departments, whose numbers will "never" change. So, I envision an expression using a block of if, then, else, or something like that against a static list of variables.. unless this is just not the best way to tackle the problem. I'm open to any practical approach.

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mtaylor29902

ASKER

OK, I'm going to go with it. You make a good point about it being important enough for a report that is should be in a table.

Thanks,

MikeT