Home » Applications » Oracle Fusion Apps & E-Business Suite » need sum of a column based upon another column's grouping (XML Publisher (BI Publisher))
need sum of a column based upon another column's grouping [message #655594] Mon, 05 September 2016 05:44 Go to next message
m_shehpar
Messages: 73
Registered: October 2010
Location: Pakistan
Member
Hi all,

I have a scenario in a matrix report, the report is all ready by its row-wise and column wise data representation. Now i have another column (CS_DEPR) which exists in the same group but I want to show it one time and take its sum at the end of the report. But the problem is, it has data for each line of the matrix column (which due to matrix has been shown row wise). But i have to show this column only one time and take sum of it in the end.


I have attached the snapshot of my .RDF report. My template file (please save as .rtf before using it) and the xml datafile.

The .rtf file is the template of a matrix report, which runs all very fine. I need another column "Depreciation" which is shown only once (which is fine also) but the sum (sub total) gets erroneous, since Depreciation column has a value (same for each matrix column) and it sums up for each column

The xml file which i have attached, only group G_VTI is used in the .rtf file. A snapshot of .RDF has also been attached.

Thanking in anticipation.

Regards

Shabbir

Report message to a moderator

Re: need sum of a column based upon another column's grouping [message #655595 is a reply to message #655594] Mon, 05 September 2016 05:49 Go to previous messageGo to next message
m_shehpar
Messages: 73
Registered: October 2010
Location: Pakistan
Member
xml data file data is below while report RDF snapshot has also been attached:

<?xml version="1.0" encoding="WINDOWS-1252"?>
<!-- Generated by Oracle Reports version 10.1.2.0.2 -->
<PVTCRHRB>
<LIST_G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Depreciation - Office Equipments</NAT>
<BALANCE>10000.0000000000000000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Depreciation - Building on Freehold Land</NAT>
<BALANCE>50000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Miscellaneous Expenses</NAT>
<BALANCE>1690000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2102</VTI_CODE>
<VTI>VTI Governer House</VTI>
<NAT>Miscellaneous Expenses</NAT>
<BALANCE>300000</BALANCE>
<LIST_G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR></CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Security Expenses</NAT>
<BALANCE>140000.000000000000000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2102</VTI_CODE>
<VTI>VTI Governer House</VTI>
<NAT>Office Supplies</NAT>
<BALANCE>13560</BALANCE>
<LIST_G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR></CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2201</VTI_CODE>
<VTI>VTI Gujranwala</VTI>
<NAT>Salaries &amp; Wages</NAT>
<BALANCE>195000</BALANCE>
<LIST_G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR></CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2204</VTI_CODE>
<VTI>VTI Sheikhupura</VTI>
<NAT>Office Supplies</NAT>
<BALANCE>205000.000000000000000</BALANCE>
<LIST_G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR></CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Depreciation - Lab Equipments</NAT>
<BALANCE>60000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Depreciation - Electronic / IT / Communication Equipments</NAT>
<BALANCE>40000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Leave Fare Assistance</NAT>
<BALANCE>100000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Misc. Project Expense</NAT>
<BALANCE>270000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2102</VTI_CODE>
<VTI>VTI Governer House</VTI>
<NAT>Salaries &amp; Wages</NAT>
<BALANCE>200000.000000000000000</BALANCE>
<LIST_G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR></CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Office Supplies</NAT>
<BALANCE>75000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Depreciation - Vehicle</NAT>
<BALANCE>95000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Depreciation - Furniture &amp; Fixture</NAT>
<BALANCE>55000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
<G_VTI>
<VTI_CODE>2113</VTI_CODE>
<VTI>VTI Green Town</VTI>
<NAT>Salaries &amp; Wages</NAT>
<BALANCE>100000</BALANCE>
<LIST_G_SEGMENT2>
<G_SEGMENT2>
<DEPR>352000.000000000000000</DEPR>
<SEGMENT2>2113</SEGMENT2>
</G_SEGMENT2>
</LIST_G_SEGMENT2>
<CS_DEPR>352000.000000000000000000000000000000000</CS_DEPR>
</G_VTI>
</LIST_G_VTI>
<CF_BUD_PERIOD>'JUN-16</CF_BUD_PERIOD>
<CF_DUR_MON>12</CF_DUR_MON>
<CF_BUDGET>BUDGET_2015-16</CF_BUDGET>
<CF_REGION>Center Region</CF_REGION>
</PVTCRHRB>

/forum/fa/13253/0/

[mod-edit: image inserted into message body by bb]

[Updated on: Mon, 05 September 2016 17:05] by Moderator

Report message to a moderator

Re: need sum of a column based upon another column's grouping [message #655599 is a reply to message #655594] Mon, 05 September 2016 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Maybe you should think to feedback to your previous topics (and thank people) before posting a new question, something you did only once in October 2013.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Report message to a moderator

Re: need sum of a column based upon another column's grouping [message #655600 is a reply to message #655599] Mon, 05 September 2016 08:08 Go to previous messageGo to next message
m_shehpar
Messages: 73
Registered: October 2010
Location: Pakistan
Member
so you mean I do not qualify for getting help here ?

Tool used here is B.I publisher 10g (10.1.3.4.2) oracle db 10gR2
upl
xml data file has been uploaded as text attached here (since it couldnt be uploaded in xml fomat)
  • Attachment: PVTCRHRB.txt
    (Size: 6.48KB, Downloaded 2435 times)

[Updated on: Mon, 05 September 2016 08:11]

Report message to a moderator

Re: need sum of a column based upon another column's grouping [message #655602 is a reply to message #655600] Mon, 05 September 2016 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
so you mean I do not qualify for getting help here ?
What do you think about this?
Do you deserve to be helped or not?

Report message to a moderator

Re: need sum of a column based upon another column's grouping [message #655604 is a reply to message #655602] Mon, 05 September 2016 13:25 Go to previous messageGo to next message
m_shehpar
Messages: 73
Registered: October 2010
Location: Pakistan
Member
seriously !!!! i am not a terrorist... i just asked a technical and a relevant question. i was not expecting a police interrogation here... (as you wish...)

Report message to a moderator

Re: need sum of a column based upon another column's grouping [message #655605 is a reply to message #655604] Mon, 05 September 2016 13:40 Go to previous message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I remark you did not answer to my questions nor go to your previous topics to feedback... (as you wish...)

Report message to a moderator

Previous Topic: Fusion Apps
Next Topic: Internal Orders Created under Org
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 09 07:36:03 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.