Forum Stats

  • 3,855,318 Users
  • 2,264,499 Discussions


Selecting top N clients by branch or in the whole company

656440 Member Posts: 3
edited Sep 18, 2008 3:56PM in Discoverer
Hi all!
I have a report that selects the top 20 clients of each branch of the company. The company has more than 700 hundred branches. The report was done by:
1) creating a calculation using the rank function, partitioned by rank, ordered by descending total debt.
2) filtering the registers where rank <= 20.
In the report, the branch dimension is a page item.

What I need is that, when I select "ALL" in the page item (branch), the report brings the top 20 clients of the whole company. But, as the rank function calculates the rank for each branch, I have more than 700 clients ranked as 1, as 2, as 3 ,...

Is there a way of doing so, or do I have to create a new sheet just for a rank not partitioned by branch?



  • Hi William
    Here's something to try.

    Take your filter which registers rank <= 20 and make it into a Boolean OR like this:

    Rank <= 20 OR Rank <= 20

    This will force the query to run first with the filter being applied at the end which might get you what you want. If this doesn't help perhaps you could explain a little further with perhaps some screen shots and / or your actual code.

    Best wishes
    Michael Armstrong-Smith
  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy

    Unfortunately there is no way to do this other than by having a separate worksheet or by using a parameter to select all branches.

    Rod West
    Rod West
  • puppethead-Oracle
    puppethead-Oracle Member Posts: 958 Employee
    I would agree with Rod. More than likely, there is a grouping by branch which is still being enforced when "All" is selected at the page item level.
  • 656440
    656440 Member Posts: 3
    thanks for the "crazy" suggestion, but it hasn't worked right. I tried to ask just the point in which my report was getting a problem, so the question is indeed not as complex as the real problem. Now I am trying to reproduce the case in a simpler report so I can post it here.

    Thanks anyway!
  • Hi William
    I understand. If there is anyway we can make Discoverer do what you want, this is the right forum. There are lots of good, knowledgeable people who answer questions in here.

  • 656440
    656440 Member Posts: 3
    edited Sep 18, 2008 1:46PM
    Hi all, again!
    After a lot of consulting, I came across an almost satisfying workaround (without using LOV):
    1) Create parameter "Scope" that can assume 2 values: "Branch" or "Country"; checking "Require user to enter value"checkbox.
    2) Create calculation "Level of analysis":
    when (:Scope = "Branch") then Branch.Code
    when (:Scope = "Country") then "Brazil"
    3) Put "Level of analysis" as page item.
    4) Modify the rank formula to:
    DENSE_RANK() OVER(PARTITION BY *"Level of analysis"* ORDER BY "Total Debt" DESC )

    So, if the user wants the report at a branch level, he/she can input "Branch" in the "Scope" parameter. If he/she wants the report at a national level, he/she can input "Country" in the "Scope" parameter.

    The only disadvantage is that each time the user wants to change the report from the branch level to the national level (or vice-versa), he/she will have to change the parameter value and rerun the query. In my case, it is very fast...

    Thanks for all of those who have spent any time thinking about my situation.

    Edited by: wlsuzuki on Sep 18, 2008 10:46 AM
  • puppethead-Oracle
    puppethead-Oracle Member Posts: 958 Employee
    Congrats on the solution!

    Two small suggestions - since the users are typing in a value for the parameter, include an ELSE clause on the CASE statement in the Level of Analysis calculation. If the users type something incorrectly, it could keep the workbook from blowing up. Also, you may want to change the ELSE conditions to upper(:Scope) = "BRANCH" to remove the case dependency on what the user entered (if you want the parameters to be case-dependent, then leave things as they are).
This discussion has been closed.