I need HELP getting the ALL Report choice in the drop down to run the Report for ALL

Question

I'm pulling from Table A which has 4 Transaction Statuses... '03', '05', '06', & '07' I've defined a dataset which pulls them...

SELECT 'ALL' AS label, NULL AS value, 0 AS iordr
UNION ALL
SELECT '03' AS label, '03' AS value, 1 AS iordr
UNION ALL    
SELECT '05' AS label, '05' AS value, 2 AS iordr
UNION ALL
SELECT '06' AS label, '06' AS value, 3 AS iordr
UNION ALL
SELECT '07' AS label, '07' AS value, 4 AS iordr
ORDER BY iordr

But when I try to define the Parameter, when I select ALL from the drop down menu of the report, it doesn't pull '03', '05', '06' and '07' - in fact, it pulls NOTHING... how do I make it so that the 'ALL' choice will pull ALL statuses in one report? When I select any of the individual statuses, it runs the report just fine... but ALL doesn't get the report to run it for ALL statuses Report Parameter Properties:

General
Data Type: Text; Allow null Value
Select parameter visibility: Visible
Available Values:
Select from one of the following options:
Get values from a query
Dataset (the one defined above)
Value field: value
Label field: label
Default Values:
Select from one of the following options: No default value

Answer 1

How are you passing the parameter to your report and generating the report data?

My assumption, based on what you've given, is that your report data is coming from a stored procedure, called by a statement along the lines of exec sp_GetReport @Param=<foo> where foo comes from your list of values above.

In which case, I would expect your stored procedure to contain a SELECT statement with a WHERE clause such as (assuming your variable is @foo):

...WHERE 
    ( ((@foo IS NULL) AND (1=1) )
       OR ((@foo = '03') AND (TransactionStatus = '03'))
       OR ((@foo = '05') AND (TransactionStatus = '05'))
       OR ((@foo = '06') AND (TransactionStatus = '06'))
       OR ((@foo = '07') AND (TransactionStatus = '07'))
    )
    AND ... <rest of your selection criteria here>

For example:

DECLARE @foo CHAR(2)
SELECT @foo = '07' -- comment out this line to retrieve all data

DECLARE @SampleData TABLE (TransactionStatus CHAR(2) NOT NULL, Data1 INT)

INSERT INTO @SampleData SELECT '03', 3 UNION ALL SELECT '04', 4 UNION ALL SELECT '05',5 UNION ALL SELECT '06', 6 UNION ALL SELECT '07',7 UNION ALL SELECT '08',8

SELECT * FROM @SampleData 
WHERE 
    ( ((@foo IS NULL) AND (1=1) )
       OR ((@foo = '03') AND (TransactionStatus = '03'))
       OR ((@foo = '05') AND (TransactionStatus = '05'))
       OR ((@foo = '06') AND (TransactionStatus = '06'))
       OR ((@foo = '07') AND (TransactionStatus = '07'))
    )

If you're confident that the TransactionStatus will always exactly match the parameter passed, then you can replace the WHERE clause with:

SELECT * FROM @SampleData 
WHERE 
    ( ((@foo IS NULL) AND (1=1) )
        OR (@foo = TransactionStatus)
    )

Answer 2

0

In general you have 2 options.

Use your current implementation where you have NULL as the value. Then you have to handle the NULL value in a query which process the input parameter. Something like WHERE (aField = @value OR @value IS NULL). The @value variable contains the value from your Drop Down.
Define the value for 'ALL' as comma separated list of all the values. Then you will have to process the list in the query.

The option 1 is better from my point of view. So you only need to handle the NULL passed in the query.

answered Oct 22 '11 at 11:03 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

(comments are locked)

I need HELP getting the ALL Report choice in the drop down to run the Report for ALL

Follow this question

Related Questions