Scenario: Lets say I have several tables for a software ticketing system: Developer, Supervisor, TicketType, TicketStatus and a Ticket table to join them on. Assume I have done my inner joins and now have a complete table listing all assigned Ticket info.
Id|TicketName|TicketStatus|Developer|Supervisor|DateAssigned| DateDue | ------------------------------------------------------------------------------ 1 | Report | Open | Josh | Bob | 20130702 | 20130705 2 | App | Pending | Brian | Bob | 20130701 | 20130802 3 | App | Closed | Brian | Hannibal | 20130701 | 2013----
Obviously the SQL for this is pretty basic:
SELECT TicketId, TicketName, TicketStatus ... FROM Ticket INNER JOIN TicketType ON [Ticket].TicketID = [TicketType].TicketName INNER JOIN [Status] ON .... ....
So that's the DEFAULT static portion of the report. This is what initializes first, however the client, customer, manager, etc. wants the option of selecting zero, one, or more fields to sort data on for all fields in the report. For example:
User may choose 'app' from the TicketName category, 'Josh' as Developer, and BETWEEN date1 AND date2, while TicketStatus Remains NULL or ' '. The table should display all 'app' tickets assigned to 'Josh' between selected dates, while not breaking due to TicketStatus not being selected. Any ticket status should appear.
User could choose not to select any values, in which case the report should default to the example above, without breaking. Meaning I cannot use NULL as a place holder bacause it would cause the initial report to not initialize (unless I did something wrong?)
User can basically mix and match any variation of the fields and the table should sort on those selected fields.
Question: How can someone return a table based on all or no options being selected, without breaking the query?
Using SQL Server 2008 r2, Business Intelligence Development Studio, T-SQL.
I have tried declaring variables and using LIKE statements, but this requires the user to select an option before the report will return anything. The User may not know what the field values are from memory.
CREATE PROCEDURE WorkAssignmentReport @Category varchar(25) ,@---Name varchar(25) ,@Stats varchar(25) ,@---Name varchar(25) ......... ...... FROM [Ticket] INNER JOIN dbo.[TicketType] ON [Ticket].TicketTypeID = [TicketType].TicketTypeID INNER JOIN dbo.[Status] ON [Ticket].Stat .... ..... WHERE [TicketType].Name LIKE @Category AND [Ticket].Name LIKE @ReportName AND [Status].StatusName LIKE @Status AND [Phase].PhaseName LIKE @PhaseName AND (BA.FullName = @Name1 OR AD.FullName = @Name1)