3 Replies Latest reply: Jul 25, 2013 6:18 AM by 937325 RSS

    Sub query Report

    970365


      Hi

      I am using OBIEE 11.1.1.6.6.I have prompt like

      Date between:From  and To

      Country:US,GB,IN,....

      Client: NAABB

       

      Now could you please let me know how can i generate report based on below queries

       

      ----------Main Query-----------------

      select count(*),RECEIVER_Cty_code from AAAAAAAA

      where business_date>='20-Jun-2013' and business_date<='25-Jun-2013'

      and MSG_IN_OUT_IND='SEND'

      and MSG_TYPE_CODE='103'

      and SENDER_Cty_code='US' (Country Prompt Value)

      and ADDL_REF_NO in (ADDL_REF_NO sub query values)

      group by RECEIVER_Cty_code

      order by 1 desc

      --------Sub Query-------------------

      select distinct ADDL_REF_NO from AAAAAAAA

      where business_date>='20-Jun-2013' and business_date<='25-July-2013'

      and MSG_IN_OUT_IND='RECEIVE'

      and MSG_TYPE_CODE='103'

      and RECEIVER_Cty_code='US' (Country Prompt Value)

      and sender_Code='NAABB' (Client Prompt Value)

      order by 1 ASC

       

      Could you please let me know how can i create report.First i have to select prompt after that My sub query will run and get values.

      Based on sub query values i have to pass to main query.Main query.Give me steps

       

      Thanks

      Gram

        • 1. Re: Sub query Report
          Amol Palkar

          Hi Gram,

           

          first create your subquery report. If the subject area does not exist do it by direct database request. Save the report by name 'xyz'.

          Later create main report by the same way and filter the column of the main query by "is based on results from another analysis". Now whenever you will run this main report , it will be filtered based on your sub query report.

           

          Thanks,

          Amol

          (Please mark this answer, if you find helpful)

          • 2. Re: Sub query Report
            970365

            Thanks a lot.Could you please let me know How can i place main report into dashboard without sub report.

             

            Note:My both report,have the prompt values.Once i select prompt values then only my sub report it will return values.Based on subreport then main report it will work.

             

            But i dont want to display subreport into my dashboard.

             

            Could you pls let me know How can i achive this.

             

            Thanks

            Gram

            • 3. Re: Sub query Report
              937325

              Hi,

               

              After setting the filter based on another analysis(sub query report) save the report and place in the dashboard in the same way as other reports.

              create new section under the main report and pull the sub query report and in the section properties under CSS type Display: None. so that this report wont display in the dashboard but what ever the values you select in the prompt will reflect in the sub query report based on that the main report will reflect.


              Note: Don't forget to set protect filter for sub query report.


              Mark if Helpful/correct.


              Thanks.