I am using native SQL in Query Explorer by connecting data source directly in Report studio because of subqueries. I have made all other prompts optional by using macros but this one is tough. I am not able to use detail filter in report because this where clause is in subquery and filtered at that level itself.
SQL is as below:
SELECT BP,ID,IA,Date,Hours,Bill,Co
FROM Table B, Table D LEFT OUTER JOIN Table E ON D.NUM = E.NUM
AND (RTRIM(UCASE(E.NUM)) NOT IN ('N/A', 'NONE', 'IM ONLY') OR (RTRIM(UCASE(E.NUM)) IN ('N/A', 'NONE', 'IM ONLY') AND D.ID = E.ID))
WHERE (E.BM = 'AP' OR E.BM IS NULL )
AND E.BM = B.BM AND E.ROLE = B.ROLE
AND E.CO = B.CO
AND (YEAR(Date) = CT_YR)
AND ( Date >= '2016-01-01' ) AND ( Date <= '2016-01-10' )-----/* only required parameter remaining all are optional in this where clause*/
AND ( ENDDT >= '2016-01-02' ) AND ( ENDDT <= '2016-01-08' )
AND ( E.REGION ='USA' ) AND ( E.CO ='IU' )
AND ( UPPER(E.NUM) ='123456' ) AND ( UPPER(E.S_NUM) ='0D34' )
AND ( UPPER(A_R_ID) ='RD37' ) AND ( TID =9928 )
AND ( FLAG ='S' ) AND ( ROLE ='IC' )
AND ((R_IND= #prompt('RIndicator','string', sq('NoSelected'))# )OR ('NoSelected' = #prompt('RIndicator','string', sq('NoSelected'))#)) --------/* I made parameters optional using macro like this one for all others*/
AND A_IND = 'N'
AND (
AND STATE NOT LIKE ('00%') /* optional with check boxes as below */
AND STATE NOT LIKE ('85%')
AND STATE NOT LIKE ('90%')
AND STATE NOT LIKE ('175%'))
AND ( A_IND IS NULL OR A_IND <> 'Y' )/* optional with check boxes with conditions as below*/
AND (
( M_NAME LIKE 'ST%' )
OR ( M_NAME LIKE 'JAN%' )
)
AND (
( M_NAME = (SELECT DISTINCT R_NAME FROM Table WHERE B_R_ID = '234' and BM = 'AP'))
OR ( M_NAME = (SELECT DISTINCT R_NAME FROM Table WHERE B_R_ID = '9990' and BM = 'AP'))
)
For example :- If we say check boxes
Include Adjustments -P1
Only Adjustments-P2
Active only -P3
condition is as ,
[P1 and P2 are mutually exclusive but both can be unchecked]
If P2 unchecked and P1 unchecked
AND ( A_IND IS NULL OR A_IND <> 'Y' )
If P1unchecked and P2 checked
AND ( A_IND = 'Y' )
If P1 checked and P2 unchecked
<no special filter>
If P3 is cehcked
then
AND A_IND = 'N'
AND (
AND STATE NOT LIKE ('00%')
AND STATE NOT LIKE ('85%')
AND STATE NOT LIKE ('90%')
AND STATE NOT LIKE ('175%'))
How to achieve this ?