This discussion is archived
10 Replies Latest reply: Nov 28, 2012 2:10 AM by layman RSS

SQL prompts show no data OBIEE 11.1.1.6

JoeSSI Newbie
Currently Being Moderated
Dear Gurus,

I have prompt with parameters like:
Province
City
Hospital Name

I want Hospital Name parameter filtered by Province & City so I use this in my Sql Prompts Syntax:

SELECT "Dim Hospital"."Hospital Name" FROM "eHealth CCD Vital Sign" WHERE "Dim Region"."Province"='{v_province}'

I tested it and it show no data (appear search....) in Hospital Name drop down.
I run it in Firefox, IE, Chrome, still the same.
It work when I remove WHERE from the syntax.

Is this bugs?
I use obiee 11.1.1.6.0

Any help will be appreciated

Regards

JOE
  • 1. Re: SQL prompts show no data OBIEE 11.1.1.6
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Are you using presentation variable? In that case it suppose to be like '@{v_province}'
    If this is not helping you then use default value for variable like '@{v_province}{value}'

    If helps pls mark as correct else let me know details.
  • 2. Re: SQL prompts show no data OBIEE 11.1.1.6
    JoeSSI Newbie
    Currently Being Moderated
    Hi,

    Thx for reply, yes I used the presentation variable(set in dashboard).
    what function of {value} next to {v_province}.
    I have to put {value} this?

    Regards

    JOE
  • 3. Re: SQL prompts show no data OBIEE 11.1.1.6
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Then your query should be like


    SELECT "Dim Hospital"."Hospital Name" FROM "eHealth CCD Vital Sign" WHERE "Dim Region"."Province"='@{v_province}'

    for more details check these
    http://gerardnico.com/wiki/dat/obiee/presentation_variable
    http://obieetutorialguide.blogspot.com/2012/02/using-variables-in-obiee.html

    If helps pls mark as correct/helpful
  • 4. Re: SQL prompts show no data OBIEE 11.1.1.6
    JoeSSI Newbie
    Currently Being Moderated
    Hi,

    I have used:
    SELECT "Dim Hospital"."Hospital Name" FROM "eHealth CCD Vital Sign" WHERE "Dim Region"."Province"='@{v_province}'

    and when click drop down in Hospital Name, still show search.... and no data appear

    Please help

    Regards

    JOE
  • 5. Re: SQL prompts show no data OBIEE 11.1.1.6
    MuRam Expert
    Currently Being Moderated
    Hi,

    Actually when you need to populate the prompt values based on another prompt then you need to click on the APPLY button first to set the presentation variable value and then use them in the prompt.
    Or you can create separate prompt sections with two APPLY buttons.
    However without using the first prompt with presentation variable in the second prompt will show 'NO DATA' only and its logically correct :)

    Hope this helped/ answered

    Regards
    MuRam

    Edited by: MuRam on Nov 22, 2012 1:03 AM
  • 6. Re: SQL prompts show no data OBIEE 11.1.1.6
    layman Newbie
    Currently Being Moderated
    If Hospital and Province are columns in the same prompt, you can use the 'Limit values by' functionality in the Hospital column criteria(along with All column values instead of SQL results) and choose Province (or All prompts if indeed need to restrict by all other columns in that prompt). This way the Hospital column will be restricted based on Province as soon as Province is selected (ie without hitting Apply)

    If Hospital and Province are in different prompts then you can use the presentation variable and SQL results method you have been trying.

    Increase the logging level for the user and check what physical query is being fired for populating the Hospital column.
  • 7. Re: SQL prompts show no data OBIEE 11.1.1.6
    JoeSSI Newbie
    Currently Being Moderated
    Hi,

    Thank you for explanation.
    My complete parameters are:
    Province
    Kabupaten
    Kecamatan
    Hospital Name

    Which I want the Hospital Name depend on 3 other parameters above it.
    @Muram, I have followed your suggest to apply Province Parameter first then click on Hospital Name Parameter.
    Still not show any data.

    Please help

    Regards

    JOE
  • 8. Re: SQL prompts show no data OBIEE 11.1.1.6
    layman Newbie
    Currently Being Moderated
    If you want to restrict hospital name by all the other 3 parameters in the prompt, have you tried using the Limit values by - 'All prompts' feature available? Not sure why you would want to use a SQL to achieve it. If the logical model is built correctly, you would certainly get right results with this feature, even without hitting apply
  • 9. Re: SQL prompts show no data OBIEE 11.1.1.6
    JoeSSI Newbie
    Currently Being Moderated
    Hi,

    Thx for reply, I want to use SQL because if one of the parameters above Hospital Name not choosen, the hospital name still depend on another parameters which choosen.

    Regards

    JOE
  • 10. Re: SQL prompts show no data OBIEE 11.1.1.6
    layman Newbie
    Currently Being Moderated
    Not sure how you are going to achieve that in the SQL either

Legend

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