2 Replies Latest reply: Jan 15, 2013 2:39 PM by Ramki296 RSS

    Displaying the measure column names in the Rows section of Pivot Table

    Ramki296
      Hi All,

      I have a report requirement in BI Publisher to achieve the below lay out format for Pivot Report. I am trying to achieve this in RTF template. But not getting any result. Normally when we take the measures, their column names would be displayed under the column section, that is under Budget Type column,( in this example) but in my layout, the measure headings should be displayed in the row section, as mentioned in the below layout. I could get the report layout to some extent i.e, if there is only one value for Region and Budget Type, but if there are multiple values for Region and Budget Type, the output is not coming properly. Can anyone suggest me how to achieve this? Below is the XML data.

      Any help on this greatly appreciated.

      Desired Report Layout Format:
      ------------------------------------------

      Here is my Report Layout Link [http://tinypic.com/r/5vp3qo/6]



      Sample XML Data:
      ------------------------

      <?xml version="1.0" encoding="UTF-8"?>
      <!--Generated by Oracle BI Publisher 11.1.1.6.0-->
      <DATA_DS>
      <G_1>
      <STATUSCODE>9</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Approved</STATUS>
      <STATUS_DAYS>8.110300081103000811030008110300081103E-03</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>1233</PURCHASE_REQ_COUNT>
      <SUM_FX100_>87215683.11</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>14</STATUSCODE>
      <BUDGETTYPE>Expense</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Interfaced</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>248</PURCHASE_REQ_COUNT>
      <SUM_FX100_>25284813.812</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>14</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Interfaced</STATUS>
      <STATUS_DAYS>8.36038961038961038961038961038961038961E-02</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>1232</PURCHASE_REQ_COUNT>
      <SUM_FX100_>87215163.11</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>7</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>ED Review Complete</STATUS>
      <STATUS_DAYS>2.57407407407407407407407407407407407407E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>108</PURCHASE_REQ_COUNT>
      <SUM_FX100_>17837162.13</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>8</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>RVP Review Complete</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>4</PURCHASE_REQ_COUNT>
      <SUM_FX100_>520773</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>5</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Capital Finance Review Complete</STATUS>
      <STATUS_DAYS>1.14285714285714285714285714285714285714E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>7</PURCHASE_REQ_COUNT>
      <SUM_FX100_>698604.88</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>2</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Complete</STATUS>
      <STATUS_DAYS>6.66666666666666666666666666666666666667E-01</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>9</PURCHASE_REQ_COUNT>
      <SUM_FX100_>500151.93</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>8</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>RVP Review Complete</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>118</PURCHASE_REQ_COUNT>
      <SUM_FX100_>17677072.88</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>1</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>CENTRAL</REGION>
      <STATUS>Draft</STATUS>
      <STATUS_DAYS>7.28571428571428571428571428571428571429E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>7</PURCHASE_REQ_COUNT>
      <SUM_FX100_>13552285.61</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>4</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Requisitioner Review Complete</STATUS>
      <STATUS_DAYS>6.30583501006036217303822937625754527163E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>1491</PURCHASE_REQ_COUNT>
      <SUM_FX100_>122533258.33001</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>4</STATUSCODE>
      <BUDGETTYPE>Expense</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Requisitioner Review Complete</STATUS>
      <STATUS_DAYS>6.97198879551820728291316526610644257703E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>357</PURCHASE_REQ_COUNT>
      <SUM_FX100_>41327730.092</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>6</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Expense Finance Review Complete</STATUS>
      <STATUS_DAYS>1.42857142857142857142857142857142857143E-01</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>7</PURCHASE_REQ_COUNT>
      <SUM_FX100_>698604.88</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>9</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>CENTRAL</REGION>
      <STATUS>Approved</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>6</PURCHASE_REQ_COUNT>
      <SUM_FX100_>325643.93</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>14</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>CENTRAL</REGION>
      <STATUS>Interfaced</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>6</PURCHASE_REQ_COUNT>
      <SUM_FX100_>325643.93</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>7</STATUSCODE>
      <BUDGETTYPE>Expense</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>ED Review Complete</STATUS>
      <STATUS_DAYS>2.13636363636363636363636363636363636364E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>22</PURCHASE_REQ_COUNT>
      <SUM_FX100_>1688010.63</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>4</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>CENTRAL</REGION>
      <STATUS>Requisitioner Review Complete</STATUS>
      <STATUS_DAYS>1.66666666666666666666666666666666666667E-01</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>6</PURCHASE_REQ_COUNT>
      <SUM_FX100_>325643.93</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>7</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>ED Review Complete</STATUS>
      <STATUS_DAYS>2.33333333333333333333333333333333333333E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>3</PURCHASE_REQ_COUNT>
      <SUM_FX100_>425573</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>1</STATUSCODE>
      <BUDGETTYPE>Expense</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Draft</STATUS>
      <STATUS_DAYS>2.78620689655172413793103448275862068966E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>290</PURCHASE_REQ_COUNT>
      <SUM_FX100_>23983408.08</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>3</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>CENTRAL</REGION>
      <STATUS>Under Requisitioner Review</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>6</PURCHASE_REQ_COUNT>
      <SUM_FX100_>325643.93</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>3</STATUSCODE>
      <BUDGETTYPE>Expense</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Under Requisitioner Review</STATUS>
      <STATUS_DAYS>2.21130221130221130221130221130221130221E-02</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>407</PURCHASE_REQ_COUNT>
      <SUM_FX100_>44950138.152</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>6</STATUSCODE>
      <BUDGETTYPE>Expense</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Expense Finance Review Complete</STATUS>
      <STATUS_DAYS>1.06882591093117408906882591093117408907E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>247</PURCHASE_REQ_COUNT>
      <SUM_FX100_>25175900.562</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>5</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>CENTRAL</REGION>
      <STATUS>Capital Finance Review Complete</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>6</PURCHASE_REQ_COUNT>
      <SUM_FX100_>325643.93</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>1</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Draft</STATUS>
      <STATUS_DAYS>.25</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>4</PURCHASE_REQ_COUNT>
      <SUM_FX100_>79049.5</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>5</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Capital Finance Review Complete</STATUS>
      <STATUS_DAYS>2.29387755102040816326530612244897959184E-01</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>1225</PURCHASE_REQ_COUNT>
      <SUM_FX100_>88746421.45</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>1</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Draft</STATUS>
      <STATUS_DAYS>3.76054324517512508934953538241601143674E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>1399</PURCHASE_REQ_COUNT>
      <SUM_FX100_>107810915.31001</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>3</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Under Requisitioner Review</STATUS>
      <STATUS_DAYS>1.30283729009843659525188187608569774175E-02</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>1727</PURCHASE_REQ_COUNT>
      <SUM_FX100_>147196975.97001</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>3</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Under Requisitioner Review</STATUS>
      <STATUS_DAYS>2.72727272727272727272727272727272727273E-01</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>11</PURCHASE_REQ_COUNT>
      <SUM_FX100_>975065.83</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>9</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Approved</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>8</PURCHASE_REQ_COUNT>
      <SUM_FX100_>793804.88</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>2</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Complete</STATUS>
      <STATUS_DAYS>1.94703511801957397812320092112838226828E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>1737</PURCHASE_REQ_COUNT>
      <SUM_FX100_>147211761.43001</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>10</STATUSCODE>
      <BUDGETTYPE>Expense</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Rejected</STATUS>
      <STATUS_DAYS>7.38383838383838383838383838383838383838E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>99</PURCHASE_REQ_COUNT>
      <SUM_FX100_>18339720.44</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>8</STATUSCODE>
      <BUDGETTYPE>Expense</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>RVP Review Complete</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>18</PURCHASE_REQ_COUNT>
      <SUM_FX100_>2037327.13</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>4</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Requisitioner Review Complete</STATUS>
      <STATUS_DAYS>4.28571428571428571428571428571428571429E-01</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>7</PURCHASE_REQ_COUNT>
      <SUM_FX100_>698604.88</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>2</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>CENTRAL</REGION>
      <STATUS>Complete</STATUS>
      <STATUS_DAYS>2</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>3</PURCHASE_REQ_COUNT>
      <SUM_FX100_>289277.93</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>2</STATUSCODE>
      <BUDGETTYPE>Expense</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Complete</STATUS>
      <STATUS_DAYS>1.9528535980148883374689826302729528536E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>403</PURCHASE_REQ_COUNT>
      <SUM_FX100_>44540480.51</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>10</STATUSCODE>
      <BUDGETTYPE>Capital</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Rejected</STATUS>
      <STATUS_DAYS>1.27475570032573289902280130293159609121E01</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>307</PURCHASE_REQ_COUNT>
      <SUM_FX100_>30090312.9</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>9</STATUSCODE>
      <BUDGETTYPE>Expense</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Approved</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>239</PURCHASE_REQ_COUNT>
      <SUM_FX100_>24926403.312</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>14</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Interfaced</STATUS>
      <STATUS_DAYS>0</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>8</PURCHASE_REQ_COUNT>
      <SUM_FX100_>793804.88</SUM_FX100_>
      </G_1>
      <G_1>
      <STATUSCODE>10</STATUSCODE>
      <BUDGETTYPE>Mixed</BUDGETTYPE>
      <REGION>HEADQUARTERS</REGION>
      <STATUS>Rejected</STATUS>
      <STATUS_DAYS>1.33333333333333333333333333333333333333E00</STATUS_DAYS>
      <PURCHASE_REQ_COUNT>3</PURCHASE_REQ_COUNT>
      <SUM_FX100_>39004</SUM_FX100_>
      </G_1>
      </DATA_DS>



      Thanks.

      Edited by: Ramki296 on Jan 11, 2013 9:00 PM

      Edited by: Ramki296 on Jan 14, 2013 10:02 AM

      Edited by: Ramki296 on Jan 14, 2013 10:05 AM

      Edited by: Ramki296 on Jan 15, 2013 10:43 AM