October 9, 2012

How to Combine Fields Into One Group in SSRS 2008

In SSRS reporting if you are creating reports using Data Views. In the database you are  designing report and  trying to group by State or any other column, but 1 state may be entered a few different ways. (Ex. TX, Texas, Tex. TX.) When you insert a Group Row how could you group all of the same states together in one called TX? 

you can handle the grouping in TSQL with a CASE statement:

SELECT CASE WHEN [State] = 'TX' THEN TX WHEN [State] = 'Tex.' THEN 'TX ?. END AS [State]

Or

SELECT CASE WHEN [State] LIKE 'T%X%' THEN 'TX' END AS [State]

Hope that helps. SSRS can handle the grouping and sorting.

No comments:

Post a Comment