[Oracle]How to design dynamic report

 參考資料：http://jayantaapps.blogspot.tw/2011/11/oracle-reports-builder-query.html：

Lexical Parameter

使用 & 這個符號當作關鍵字

1. Data Model > Queries > SQL Query Statement

   SELECT T.AAA, T.BBB
     FROM TABLE T
    WHERE 1 = 1
      AND &CP_PARAMETER
   

2. Data Model > Placeholder Column
   Add
       CP_PARAMETER
   Set
       Datatype = Character
       Width = 200
       …..
3. Report Tiggers > BEFORE REPORT

   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
   BEGIN
   
     :CP_PARAMETER = ' AND T.AAA = ' TRUE '  AND T.BBB = BLUE '
     
       RETURN(TRUE);
   END;

 

Bind Variable

使用 :    這個符號當作關鍵字
    

1. Application Developer > Concurrent Progam >    Parameters
   Set
      Token = P_PARAMETER
       …..
2. Data Model > Queries > SQL Query Statement

   SELECT T.AAA, T.BBB
     FROM TABLE T
    WHERE 1 = 1
      AND T.AAA = :P_PARAMETER

3. When user open the report and input parameter

    

Other

1. Data Model > Queries > SQL Query Statement
       &P_PARAMETER

2. 2、Data Model > User Parameters
      Add
       P_PARAMETER    
      Set
       Datatype = Character
       Width = 200
       Inital Value = SELECT 'This is test' FROM DUAL
       …..