Optional parameters In SSRS means if parameters have been selected, it would be passed back to SQL query as condition in WHERE clause, and when no parameter(s) have been selected, the WHERE condition should be not in use. Dynamic SQL is not the best solution. Following solution is much better:
SELECT Col_A
, Col_B
FROM Tab
WHERE (Col_A=@para_A OR @para_A IS NULL)
AND (Col_B=@para_B OR @para_B IS NULL)
What make life harder is when parameters are selected, but due to lack of records, the result is nil? These would be happened often when multiple parameters get involved. Depends on situations, you may wish to abandon that condition completely. So, the real issue is not if you have option to select a particular parameter, it is if you have selected that parameter but it turns out nil result and you need to abandon it after this discovery.
There is no programmatic solution so far. However, following approach may help:
1, Limit the number of parameters, because you will need to combine them in matrix. Too many parameters will increase the task load exponentially.
2, Classify parameters into hierarchy.
3, Write queries for each combination case of the parameters. Hierarchical structure of parameters will help a lot.
4, From low position in hierarchy to pick up the best available result amongst the results generated from above queries. Following is an example to do this procedure:
; SET @Stopper = 0
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_1' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_2' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
...
What you need to do next is in SSRS to enable the optional parameters. While you have made it possible to accept the NULL value in your back-end queries, by default the report interface of SSRS cannot be flagged out the parameters. The users would have no choice but to select parameters. In this case, include NULL value record for your parameter data set:
SELECT NULL AS Id
, 'Unknown' AS Name
UNION
SELECT DISTINCT Id
, Name
FROM Tab
If you have problem on how to position this NULL value record, please refer to following article:
Special Function of ORDER BY Clause in T-SQL
Next, in Report Parameters window of SSRS, make sure the "Allow null value" option has been selected for this particular parameter.
Done.
http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx
Showing posts with label WHERE Condition. Show all posts
Showing posts with label WHERE Condition. Show all posts
Delete or Select Records Which Is In Or Not In A View
MySQL
Delete records in a table based on the condition that keys of that table is in a another view or is not in the view will not work in MySQL, because either IN or NOT IN needs to be followed by a string delimited by comma. Sure, you can program it to turn a view into a string by insert comma in it. Following is an example which does not work in MySQL:
DELETE FROM tab
WHERE tab.id IN
( SELECT tab.id
FROM tab
WHERE ...
)
An easier way is to use INNER JOIN method to accomplish the task. Here is how to do this:
DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE ...
) AS vCondition
ON vCondition.joinKey = tab.joinKey
Please remember between DELETE and FROM you would need to insert what is going to be deleted. In this case, it is tab.*, represents all fields in the records which meets the condition.
For NOT IN condition, you would need to make sure what is going to be joined would be opposite to NOT IN condition:
DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE tab.id NOT IN
( SELECT tab.id
FROM tab
WHERE ...
)) AS vCondition
ON vCondition.joinKey = tab.joinKey
Don't ask why this time NOT IN works. If you do, please ask MySQL developers directly why they make life so hard. At the end, it is free. So, would you please shut up?
T-SQL
Delete or select records in a table based on the condition that keys of that table is in a another view or is not in the view will work in SQL Server. However, if that is dynamic T-SQL, it won't work. Following is the solution:
SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
( SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query = ' SELECT *
INTO Temp_Transform_Result
FROM
( SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
( SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)
http://www.electrictoolbox.com/article/mysql/cross-table-delete/
Delete records in a table based on the condition that keys of that table is in a another view or is not in the view will not work in MySQL, because either IN or NOT IN needs to be followed by a string delimited by comma. Sure, you can program it to turn a view into a string by insert comma in it. Following is an example which does not work in MySQL:
DELETE FROM tab
WHERE tab.id IN
( SELECT tab.id
FROM tab
WHERE ...
)
An easier way is to use INNER JOIN method to accomplish the task. Here is how to do this:
DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE ...
) AS vCondition
ON vCondition.joinKey = tab.joinKey
Please remember between DELETE and FROM you would need to insert what is going to be deleted. In this case, it is tab.*, represents all fields in the records which meets the condition.
For NOT IN condition, you would need to make sure what is going to be joined would be opposite to NOT IN condition:
DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE tab.id NOT IN
( SELECT tab.id
FROM tab
WHERE ...
)) AS vCondition
ON vCondition.joinKey = tab.joinKey
Don't ask why this time NOT IN works. If you do, please ask MySQL developers directly why they make life so hard. At the end, it is free. So, would you please shut up?
T-SQL
Delete or select records in a table based on the condition that keys of that table is in a another view or is not in the view will work in SQL Server. However, if that is dynamic T-SQL, it won't work. Following is the solution:
SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
( SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query = ' SELECT *
INTO Temp_Transform_Result
FROM
( SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
( SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)
http://www.electrictoolbox.com/article/mysql/cross-table-delete/
Contain Data Layout in SQL Part for Reporting Services
Business Intelligence in both ORACLE and SQL Server has offered standard tools and settings, such as cube and dimensions. However, for complicated reports, these tools may not be very useful. The orthodox approach to this issue is to have a customized OLAP database built. In some case, even with OLAP database, complicated queries still needed. This issue does also cause the further problem of data layout in report. Here is an approach to deal with these issues. Basically, this approach does contain the task of data layout in SQL part to avoid complex programming in Stored Procedures.
Let us say a report is required to present a daily summary of patient movement within a State, with certain criteria. It requires 5 columns: Date, Sending Facility, Receiving Facility, total transports between these two facilities in that day [Transports], and the Daily Subtotal for whole State. Indeed, Daily Subtotal and Transports can share same column. However, for better visual effect, it is required to have separate columns. GROUP BY and other analytic functions can be used to generate numbers for Transports and Daily Subtotal. However, for the sake of data layout in report, it is suggested only GROUP BY is used. As at today, almost all other analytic functions just generate undesired data layout, for instance, extra but unneeded columns from PIVOT.
The data was not located in a single table, so joins are necessary. Since the selection criteria (WHERE Conditions) are not strictly have one-to-one relationship, the joins therefore are Outer Joins to ensure needed records won’t be excluded. Here is FROM clause:
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN ……
The sending facility and receiving facility indeed is in same table. So, it had been outer joined twice with flag as s and r.
Because of one-to-more relationship, it is for sure multiple rows will be generated for some records. So, mechanism to distinct records is needed. Unfortunately, DISTINCT function does not work with GROUP BY. So, an inner query is used strictly only selecting the essential fields together with DISTINCT:
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
) AS Distinct_Q
Do not forget to add “AS [name_of_inner_query]” at the end of inner query, which is required by Transact-SQL. DATEDIFF(DD, 0, t.add_datetime) is used here to uniform the time during the date enabling the use of GROUP BY. DATEADD(HH, 12, …) will be explained late. If Distinct_Q contains less fields needed by next step, it is neccessary to join back to retrieve the missing fields.
Next step is to use GROUP BY:
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM
(…) AS Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
So far, we generate all essential parts of the report except the Daily Subtotal. We can use same structure for Daily Subtotal. Since inner query Distinct_Q used twice, it is worthwhile to move this temporary resultset to the head with WITH:
WITH Distinct_Q
AS
(…
)
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
Note, DATEADD(HH, -1, Entry_Date) is used again here.
We can use UNION to put two temporary resultsets Detail_Q and Daily_Subtotal_Q together:
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
(…) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(…) AS Daily_Subtotal_Q
The purpose of CAST(… AS VARCHAR(3)) used here is to avoid the empty string '' automatically turns to 0 after UNION. Field [Daily Subtotal] in Detail_Q is string data type and Daily_Subtotal_Q is number. So does for field [Transports].
The final step to to add ORDER BY at the end:
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
It is time to explain why using DATEADD() function above. Since [Entry Date] is to be sorted DESCENT-ly, one hour difference as result of two DATEADD() functions could place row of ‘Daily Subtotal’ at the end of every rows for that day.
Here is completed SQL script:
WITH Distinct_Q
AS
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
)
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
This blog is to illustrate how to manipulate the data layout in report simply by use SQL. You can also refer to http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html.
http://koncordpartners.blogspot.com/2010/12/special-function-of-order-by-clause-in.html
Let us say a report is required to present a daily summary of patient movement within a State, with certain criteria. It requires 5 columns: Date, Sending Facility, Receiving Facility, total transports between these two facilities in that day [Transports], and the Daily Subtotal for whole State. Indeed, Daily Subtotal and Transports can share same column. However, for better visual effect, it is required to have separate columns. GROUP BY and other analytic functions can be used to generate numbers for Transports and Daily Subtotal. However, for the sake of data layout in report, it is suggested only GROUP BY is used. As at today, almost all other analytic functions just generate undesired data layout, for instance, extra but unneeded columns from PIVOT.
The data was not located in a single table, so joins are necessary. Since the selection criteria (WHERE Conditions) are not strictly have one-to-one relationship, the joins therefore are Outer Joins to ensure needed records won’t be excluded. Here is FROM clause:
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN ……
The sending facility and receiving facility indeed is in same table. So, it had been outer joined twice with flag as s and r.
Because of one-to-more relationship, it is for sure multiple rows will be generated for some records. So, mechanism to distinct records is needed. Unfortunately, DISTINCT function does not work with GROUP BY. So, an inner query is used strictly only selecting the essential fields together with DISTINCT:
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
) AS Distinct_Q
Do not forget to add “AS [name_of_inner_query]” at the end of inner query, which is required by Transact-SQL. DATEDIFF(DD, 0, t.add_datetime) is used here to uniform the time during the date enabling the use of GROUP BY. DATEADD(HH, 12, …) will be explained late. If Distinct_Q contains less fields needed by next step, it is neccessary to join back to retrieve the missing fields.
Next step is to use GROUP BY:
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM
(…) AS Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
So far, we generate all essential parts of the report except the Daily Subtotal. We can use same structure for Daily Subtotal. Since inner query Distinct_Q used twice, it is worthwhile to move this temporary resultset to the head with WITH:
WITH Distinct_Q
AS
(…
)
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
Note, DATEADD(HH, -1, Entry_Date) is used again here.
We can use UNION to put two temporary resultsets Detail_Q and Daily_Subtotal_Q together:
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
(…) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(…) AS Daily_Subtotal_Q
The purpose of CAST(… AS VARCHAR(3)) used here is to avoid the empty string '' automatically turns to 0 after UNION. Field [Daily Subtotal] in Detail_Q is string data type and Daily_Subtotal_Q is number. So does for field [Transports].
The final step to to add ORDER BY at the end:
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
It is time to explain why using DATEADD() function above. Since [Entry Date] is to be sorted DESCENT-ly, one hour difference as result of two DATEADD() functions could place row of ‘Daily Subtotal’ at the end of every rows for that day.
Here is completed SQL script:
WITH Distinct_Q
AS
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
)
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
This blog is to illustrate how to manipulate the data layout in report simply by use SQL. You can also refer to http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html.
http://koncordpartners.blogspot.com/2010/12/special-function-of-order-by-clause-in.html
Subscribe to:
Posts (Atom)
Posts
