3 Replies Latest reply: May 28, 2013 1:30 PM by 1011441 RSS

    Generating Custom Group members using SQL

    VictorFagundo
      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
          Use {} or try out as below
          http://www.cool-bi.com/Tweaks/AllColumnValues.php
          • 2. Re: Generating Custom Group members using SQL
            VictorFagundo
            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
              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".