Forum Stats

  • 3,872,071 Users
  • 2,266,376 Discussions


How use "All Values" in the pages of pivot tables

Hi everyone,

I’m using Oracle Bi Answers and I have the following problem:

I’m doing reports based in a datamart with 2 fact table each with about 28 Million records per month and with a medium of 14 dimensions.

The users want to have the choice to select 5 parameters for example (region, district, type of product, month of analysis, subtype of product) where they can select an individual option as well as select an “all values” option. They want this for all the parameters.
We are having problems doing this without having the report to timeout.

Approaches used
We are trying to do this by using pivot tables and combo boxes in the reports (one for each parameter). The combos are in the pages area.

We are trying to do a calculated item with Sum(*) to have a “ALL Values” in the combo box but this makes the report extremely slow.
We also tried to do Sum in the pages section of the pivot table but it only displays the “All pages value” of the first combo box.

So actually I don’t know what to try. Can you help me with the best way to do this?


Edited by: user8727081 on Sep 28, 2009 2:45 AM


  • Madan Thota
    Madan Thota Member Posts: 839
    Why dont you try dashboard prompts instead of pages in pivot.
  • David_T
    David_T Member Posts: 2,432
    If you want "All Pages" in the combo box that is in the Pages Section of your Pivot Table, do this:

    Left click the "sigma" in Pages section and click "after" or "before" if you prefer. This will put the option of "All Pages" in your combo box.
  • 724708
    724708 Member Posts: 11

    I tried with dashboards and it works fine.

    But, why i can't do the same in Answers?
  • David_T
    David_T Member Posts: 2,432
    I don't understand. This works in Answers as well. Describe what is happening detail so we can determine if you are doing things correctly.
  • 724708
    724708 Member Posts: 11

    I have 5 prompts in page section of pivot table. How i put "All Values" in all prompts?


    prompt1 -- All Values
    prompt2 -- All Values
    prompt3 -- All values
    prompt4 -- year = 2006
    prompt5 -- All values

    In dashboard i create 5 dashboard prompt's, they have "all values" for default, so it work's and it's fast. In answers i'm doing this by creating a calculated item with the formula "SUM(*)". I have to do this to five prompts so when i execute the report it gives timeout.
  • David_T
    David_T Member Posts: 2,432
    OK, yeah, I see what you are saying after re-reading your post. For what you want, you are going to have to use dashboard prompts. The reason only the first prompt has the "All values" is because the values listed below them are for each combination of the other prompts. In other words, "all" means "everything," not just "all of the first prompt." The rest of the values are all the possible combinations. Sorry.
  • 724708
    724708 Member Posts: 11
    Thanks for help.
  • David_T
    David_T Member Posts: 2,432
    Top right corner of a post is where you can indicate "helpful' if help is indeed what you got! Thanks.
  • 727942
    727942 Member Posts: 1
    I had the same problem, and I solved it using the dashboard prompts.
    But sometimes users are not happy. I explaine why.
    Suppose a dimension Nation:
    Italy, Germany, UK, France, USA
    1) I create a report with sales quantity.
    2) Report has a filter --> "nation" = is prompted
    3) I create a dashboard prompt "Nations" based on dimension Nation with All Values
    4) I publish the report in a dashboard with the dashboard prompt "Nations"
    When user choose the nation from the prompt, all nations are displayed, also if I have NO sales for some of these.
    My goal: only nations with sales must be shown
This discussion has been closed.