OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » Order by Clause (report builder 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Order by Clause [message #611532]

Thu, 03 April 2014 07:36

Hometown1
Messages: 23
Registered: March 2011

Junior Member

  1  SELECT DISTINCT Initcap(Ltrim(tbl_canvasser.canvasser_name)),
  2                  ' '
  3                  ||currency_name              currency_name,
  4                  SUM(Nvl(category_amount, 0)) sum_amount
  5  FROM   tbl_donation,
  6         tbl_currency,
  7         tbl_category_detail,
  8         tbl_canvasser
  9  WHERE  tbl_donation.donation_code = tbl_category_detail.donation_code(+)
 10         AND tbl_donation.currency_code = tbl_currency.currency_code(+)
 11         AND tbl_donation.canvasser_code = tbl_canvasser.canvasser_code
 12  GROUP  BY Initcap(Ltrim(tbl_canvasser.canvasser_name)),
 13            currency_name
 14* ORDER  BY SUM(Nvl(category_amount, 0)) DESC
SQL> /

INITCAP(LTRIM(TBL_CANVASSER.CANVASSER_NAME))  CURRENCY_NAME                                  SUM_AMOUNT
--------------------------------------------- ---------------------------------------------- -------
Hamza Yaqoob                                   Pounds                                             14675
Akhtar Geoff And Jubaid                        Pounds                                          11469.95
Usama Haroun                                   Pounds                                          11249.79
Tanzeela R & Mehvish R & Sajida S              Pounds                                          10862.11
Faruk And Hiba                                 Pounds                                           6901.32
Zayna And Farhan                               Pounds                                           6293.01
Tasnim Zarin & Nahida Bibi                     Pounds                                           4963.26
Mariya Kazi                                    Pounds                                            4662.7
Syed Raza Hussain Gillani                      Pounds                                              2061
Leena & Shareen Juwle                          Pounds                                           1374.18
Akhtar Miah                                    Pounds                                              1185
Afifah Kazi                                    Pounds                                           1144.84
Ahmad Raza                                     Pounds                                            632.01
Abdul Rehman Tobin                             Pounds                                             466.3
Sabeea Nasreen                                 Pounds                                            392.47
Hani & Samiya                                  Pounds                                            203.51
Allama Asif Qadri                              Pounds                                               150
M Sheikh                                       Pounds                                               140
Taksima & Firdous                              Pounds                                            106.64
Sadak Mirza                                    Pounds                                               100
Amira Barre                                    Pounds                                             63.34

With reference to report builder running on windows xp service pack2, the above mentioned query runs and displays result ordered by category_amount in Descending order in SQL PLUS, but when I run this query in report builder it displays result ordered by canvasser_name field that is A-Z format. That's not what I want, How do I get the result as indicated above in Report builder

Report message to a moderator

Re: Order by Clause [message #611533 is a reply to message #611532]

Thu, 03 April 2014 07:50

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Open Data Model Editor. Review the group. Check whether there's "Ascending" break order set for the CANVASSER_NAME column. If so, set it to NONE and set break order of the SUM_AMMOUNT column to "Ascending".

Report message to a moderator

Re: Order by Clause [message #611534 is a reply to message #611533]

Thu, 03 April 2014 07:59

Hometown1
Messages: 23
Registered: March 2011

Junior Member

Thanks for your prompt reply, after applying your suggestion, it now says

REP-1259:  Group '<group name>' has no break columns. 
Cause:	You have created a parent group that has no break columns.  
Action:	Mark the Break Order check box(es) of the break column(s) of the group.

Report message to a moderator

Re: Order by Clause [message #611538 is a reply to message #611534]

Thu, 03 April 2014 08:10

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

The error message means what it says.
Order by applies to the lowest child group for a query. Groups above the lowest need to have break order set on at least one item.
What columns are in what groups?

Report message to a moderator

Re: Order by Clause [message #611546 is a reply to message #611538]

Thu, 03 April 2014 09:19

Hometown1
Messages: 23
Registered: March 2011

Junior Member

This is a matrix report and the details are. Please refer to the attachment for data model view

Matrix Row Field
Level 1 Canvasser_name

Matrix Column Field
Level 1 Currency_name

Matrix Cell Field
Sum_amount

Matrix Totals
Sum(sum_amount)

Attachment: data model view.JPG
(Size: 120.19KB, Downloaded 1746 times)

Report message to a moderator

Previous Topic:	rdf report
Next Topic:	reportsConfig.properties use?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 19:41:59 CST 2025