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

avatar 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

avatar image

ThomasRushton ♦♦
39.8k 20 49 52

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

avatar image

Pavel Pawlowski
22.7k 10 15 26

(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.

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:

x2076
x650

asked: Oct 22, 2011 at 10:05 AM

Seen: 682 times

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

Copyright 2016 Redgate Software. Privacy Policy