August 5, 2014 at 3:46 am
Hi,
I need to custom sort a list of countries populated through SQL code. At the moment, it follows a A-Z sort as follows:
AUSTRIA
BALTIC
BELGIUM
BUFFER
CZECH REPUBLIC
DENMARK
FINLAND
FRANCE
GERMANY
IRELAND
NETHERLANDS
NORWAY
PORTUGAL
SPAIN
SWEDEN
SWITZERLAND
But I need the sort in the following manner (include BUFFER and BALTIC at the end):
AUSTRIA
BELGIUM
CZECH REPUBLIC
DENMARK
FINLAND
FRANCE
GERMANY
IRELAND
NETHERLANDS
NORWAY
PORTUGAL
SPAIN
SWEDEN
SWITZERLAND
BALTIC
BUFFER
Could someone please advice if this is possible and how this can be accomplished ?
I really do not wish to change the SQL code as it already too complex and so was wondering if this can be done at the SSRS level itself.
The SQL code which runs the report is below:
----------------------
SELECT DISTINCT
mgd.BOOKING_NUM,
CASE WHEN MC.COUNTRY_DSC = 'BELGIUM' THEN 'BELGIUM' WHEN MC.COUNTRY_DSC = 'AUSTRIA' THEN 'AUSTRIA' WHEN MC.COUNTRY_DSC = 'GERMANY' THEN
'GERMANY' WHEN MC.COUNTRY_DSC = 'FRANCE' THEN 'FRANCE' WHEN MC.COUNTRY_DSC = 'NETHERLANDS' THEN 'NETHERLANDS' WHEN MC.COUNTRY_DSC
= 'CZECH REPUBLIC' THEN 'CZECH REPUBLIC' WHEN MC.COUNTRY_DSC = 'SWITZERLAND' THEN 'SWITZERLAND' WHEN MC.COUNTRY_DSC = 'PORTUGAL' THEN
'PORTUGAL' WHEN MC.COUNTRY_DSC = 'SPAIN' THEN 'SPAIN' WHEN MC.COUNTRY_DSC = 'IRELAND' THEN 'IRELAND' WHEN MC.COUNTRY_DSC = 'SWEDEN' THEN
'SWEDEN' WHEN MC.COUNTRY_DSC = 'FINLAND' THEN 'FINLAND' WHEN MC.COUNTRY_DSC = 'DENMARK' THEN 'DENMARK' WHEN MC.COUNTRY_DSC = 'NORWAY'
THEN 'NORWAY' WHEN MC.COUNTRY_CD IN ('LT', 'LV', 'EE', 'BY', 'SK', 'RU') THEN 'BALTIC' WHEN MC.COUNTRY_DSC NOT IN ('BELGIUM', 'AUSTRIA', 'GERMANY',
'NETHERLANDS', 'CZECH REPUBLIC', 'SWITZERLAND', 'PORTUGAL', 'SPAIN', 'IRELAND', 'SWEDEN', 'FINLAND', 'DENMARK', 'NORWAY') AND
MC.COUNTRY_CD NOT IN ('LT', 'LV', 'EE', 'BY', 'SK', 'RU') THEN 'BUFFER' END AS COUNTRY_DSC, CASE WHEN POL_LOCATION_CD IN ('DKAAR', 'DKCPH', 'FIHEL',
'FIKTK', 'IEORK', 'IEDUB', 'NLRTM', 'PTLEI', 'PTOPO', 'PTLIS', 'ESBIO', 'ESVGO', 'SEGOT', 'SEHEL', 'GBBEL') THEN 'NLRTM' WHEN POL_LOCATION_CD IN ('DKFRC',
'FIRAU', 'FIOUL', 'DEBRV', 'NOAES', 'NOBGO', 'NOFRK', 'NOHAL', 'NOHAU', 'NOKRS', 'NOKSU', 'NOLAR', 'NOMSS', 'NOOSL', 'NOTAE', 'PLGDY', 'SEGVX', 'SEMMA',
'SENRK', 'SESTO', 'DEHAM')
THEN 'DEBRV' WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR' WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH' ELSE POL_LOCATION_CD END AS POL_LOCATION_CD,
mgd.TEU, mcc.TYPE_CD, mbi.VESSEL_CD, mbi.VOYAGE_CD
FROM MG_BOOKING_DATASET AS mgd INNER JOIN
MG_BOOKING_ITINERARY AS mbi ON mgd.BOOKING_ID = mbi.BOOKING_ID AND mbi.VESSEL_CD IN (@vessel) AND mbi.VOYAGE_CD IN (@voyage)
LEFT OUTER JOIN
MG_OFFICE AS mo ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P') RIGHT OUTER JOIN
MG_COUNTRY AS mc ON mo.COUNTRY_CD = mc.COUNTRY_CD LEFT OUTER JOIN
MG_BOOKING_EQUIPMENT_REQ AS ber ON mgd.BOOKING_ID = ber.BOOKING_ID LEFT OUTER JOIN
MG_CONTAINER_CODE AS mcc ON ber.KL_EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
ORDER BY COUNTRY_DSC
----------------------
Thanks.
August 5, 2014 at 12:17 pm
In SSRS you might be able to do it by just adding a textbox below the detail row with the fixed values
BALTIC
BUFFER
But you would still need to exclude them from your regular result set.
Here is a way to relatively simply modify your SQL query and get your sort. Add this to the SELECT:
,CASE WHEN COUNTRY_DSC= 'BALTIC' THEN 500
WHEN COUNTRY_DSC= 'BUFFER' THEN 501
ELSE ROW_NUMBER() OVER(ORDER BY COUNTRY_DSC)
END AS sort
Then change the ORDER to
ORDER BY sort
August 5, 2014 at 11:16 pm
pwalter83 (8/5/2014)
Hi,I need to custom sort a list of countries populated through SQL code. At the moment, it follows a A-Z sort as follows:
AUSTRIA
BALTIC
BELGIUM
BUFFER
CZECH REPUBLIC
DENMARK
FINLAND
FRANCE
GERMANY
IRELAND
NETHERLANDS
NORWAY
PORTUGAL
SPAIN
SWEDEN
SWITZERLAND
But I need the sort in the following manner (include BUFFER and BALTIC at the end):
AUSTRIA
BELGIUM
CZECH REPUBLIC
DENMARK
FINLAND
FRANCE
GERMANY
IRELAND
NETHERLANDS
NORWAY
PORTUGAL
SPAIN
SWEDEN
SWITZERLAND
BALTIC
BUFFER
Could someone please advice if this is possible and how this can be accomplished ?
I really do not wish to change the SQL code as it already too complex and so was wondering if this can be done at the SSRS level itself.
The SQL code which runs the report is below:
----------------------
SELECT DISTINCT
mgd.BOOKING_NUM,
CASE WHEN MC.COUNTRY_DSC = 'BELGIUM' THEN 'BELGIUM' WHEN MC.COUNTRY_DSC = 'AUSTRIA' THEN 'AUSTRIA' WHEN MC.COUNTRY_DSC = 'GERMANY' THEN
'GERMANY' WHEN MC.COUNTRY_DSC = 'FRANCE' THEN 'FRANCE' WHEN MC.COUNTRY_DSC = 'NETHERLANDS' THEN 'NETHERLANDS' WHEN MC.COUNTRY_DSC
= 'CZECH REPUBLIC' THEN 'CZECH REPUBLIC' WHEN MC.COUNTRY_DSC = 'SWITZERLAND' THEN 'SWITZERLAND' WHEN MC.COUNTRY_DSC = 'PORTUGAL' THEN
'PORTUGAL' WHEN MC.COUNTRY_DSC = 'SPAIN' THEN 'SPAIN' WHEN MC.COUNTRY_DSC = 'IRELAND' THEN 'IRELAND' WHEN MC.COUNTRY_DSC = 'SWEDEN' THEN
'SWEDEN' WHEN MC.COUNTRY_DSC = 'FINLAND' THEN 'FINLAND' WHEN MC.COUNTRY_DSC = 'DENMARK' THEN 'DENMARK' WHEN MC.COUNTRY_DSC = 'NORWAY'
THEN 'NORWAY' WHEN MC.COUNTRY_CD IN ('LT', 'LV', 'EE', 'BY', 'SK', 'RU') THEN 'BALTIC' WHEN MC.COUNTRY_DSC NOT IN ('BELGIUM', 'AUSTRIA', 'GERMANY',
'NETHERLANDS', 'CZECH REPUBLIC', 'SWITZERLAND', 'PORTUGAL', 'SPAIN', 'IRELAND', 'SWEDEN', 'FINLAND', 'DENMARK', 'NORWAY') AND
MC.COUNTRY_CD NOT IN ('LT', 'LV', 'EE', 'BY', 'SK', 'RU') THEN 'BUFFER' END AS COUNTRY_DSC, CASE WHEN POL_LOCATION_CD IN ('DKAAR', 'DKCPH', 'FIHEL',
'FIKTK', 'IEORK', 'IEDUB', 'NLRTM', 'PTLEI', 'PTOPO', 'PTLIS', 'ESBIO', 'ESVGO', 'SEGOT', 'SEHEL', 'GBBEL') THEN 'NLRTM' WHEN POL_LOCATION_CD IN ('DKFRC',
'FIRAU', 'FIOUL', 'DEBRV', 'NOAES', 'NOBGO', 'NOFRK', 'NOHAL', 'NOHAU', 'NOKRS', 'NOKSU', 'NOLAR', 'NOMSS', 'NOOSL', 'NOTAE', 'PLGDY', 'SEGVX', 'SEMMA',
'SENRK', 'SESTO', 'DEHAM')
THEN 'DEBRV' WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR' WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH' ELSE POL_LOCATION_CD END AS POL_LOCATION_CD,
mgd.TEU, mcc.TYPE_CD, mbi.VESSEL_CD, mbi.VOYAGE_CD
FROM MG_BOOKING_DATASET AS mgd INNER JOIN
MG_BOOKING_ITINERARY AS mbi ON mgd.BOOKING_ID = mbi.BOOKING_ID AND mbi.VESSEL_CD IN (@vessel) AND mbi.VOYAGE_CD IN (@voyage)
LEFT OUTER JOIN
MG_OFFICE AS mo ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P') RIGHT OUTER JOIN
MG_COUNTRY AS mc ON mo.COUNTRY_CD = mc.COUNTRY_CD LEFT OUTER JOIN
MG_BOOKING_EQUIPMENT_REQ AS ber ON mgd.BOOKING_ID = ber.BOOKING_ID LEFT OUTER JOIN
MG_CONTAINER_CODE AS mcc ON ber.KL_EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
ORDER BY COUNTRY_DSC
----------------------
Thanks.
Doing what you want is pretty easy in SSRS.
In Report Designer, go to the Sort option for either the TABLIX or the group and click "Add" twice.
For the first sort, click on "fx" towards the end of the line and enter
=IIF(Fields!COUNTRY_DSC.Value="BALTIC" OR Fields!COUNTRY_DSC.Value="BUFFER", 2, 1)
For the second sort, select "COUNTRY_DSC" from the drop down list.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply