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

    Generating Custom Group members using SQL


      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:

      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?

        • 1. Re: Generating Custom Group members using SQL
          Srini VEERAVALLI
          Use {} or try out as below
          • 2. Re: Generating Custom Group members using SQL
            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.

            • 3. Re: Generating Custom Group members using SQL
              You can mention your custom name for all values in the 'default value' part.

              @{Presentation Variable}{Default value}

              @{v_products}{All Product Values}

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