asked
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
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=` 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 ... 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) )
You were a little bit quicker with example @ThomasRushton. Btw, any reason for the `(1=1)` in the `WHERE` condition?
@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.
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.
