Geek asks and answers

SSRS - Add a static null value to a parameter request

I am looking for a way to add a value to a parameter whose available values ​​are obtained from a simple query. I just started talking to SSRS today, I hope that I'm not mistaken.

Performing the following procedure from the draft report (randomly, I know):

CREATE PROCEDURE RAW_TIME_VAR
        @EMPLID as varchar(6) = NULL
,       @EMPLNAME as varchar(80) = NULL
,       @CHARGENO as int = NULL
,       @TYPE as varchar(8) = NULL
,       @STARTDATE as date = NULL
,       @ENDDATE as date = NULL
AS
Select * from DBO.RAW_TIME
Where   ([EMPL ID] = @EMPLID OR @EMPLID IS NULL)
AND     ([EMPL NAME] = @EMPLNAME OR @EMPLNAME IS NULL)
AND     ([CHARGE NO] = @CHARGENO OR @CHARGENO IS NULL)
AND     ([CHARGE TYPE] = @TYPE OR @TYPE IS NULL)
AND     [CHARGE DATE] Between @STARTDATE AND @ENDDATE

There are two data sets in the report project (I use VS2008 Business Intelligence Development Studio). One of the above procedures (RAW_TIME_DATASET), and another request from the same view to fill in the employee identifier and names (NAMES):

SELECT DISTINCT [EMPL ID], [EMPL NAME]
FROM RAW_TIME
ORDER BY [EMPL ID]

@EMPLNAME NAMES, NULL, "" combobox. "" UNION, , , NULL. Errr... , .

+3
2

.

report_data, . , . EMPLNAME, , :

enter image description here

, ,

NAMES. , . , - :

union SELECT null as [EMPL ID], 'NULL' as [EMPL NAME]

, , .

( 2 ):

enter image description here

+5

: , , , , . . .

@EMPLNAME, " " (msdn sample). , where-clause :

AND     ([EMPL NAME] IN (@EMPLNAME) OR @EMPLNAME IS NULL)

, SSRS (@EMPLNAME) , , ( "IN @param" () SQL-. MSDN , .

: (), , @EMPLID @EMPLNAME? ID , NAME - . :

Where   ([EMPL ID] IN (@EMPLID) OR @EMPLID IS NULL)
-- AND     ([EMPL NAME] = @EMPLNAME OR @EMPLNAME IS NULL) -- not needed anymore
+1

More articles:


All Articles