1 2 Previous Next 29 Replies Latest reply on Dec 29, 2015 11:59 AM by Saro

    Hiding rows in obiee 11g

    Saro

      Hi friends,

       

      I have the below report with measure values as im displaying measure value as label like below

       

      img4.PNG

       

      In the above report you can see that for the measure column Furniture Adjustment Renewal measure column is 0, so i do want to hide that entire column in report if that value is 0. Hence for that i tried like by applying the condition under the column properties like Furniture adjustment renewal is equal to 0 then under the custom CSS style i added display:none and as a result im getting like below

       

      img5.PNG

       

      But i can see the values been erased, but i dont want that to happen instead i need the entire Furniture adjustment renewal column to be removed if the value is equal to 0.

       

      How i can achieve with my scenario friends. Thanks in advance.

       

      Regards,

      Saro

        • 1. Re: Hiding rows in obiee 11g
          Thomas Dodds

          Filter the report for measure value not equal to 0 ...

          • 2. Re: Hiding rows in obiee 11g
            Saro

            nope the filter is not working, as it is resulting in no values if i do so. In my report im displaying measures labels as a row in the report.

             

            Thanks for your reply.

            • 3. Re: Hiding rows in obiee 11g
              Thomas Dodds

              so your data is coming in columns ... each measure in it's own column ... once you have the data you could apply Selection Steps to keep/remove each measure based on whether it is equal to zero or not (from the OBI Docs:  Selection steps are applied after the query is aggregated and affect only the members displayed, not the resulting aggregate values.)

              • 4. Re: Hiding rows in obiee 11g
                SriniVEERAVALLI

                Option 1: Put report level condition to show values >0

                Option 2: Create another column for metric as a flag with case statement case when metric>0 then 'Y' else 'N' end

                use this column in the table/pivot's page section; when you select Y that would exclude 0 rows.


                1 person found this helpful
                • 5. Re: Hiding rows in obiee 11g
                  Saro

                  Dear Srini,

                   

                  Thanks for the idea, will work on it and let you know;-)

                   

                  Regards,

                  Saro

                  • 6. Re: Re: Hiding rows in obiee 11g
                    Saro

                    Hi Thomas,

                     

                    so your data is coming in columns ... each measure in it's own column ... once you have the data you could apply Selection Steps to keep/remove each measure based on whether it is equal to zero or not (from the OBI Docs:  Selection steps are applied after the query is aggregated and affect only the members displayed, not the resulting aggregate values.)


                    Though i do understand your suggestion correctly, as im displaying each column as row in my report with measure labels as row. Like you said if i try to restrict the zero rows(in my case a column) to exclude zero i cannot do that, as the restriction can be made only to the members of the measures and here my measure labels are not appearing as the members that is been created using calculative item or group. Due to that i couldnt deal with selection steps. Correct me if im wrong;-)


                    Thanks for your reply.


                    Regards,

                    Saro

                    • 7. Re: Hiding rows in obiee 11g
                      Thomas Dodds

                      The only 'other' way I know to hide a column is in the RPD via session variables ...

                       

                      Essentially you create a variable for each column you want to hide and set the default initializer string to 'HIDE' ... then you query a table for that column and if there is any value greater than zero (or what ever your case is) you change the initialized value to something else:

                       

                      SELECT CASE WHEN SUM(NVL(MEASURE_COLUMN_NAME,0)) > 0 then 'SHOW' ELSE 'HIDE' END

                       

                      Then you changes the presentation column's Hide if property to evaluate the session variable to be: VALUEOF(NQ_SESSION.MEASURE_VAR_HIDE) = 'HIDE'

                       

                      The above would be a 'global' switch based on the condition of the data at the time of the user session start, not on a query by query basis.

                      1 person found this helpful
                      • 8. Re: Hiding rows in obiee 11g
                        Saro

                        Thanks Thomas i will check with your approach too;-)

                        • 9. Re: Hiding rows in obiee 11g
                          Saro

                          Hi Thomas,

                          I tried with your way but it dint work.

                           

                          Same like i created session intialization block called hide property with the below as default intialization string

                           

                          select case when sum(nvl(basic_sal,0)) > 0 then 'SHOW' ELSE 'HIDE' END, case

                          when sum(nvl(furnishing_adjustment_renewal,0)) > 0 then 'SHOW' ELSE 'HIDE' END, case

                          when sum(nvl(exam_tuition_reimbursement,0)) > 0 then 'SHOW' ELSE 'HIDE' END, case

                          when sum(nvl(transport_allowance,0)) > 0 then 'SHOW' ELSE 'HIDE' END

                          FROM XXMP_PAY_ALLOW_FACT_V

                           

                          and with the below session variables

                          img8.PNG

                          With the Test results i getting the below value

                           

                          img9.PNG

                           

                          So from the above the basic sal and the transport allowance has to show and the remaining furnishing and exam tuition fees columns need to hide in the report.

                           

                          Also in the respective presentation column i have specified hide if property like below

                           

                          VALUEOF(NQ_SESSION."FURNISHING_ADJUSTMENT_RENEWAL") = 'HIDE'

                           

                          Similarly i have set the above property for my remaining four columns with their respective variable name.

                           

                          Now if i check the report in the front end, there it is not working as it showing both the furnishing and exam tuition fees columns that suppose to hide in the report, as its value is 0.

                           

                          Regards,

                          Saro

                          • 10. Re: Hiding rows in obiee 11g
                            Thomas Dodds

                            What version of OBIEE are you running?

                             

                            It's a bug in OBIEE 11.1.1.5.

                             

                            Bug 12352010: RESTRICTED COLUMNS SHOULD NOT SHOW IN SUBJECT AREA

                             

                            It is fixed in the version 11.1.1.6.

                            • 11. Re: Hiding rows in obiee 11g
                              Saro

                              Hi Thomas,

                               

                              Im using 11.1.1.7. Is the steps that i followed is correct or did i missed any?

                               

                              Regards,

                              Saro

                              • 12. Re: Hiding rows in obiee 11g
                                Thomas Dodds

                                Look in the docs - section :

                                Controlling Presentation Object Visibility

                                http://docs.oracle.com/cd/E28280_01/bi.1111/e10540/presentationlayer.htm#BIEMG3847

                                 

                                There's a bit that states:  Constant. Enter any non-zero constant in the field to hide the object. Enter 0 or leave the field blank to display the object.

                                 

                                You could try setting your hide variables to 1 and then switch to 0 to show ... but I've seen the 'HIDE'/'SHOW' work -- might be a version thing ... so try the 1s and 0s.

                                • 13. Re: Hiding rows in obiee 11g
                                  Saro

                                  Will check by replacing show as 0 and hide as 1 in the initialization block and with variable default initializer as 1(which is used for hide) and under the hide if property of the presentation column will replace like below and will let you know. In the meantime kindly confirm whether the change of steps that i just briefed is fine.

                                   

                                  VALUEOF(NQ_SESSION."FURNISHING_ADJUSTMENT_RENEWAL") = 1

                                   

                                  Thanks for your time.

                                   

                                  Regards,

                                  Saro

                                  • 14. Re: Hiding rows in obiee 11g
                                    Saro

                                    Hi Thomas,

                                     

                                    Nope no luck interms of 0 and 1 still the same issue as i can see the object column which shows zero in my report still.

                                     

                                    Regards,

                                    Saro

                                    1 2 Previous Next