9 Replies Latest reply on Mar 13, 2017 6:34 AM by Saro

    Column hide is not working

    Saro

      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

        • 1. Re: Column hide is not working
          Pedro F

          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")

          1 person found this helpful
          • 2. Re: Column hide is not working
            Saro

            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

            • 3. Re: Column hide is not working
              Gianni Ceresa

              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 ...

              • 4. Re: Column hide is not working
                Saro

                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

                • 5. Re: Column hide is not working
                  Pedro F

                  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.

                  1 person found this helpful
                  • 6. Re: Column hide is not working
                    Saro

                    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

                    • 7. Re: Column hide is not working
                      Pedro F

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

                       

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

                      • 8. Re: Column hide is not working
                        Saro

                        Hi Pedro,

                         

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

                         

                        Saro

                        • 9. Re: Column hide is not working
                          Saro

                          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