14 Replies Latest reply on May 15, 2015 9:31 PM by Gianni Ceresa

    Top 1o filter

    7688438

      Hi Gurus, i'm new to OBIEE world. in the below screen shot i have all employees with total expenses.But the report title is top 10 employee spend.How to add the filter which shows only top 10 employees with expenses.

       

      Report1.PNG

       

       

      Thanks in advance

       

      R

        • 1. Re: Top 1o filter
          Gianni Ceresa

          Hi,

          You can do it in Selection Steps:

          Capture.PNG

          On your employee number add a new step, select "apply a condition", condition type is "top 10 based on X", and then just set your params.

          (action = keep only, operator = is top, rank = 10, measure = your "Total expenses" measure)

          • 2. Re: Top 1o filter
            Gianni Ceresa

            Second option:

            add a filter on a column with this formula: RANK("your column with the expense")

            and the condition of your filter is "is less than or equal to" and value is 10.

            Capture.PNG

            • 3. Re: Top 1o filter
              Gianni Ceresa

              Third option:

              Add a column using the TOPN function: TopN("your column with the expense", 10).

              It will implicitly filter and display only the TOP10.

              Capture.PNG

               

              The order of options isn't the one from the best to the worst options, just to give you options ....

              I would leave the Selection Steps as last option because it's filtered by the BI Server and the DB will still return you to full list.

              • 4. Re: Top 1o filter
                7688438

                Giana, Thanks for the update.I already tried 2nd & 3rd options.the below are results.

                 

                there is dollar sign on the expenses field at the column data format for ex: $10,000

                 

                 

                Options.PNG

                 

                 

                Thanks

                R

                • 5. Re: Top 1o filter
                  Gianni Ceresa

                  Please, tell me you don't really have the dollar sign in the column value itself but that it's just in the column format, right?

                  Total expense is supposed to be a measure, just a number, nothing else ...

                   

                  So if it's the case and it's a normal measure the format adding the dollar sign has no impact on the result as it's a number.

                  • 6. Re: Top 1o filter
                    Gianni Ceresa

                    I guess the result you saw is because your "expense type".

                    If you try RANK("your expense column" by "expense type column") you will get the RANK of each expense row by type, so each type start with a RANKING = 1.

                    In that case for each value of expense type you will have your 10 rows of expenses.

                    • 7. Re: Top 1o filter
                      2701319

                      Giana,

                       

                      Thanks for the response.I tried your solution but it is effecting the results i.e the results are not correct for the view selector if i add rank <=10 condition(filter) to show top 10 results.the filter should be on top of the main query.

                       

                      The requirement for view selector is to show top total expenses.

                       

                      rank2.PNG

                       

                      Thanks

                      R

                      • 8. Re: Top 1o filter
                        Gianni Ceresa

                        So now there is also a view selector?

                        Honestly next time post your full question with the details at the beginning ... (How to ask questions and get faster answers)

                         

                        What is your view selector? Why results would be wrong? If you want top 10 expenses by expense type the RANK( by ) does exactly that.

                        In case your view selector display multiple measures / granularity of course the result is wrong as the RANK() is fixed on a given measure and level (expense type) ...

                        • 9. Re: Top 1o filter
                          7688438

                          Giana,

                           

                          sorry for the confusion.I'm trying to achieve top 10 spenders by expense type in view selector.But rank<=10 filter is applying at the main query.

                           

                          It should apply on top of the main query.

                           

                          Please guide.

                           

                           

                          Thanks

                          R

                          • 10. Re: Top 1o filter
                            Gianni Ceresa

                            So, let me see if I got the full picture:

                            You have an analysis where you want to see the in one view the top10 spenders by expense type and in another view something else? What is this "something else"? It's something you don't want to limit with the top10 by expense type filter?

                             

                            When you talk about main query it's a little confusing as the query is always the same in the end, the analysis is 1 query, the views are just on screen things but the LSQL produced by the analysis is one and unique.

                            • 11. Re: Top 1o filter
                              Christian Berg

                              Your patience is mindblowing :-)

                              • 12. Re: Top 1o filter
                                7688438

                                Giana,

                                 

                                here is full picture:

                                I have employee top spenders reports one with

                                1)pivot view -with employee total expenses

                                2)view selector-employees with expenses by expense type name(from drop down list). Currently with view selector i can see all the employees with expenses(total-169). I just want see only top 10 as the report name is top10 employee spend by category.

                                 

                                VIEW1.PNG

                                • 13. Re: Top 1o filter
                                  Christian Berg

                                  Just quickly - you have 2 basic problems:

                                   

                                  1.) You're trying to do two different things in one analysis which isn't a good idea to begin with. Split those two views into two distinct analysis object and all issues relating to mixed granularities will disappear.

                                   

                                  2.) If you don't want all employees to show you have to actually cut off the data stream feeding into the analysis - i.e. doing the filtering ealier - rather than using a post-calculation approach based off of all data in the filter perimeter. Remember: selection steps don't filter the data stream. They massage the information ex-post.

                                   

                                  I'm not invalidating anything that Gianni Ceresa - not "Giana" by the way... - says. It's just that your starting point of a mixed-use analysis isn't a clean one to boot.

                                  • 14. Re: Top 1o filter
                                    Gianni Ceresa

                                    Adding to what Christian Berg said (and it's a good summary of what I also tried to say...): a single analysis has a single LSQL (except if you play around with variables in formulas etc.). So you can easily see you have an issue mixing granularity and wanting to filter things on mixed granularity.

                                    Split the analysis = problem solved!