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

Posted on 2009-02-23
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
    LVL 42

    Expert Comment

    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


    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

    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

    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now