7 Replies Latest reply: Jan 9, 2013 1:01 PM by Srini VEERAVALLI RSS

    How to specify a constraint that is not a selected column

    983156
      Assuming I have the following dimensions:
           person_id
           case_number
           report_date
      and 1 measure, Payment_Amount

      1. In OBIEE Answers, how do I aggregate-sum the Payment_Amount by person_id, and case_number where
      report_date <= (some user specified date)

      2. How does the user pass the parameter for the report_date?

      In SQL a prototype may look like:
      Select person_id, case_number, sum(Payment_Amount) from Payment_Table
      where report_date <= (user specified date)
      group by person_id,case_number

      Thanks in advance, szzfrank
        • 1. Re: How to specify a constraint that is not a selected column
          Srini VEERAVALLI
          You can get it done using presentation variable; make sure you convert it when you are using it in answers since date is passed as string to answers.

          Any of these links helps you for the same
          http://bischool.wordpress.com/2009/05/05/presentation-variable-values-in-report-titles-or-using-presentation-variable-in-dashboard-text-object-and-narrative-view-directly/
          http://prasadmadhasi.com/2012/02/28/variable-prompt-in-obiee-11g/
          or
          http://oraclebi.blog.com/example-of-presentation-variable-in-obiee/
          http://gerardnico.com/wiki/dat/obiee/presentation_variable

          If helps pls mark or else let us know for issues
          • 2. Re: How to specify a constraint that is not a selected column
            983156
            Thank you for your prompt response but I don't quite see the application of the examples you cited for filtering by the user selected date.

            Please note the "Where" clause, or the date filter, is being applied to a field that is not displayed or selected as a column.

            The desired behavior is that the sum(Payment_Amount) only uses Payment_Amounts prior to the user_selected_date.

            Where and how can I apply this filter? Can you please elaborate?

            Thanks again,
            • 3. Re: How to specify a constraint that is not a selected column
              Srini VEERAVALLI
              http://bischool.wordpress.com/2009/05/05/presentation-variable-values-in-report-titles-or-using-presentation-variable-in-dashboard-text-object-and-narrative-view-directly/
              ---> Step # 7 shows how to add the filter, you can use CTRL+Click on Criteria or else add column apply the filter as shown and delete it from criteria

              And the below are about presentation variable example my not fit 100% but you can get some info

              http://prasadmadhasi.com/2012/02/28/variable-prompt-in-obiee-11g/
              or
              http://oraclebi.blog.com/example-of-presentation-variable-in-obiee/
              http://gerardnico.com/wiki/dat/obiee/presentation_variable


              Hope this helps
              • 4. Re: How to specify a constraint that is not a selected column
                983156
                Srini,

                You've answered my second question but not my first:

                1. In OBIEE Answers, how do I aggregate-sum the Payment_Amount by person_id, and case_number where
                report_date <= (some user specified date), when report_date is not a specified column. Where do I specify the filter?

                Thanks again,
                Sid
                • 5. Re: How to specify a constraint that is not a selected column
                  983156
                  I think I figured this out with the help of the documentation you sent to me.


                  1. I added the report_date column to the report
                  2. I created a prompt to filter the report based on this column
                  3. I deleted the column from the report

                  It now filters on report_date even though report_date is not a specified column in the report.

                  Thanks again,
                  Sid

                  Edited by: szzfrank on Jan 9, 2013 10:30 AM
                  • 6. Re: How to specify a constraint that is not a selected column
                    Srini VEERAVALLI
                    Can you mark on any of last messages as correct?
                    • 7. Re: How to specify a constraint that is not a selected column
                      SPowell42
                      In 10g, you could right click on the column and it would add it to the filters area without adding it to the request line. In 11g you can no longer right mouse click, but there is a little, very hard to find option to do it on the border menu line that separates the requests and filters area.

                      Or just do it the way you did, add the column, create the filter, and delete the column, probably easiest in 11g that way.

                      Thx,
                      Scott