8 Replies Latest reply on Feb 7, 2012 11:11 PM by Dhar

    Applying data filter using application roles

    user6287828
      In OBI-11g. I am creating new application roles and adding data filters to these roles.
      A user that is assigned this role using the database.

      When this user runs a report, the filter is not being applied to the report. Any ideas why.
      Also I was trying to look into the logs which might hint towards why the filter is not being applied, but didn't find anything. Which log might have information logged about this?


      Thank you
        • 1. Re: Applying data filter using application roles
          Dhar
          Hi,

          Can you please let us know, at what layer have you applied the filters. If you have applied at the presentation objects, can you put the filter on the BMM objects and see? I would try it on my end too and let you know if this is the same behavior even for me.

          Hope this helps.

          Thank you,
          Dhar
          • 2. Re: Applying data filter using application roles
            user6287828
            I first applied using a logical table, and made sure the query uses that logical table.

            And when that did not worked, I applied the filter to the presentation layer.
            and in one case filter is using a static repository variable and in the other filter is using a dynamic repository variable.

            Also where do you check if the filter is being applied. Because I was looking in the nqquery logs.

            Thank you

            Edited by: user6287828 on Feb 2, 2012 9:37 AM
            • 3. Re: Applying data filter using application roles
              MuRam
              Hi,

              In the logical table, in the content tab where clause try to give these variables and save. And in the answers pull one of the column from this logical table and check the query in the Administration-->Manage Session-->View Log of the respective report query (this produces the physical query). Copy and check the query.
              And if you are trying to use the query in one of the report in the answers, then use the variables created in the rpd as - Date_column is equal to VALUEOF(repository_variable_name). Please do check the syntax how to use the variable in the filter criteria.

              Regards,
              MuRam
              1 person found this helpful
              • 4. Re: Applying data filter using application roles
                user6287828
                Thanks for your suggestion.

                In my original problem, I am trying to apply filter based on an application role.
                But in your solution that you have suggested, every query from each user will get that filter, irrespective of the application role or group they belong to.

                In you solution, how to control that those filters are only applied to a particular group or a particular application role?

                Also I do not want to use that variable as a filter in each report and that's why I am trying to use data filters through application roles.
                Thank you

                Edited by: user6287828 on Feb 3, 2012 4:05 PM
                • 5. Re: Applying data filter using application roles
                  MuRam
                  Hi,

                  Take Advanced SQL filter with the variable condition in the report as:

                  case when VALUEOF(varaible_name)='ROLE_NAME' then security.country_id
                  else fact.country_id end=fact.country_id

                  (You can write your own customized code)

                  The above will form a join with fact and the security table when variable name =' ROLENAME' else it will take a self join.

                  You can use this for a single report or else if you need that for the fact table, then in the rpd in the logical source in the content tab-'where clause' try the same condition (good to use in the logical source). Or if you want to use in the prompt then write in the SQL query result.

                  Regards,
                  MuRam
                  • 6. Re: Applying data filter using application roles
                    Dhar
                    Hi,

                    The data level security should be applied automatically when you added filters on the application roles. May be you would like to revisit your steps at http://docs.oracle.com/cd/E21764_01/bi.1111/e10540/dataaccess.htm#CHDIFDJD.

                    However, you said that you do not find anything in the logs. Request you increase the log level for this user in the .rpd or may be through an initialization block and see there are any log entries. Also, please do let us know which version you are on. Is it .3 or .5?

                    Thank you,
                    Dhar
                    1 person found this helpful
                    • 7. Re: Applying data filter using application roles
                      user6287828
                      In 11g- the old 10g method of creating application roles in RPD is still retained.
                      But it only worked for me, when the application role was created using weblogic.

                      Why could that be the case.
                      • 8. Re: Applying data filter using application roles
                        Dhar
                        Hi,

                        I am sorry, if I got you wrong but were you referring to the groups in 10g because there were no application roles in 10g? If its yes, yes the 11g still supports the groups that were created in 10g because they get upgraded to application roles.

                        Hope this helps.

                        Thank you,
                        Dhar