Selection Criteria Parameter double-trouble

[dianemarie]

Hello, for many reports we allow users to select the date range via parameter (?Date Range), but also (via parameter
?Date Type) whether they want the report to run by order date, or invoice date. In Crystal we accomplished this by referring to a formula for DateType in the selection criteria. The formula is @DateType:

If {?Date Type} = "Order" then ordhist.orddate else ordhist.invdate

Then in select expert we say:

{@DateType} in {?Date Range}

Any ideas how I can accomplish this in SSRS? I started playing with Select Case statements but I'm lost (trying to put a parameter within a parameter). I'd be embarrassed to show you the code, I'm so far off. As always, thank you in advance for any guidance.

 

[xlbo]

Parameter in RS: @DateType

Values ordhist.orddate & ordhist.invdate
Labels Order Date & InvoiceDate

Pass parameter to stored proc as @DAteType (may be able to do it in vanilla SQL as well)

SELECT a,b,c, @DateType, d,e,f.....

as long as your VALUE selections in the RS parameter are named the same as your field names then this should work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dianemarie]

Hi Geoff, I created the parameter @DateType as a DateTime with the labels and values. When I tried to select an actual field as the value, I got "report item not linked to a dataset" message. So I entered manually into the value field:

=fields!ordhist.ordrdate

"Order Date" as the label and did the same for the invoice date. Then I dropped this parameter into my Select statement and in the Where statement I put:

WHERE

@DateType >= @StartDate and @DateType <= @EndDate

Not working. The query runs without error but returns no records. So I commented out the Where statment and then it runs OK but the parameter field returns "Order Date" for each record (when Order Date is selected on the parameter). I kind of see how this should work, but how do I connect a report parameter to the dataset, is that where it's going wrong? Or is it my Where statement? (I'm not connecting to a stored procedure, I just have a query on the Data tab trying to do all this. Maybe I should rethink and start with a stored procedure? (The senior developer creates the SPs for the most part.) Thanks again.

 

[xlbo]

? I thought @DateType as determining whether you wanted to use Order dates or Invoice Dates - not the actual dates themselves

You need an @StartDate, @EndDate & @DateType parameters

you will actually need to build a SQL string like:


="Select x,y,z, " & @DateType & "FROM Tables WHERE "
& @DateType & ">=" & @DateStart & " AND " @DateType & "<=" & @DateEnd

...if I have understood your requirement correctly....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MisterMo]

try something like this

declare @DateType varchar(20), @Flag  int, @StartDate datetime, @EndDate datetime
set @DateType = 'DueDatess'
set @StartDate = '2001/01/01'
set @EndDate = '2002/02/02'

if @DateType = 'DueDate' set @Flag = 1 else set @Flag = 2

select PurchaseOrderID, OrderQty,ProductID, @DateType, DueDate as Date, @Flag
from Purchasing.PurchaseOrderDetail
where 1 = @Flag
and DueDate between @StartDate and @EndDate
union 
select PurchaseOrderID, OrderQty,ProductID, @DateType, ModifiedDate as Date, @Flag
from Purchasing.PurchaseOrderDetail
where 2 = @Flag
and ModifiedDate between @StartDate and @EndDate

I have used the AdventureWorks Database as an example but the logic is sound you have a union so that you can have both scripts at the same time but in the end only one will return data

-Mo

If you don't stand for something, you'll fall for anything