13 Replies Latest reply on Dec 10, 2015 9:45 PM by 2657149

    filtering OBIEE report


      Hi All,

      We have 10 reports with regards to the same requirement.

      I have recently got a enhancement request to modify these reports to display the records based on a new condition that is to be added.


      The requirement is to get a new custom dashboard prompt with Specific values 'Op' and 'Fis'

      When the user selects 'Op', all the records except the ones with a column value having Corporate Operations should not be present on the report.

      When the user selects 'Fis', all the records should be shown.

      We have several prompts on the dashboard.

      The filter Op/Fis should work on top of the other existing prompts on the dashboard.


      It should be like all the records to come up from the database for all the applicable prompts that has been entered by the user-- (1)

      When Op is selected, all the records from (1) should be displayed taking out the ones with a field value = Corporate Operations.

      When Fis is selected, all the records from (1) should be displayed.


      Hence, I am looking for an option which can control the result set retrieved and then apply the filter 'Op'/'Fis' as required.




        • 1. Re: filtering OBIEE report
          Gianni Ceresa


          You don't really want your DB to return all the records all the time and then filter them out at the BI Server level, believe me

          You would prefer to set the correct filter before to send the query to your DB


          Is this new Op/Fis prompt based on a column? It sounds more like a presentation variable filter ...


          So, assuming it sets a presentation variable you can use a filter like:

          '@{your_op_fis_pres_variable}{zzzz} = 'Op' AND "Field value" NOT IN ('Corporate Operations')


          The idea is to have 2 conditions: one is the exclusion of your "Corporate Operations" rows (a standard OBIEE filter on a column) and the other one is a filter acting like 1=1 or 1=2 to enable to disable to other filter.

          Join them with a AND and it's done.

          • 2. Re: filtering OBIEE report

            Hi Gianni Ceresa,


            Thank you for your response.


            Op/Fis are just specific values that drive the display of records with Corporate Operations or not.


            Also, One of the examples my report should work is if users filters the report with a prompt value='X', the report should bring up all the X and field value(Org1) = Corporate Operations for fiscal and take these out for Fis.

            The similar case should work for all the other prompts or combinations of the prompts.


            Org1Org2 Org3 Project IDTransaction Value
            Corporate OperationsLS Industry 123410000
            LS ConsolidatedLSC Company1234-5000


            If the above result is the sample data, when we filter the report on Proj ID = 1234, Op should retrieve only second record but Fis should return both the records.

            Even when we generate the report for different prompt fields like Org1/Org2/Org3, the first record with Corporate Operations should be displayed for Fis and not for Op.


            I am not sure how do I write the above 2 conditions in your above post along with other prompt conditions, because for fis I need to show up the records with Corporate operations, If I write the conditions as AND, the record will not be displayed, Am I right?


            Please correct me if I am wrong.


            As of now, I am thinking to add the field =Corporate Operations in an OR condition with each of the field on the filters section for is prompted, and include other AND conditions using case stmt for returning op/fis or return op/op.




            • 3. Re: filtering OBIEE report
              Thomas Dodds

              If I understand correctly,the selection of OP/FIS only drives a filter OP=filter and FIS=no filter on that specific column ... if that is the case ... then put the OP/FIS values in a prompt (could do  SELECT 'OP' FROM DUAL UNION SELECT 'FIS' FROM DUAL;), set a presentation variable and populate a hidden report that does the filtering based on the variable value, then base the rest of your reports off the values in that hidden report for the values in Org1 (the driving column)

              • 4. Re: filtering OBIEE report

                Hi Thomas,

                Thanks for your response, but unfortunately I was not able to get this to work.


                FIS should bring in all the applicable data(user selected prompt values) along with the ones with Org1=Corporate Operations.

                The idea you have suggested does not work because filtering the report on Org1=LS Consolidated or Org2=LSC will restrict the record with Org1=Corporate Operations from being displayed on the report even when we select Fis as the value for Presentation variable.




                • 5. Re: filtering OBIEE report
                  Thomas Dodds

                  OP/FIS would have to be an exclusive prompt, not ORG1 ... ORGN ... basically you need it to function as a boolean to stripe your data independent of the other prompts selected...

                  • 6. Re: filtering OBIEE report

                    Hi Thomas,


                    Can you please elaborate your recent post to function it as boolean to stripe the data...


                    As our is the project analytics with EBS as source, Also, the transactions are tied with a value Org1=Corporate Operations at a project Level, Hence, when the user queries on any prompt, the report should bring in the projects tied up with the user filter and the transactions with Org1=Corporate Operations for Fis and others for Op.


                    Hence, I need to include a filter to allow records of Corporate Operations along with a filter to get the required projects as per the user filter using a sub report or an advanced sql filter and then include/exclude the records with Corporate Operations based on the presentation variable.

                    Is there any other alternate way to get these projects without using a sub report to improve the performance.




                    • 7. Re: filtering OBIEE report
                      Thomas Dodds

                      is the decision between OP and FIS a security thing?  if so, implement a BI role and use row-level filtering for the 'stripe' ...

                      • 8. Re: filtering OBIEE report

                        No Thomas, It is a not based on row level security.

                        Row level security is being driven by some other columns.




                        • 9. Re: filtering OBIEE report
                          Thomas Dodds

                          You are asking to show certain rows for certain people ... that IS row-level filtering.  The question is do you want it governed by an Application Role or do you want it governed by the user's actions on a dashboard?

                          • 10. Re: filtering OBIEE report

                            Hi Thomas,


                            Sorry for the confusion around.

                            I want to be governed based on the user action on dashboard.




                            • 11. Re: filtering OBIEE report
                              Thomas Dodds

                              Then you need:


                              • a boolean style prompt to turn on/off
                              • a hidden report to evaluate whether or not to show all rows (off) or just some (on)
                              • the rest of the reports to use that report to drive the filtering action
                              • 12. Re: filtering OBIEE report
                                Thomas Dodds

                                For starters ... the prompt might be something like:



                                make sure you can do Direct SQL ...

                                • 13. Re: filtering OBIEE report

                                  Hi Thomas,


                                  This does not work.

                                  The similar transactions will be existing for different projects for different organizations.

                                  Hence, when the user filter for a specific organization, for Fis, all the corporate operations irrespective of the organizations and the projects will come up.


                                  I just realized today that there has be a data model change to have a new column included for identifying these transactions to have the data correctly displayed according to the prompts that I had on all of my dashboards.


                                  Thank you for your inputs,