Andy K Andy K - 2 months ago 7
SQL Question

SSRS - Empty value

I have a parameter

@Destinataire
in SSRS that has a list of values plus an empty string

enter image description here

I've created a query that I set as available values, which gives me the drop down list

SELECT code_name FROM tableA UNION ALL SELECT ''


When running the reports with the empty string, I have no results

enter image description here

I tried to set the parameter as text box and it does not do anything too

Yet when running the sql query I'm using to run this report, things are fine as I have all my rows retrieved (see query below)

DECLARE @DateCmt DATE = '05/09/2015',
@DateFin DATE = '05/09/2016',
@Restriction INT = 1,
@Destinataire VARCHAR(5) = ''
--
;

--SELECT @DateCmt,@DateFin

SELECT DISTINCT
CFE_EDI.IU_LIASSE
,CFE_EDI.ETAT
,CFE_EDI.DATHRMAJ -- nouveau
,CFE_EDI.ESP_APPLI
,CFE_EDI.NOM_RS
,PARTENAIRES.LIBEL
,PARTENAIRES.CODE_INSEE
,CFE_EDI.DATHR_ENV -- nouveau
,CFE_EDI.DATHR_MEF -- nouveau
,CFE_EDI.DATHR_PRE -- nouveau
,CFE_EDI.DATHR_OUV -- nouveau
--,CFE_EDI.DATEHR_DEPOT-- mettre l'heure
,CFE_EDI.GESTDEL
--,CFE_SERVICE_DEST.IU_DEST
--,CFE_SERVICE.IU_LIASSE
,CASE WHEN CFE_EDI.ETAT = 'MEF' THEN 'En Attente le'
WHEN CFE_EDI.ETAT = 'PRE' THEN 'Préparé le'
WHEN CFE_EDI.ETAT = 'ENV' THEN 'Envoyé le'
WHEN CFE_EDI.ETAT = 'OUV' THEN 'Réceptionné le'
WHEN CFE_EDI.ETAT = 'NRM' THEN 'Non remis le'
WHEN CFE_EDI.ETAT = 'NAQ' THEN 'Non acquitté le'
END AS ChampEtat
,CASE WHEN CFE_EDI.ETAT = 'OUV' THEN 'Date d''envoi : ' + CONVERT(VARCHAR,CFE_EDI.DATHR_ENV,103)
END AS Date_Envoi,
CASE
WHEN CFE_EDI.ETAT='MEF' THEN CONVERT(VARCHAR,CFE_EDI.DATHR_MEF,103)
WHEN CFE_EDI.ETAT='PRE' THEN CONVERT(VARCHAR,CFE_EDI.DATHR_PRE,103)
WHEN CFE_EDI.ETAT='ENV' THEN CONVERT(VARCHAR,CFE_EDI.DATHR_ENV,103)
WHEN CFE_EDI.ETAT='OUV' THEN CONVERT(VARCHAR,CFE_EDI.DATHR_OUV,103)
ELSE CONVERT(VARCHAR,CFE_EDI.DATHR_DEPOT,103) END AS DateMaj ,
CASE
WHEN CFE_EDI.ETAT='MEF' then CONVERT(VARCHAR,CFE_EDI.DATHR_MEF,108)
WHEN CFE_EDI.ETAT='PRE' then CONVERT(VARCHAR,CFE_EDI.DATHR_PRE,108)
WHEN CFE_EDI.ETAT='ENV' then CONVERT(VARCHAR,CFE_EDI.DATHR_ENV,108)
WHEN CFE_EDI.ETAT='OUV' then CONVERT(VARCHAR,CFE_EDI.DATHR_OUV,108)
ELSE CONVERT(VARCHAR,CFE_EDI.DATHR_DEPOT,108) END AS HeureMaj,
PARTENAIRES.LIBEL + '(' + CFE_EDI.CODE_INSEE + ')' AS LibelDestinataire
--,CASE WHEN @Restriction = 1 THEN '1'
-- WHEN @Restriction = 0 THEN '0' END AS Restriction
,CASE WHEN @DateCmt != @DateFin AND @DateCmt < @DateFin THEN 'Diffusion Xml du ' + CONVERT(VARCHAR,(@DateCmt),103) + ' au ' + CONVERT(VARCHAR,(@DateFin),103) ELSE
'Diffusion EDI Xml du ' + CONVERT(VARCHAR,@DateCmt,103) END AS Plage_Diffusion
-- INTO
FROM
(PARTENAIRES
INNER JOIN dbo.CFE_EDI ON PARTENAIRES.CODE_INSEE = CFE_EDI.CODE_INSEE)
INNER JOIN dbo.CFE_SERVICE ON CFE_EDI.IU_LIASSE = CFE_SERVICE.IU_LIASSE
INNER JOIN dbo.CFE_SERVICE_DEST ON (PARTENAIRES.IU_PART = CFE_SERVICE_DEST.IU_PART_CFE)
WHERE
case when @Restriction = 1
then case when CFE_EDI.ETAT in('ENV','OUV') then 1 else 0 end
when @Restriction = 0
then case when CFE_EDI.ETAT not in('ENV','OUV') then 1 else 0 end
else case when CFE_EDI.ETAT <> '' then 1 else 0 end
end = 1
AND
CFE_EDI.CODE_INSEE IS NOT NULL AND CFE_EDI.CODE_INSEE != ''
AND CASE --WHEN CFE_EDI.CODE_INSEE IS NOT NULL AND CFE_EDI.CODE_INSEE !=''
--THEN CASE
WHEN @Destinataire != '' AND (@Destinataire) IS NOT NULL
THEN CASE WHEN CFE_EDI.CODE_INSEE = @Destinataire THEN 1 ELSE 0 END
ELSE CASE WHEN CFE_EDI.CODE_INSEE = PARTENAIRES.CODE_INSEE
AND cfe_edi.dathrmaj > @DateCmt AND cfe_edi.dathrmaj < @DateFin
AND CFE_EDI.GESTDEL = '1' THEN 1 ELSE 0 END
END = 1


First question would be to know if there is way to setup the parameter without using my stupid trick.

Second question is why the query with parameter with an empty string does the trick and once you use SSRS, nothing.

Thanks in advance for your help

Update I tried to set the
WHEN LEN(@Destinataire) > 0
with the
@Destinataire = ''
but no luck on that one.

Update 2 My aim now is to have a solution that will retrieve all the datas, in case the
@Destinataire
is equal to '' or
NULL
. However, thinking about it, this solution is equivalent to having all the values populated in
@Destinataire
. So one way or another, I would say.

Final update I've recreated everything from scratch and oh! magic, the grouping or the everything option worked as wish. I still don't know what was wrong but I'm fine with the results. Many thanks for your help and support.

Answer

looking at your query and where you pass @Destinataire you should be able to pass a null value according to the where clause and get the same effect as passing ''

 CASE --WHEN CFE_EDI.CODE_INSEE IS NOT NULL AND CFE_EDI.CODE_INSEE !=''
       --THEN CASE 
       WHEN @Destinataire != '' AND (@Destinataire) IS NOT NULL
          THEN CASE WHEN CFE_EDI.CODE_INSEE = @Destinataire THEN 1 ELSE 0 END
       ELSE CASE WHEN CFE_EDI.CODE_INSEE = PARTENAIRES.CODE_INSEE 
                AND cfe_edi.dathrmaj > @DateCmt AND cfe_edi.dathrmaj < @DateFin 
                AND CFE_EDI.GESTDEL = '1' THEN 1 ELSE 0 END  
       END = 1

I would try to just set that particular paramter to allow nulls in the ssrs report which can be found as a checkbox in your parameter settings window.