Home » Fusion Middleware & Colab Suite » Business Intelligence » variable prompt - default selection - SQL results (OBIEE 12.2.1.2.0)
variable prompt - default selection - SQL results [message #678460] Fri, 06 December 2019 05:39 Go to next message
t_97
Messages: 6
Registered: December 2019
Junior Member
Hello together,

I have a problem with setting a default selection for a variable prompt by using the option "SQL results".

It's a prompt for the calendar month (numeric expression from 1 to 12). I want that the default selection is 12 if the current month is January and if not it should be the previous month. So for example: If the current month is November the default selection should be 10.

I tried the following SQL text:

>> CASE WHEN VALUEOF("current_calendar_month") = 1 THEN 12 ELSE VALUEOF("previous_calendar_month") END<<
--> The Result: The default value is "(All Column Vlaues)".

And when I run this formula as an analysis, it returns the correct data. (Now it's December therefore the result of the analysis with this formula is 11) So the formula should be correct - at least I think so.

I did a lot of research in some forums but I didn't find anything... So I would be very gratful if somebody could help me! Smile

Best regards and thank you in advance!!

Tobias
  • Attachment: Unbenannt.png
    (Size: 32.20KB, Downloaded 488 times)
Re: variable prompt - default selection - SQL results [message #678463 is a reply to message #678460] Fri, 06 December 2019 06:14 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'd be far better off asking this question in an OBIEE forum rather than this one.
Re: variable prompt - default selection - SQL results [message #678464 is a reply to message #678463] Fri, 06 December 2019 06:35 Go to previous messageGo to next message
t_97
Messages: 6
Registered: December 2019
Junior Member
Could you recommend one? Smile
Re: variable prompt - default selection - SQL results [message #678465 is a reply to message #678464] Fri, 06 December 2019 06:52 Go to previous messageGo to next message
Bill B
Messages: 1968
Registered: December 2004
Senior Member
If you mean the current days month - 1 then the following will do what you want

SELECT CASE
           WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'MM')) - 1 = 0 THEN 12
           ELSE TO_NUMBER (TO_CHAR (SYSDATE, 'MM')) - 1
       END
  FROM DUAL;
Re: variable prompt - default selection - SQL results [message #678467 is a reply to message #678465] Fri, 06 December 2019 07:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2977
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or:

WITH T AS (
           SELECT  LEVEL MONTH
             FROM  DUAL
             CONNECT BY LEVEL <= 12
          )
SELECT  MONTH,
        MOD(MONTH + 10,12) + 1 PREVIOUS_MONTH
  FROM  T
/

     MONTH PREVIOUS_MONTH
---------- --------------
         1             12
         2              1
         3              2
         4              3
         5              4
         6              5
         7              6
         8              7
         9              8
        10              9
        11             10

     MONTH PREVIOUS_MONTH
---------- --------------
        12             11

12 rows selected.

SQL> 
SY.
Re: variable prompt - default selection - SQL results [message #678470 is a reply to message #678467] Fri, 06 December 2019 07:55 Go to previous messageGo to next message
t_97
Messages: 6
Registered: December 2019
Junior Member
Thank your for your help!!! Smile

But unfortunately it still doesn't work Sad
Re: variable prompt - default selection - SQL results [message #678471 is a reply to message #678470] Fri, 06 December 2019 07:59 Go to previous messageGo to next message
t_97
Messages: 6
Registered: December 2019
Junior Member
There is always the following error message:

"Invalid Alias Format : Table_name.Column_name required. OK (Ignore Error)"
Re: variable prompt - default selection - SQL results [message #678472 is a reply to message #678471] Fri, 06 December 2019 08:07 Go to previous messageGo to next message
t_97
Messages: 6
Registered: December 2019
Junior Member
I tried something:

If I use the following SQL formula in an analysis the result is 12. --> MONTH(TIMESTAMPADD(SQL_TSI_MONTH, 0, CURRENT_DATE))

But if I use this SQL text in the dashboard prompt for defining the default selection using the opiton "SQL results", OBIEE selects "(All Column Vlaues)" in the prompt preview... Is somebody able to explain to me why this formula works in an analysis but not in a prompt? :/
Re: variable prompt - default selection - SQL results [message #678473 is a reply to message #678472] Fri, 06 December 2019 08:29 Go to previous message
t_97
Messages: 6
Registered: December 2019
Junior Member
I've found a solution! Smile

SELECT CASE
WHEN MONTH(TIMESTAMPADD(SQL_TSI_MONTH, 0, CURRENT_DATE)) = 1 THEN 12
ELSE MONTH(TIMESTAMPADD(SQL_TSI_MONTH, -1, CURRENT_DATE))
END
FROM "Training"

Nevertheless: Thanks to everybody who has commended something! Smile
Previous Topic: How to get Physical sqls in Obiee12c
Next Topic: Which One Is The Best BI Tool
Goto Forum:
  


Current Time: Wed Aug 12 07:41:07 CDT 2020