0 Replies Latest reply: May 21, 2013 6:42 AM by 1010134 RSS

    XML in OBIEE

    1010134
      Hi,

      I have a problem with formats when i put two criterias in union. I would like to put different formats in columns. In this post i sent XML code which has two parts. First is for declaration of columns and the second is XML for UNION which stand in <saw:from> element.

      <saw:from> element has XML code for both criteria. I would like to format column "PELE#1"."Measures" as percentage and other columne KolonaB from criteria-two have to stay numeric.

      Please help



      <saw:columns>
      <saw:column columnID="c1a6478c81b1eba09" xsi:type="saw:derivedColumn">
      <saw:displayFormat>
      <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
      <saw:tableHeading/>
      <saw:columnHeading>
      <saw:caption fmt="text">
      <saw:text>Indicators</saw:text></saw:caption>
      <saw:displayFormat>
      <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
      <saw:column columnID="c89b259d8f12de77e" xsi:type="saw:derivedColumn">
      <saw:tableHeading>
      <saw:caption>
      <saw:text>Vrijeme</saw:text></saw:caption></saw:tableHeading>
      <saw:columnHeading>
      <saw:caption>
      <saw:text>Gen2,Vrijeme</saw:text></saw:caption></saw:columnHeading></saw:column>
      <saw:column columnID="cc14b1b3a3f49fdc8" xsi:type="saw:derivedColumn">
      <saw:tableHeading>
      <saw:caption>
      <saw:text>PELE#1</saw:text></saw:caption></saw:tableHeading>
      <saw:columnHeading>
      <saw:caption>
      <saw:text>Measures</saw:text></saw:caption></saw:columnHeading></saw:column>

      </saw:columns>



      <saw:from>

      <saw:criteria xsi:type="saw:setCriteria" op="union">


      <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;CB_PFT&quot;">
      <saw:columns>
      <saw:column xsi:type="saw:regularColumn" columnID="ccb988563d5e15afa">
      <saw:columnFormula>
      <sawx:expr xsi:type="sawx:sqlExpression">"Accounts"."Gen4,Accounts"</sawx:expr></saw:columnFormula>
      <saw:tableHeading/>
      <saw:columnHeading>
      <saw:caption fmt="text">
      <saw:text>Indicators</saw:text></saw:caption></saw:columnHeading></saw:column>
      <saw:column xsi:type="saw:regularColumn" columnID="cd1c5a8ce3442336f">
      <saw:columnFormula>
      <sawx:expr xsi:type="sawx:sqlExpression">"Vrijeme"."Gen2,Vrijeme"</sawx:expr></saw:columnFormula></saw:column>
      <saw:column xsi:type="saw:regularColumn" columnID="c740ebb02a59a57cd">
      <saw:columnFormula>
      <sawx:expr xsi:type="sawx:sqlExpression">"PELE#1"."Measures"</sawx:expr></saw:columnFormula></saw:column></saw:columns>
      <saw:filter>
      <sawx:expr xsi:type="sawx:logical" op="and">
      <sawx:expr op="in" xsi:type="sawx:list">
      <sawx:expr xsi:type="sawx:sqlExpression">"Accounts"."Gen4,Accounts"</sawx:expr>
      <sawx:expr xsi:type="xsd:string">Spread</sawx:expr>
      <sawx:expr xsi:type="xsd:string">Volumen AVG</sawx:expr>
      <sawx:expr xsi:type="xsd:string">Volumen EOP</sawx:expr></sawx:expr>
      <sawx:expr op="equal" xsi:type="sawx:comparison">
      <sawx:expr xsi:type="sawx:sqlExpression">"Vrijeme"."Gen2,Vrijeme"</sawx:expr>
      <sawx:expr xsi:type="xsd:string">2012</sawx:expr></sawx:expr></sawx:expr></saw:filter></saw:criteria>


      <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;CB_PFT&quot;">
      <saw:columns>
      <saw:column xsi:type="saw:regularColumn" columnID="c3e5ca8b9b6ce451e">
      <saw:columnFormula>
      <sawx:expr xsi:type="sawx:sqlExpression">"Accounts"."Gen4,Accounts"</sawx:expr></saw:columnFormula>
      <saw:tableHeading/>
      <saw:columnHeading>
      <saw:caption fmt="text">
      <saw:text>Indicators</saw:text></saw:caption></saw:columnHeading></saw:column>
      <saw:column xsi:type="saw:regularColumn" columnID="cf4445e1ce083bae9">
      <saw:columnFormula>
      <sawx:expr xsi:type="sawx:sqlExpression">'Change'</sawx:expr></saw:columnFormula>
      <saw:tableHeading/>
      <saw:columnHeading>
      <saw:caption fmt="text">
      <saw:text>VrijemeB</saw:text></saw:caption></saw:columnHeading></saw:column>
      <saw:column xsi:type="saw:regularColumn" columnID="c6c768cb7f320a1c4">
      <saw:columnFormula>
      <sawx:expr xsi:type="sawx:sqlExpression">case when
      "Accounts"."Gen4,Accounts" in (
      'Spread kumulativ', 'Interna cijena kumulativ', 'Kamatna stopa kumulativ')
      then
      ("PELE#1"."Measures Yago God" - "PELE#1"."Measures Yago2 God")*100

      else
      (("PELE#1"."Measures Yago God"/
      "PELE#1"."Measures Yago2 God")-1)*100
      end
      </sawx:expr></saw:columnFormula>
      <saw:tableHeading/>
      <saw:columnHeading>
      <saw:caption fmt="text">
      <saw:text>KolonaB</saw:text></saw:caption></saw:columnHeading></saw:column></saw:columns>
      <saw:filter>
      <sawx:expr xsi:type="sawx:logical" op="and">
      <sawx:expr op="equal" xsi:type="sawx:comparison">
      <sawx:expr xsi:type="sawx:sqlExpression">"Vrijeme"."Gen2,Vrijeme"</sawx:expr>
      <sawx:expr xsi:type="xsd:string">2012</sawx:expr></sawx:expr>
      <sawx:expr op="in" xsi:type="sawx:list">
      <sawx:expr xsi:type="sawx:sqlExpression">"Accounts"."Gen4,Accounts"</sawx:expr>
      <sawx:expr xsi:type="xsd:string">Spread</sawx:expr>
      <sawx:expr xsi:type="xsd:string">Volumen AVG</sawx:expr>
      <sawx:expr xsi:type="xsd:string">Volumen EOP</sawx:expr></sawx:expr></sawx:expr></saw:filter></saw:criteria>
      </saw:criteria>




      </saw:from>