1 2 3 Previous Next 36 Replies Latest reply on Jan 23, 2015 1:40 PM by Alex1

    replace null value on answers

    Alex1

      Hi experts

      I'm using 11.1.1.7.0 version.

      I have a table that contains NULL Values,

      I want to replace NULL with -1000 but I can't use this option:

      Go criteria tab and edit the ‘column properties’ associated with your fact measure.

      Choose the ‘Data Format’ tab, tick to override the default format and choose ‘Custom’.

      #,##0;-#,##0;0

       

      Can you say me if exists any option?

      I read that it's a bug but on version 11.1.1.7 is fixed.

       

      Any help?

      Many thanks!

        • 1. Re: replace null value on answers
          Nosenco Victor
          • 2. Re: replace null value on answers
            Alex1

            Hi,

            Thanks for your answer but not work.

             

            I have written in measure column this formula:

             

            My pivot table result don't show "-1000" instead NULL values...

             

             

            Any help?

            • 3. Re: replace null value on answers
              Nosenco Victor

              Hi,

              It is impossible .

              Are you sure that had modified formula or column formatting for the desired column.

              If you are sure post entire xml of the analysis.

              • 4. Re: replace null value on answers
                Christian Berg

                Do it pre-agg in the business model. Change the physical mapping of the column to an IfNull formula.

                • 5. Re: replace null value on answers
                  Alex1

                  Of course!

                   

                  <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">

                     <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Test&quot;">

                        <saw:columns>

                           <saw:column xsi:type="saw:regularColumn" columnID="c7b0869ee87c35b79">

                              <saw:columnFormula>

                                 <sawx:expr xsi:type="sawx:sqlExpression">"Dim_ESL"."ID A"</sawx:expr></saw:columnFormula>

                              <saw:tableHeading>

                                 <saw:caption fmt="text">

                                    <saw:text>Dim_A</saw:text></saw:caption></saw:tableHeading>

                              <saw:columnHeading>

                                 <saw:caption fmt="text">

                                    <saw:text>ID A</saw:text></saw:caption></saw:columnHeading></saw:column>

                           <saw:column xsi:type="saw:regularColumn" columnID="cd7e10810ca5d5543">

                              <saw:columnFormula>

                                 <sawx:expr xsi:type="sawx:sqlExpression">"Dim_Titols"."ID B"</sawx:expr></saw:columnFormula>

                              <saw:tableHeading>

                                 <saw:caption fmt="text">

                                    <saw:text>Dim_B</saw:text></saw:caption></saw:tableHeading>

                              <saw:columnHeading>

                                 <saw:caption fmt="text">

                                    <saw:text>ID B</saw:text></saw:caption></saw:columnHeading></saw:column>

                           <saw:column xsi:type="saw:regularColumn" columnID="c71f921dc5171a83e">

                              <saw:columnFormula>

                                 <sawx:expr xsi:type="sawx:sqlExpression">IFNULL("Table"."Value", 0)</sawx:expr></saw:columnFormula>

                              <saw:displayFormat>

                                 <saw:formatSpec suppress="repeat" wrapText="true">

                                    <saw:dataFormat xsi:type="saw:number" commas="true" negativeType="minus" minDigits="0" maxDigits="0"/></saw:formatSpec></saw:displayFormat>

                              <saw:columnHeading>

                                 <saw:displayFormat>

                                    <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column></saw:columns></saw:criteria>

                     <saw:views currentView="0">

                        <saw:view xsi:type="saw:compoundView" name="compoundView!1">

                           <saw:cvTable>

                              <saw:cvRow>

                                 <saw:cvCell viewName="pivotTableView!1"/></saw:cvRow></saw:cvTable></saw:view>

                        <saw:view xsi:type="saw:pivotTableView" name="pivotTableView!1" scrollingEnabled="true">

                           <saw:edges>

                              <saw:edge axis="page" showColumnHeader="true"/>

                              <saw:edge axis="section"/>

                              <saw:edge axis="row" showColumnHeader="true">

                                 <saw:edgeLayers>

                                    <saw:edgeLayer type="column" columnID="c7b0869ee87c35b79"/></saw:edgeLayers></saw:edge>

                              <saw:edge axis="column" showColumnHeader="rollover">

                                 <saw:edgeLayers>

                                    <saw:edgeLayer type="column" columnID="cd7e10810ca5d5543"/>

                                    <saw:edgeLayer type="measure"/></saw:edgeLayers></saw:edge></saw:edges>

                           <saw:measuresList>

                              <saw:measure columnID="c71f921dc5171a83e"/></saw:measuresList></saw:view></saw:views></saw:report>

                   

                   

                   

                  Thanks for your time!

                  • 6. Re: replace null value on answers
                    Alex1

                    Hi Christian,

                     

                    I click on source properties of my subject area (logical layer), then I choose Column Mapping tab and I have chosen measure that I want to see -1000 values instead NULL.

                     

                    I edit formula with expression builder like this: IfNull("conn".""."AA"."Fact_Table"."Value" , -1000)

                    Save RPD without errors..

                    I have a new report and I don't see -1000 values. It seems that it has no effect this advice .

                     

                    Any help?

                    • 7. Re: Re: replace null value on answers
                      Nosenco Victor

                      It`s a magic.

                      The issue can be in default format settings.

                      Try this xml

                      <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
                      
                          <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Test&quot;">
                      
                              <saw:columns>
                      
                                  <saw:column xsi:type="saw:regularColumn" columnID="c7b0869ee87c35b79">
                      
                                      <saw:columnFormula>
                      
                                          <sawx:expr xsi:type="sawx:sqlExpression">"Dim_ESL"."ID A"</sawx:expr>
                                      </saw:columnFormula>
                      
                                      <saw:tableHeading>
                      
                                          <saw:caption fmt="text">
                      
                                              <saw:text>Dim_A</saw:text>
                                          </saw:caption>
                                      </saw:tableHeading>
                      
                                      <saw:columnHeading>
                      
                                          <saw:caption fmt="text">
                      
                                              <saw:text>ID A</saw:text>
                                          </saw:caption>
                                      </saw:columnHeading>
                                  </saw:column>
                      
                                  <saw:column xsi:type="saw:regularColumn" columnID="cd7e10810ca5d5543">
                      
                                      <saw:columnFormula>
                      
                                          <sawx:expr xsi:type="sawx:sqlExpression">"Dim_Titols"."ID B"</sawx:expr>
                                      </saw:columnFormula>
                      
                                      <saw:tableHeading>
                      
                                          <saw:caption fmt="text">
                      
                                              <saw:text>Dim_B</saw:text>
                                          </saw:caption>
                                      </saw:tableHeading>
                      
                                      <saw:columnHeading>
                      
                                          <saw:caption fmt="text">
                      
                                              <saw:text>ID B</saw:text>
                                          </saw:caption>
                                      </saw:columnHeading>
                                  </saw:column>
                      
                                  <saw:column xsi:type="saw:regularColumn" columnID="c71f921dc5171a83e">
                      
                                      <saw:columnFormula>
                      
                                          <sawx:expr xsi:type="sawx:sqlExpression">IFNULL("Table"."Value", 0)</sawx:expr>
                                      </saw:columnFormula>
                      
                                      <saw:displayFormat>
                                          <saw:formatSpec suppress="repeat" width="10%" wrapText="false">
                                              <saw:dataFormat xsi:type="saw:custom" customFormat="#,##0.00;-#,##0.00;-1000"/>
                                          </saw:formatSpec>
                                      </saw:displayFormat>
                      
                                      <saw:columnHeading>
                      
                                          <saw:displayFormat>
                      
                                              <saw:formatSpec/>
                                          </saw:displayFormat>
                                      </saw:columnHeading>
                                  </saw:column>
                              </saw:columns>
                          </saw:criteria>
                      
                          <saw:views currentView="0">
                      
                              <saw:view xsi:type="saw:compoundView" name="compoundView!1">
                      
                                  <saw:cvTable>
                      
                                      <saw:cvRow>
                      
                                          <saw:cvCell viewName="pivotTableView!1"/>
                                      </saw:cvRow>
                                  </saw:cvTable>
                              </saw:view>
                      
                              <saw:view xsi:type="saw:pivotTableView" name="pivotTableView!1" scrollingEnabled="true">
                      
                                  <saw:edges>
                      
                                      <saw:edge axis="page" showColumnHeader="true"/>
                      
                                      <saw:edge axis="section"/>
                      
                                      <saw:edge axis="row" showColumnHeader="true">
                      
                                          <saw:edgeLayers>
                      
                                              <saw:edgeLayer type="column" columnID="c7b0869ee87c35b79"/>
                                          </saw:edgeLayers>
                                      </saw:edge>
                      
                                      <saw:edge axis="column" showColumnHeader="rollover">
                      
                                          <saw:edgeLayers>
                      
                                              <saw:edgeLayer type="column" columnID="cd7e10810ca5d5543"/>
                      
                                              <saw:edgeLayer type="measure"/>
                                          </saw:edgeLayers>
                                      </saw:edge>
                                  </saw:edges>
                      
                                  <saw:measuresList>
                      
                                      <saw:measure columnID="c71f921dc5171a83e"/>
                                  </saw:measuresList>
                              </saw:view>
                          </saw:views>
                      </saw:report>
                      
                      • 8. Re: replace null value on answers
                        Alex1

                        Thanks for your answer but I said in my first post that I can't use default format settings


                        I knew this option but I want to see -1000 value without this option because later I have to apply conditional format and this option doesnt work for my requeriment.


                        Maybe Christian suggestion is fine because I have to modify on logical layer but I can't see good results!


                        Any help?

                        • 9. Re: Re: replace null value on answers
                          Nosenco Victor

                          Let`s try to understand is problem in logic or in visualization:

                          What value of column do you see as the result of export answer data to xml.

                          Or try to apply conditional formatting like this

                           

                          <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
                          
                              <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Test&quot;">
                          
                                  <saw:columns>
                          
                                      <saw:column xsi:type="saw:regularColumn" columnID="c7b0869ee87c35b79">
                          
                                          <saw:columnFormula>
                          
                                              <sawx:expr xsi:type="sawx:sqlExpression">"Dim_ESL"."ID A"</sawx:expr>
                                          </saw:columnFormula>
                          
                                          <saw:tableHeading>
                          
                                              <saw:caption fmt="text">
                          
                                                  <saw:text>Dim_A</saw:text>
                                              </saw:caption>
                                          </saw:tableHeading>
                          
                                          <saw:columnHeading>
                          
                                              <saw:caption fmt="text">
                          
                                                  <saw:text>ID A</saw:text>
                                              </saw:caption>
                                          </saw:columnHeading>
                                      </saw:column>
                          
                                      <saw:column xsi:type="saw:regularColumn" columnID="cd7e10810ca5d5543">
                          
                                          <saw:columnFormula>
                          
                                              <sawx:expr xsi:type="sawx:sqlExpression">"Dim_Titols"."ID B"</sawx:expr>
                                          </saw:columnFormula>
                          
                                          <saw:tableHeading>
                          
                                              <saw:caption fmt="text">
                          
                                                  <saw:text>Dim_B</saw:text>
                                              </saw:caption>
                                          </saw:tableHeading>
                          
                                          <saw:columnHeading>
                          
                                              <saw:caption fmt="text">
                          
                                                  <saw:text>ID B</saw:text>
                                              </saw:caption>
                                          </saw:columnHeading>
                                      </saw:column>
                          
                                      <saw:column xsi:type="saw:regularColumn" columnID="c71f921dc5171a83e">
                          
                                          <saw:columnFormula>
                          
                                              <sawx:expr xsi:type="sawx:sqlExpression">IFNULL("Table"."Value", -1000)</sawx:expr>
                                          </saw:columnFormula>
                          
                                          <saw:displayFormat>
                          
                                              <saw:formatSpec suppress="repeat" wrapText="true">
                          
                                                  <saw:dataFormat xsi:type="saw:number" commas="true" negativeType="minus" minDigits="0" maxDigits="0"/></saw:formatSpec>
                                                                 <saw:conditionalDisplayFormats>
                                            <saw:conditionalDisplayFormat>
                                               <saw:formatRule>
                                                  <saw:condition>
                                                     <sawx:expr xsi:type="sawx:comparison" op="equal">
                                                        <sawx:expr xsi:type="sawx:columnRefExpr" columnID="c960e0dd0e8f4ada1"/>
                                                        <sawx:expr xsi:type="xsd:decimal">-1000</sawx:expr></sawx:expr></saw:condition>
                                                  <saw:formatSpec backgroundColor="#CC3333" wrapText="true"/></saw:formatRule></saw:conditionalDisplayFormat></saw:conditionalDisplayFormats>
                                              
                                          </saw:displayFormat>
                          
                                          <saw:columnHeading>
                          
                                              <saw:displayFormat>
                          
                                                  <saw:formatSpec/>
                                              </saw:displayFormat>
                                          </saw:columnHeading>
                                      </saw:column>
                                  </saw:columns>
                              </saw:criteria>
                          
                              <saw:views currentView="0">
                          
                                  <saw:view xsi:type="saw:compoundView" name="compoundView!1">
                          
                                      <saw:cvTable>
                          
                                          <saw:cvRow>
                          
                                              <saw:cvCell viewName="pivotTableView!1"/>
                                          </saw:cvRow>
                                      </saw:cvTable>
                                  </saw:view>
                          
                                  <saw:view xsi:type="saw:pivotTableView" name="pivotTableView!1" scrollingEnabled="true">
                          
                                      <saw:edges>
                          
                                          <saw:edge axis="page" showColumnHeader="true"/>
                          
                                          <saw:edge axis="section"/>
                          
                                          <saw:edge axis="row" showColumnHeader="true">
                          
                                              <saw:edgeLayers>
                          
                                                  <saw:edgeLayer type="column" columnID="c7b0869ee87c35b79"/>
                                              </saw:edgeLayers>
                                          </saw:edge>
                          
                                          <saw:edge axis="column" showColumnHeader="rollover">
                          
                                              <saw:edgeLayers>
                          
                                                  <saw:edgeLayer type="column" columnID="cd7e10810ca5d5543"/>
                          
                                                  <saw:edgeLayer type="measure"/>
                                              </saw:edgeLayers>
                                          </saw:edge>
                                      </saw:edges>
                          
                                      <saw:measuresList>
                          
                                          <saw:measure columnID="c71f921dc5171a83e"/>
                                      </saw:measuresList>
                                  </saw:view>
                              </saw:views>
                          </saw:report>
                          
                          • 10. Re: replace null value on answers
                            Alex1

                            Hi!

                            Thanks for your time,

                             

                            I explain my problem.

                            I would like to able to show a conditional format like previous  table.

                             

                            You can see that my table has two diferent values on column with ID=3. Normal value and NULL value.

                            I thought that if I convert NULL values with a number my conditional could be more easy to do.

                             

                            How can I achieve this?

                             

                            Thanks!

                            • 11. Re: replace null value on answers
                              Christian Berg

                              Hi Alex,

                               

                              I just did it in a sample table and it works perfectly fine. Are you sure the modifications are present and loaded? Maybe create a second (new) column with a new name to test it and check the SQL generated for each of the fact columns.

                              • 12. Re: replace null value on answers
                                Alex1

                                Hi Christian,

                                 

                                I did with a new column and I don't see IFNULL value and my table shows NULL values.

                                 

                                I did in this window. Is correct?

                                 

                                 

                                I restart obiee server and I still see this pivot table:

                                 

                                in Value I add IFNULL formula in answers but I don't have good results.

                                 

                                • 13. Re: replace null value on answers
                                  Felipe_Idalgo

                                  Hi,

                                   

                                  Try to use that in other object view, like a table (not pivot table).

                                   

                                  Let me know about results.

                                   

                                  Regards,

                                   

                                  Felipe Idalgo

                                  • 14. Re: replace null value on answers
                                    Sudipta Chowdhury

                                    Try to write a case statement .

                                    1 2 3 Previous Next