This discussion is archived
3 Replies Latest reply: May 28, 2013 11:30 AM by 1011441 RSS

Generating Custom Group members using SQL

VictorFagundo Newbie
Currently Being Moderated
Greetings,

I have a need to create a more user friendly "All Column Values" heading than the default supplied by BI 11.

Using this method i can accomplish that:
http://blog.guident.com/2012/10/customizing-obiee-11g-prompts-all-column-values-to-a-custom-value/

however in this method you have to manually select all the members that are to make up your new group.

Is is possible to select members using SQL?

-VAF
  • 1. Re: Generating Custom Group members using SQL
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Use {} or try out as below
    http://www.cool-bi.com/Tweaks/AllColumnValues.php
  • 2. Re: Generating Custom Group members using SQL
    VictorFagundo Newbie
    Currently Being Moderated
    Hi Srini,

    Thanks for your response, but I'm not trying to pass the "all values" through a presentation variable. Rather I'm attempting to create a CUSTOM naming for "(ALL COLUMN VALUES)"

    Example, I have a dimension that is a list of products with 20K+ distinct values. On the prompt that filters this dimension i want the "All Column Values" selection to display as "All Products".

    The article i linked in my original post shows how to do this using a custom group, but the author selects the 50 members manually. This works if your dimension is something like states, but not for my "products" dimension that has 20k+ members.

    Furthermore it would be useful to have the custom group only made up of SELECT members that i chose with a SQL statement, such as all members with sales > 100k for the last 6 months.

    Yes giving the "All column values" a custom name is splitting hairs, but the summation of small tweaks like this is what sets apart "intuitive" reports from "bulky" reports.

    Possible?
  • 3. Re: Generating Custom Group members using SQL
    1011441 Newbie
    Currently Being Moderated
    You can mention your custom name for all values in the 'default value' part.

    Syntax:
    @{Presentation Variable}{Default value}

    Example:
    @{v_products}{All Product Values}

    So when my selection is open for all products, it will display "All Product Values".

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points