Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Design a table to hold filters for selection criteria Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 5:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:30 PM
Points: 76, Visits: 290
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.
Post #1567740
Posted Wednesday, May 7, 2014 11:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:30 PM
Points: 76, Visits: 290
No replies?

I would appreciate any kind of help on this!
Post #1568620
Posted Wednesday, May 7, 2014 11:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 1,886, Visits: 18,542
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 !
__________________________________________________________________
Post #1568627
Posted Wednesday, June 18, 2014 4:45 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:45 AM
Points: 317, Visits: 823
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.
Post #1583461
Posted Wednesday, June 18, 2014 4:49 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:45 AM
Points: 317, Visits: 823
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.
Post #1583463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse