May 5, 2014 at 5:17 pm
I have an ordering database with several tables that store data of orders belonging to a wide variety of clients. There is a generic report that I need to run which outputs the same data elements. However the criteria to select these orders will vary widely between each client. For e.g.,
i) for client# 1 it could be all orders that are still open after 30 days of placing an order
(
OrderStatus = 'Open'
AND
GetDate() - OrderCreationDate >= 30
)
ii) for client# 2 it could be all orders that have been completed 60 days or earlier
(
OrderStatus = 'Completed'
AND
GetDate() - OrderCompletedDate >= 60
)
iii) for client# 3 it could be a combination of different things (all orders in West Region that are in hold status for more than 10 days + all orders in Eastern Region that are in shipping and are expected to be delivered in the next 2 days + all completed orders for the rest of the regions).
(
OrderRegion = 'West'
AND
OrderStatus = 'Hold'
AND
GetDate() - OrderHoldDate >= 10
)
OR
(
OrderRegion = 'East'
AND
OrderStatus = 'Shipping'
AND
ExpectedDeliveryDate - GetDate() BETWEEN 0 AND 2
)
OR
(
OrderRegion NOT IN ('East', 'West')
AND
OrderStatus = 'Completed'
)
I want to have a stored procedure that selects all data and dynamically attach the where condition at the end for filtering. This way I wouldn't have to worry about any additions/changes that are made to the selection criteria. I can build an interface for admins who can use the UI to maintain the selection criteria and not worry about any code changes to accommodate it. I would like to design a table that holds this criteria. At this point in time, I am thinking of using key value pairs (Column Name, Column Value) but I am not sure how to implement multiple logical operators. I am pretty sure this is not a unique requirement, so hoping some of you would have already implemented such cases. Can you please throw in your ideas for an efficient and felixible implementation of this requirement?
This is more of a design questions, so I did not have any code or sample data set to share.
Thanks in advance.
May 7, 2014 at 11:22 am
No replies? 🙁
I would appreciate any kind of help on this!
May 7, 2014 at 11:46 am
though this doesn't directly answer your method.....I think that the following
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
may provide some alternative ideas......
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 18, 2014 at 4:45 pm
My advise here is to build a more general stored procedure that brings in all the columns you need at a granular (lowest common factor) level. I assume you mean to create a SSRS report. When client one gets selected in the report parameter control for the client... you could assign it default values for the start day and other parameters. Although I've never done this it seems that it would use the functionality of cascading parameters.
----------------------------------------------------
June 18, 2014 at 4:49 pm
You would need a helper dataset query that went something like :IF (@client=1)
begin
select @datefrom = dateadd(m, -6, getdate() )
You pass the results to the master dataset which would do something like
EXEC myProc @client @fromdate.This way you can use the procedure for ad-hoc purposes outside of the ssrs report. I would advise against dynamice SQL. Someone could add a command to drop a database at the end of a text field where you would type in the client. No need to have that risk.
----------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply