3 Replies Latest reply: Sep 19, 2012 1:32 PM by AlexAnd RSS

    lexical parameter in bi report

    user13424229
      Hi All,
      I have a requirement as below :

      I have a select query in my XML file which is returning two rows for a order

      ex: select email,fax,order_number from
      oe_order_headers_all
      hz_contact_points,
      .........
      ...............

      My requirement is i need to pass 3 parameters such as order_number, email, fax to the report in that case it should take only the parameters what i am passing, and it should not take the hz_contact_points table from the query

      so i need to achieve this through lexical parameter, any one please help me on this.

      Thanks
        • 1. Re: lexical parameter in bi report
          AlexAnd
          you can try yo use lexical parameters like
          select &columns
           from  &tables
          where &where_clause
          and create beforeReportTrigger in plsql package

          as example look at https://blogs.oracle.com/xmlpublisher/entry/lexical_reference_problems
          • 2. Re: lexical parameter in bi report
            user13424229
            Hi AlexAnd,
            thanks for the reply,
            actually my requirement is to i need to execute another select statement when i am passing any parameter to that report in EBS,
            so when i am not passing any parameter to the report than it should execute the select statement whatever is ther in xml template and when i am passing any paramter in that case it should execute another select statement, so please help me on this.

            Thnaks
            • 3. Re: lexical parameter in bi report
              AlexAnd
              i think you didn't read that i'm mentioned

              in datatemplate
              ...
              <parameters>
              <parameter name="P_YOUR_PARAM" dataType = "number" defaultValue="0"></parameter>
              </parameters>
              ...
              <sqlStatement name="Q_DATA">
              <![CDATA[
              select &your_columns
               from  &your_tables
              where &your_where_clause
              ]]>
              </sqlStatement>
              </dataQuery>
              <dataTrigger name="beforeReportTrigger" source="XX_YOUR_PKG.BeforeReport"/>
              <dataStructure>
              ...
              in package
              FUNCTION BeforeReport RETURN BOOLEAN IS
              
              BEGIN
              
              IF P_YOUR_PARAM = 'some_value' THEN
                your_columns := 'column1, column2';
                your_tables := 'table1, table2';
                your_where_clause := 'table1.id = table2.id';
              ELSE
                your_columns := 'column3, column4';
                your_tables := 'table2, table3';
                your_where_clause := 'table2.id = table3.id';
              END IF;
              
              END;
              you can use another way - create your xml output by package like
              fnd_file.put_line(fnd_file.output, '<ROWSET>');
              fnd_file.put_line(fnd_file.output, '<ROW>');
              fnd_file.put_line(fnd_file.output, '<DAT>' || some_value || '</DAT>');
              fnd_file.put_line(fnd_file.output, '</ROW>');
              fnd_file.put_line(fnd_file.output, '</ROWSET>');
              in package you can use all that you want