1 2 3 Previous Next 36 Replies Latest reply on Jan 23, 2015 1:40 PM by Alex1 Go to original post
      • 30. Re: replace null value on answers
        Alex1
        Alex1 Jan 22, 2015 11:17 AM (in response to Nosenco Victor)

        Thanks Nosenco for your reply,

         

        But my database is oracle and I have this options on by default.

         

        Add some snapshot:

         

        I have trying IFNULL in logical layer or in presentation layer and I don't have any effect to my analysis.... I think that IFNull function doesn't work in obiee 11.1.1.7.0

         

        It's very weird.

         

        Any help?

        • 31. Re: replace null value on answers
          Gianni Ceresa
          Gianni Ceresa Jan 22, 2015 11:24 AM (in response to Alex1)

          IfNull perfectly work (on all the 11.1.1.7+ versions, just didn't try on the patch released 2 days ago...).

           

          As we asked few posts back: post your physical query !

           

          Because there is something you are not telling us or that we are not understanding in your model.

          • 32. Re: replace null value on answers
            Alex1
            Alex1 Jan 23, 2015 12:45 PM (in response to Gianni Ceresa)

            Thanks @Gianni Ceresa for your reply.

             

            I try to explain my problem from the beginning for a better understand.

             

            I'll explain based on Excel Demo Datasource that I have created although OracleDB should be the same...

             

            My excel DS lookslike:

            Fact Table

            Office Dim

             

            I config an ODBC DS Connection and Mapping two tables.

            Join Fact_Table through Office_Dim like this:

            Next step: Create a new businesmodel and drag two physical tables into Demo BM.

            Drag BM to Presentation Layer.

            Save RPD without any error.

            Then I’ll do an analisys.

             

            I create a PIVOT TABLE and I see this results:

            You can see that my measure is with IFNULL formula.

            But this formula doesn’t effect to my table.

            Please if you need more info please ask me.

            Any help???

            Thanks!

            • 33. Re: replace null value on answers
              Gianni Ceresa
              Gianni Ceresa Jan 23, 2015 1:18 PM (in response to Alex1)

              Hi Alex1 ,

              thanks for all the details (I guess that after 30 answers it was good to restart from scratch).

               

              So, what you see in the pivot is perfectly correct, and it's also correct that IFNULL doesn't work there because there is no NULL in your pivot.

              IFNULL would match (with your current model), if and only if in your Fact table you have 4 extra rows:

              Ana, 1, NULL
              David, 2, NULL

              John, 2, NULL

              Rachel, 1, NULL

               

              Right now your empty cells aren't returned at all from the physical query (and it's why we were asking to see it earlier).

               

              Your requirement is to have "-1000" in these empty cells, right? And you say you can't use the column format to define "-1000" as the value to display for empty cells because you need to sum rows / columns and you want the -1000 to be counted in the calculation?

              • 34. Re: replace null value on answers
                Alex1
                Alex1 Jan 23, 2015 1:17 PM (in response to Gianni Ceresa)

                Exactly Gianni Ceresa.

                 

                This is my problem.

                • 35. Re: replace null value on answers
                  Gianni Ceresa
                  Gianni Ceresa Jan 23, 2015 1:19 PM (in response to Alex1)

                  If you really want "-1000" as a real number there you need to find a way to "produce" that cell in the physical query resultset, a dirty way to do it would be a cartesian product between your 2 tables: in that way your resultset has at least a cell for every single cell of your pivot.

                  Plan B), create these empty rows in the Fact table when loading it by ETL, it's less dirty at least (your DB will not like a cartesian product done every time somebody run a query...)

                  1 person found this helpful
                  • 36. Re: replace null value on answers
                    Alex1
                    Alex1 Jan 23, 2015 1:40 PM (in response to Gianni Ceresa)

                    I'll try your options

                     

                    Thanks!

                    1 2 3 Previous Next