x

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

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
more ▼

asked Oct 22, 2011 at 10:05 AM in Default

burlingtowngal gravatar image

burlingtowngal
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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)
    )
more ▼

answered Oct 22, 2011 at 10:56 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

You were a little bit quicker with example @ThomasRushton. Btw, any reason for the (1=1) in the WHERE condition?
Oct 22, 2011 at 11:12 AM Pavel Pawlowski
@Pavel thanks. The (1=1) bit I put in to keep the pattern, and to make it absolutely clear that this is the one which retrieves all values. It's not necessary.
Oct 22, 2011 at 11:41 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

In general you have 2 options.

  1. 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.
  2. 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.
more ▼

answered Oct 22, 2011 at 11:03 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840
x554

asked: Oct 22, 2011 at 10:05 AM

Seen: 558 times

Last Updated: Oct 22, 2011 at 10:40 AM