Merging two data values into one in ssrs

Question

I am using ssrs to create a report which fetches data from two databases. Now, in the tables of both said databases, I have a column which specifies the region which I have merged using a union statement. In this column, in one database table, the value for one of the regions is defined as "North America" while in the other, it is defined as "North-America". Since they technically have different names, they are appearing as two different values when I use this data field in my report. I thought that I could use the switch function to rename one of them in hopes that they would merge, but that only succeeded in renaming the data value and not merging the data. For more details, I am displaying these regions in a bar chart.

Is there a way in which I can merge just those two data values in the region field into one data value? Thanks in advance!

Answer 1

If the pattern is just spaces replaced by hyphens, you could use REPLACE like so:

SELECT Region
FROM MyTable
UNION ALL 
SELECT REPLACE(Region, '-', ' ') AS Region
FROM OtherDatabase.dbo.OtherTable

Otherwise you might create a lookup table that you use to replace the Region name when you need to. Let's call the table RegionLookup with the fields LookupValue and ReplaceValue. You make an entry in the table to map the regions that need to be replaced, like so:

LookupValue    ReplaceValue
North-America  North America

Now your SQL looks like this:

SELECT Region
FROM MyTable
UNION ALL 
SELECT CASE WHEN RegionLookup.ReplaceValue IS NOT NULL THEN RegionLookup.ReplaceValue ELSE OtherTable.Region END AS Region
FROM OtherDatabase.dbo.OtherTable
LEFT OUTER JOIN OtherDatabase.dbo.RegionLookup ON OtherTable.Region = RegionLookup.LookupValue

When there is an entry in the 'RegionLookup` table for the Region it gets replaced by the replacement value (which should be the same as the one in the first table), otherwise you just use the original region. Now you just need to map the regions that are spelt differently between the tables and you're good to go.