CODEWITHSUNDEEP
sqlreporting-services
Imanelek
Imanelek

Reputation: 17

SSRS multiple Optional parameter

I'm having a hard time figuring out how to make some parameters required and some others optional. when i insert the value of the optional parametre the result returned it not correct for example i want that the query show me all results with MNT equal the number inser by the user

Select DISTINCT [AG].[dbo].SDG_E_FACT.FICHE,
            concat(SDG_T_SERVICE.ECO, ' - ', [AG].[dbo].[SDG_T_SERVICE].NOM_USUEL) As ÉCOLE,
            concat([AG].[dbo].[SDG_E_ELE].NOM, ', ', SDG_E_ELE.PNOM) As NOM,
            convert(varchar(10), SDG_E_FACT.DATE_ANN, 120)As 'DATE ANN',
            SDG_E_FACT.MNT , SDG_E_FACT.RAISON_ANNUL,
            SDG_E_FACT.ANNEE

FROM   SDG_T_SERVICE INNER JOIN ((SDG_E_FACT INNER JOIN SDG_E_ELE ON SDG_E_FACT.FICHE = SDG_E_ELE.FICHE) INNER JOIN SDG_E_DAN ON SDG_E_ELE.FICHE = SDG_E_DAN.FICHE) ON SDG_T_SERVICE.ID_SDG = SDG_E_FACT.ID_SDG  

WHERE  ( ((SDG_E_FACT.DATE_ANN) Is Not Null) AND ((SDG_E_FACT.MNT)>0) and SDG_E_FACT.ANNEE = @Annee AND SDG_T_SERVICE.ECO = @ÉCOLE ) And ( (SDG_E_FACT.ANNEE in (@Annee)) or ([AG].[dbo].SDG_E_FACT.FICHE in (@FICHE) or [SDG_E_ELE].NOM in (@Nom) or SDG_E_ELE.PNOM in (@Prénom) or  SDG_E_FACT.MNT in (@Montant) or SDG_E_FACT.DATE_ANN in (@Date_de) or SDG_E_FACT.DATE_ANN in (@a) ))

Order by SDG_E_FACT.FICHE,SDG_E_FACT.MNT , SDG_E_FACT.RAISON_ANNUL

Upvotes: 0

Views: 362

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

When making a parameter optional, I add a OR condition for the parameter when it equals NULL. 

WHERE  ( (SDG_E_FACT.MNT = @MNT OR @MNT IS NULL)

This way the criteria will match when MNT is equal to @MNT OR is @MNT is NULL.

So your query would look like: 

SELECT DISTINCT [AG].[DBO].SDG_E_FACT.FICHE,
            CONCAT(SDG_T_SERVICE.ECO, ' - ', [AG].[dbo].[SDG_T_SERVICE].NOM_USUEL) AS ÉCOLE,
            CONCAT([AG].[dbo].[SDG_E_ELE].NOM, ', ', SDG_E_ELE.PNOM) AS NOM,
            CONVERT(VARCHAR(10), SDG_E_FACT.DATE_ANN, 120) AS 'DATE ANN',
            SDG_E_FACT.MNT , SDG_E_FACT.RAISON_ANNUL,
            SDG_E_FACT.ANNEE
FROM   SDG_T_SERVICE 
INNER JOIN ((SDG_E_FACT 
                INNER JOIN SDG_E_ELE ON SDG_E_FACT.FICHE = SDG_E_ELE.FICHE) 
            INNER JOIN SDG_E_DAN ON SDG_E_ELE.FICHE = SDG_E_DAN.FICHE
            ) ON SDG_T_SERVICE.ID_SDG = SDG_E_FACT.ID_SDG  
WHERE  ( (SDG_E_FACT.MNT = ISNULL(@MNT, -1) OR @MNT IS NULL) 
            AND SDG_E_FACT.DATE_ANN IS NOT NULL 
            AND SDG_E_FACT.ANNEE = @Annee 
            AND SDG_T_SERVICE.ECO = @ÉCOLE ) 
    AND ( SDG_E_FACT.ANNEE IN (@Annee) 
            OR (AG.dbo.SDG_E_FACT.FICHE IN (@FICHE) 
                OR SDG_E_ELE.NOM IN (@Nom) 
                OR SDG_E_ELE.PNOM IN (@Prénom) 
                OR SDG_E_FACT.MNT IN (@Montant) 
                OR SDG_E_FACT.DATE_ANN IN (@Date_de) 
                OR SDG_E_FACT.DATE_ANN IN (@a) ))
ORDER BY SDG_E_FACT.FICHE,SDG_E_FACT.MNT , SDG_E_FACT.RAISON_ANNUL

Upvotes: 2

Related Questions