Home » Developer & Programmer » Reports & Discoverer » Order by Clause (report builder 6i)
Order by Clause [message #611532] Thu, 03 April 2014 07:36 Go to next message
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

Re: Order by Clause [message #611533 is a reply to message #611532] Thu, 03 April 2014 07:50 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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".
Re: Order by Clause [message #611534 is a reply to message #611533] Thu, 03 April 2014 07:59 Go to previous messageGo to next message
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.  
Re: Order by Clause [message #611538 is a reply to message #611534] Thu, 03 April 2014 08:10 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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?
Re: Order by Clause [message #611546 is a reply to message #611538] Thu, 03 April 2014 09:19 Go to previous message
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)
Previous Topic: rdf report
Next Topic: reportsConfig.properties use?
Goto Forum:
  


Current Time: Thu Mar 28 07:04:29 CDT 2024