SSRS Parameters - Depending one 1 parameter selection the other parameter needs to point to 2 different sets of data

Question

Post reply

SSRS Parameters - Depending one 1 parameter selection the other parameter needs to point to 2 different sets of data

sabah_seattle

SSC Journeyman

Points: 99
More actions
June 27, 2013 at 1:23 pm

#275607

Hello,
Please help. This is urgent. I have a sales report dataset where I have items sold, ship date and destination date. Ship date is when the item is shipped from warehouse and destination date is when it reaches the destination. In the report dataset I have 2 columns - ship date and destination date. The requirement is -
Have 3 parameters - WhichDate (ShipDate or DestinatioDate), StartDate (User select start date) and EndDate (User selects end date). If the user selects ShipDate from the first parameter then the StartDate and ShipDate entered in 2nd and 3rd parameters should filter on ShipDate. If the user selects DestinationDate from the first parameter then the StartDate and ShipDate entered in 2nd and 3rd parameters should filter on DestinationDate.
I am struggling with it. Any help is appreciated.
Thanks.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

if you are filtering in the SQL statement, why not just use an IF and have two queries, one for each filter?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

tomgough SSC-Addicted Points: 432 More actions · Answer 2

Hope I understand your question correctly: you want to filter a query by either ShipDate or DestinationDate depending on the first parameter selected?

If so, I'd create 1 dataset with something like this in the where clause:

case

when @WhichDate='Shipdate' and Shipdate between @StartDate and @EndDate then 1

when @WhichDate='DestinationDate' and DestinationDate between @StartDate and @EndDate then 1

else 0

end = 1

A little unorthodox maybe, but it should work (although I'd be wary of using it on very big data sets, I'm not sure how efficiently the optimiser will select indexes with this)