Sum for subtoal returns ZERO — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Sum for subtoal returns ZERO

Received Response
42
Views
2
Comments

Summary

Sum for subtoal returns ZERO

Content

Hi,

I am using OBI P 11g Layout Editor to create data models and layouts.

I have 2 subtotals and grand total in my report. all works good but first subtotal returns zero for the function SUM/Count.

Example:

Region

Region
AreaOutletOrders

Region 1

Area 1Outlet 12
Outlet 2
3
Area 1 Total:0
Area 2Outlet 33
Outlet 44
Outlet 55
Area 2 Total:0
Region 1 Total:17
Grand Total:17

Please help.

If you need code, what should I share, XPT, data in XML, or what?

Regards,

Answers

  • Muhammad Jahanzaib
    Muhammad Jahanzaib Rank 4 - Community Specialist

    Thanks for replying.

    Its XPT; please see the code:

    XPT:

    Daily Transactions/ROWSET/ROW/T_MTD/ROWSET/ROW/T_MTD_Budget/ROWSET/ROW/T_MTD/ROWSET/ROW/T_MTD_Budget/ROWSET/ROW/T_MTD/ROWSET/ROW/T_MTD_BudgetRegion Total:/ROWSET/ROW/T_D/ROWSET/ROW/T_LW/ROWSET/ROW/T_LYSD/ROWSET/ROW/T_D_Budget/ROWSET/ROW/T_MTD/ROWSET/ROW/T_LMMTD/ROWSET/ROW/T_LYSMMTD/ROWSET/ROW/T_MTD_Budget/ROWSET/ROW/T_YTD/ROWSET/ROW/T_LYYTD/ROWSET/ROW/T_YTD_Budget/ROWSET/ROW/RegionNameArea Total:/ROWSET/ROW/T_D/ROWSET/ROW/T_LW/ROWSET/ROW/T_LYSD/ROWSET/ROW/T_D_Budget/ROWSET/ROW/T_MTD/ROWSET/ROW/T_LMMTD/ROWSET/ROW/T_LYSMMTD/ROWSET/ROW/T_MTD_Budget/ROWSET/ROW/T_YTD/ROWSET/ROW/T_LYYTD/ROWSET/ROW/T_YTD_Budget/ROWSET/ROW/AreaName/ROWSET/ROW/OutletName/ROWSET/ROW/OutletName/ROWSET/ROW/T_D/ROWSET/ROW/T_D/ROWSET/ROW/T_LW/ROWSET/ROW/T_LW/ROWSET/ROW/T_LYSD/ROWSET/ROW/T_LYSD/ROWSET/ROW/T_D_Budget/ROWSET/ROW/T_D_Budget/ROWSET/ROW/LW/ROWSET/ROW/T_D/ROWSET/ROW/T_LW/ROWSET/ROW/D/ROWSET/ROW/T_D/ROWSET/ROW/T_LYSD/ROWSET/ROW/D_Budget/ROWSET/ROW/T_D/ROWSET/ROW/T_D_Budget/ROWSET/ROW/T_MTD/ROWSET/ROW/T_MTD/ROWSET/ROW/T_LMMTD/ROWSET/ROW/T_LMMTD/ROWSET/ROW/T_LYSMMTD/ROWSET/ROW/T_LYSMMTD/ROWSET/ROW/T_MTD_Budget/ROWSET/ROW/T_MTD_Budget/ROWSET/ROW/LMMTD/ROWSET/ROW/T_MTD/ROWSET/ROW/T_LMMTD/ROWSET/ROW/LYSMMTD/ROWSET/ROW/T_MTD/ROWSET/ROW/T_LYSMMTD/ROWSET/ROW/MTD_Budget/ROWSET/ROW/T_MTD/ROWSET/ROW/T_MTD_Budget/ROWSET/ROW/T_YTD/ROWSET/ROW/T_YTD/ROWSET/ROW/T_LYYTD/ROWSET/ROW/T_LYYTD/ROWSET/ROW/T_YTD_Budget/ROWSET/ROW/T_YTD_Budget/ROWSET/ROW/LYYTD/ROWSET/ROW/T_YTD/ROWSET/ROW/T_LYYTD/ROWSET/ROW/YTD_Budget/ROWSET/ROW/T_YTD/ROWSET/ROW/T_YTD_BudgetGrand Total:/ROWSET/ROW/T_D/ROWSET/ROW/T_LW/ROWSET/ROW/T_LYSD/ROWSET/ROW/T_D_Budget/ROWSET/ROW/T_MTD/ROWSET/ROW/T_LMMTD/ROWSET/ROW/T_LYSMMTD/ROWSET/ROW/T_MTD_Budget/ROWSET/ROW/T_YTD/ROWSET/ROW/T_LYYTD/ROWSET/ROW/T_YTD_Budget

    XDM:

    <?xml version="1.0" encoding="UTF-8"?>

    <!--Generated by Oracle BI Publisher 11.1.1.7.0-->

    <ROWSET>

    <ROW>

    <Brand>B1</Brand>

    <RegionName>Central</RegionName>

    <AreaName>T</AreaName>

    <SubAreaName>T</SubAreaName>

    <OutletName>Gul</OutletName>

    <D>177545.5</D>

    <LW>234987</LW>

    <LYSD>162707.5</LYSD>

    <MTD>2156775.5</MTD>

    <LMMTD>2046013.5</LMMTD>

    <LYSMMTD>2086506.46</LYSMMTD>

    <YTD>39342349.4600000001</YTD>

    <LYYTD>36904796.4300000011</LYYTD>

    <D_Budget>0</D_Budget>

    <MTD_Budget>230668.3528805166</MTD_Budget>

    <YTD_Budget>39791933.3454881576</YTD_Budget>

    <T_D>207</T_D>

    <T_LW>228</T_LW>

    <T_LYSD>185</T_LYSD>

    <T_MTD>2230</T_MTD>

    <T_LMMTD>2191</T_LMMTD>

    <T_LYSMMTD>2307</T_LYSMMTD>

    <T_YTD>40275</T_YTD>

    <T_LYYTD>42119</T_LYYTD>

    <T_D_Budget>0</T_D_Budget>

    <T_MTD_Budget>39792159.6436945514345</T_MTD_Budget>

    <T_YTD_Budget>24678.8749592718817</T_YTD_Budget>

    </ROW>

    <ROW>

    <Brand>B1</Brand>

    <RegionName>Central</RegionName>

    <AreaName>A</AreaName>

    <SubAreaName>A</SubAreaName>

    <OutletName>Lah</OutletName>

    <D>441027.5</D>

    <LW>444761.5</LW>

    <LYSD>446422.5</LYSD>

    <MTD>4653451.16</MTD>

    <LMMTD>4356929.66</LMMTD>

    <LYSMMTD>5084145.2700000009</LYSMMTD>

    <YTD>104543012.94000000729</YTD>

    <LYYTD>103028311.34000000039</LYYTD>

    <D_Budget>0</D_Budget>

    <MTD_Budget>586389.763984258817</MTD_Budget>

    <YTD_Budget>108057312.486166558968</YTD_Budget>

    <T_D>278</T_D>

    <T_LW>273</T_LW>

    <T_LYSD>291</T_LYSD>

    <T_MTD>2885</T_MTD>

    <T_LMMTD>2833</T_LMMTD>

    <T_LYSMMTD>3257</T_LYSMMTD>

    <T_YTD>62368</T_YTD>

    <T_LYYTD>64394</T_LYYTD>

    <T_D_Budget>0</T_D_Budget>

    <T_MTD_Budget>108057653.796459879874194</T_MTD_Budget>

    <T_YTD_Budget>56520.962828190183107</T_YTD_Budget>

    </ROW>

    <ROW>

    <Brand>B1</Brand>

    <RegionName>Central</RegionName>

    <AreaName>T</AreaName>

    <SubAreaName>T</SubAreaName>

    <OutletName>Def</OutletName>

    <D>276753</D>

    <LW>286461.5</LW>

    <LYSD>245443</LYSD>

    <MTD>3055394</MTD>

    <LMMTD>2836949.25</LMMTD>

    <LYSMMTD>3106019.9</LYSMMTD>

    <YTD>60754451.05000000111</YTD>

    <LYYTD>52897230.83999999991</LYYTD>

    <D_Budget>0</D_Budget>

    <MTD_Budget>362401.3120531618</MTD_Budget>

    <YTD_Budget>61597639.8899010222</YTD_Budget>

    <T_D>241</T_D>

    <T_LW>253</T_LW>

    <T_LYSD>242</T_LYSD>

    <T_MTD>2641</T_MTD>

    <T_LMMTD>2542</T_LMMTD>

    <T_LYSMMTD>2922</T_LYSMMTD>

    <T_YTD>51631</T_YTD>

    <T_LYYTD>49637</T_LYYTD>

    <T_D_Budget>0</T_D_Budget>

    <T_MTD_Budget>61597933.3028049125857</T_MTD_Budget>

    <T_YTD_Budget>37019.4237650823507</T_YTD_Budget>

    </ROW>

    <ROW>

    <Brand>B1</Brand>

    <RegionName>North</RegionName>

    <AreaName>S</AreaName>

    <SubAreaName>S</SubAreaName>

    <OutletName>Man</OutletName>

    <D>98172</D>

    <LW>72164</LW>

    <LYSD>45835.13</LYSD>

    <MTD>879779.5</MTD>

    <LMMTD>743588.5</LMMTD>

    <LYSMMTD>813865.57</LYSMMTD>

    <YTD>21344130.74</YTD>

    <LYYTD>18836221.0300000001</LYYTD>

    <D_Budget>0</D_Budget>

    <MTD_Budget>78907.81320000002</MTD_Budget>

    <YTD_Budget>19538761.02733605568</YTD_Budget>

    <T_D>54</T_D>

    <T_LW>42</T_LW>

    <T_LYSD>31</T_LYSD>

    <T_MTD>488</T_MTD>

    <T_LMMTD>459</T_LMMTD>

    <T_LYSMMTD>511</T_LYSMMTD>

    <T_YTD>11074</T_YTD>

    <T_LYYTD>11281</T_LYYTD>

    <T_D_Budget>0</T_D_Budget>

    <T_MTD_Budget>19538806.06833034978513</T_MTD_Budget>

    <T_YTD_Budget>9077.49648352344693</T_YTD_Budget>

    </ROW>

    <ROW>

    <Brand>B1</Brand>

    <RegionName>South</RegionName>

    <AreaName>U</AreaName>

    <SubAreaName>U</SubAreaName>

    <OutletName>Gar</OutletName>

    <D>52200</D>

    <LW>33887</LW>

    <LYSD>35866.5</LYSD>

    <MTD>649507</MTD>

    <LMMTD>669533.5</LMMTD>

    <LYSMMTD>844971.8</LYSMMTD>

    <YTD>14729544.16</YTD>

    <LYYTD>13647674.56000000013</LYYTD>

    <D_Budget>0</D_Budget>

    <MTD_Budget>81604.6271356013</MTD_Budget>

    <YTD_Budget>13964624.6078237234</YTD_Budget>

    <T_D>71</T_D>

    <T_LW>45</T_LW>

    <T_LYSD>66</T_LYSD>

    <T_MTD>725</T_MTD>

    <T_LMMTD>760</T_LMMTD>

    <T_LYSMMTD>978</T_LYSMMTD>

    <T_YTD>16186</T_YTD>

    <T_LYYTD>16131</T_LYYTD>

    <T_D_Budget>0</T_D_Budget>

    <T_MTD_Budget>13964705.9030492589652</T_MTD_Budget>

    <T_YTD_Budget>8950.257952823862</T_YTD_Budget>

    </ROW>

    </ROWSET>

  • Bruce Wang
    Bruce Wang Rank 3 - Community Apprentice

    Could you please share your RTF template?