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

Posted on 2009-02-23
Medium Priority
Last Modified: 2012-05-06
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.

Question by:mtaylor29902
  • 2
  • 2
LVL 42

Expert Comment

ID: 23715988
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


Author Comment

ID: 23716266

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.
LVL 42

Accepted Solution

dqmq earned 1500 total points
ID: 23716427
Yes, I assumed you have a table that assigns dept abbreviations to the dept numbers.  If not, then it seems like a very good idea since the abbreviations are apparently important enough to show up on reports.  My hunch is that such a table would be useful in many respects.  

Short of that, you can use a case structure:

Select DeptNo, DeptNo + '-' +
    Case DeptNo
          When 15050 Then MIS
          When xxxxxx Then xxx
          ...repeat for each department
   End as Dept
From YourTable

If you cannot create a table, then at least encapsulate the case statement in a UDF or a view so that the mappings are maintained in one place.

Author Closing Comment

ID: 31550319
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.



Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to increase the row limit in Jasper Server.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question