6 Replies Latest reply on Nov 9, 2015 5:22 PM by 3025352

    Creating a Threshold Prompt

    3025352

      Hi all,

       

      I have the following requirement to make as a prompt:

       

      Contract Value: Contract Value Threshold to include in output (increments of 50,000, so that user can view Top Agmts in Excess of 50k, 100k, 150k, 200k, 250k, 500k)

       

      I have a list of contracts that the user would like to see based on the 50k thresholds on an agreement value fact table. I'm not sure how to go about listing 50k, 100k etc. and having the user select the threshold and OBIEE display the contracts that are in the threshold. I tried the slider prompt but that's hard for the user to use. Any ideas?

       

      Thank you

        • 1. Re: Creating a Threshold Prompt
          Federico Venturin

          Hi 3025352,

           

          I would create a variable prompt (e.g. PV_Threshold) with custom values (e.g. 50000, 100000, etc.).

           

          Then I would add the following filter to the analysis: "Table Name"."Contract Value" > @{PV_Threshold}.

          Please note that OBIEE will compute the aggregation first and then will apply the filter to your measure column.

           

          Let me know if I understood your requirement correctly

           

           

          Regards

          • 2. Re: Creating a Threshold Prompt
            3025352

            Thank you so much Federico! I created the variable prompt and then when I create the filter on the analysis it says the value is not valid. I'm creating the prompt separate from the report to use it in a dashboard with the report. Do I need to create the variable prompt with the report?

            • 3. Re: Creating a Threshold Prompt
              Federico Venturin

              You don't need to create the variable prompt with the analysis.

              Please make sure that your presentation variable and your column have the same data type.

               

              You should also add a default value to the presentation variable in your filter to be able to display results when you click on Results tab (e.g. @{PV_Threshold}{50000}).

              Otherwise you will get an error (but the analysis will work if you use it in a dashboard with your prompt).

              • 4. Re: Creating a Threshold Prompt
                3025352

                My presentation variable's data type is a Number and the column I'm trying to filter with it is a Fact table. Would that be an issue?

                • 5. Re: Creating a Threshold Prompt
                  Federico Venturin

                  It's not an issue if your fact table column has the same data type.

                   

                  As I wrote above: please note that OBIEE will compute the aggregation first and then will apply the filter to your measure column.

                  E.g. If SUM has been setted up as the default aggregation for your fact table column then the following filter will be applied by OBIEE: SUM(Column) > @{Presentation_Variable}.

                  • 6. Re: Creating a Threshold Prompt
                    3025352

                    Thank you Federico so much. After I kept looking over the prompt and how I was filtering off of it, I realized that I didn't have the default value listed in the correct spot. I had the default value listed in the prompt but not in the filter. It's now working! Cheers!