Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Column hide is not working

Received Response
45
Views
9
Comments
Saro
Saro Rank 5 - Community Champion
edited Aug 13, 2024 6:05PM in Oracle Analytics Cloud and Server

Hi friends,

Im in obiee 11.1.1.7.150120. I need to hide a column in the report based upon the presentation variable value from the prompt. Hence for that i created one dummy column in the BMM layer named as None with a value like ''

image1.PNG

After that i dragged that column None in my presentation layer and used in my analysis with the below property setting

image2.PNG

Now if i use that column None in my analysis for the other columns in my analysis under the edit formula like below means,

image3.PNG

So if u take in my above case, if my presentation variable p_c1 returns null then it has to take the column "none"."none" which is nothing like this columns needs to hide. But the problem is if i dint select any value in my prompt, then the presentation variable is returning 0 and not null and because of that my column is not hiding in the report.

I referred the below link for doing this hide option activity

OBIEE in IL: OBIEE - Selecting columns dynamically - part4, working with and selecting none

Not sure how i can make my presentation variable to return null if i dint select any value in my prompt(that is referred with my presentation variable) inorder for my column to hide.

Thanks in advance.

Regards,

Saro

Answers

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    Hi,

    You don't really have to make your presentation variable return null, you can just set the default value of your presentation variable in your column formula be null with something like this:

    ifnull(@{p_c1}['@']{cast (null as char)}, "None"."None")

  • Saro
    Saro Rank 5 - Community Champion

    Dear Pedro,

    Thanks for the reply. Actually the value that im passing to my presentation variable p_c1 through variable prompt will bring the measure value in my column and not the attribute value. Hence the below formula of

    ifnull(@{p_c1}['@']{cast (null as char)}, "None"."None") is not hiding the column, as my presentation variable by default passes 0 as a measure value from the variable prompt to my column which inturn stopping me from hiding the column from the analysis.

    Regards

    Saro

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    is it me or your whole thread focus on the wrong bit of your situation?

    You posted lot of details about your columns etc. but still say your variable return 0 and so the column isn't "None"."None" but the value of the variable ... 0.

    So where does your variable come from? How did you setup the prompt defining the variable?

    That's the important piece, piece about which I don't see a single word ...

  • Saro
    Saro Rank 5 - Community Champion

    Dear Gianni,

    Apologize if my thread dint explain the problem clearly before.

    Here is a brief scenario:

    1.) My ultimate output is i need to have the above two measure columns(line base amount and pda amount billed) as a prompt values in one of my dashboard prompt, so that i can select those columns as a value in the prompt to see the column appearing in the report[hide/show], similarly like in the below screenshot

    Example screenshot: before selection

    gianni2.PNG

    After selection:

    gianni3.PNG

    But my ultimate output is the one expected in the step 2.

    Now finally i have the below analysis, but a slight change instead of picking up the measure column directly in my analysis im substituting the presentation variable p_c1 to bring the respective col_code from my prompt to the column formula section like below

    gianni7.PNG

    gianni8.PNG

    I have substituted like above in the column formula to get my column code through presentation variable from the variable prompt, so that i can see my measure value in the column according to my prompt column selection in the dashboard.

    Also if you noticed that i have substituted none.none column in the expression which i explained in steps in my first post to hide the column if the p_c1 returns null(that is if i dont select any value in the prompt)

    But the problem here is, i dint select any value in my prompt p_c1 but it returns 0. Because it is returning zero instead of null my formula that is substituted for the column @{p_c1a}: ifnull(@{p_c1},"None"."None") is not hiding the column.

    I hope that i explained my scenario to ur understanding. Let me know if any issues.

    Thanks for your time.

    Saro

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    Hi Saro,

    If I understood your situation correctly, I think you're overcomplicating your analysis. You want an analysis where the 1st column is "Pha Vendor Name" and the 2nd column is either "Fact - PO"."Line Based Amount", "Fact - PO"."Pda Amount Billed" or an hidden column.

    If that's what you want, just build an analysis where the 1st column is "Pha Vendor Name" and your 2nd column has the following formula:

    CASE WHEN '@{pv_var}' = '"Fact - PO"."Line Based Amount"' THEN "Fact - PO"."Line Based Amount"

    WHEN '@{pv_var}' = '"Fact - PO"."Line Based Amount"' THEN "Fact - PO"."Line Based Amount"

    ELSE "None"."None"

    END

    Then create a dashboard prompt with a variable prompt where you have a choice list with 2 values: "Fact - PO"."Line Based Amount" and "Fact - PO"."Pda Amount Billed" and you leave the default value empty. That way, when you load the analysis on a dashboard page, you should only see the 1st column. Once the user selects a value in the prompt, you'll see the 2nd column.

  • Saro
    Saro Rank 5 - Community Champion

    Hi Pedro,

    Again thanks for the reply. Nope i dont want to show different column value in the same column. I want to hide/show the column in the analysis according to the selection of the columns in the Prompt.

    The below link exactly explains, my scenario. Also if u can have a look @ my previous reply step by step then u can understand my scenario where im facing difficulty in hiding the column if the prompt value is not selected.

    OBIEE in IL: OBIEE - Selecting columns dynamically - part5, the single prompt solution

    Saro

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    Try changing your formula to ifnull('@{p_c1}',"None"."None")

    Putting the single quotes around the presentation variable may solve your issue

  • Saro
    Saro Rank 5 - Community Champion

    Hi Pedro,

    My variable p_c1 is returning number and not the attribute so i cant invoke that using '@{p_c1}'

    Saro

  • Saro
    Saro Rank 5 - Community Champion

    Hi Pedro,

    Finally the column hide worked with the below formula, if the prompt value is null. This worked, after specifying the hideif condition in presentation column of None to 1=1. Now i can see the columns hide/show in the analysis according to the prompt selection in dashboard.

    ifnull(@{p_c1},"None"."None")

    Thanks for your reply.

    Regards,

    Saro