0

I have a query whereby parameters are being passed in in the format 'GRB','MIN','OSH' and so on. These are called "Divisions". They may be passed in singly or in a list of any number of them. These are used in one of the tables n a report. The problem is that the table I am going to read other divisions are going to have them in numerical format. so GRB = 02, MIN=04 and OSH = 08. There are 12 different values. So in essence, the SSRS report might pass in all these for an "IN" statement. They have to be presented in this manner. I need to find a way to convert all of these alphanumeric codes into their corresponding numeric codes. Main query with alphanumeric:

dbo.TQMNCR.PlantID IN (@PlantID)

Temp table with (hopefully) corresponding numeric values:

ProdTable.DIMENSION2_ in (????????)
rigamonk
  • 1,179
  • 2
  • 17
  • 45
  • Can't you set up the parameter list to show the Name as the letter codes and the Value as the integers? Then you would pass the parameter list using this duplicate question: http://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services – TTeeple Apr 15 '15 at 14:58
  • They need to see the actual name of the plant for the parameter selection. So GRB shows up as "Green Bay, WI". The query that fills the parameter selection is already SELECT PlantName, PlantID. Theres no room for another value in the SSRS parameter values – rigamonk Apr 15 '15 at 15:00
  • Then substitute the full name for the letter codes and still use the Value attribute as integers. You would setup a second dataset to drive the parameter so you wouldn't have to hardcode anything. – TTeeple Apr 15 '15 at 15:03
  • My boss won't let that happen. It has to read the verbiage, not the codes. Good idea though – rigamonk Apr 15 '15 at 15:04
  • I don't understand what the problem is. It would read the verbiage to the people running the report. The integer value gets passed behind the scenes, never visible to anyone. – TTeeple Apr 15 '15 at 15:05
  • The main table needs the GRB etc, not the 02. I'm dealing with a temp table which is still driven off of the division, just in a different format. So i essentially have 2 queries, both using the same parameter(s) in different formats. – rigamonk Apr 15 '15 at 15:09
  • Then you need to join those tables in that second dataset I was talking about and use that query to drive the parameter. – TTeeple Apr 15 '15 at 15:34

1 Answers1

1

Presuming that dbo.TQMNCR has a PlantNumber column for the numeric key, you could use a join like this.

SELECT ...
FROM dbo.TQMNCR AS Source
    INNER JOIN ProdTable AS Mapping
        ON Mapping.DIMENSION2_ = Source.THE_NUMERIC_KEY_COLUMN_YOU_SPEAK_OF
WHERE Mapping.PlantID IN (@PlantID)

Alternatively, if you don't want to use a permanent table to map them, you can also use a common table expression (numerous other options).

WITH Mapping_CTE AS (
    SELECT 'GRB' AS PlantID, '02' AS PlantNumber
    UNION ALL
    SELECT 'MIN' AS PlantID, '04' AS PlantNumber
    UNION ALL
    ...
)
SELECT ...
FROM dbo.TQMNCR AS Source
    INNER JOIN Mapping_CTE AS Mapping
        ON Mapping.PlantNumber = Source.THE_NUMERIC_KEY_COLUMN_YOU_SPEAK_OF
WHERE Mapping.PlantID IN (@PlantID)
Biscuits
  • 1,767
  • 1
  • 14
  • 22
  • No, unfortunately Prodtable has no PlantNumber. PlantNumbers only used in the TQMNCR Table – rigamonk Apr 15 '15 at 15:29
  • You need a table that maps the codes you're describing (GRB, MIN, OSH, etc) with the numbers that you're talking about (02, 04, 08, etc). Your question leads me to thinking that the ProdTable table is (hopefully) this mapping table, which I've used to illustrate my proposed solution of joining to a mapping table. If that table is insufficient, you need a mapping table source. – Biscuits Apr 15 '15 at 15:47
  • I've updated the answer to also show how the mapping can be done using a common table expression. – Biscuits Apr 15 '15 at 15:55