Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Developer & Programmer » Reports & Discoverer » ordering by financial year
ordering by financial year [message #266124] Sun, 09 September 2007 22:20 Go to next message
alister
Messages: 67
Registered: August 2007
Location: India
Member
I have a report in which I want the output as below

2004-05 2005-06 2006-07
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar

But I get the output as Apr,Aug .. in alphabetical order so I added a numeric before the month as
2004-05 2005-06 2006-07
01Apr
02May
03Jun
04Jul
05Aug
06Sep
07Oct
08Nov
09Dec
10Jan
11Feb
12Mar

I am using Reports 3.0.5.8.0. and Oracle 8.0.1.7

Can anyone tell me how to get the output without the numberic values.

Alister
Report message to a moderator
Re: ordering by financial year [message #266155 is a reply to message #266124 ] Mon, 10 September 2007 01:00 Go to previous messageGo to next message
apps_ara
Messages: 26
Registered: January 2007
Location: Pune
Junior Member
Try using substr function for the first two characters of the column.
Report message to a moderator
Re: ordering by financial year [message #266161 is a reply to message #266124 ] Mon, 10 September 2007 01:12 Go to previous messageGo to next message
Littlefoot
Messages: 5640
Registered: June 2005
Location: Croatia, Europe
Senior Member
Such an ORDER BY clause might help:
SQL> WITH TEST AS
  2    (SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'),  LEVEL - 1), 'moN') result
  3     FROM dual
  4     CONNECT BY LEVEL <= 12
  5    )
  6  SELECT * FROM TEST
  7  ORDER BY DECODE(UPPER(result),
  8                  'APR', 1, 'MAY',  2, 'JUN',  3, 'JUL',  4,
  9                  'AUG', 5, 'SEP',  6, 'OCT',  7, 'NOV',  8,
 10                  'DEC', 9, 'JAN', 10, 'FEB', 11, 'MAR', 12
 11                 );

RES
---
apr
may
jun
jul
aug
sep
oct
nov
dec
jan
feb
mar

12 rows selected.
Report message to a moderator
Re: ordering by financial year [message #267273 is a reply to message #266124 ] Wed, 12 September 2007 22:50 Go to previous messageGo to next message
alister
Messages: 67
Registered: August 2007
Location: India
Member
Dear Littlefoot
Thank you for the script. It is working fine at the SQL promt. But when I use it in a matrix report, It does not work. It gets ordered alphabetically. Can you please tell me why.

Alister
Report message to a moderator
Re: ordering by financial year [message #267306 is a reply to message #267273 ] Thu, 13 September 2007 00:24 Go to previous messageGo to next message
skooman
Messages: 794
Registered: March 2005
Location: Netherlands
Senior Member
You could try to use order by to_number(to_char(<datecol>,'YYYYMM')). In Discoverer you can order by a column that you don't show (like: show values Apr, May, etc, but order by 200704,200705 etc.). Please note that I added YYYY to the ordering format, probably you think that you'll only do that report for one year, but just in case somewhere in the future some user wants more years... Furthermore, note the to_number, if you omit that, the "numbers" will be sorted alphabetically, you don't want that.

LF, you probably know a way to do that sorting on a non-shown-column in reports?
Report message to a moderator
Re: ordering by financial year [message #267346 is a reply to message #267306 ] Thu, 13 September 2007 01:36 Go to previous messageGo to next message
Littlefoot
Messages: 5640
Registered: June 2005
Location: Croatia, Europe
Senior Member
ORDER BY is written in a query (i.e. as a part of the SELECT statement). Columns contained in the ORDER BY clause may, but doesn't have to be displayed - it really doesn't matter.

Alister, could you attach a sample report (RDF) (based upon Scott's schema) so that someone might take a look?
Report message to a moderator
Re: ordering by financial year [message #267620 is a reply to message #266124 ] Thu, 13 September 2007 22:34 Go to previous messageGo to next message
alister
Messages: 67
Registered: August 2007
Location: India
Member
I am attaching the RDF file. Can anyone please spare some time to go through it.


Alister

  • Attachment: dli.RDF
    (Size: 172.00KB, Downloaded 39 time(s))

Report message to a moderator
Re: ordering by financial year [message #267660 is a reply to message #267620 ] Fri, 14 September 2007 02:26 Go to previous messageGo to next message
Littlefoot
Messages: 5640
Registered: June 2005
Location: Croatia, Europe
Senior Member
This is your query:
SELECT      SUBSTR (getfinyear (TO_DATE (   '01-'
                                         || SUBSTR (yearmonth, 5, 2)
                                         || '-'
                                         || SUBSTR (yearmonth, 1, 4),
                                         'dd-mm-rrrr'
                                        )
                               ),
                    1,
                    4
                   )
         || '-'
         || SUBSTR (getfinyear (TO_DATE (   '01-'
                                         || SUBSTR (yearmonth, 5, 2)
                                         || '-'
                                         || SUBSTR (yearmonth, 1, 4),
                                         'dd-mm-rrrr'
                                        )
                               ),
                    5,
                    4
                   ) finyear,
         yearmonth, cb, empcode, mmth MONTH, debits
    FROM adjdli
   WHERE empcode = :pfno
     AND yearmonth BETWEEN TO_CHAR (ADD_MONTHS (:stmmth, -36), 'rrrrmm')
                       AND TO_CHAR (ADD_MONTHS (:stmmth, -1), 'rrrrmm')
ORDER BY mmth

Where did you implement the suggested ORDER BY?

By the way, if you really want someone to RUN and TEST this report, you'd better prepare CREATE TABLE and INSERT INTO sample data statements. I have no idea what is written in 'adjdli' table.
Report message to a moderator
Re: ordering by financial year [message #267953 is a reply to message #266124 ] Sun, 16 September 2007 22:39 Go to previous messageGo to next message
alister
Messages: 67
Registered: August 2007
Location: India
Member
I used the given order by instead of mmth but it got ordered alphbetically, so I removed it. I am giving the Create Table and its data below:

CREATE TABLE adjdli
(yearmonth VARCHAR2(Cool,
cb NUMBER,
empcode VARCHAR2(Cool,
mmth VARCHAR2(5),
debits NUMBER)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE afres
STORAGE (
INITIAL 1048576
NEXT 1048576
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
---------------------------------------------
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',46610,'02506348','01Apr',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',48099,'02506348','02May',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',49588,'02506348','03Jun',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',51477,'02506348','04Jul',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',53366,'02506348','05Aug',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',55255,'02506348','06Sep',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',57144,'02506348','07Oct',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',59233,'02506348','08Nov',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',61322,'02506348','09Dec',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',66611,'02506348','10Jan',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',72950,'02506348','11Feb',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20032004',79289,'02506348','12Mar',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',87651,'02506348','01Apr',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',91590,'02506348','02May',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',95529,'02506348','03Jun',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',99468,'02506348','04Jul',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',103407,'02506348','05Aug',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',107346,'02506348','06Sep',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',116121,'02506348','07Oct',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',120060,'02506348','08Nov',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',124046,'02506348','09Dec',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',129519,'02506348','10Jan',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',134992,'02506348','11Feb',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20042005',140465,'02506348','12Mar',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',154939,'02506348','01Apr',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',160412,'02506348','02May',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',166150,'02506348','03Jun',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',155965,'02506348','04Jul',20000)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',160740,'02506348','05Aug',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',166315,'02506348','06Sep',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',176739,'02506348','07Oct',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',182314,'02506348','08Nov',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',187889,'02506348','09Dec',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',192464,'02506348','10Jan',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',197039,'02506348','11Feb',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20052006',201614,'02506348','12Mar',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',220206,'02506348','01Apr',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',224803,'02506348','02May',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',229900,'02506348','03Jun',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',234997,'02506348','04Jul',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',240094,'02506348','05Aug',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',250533,'02506348','06Sep',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',255630,'02506348','07Oct',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',260727,'02506348','08Nov',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',265824,'02506348','09Dec',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',270921,'02506348','10Jan',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',231618,'02506348','11Feb',50000)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20062007',235915,'02506348','12Mar',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',260686,'02506348','01Apr',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',266005,'02506348','02May',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',271324,'02506348','03Jun',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',276643,'02506348','04Jul',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',281962,'02506348','05Aug',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',288281,'02506348','06Sep',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',288281,'02506348','07Oct',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',288281,'02506348','08Nov',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',288281,'02506348','09Dec',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',288281,'02506348','10Jan',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',288281,'02506348','11Feb',0)
/
INSERT INTO &&table_name
(YEARMONTH,CB,EMPCODE,MMTH,DEBITS)
VALUES
('20072008',288281,'02506348','12Mar',0)
/
----------------------------------
In the previous query I used a function getfinyear, I have replaced the values of yearmonth with the finyear. So I am giving the revised query below


SELECT substr(yearmonth,1,4)||
'-'||substr(yearmonth,5,4) finyear,
yearmonth, cb, empcode,mmth month,debits
FROM adjdli
where empcode=:pfno and yearmonth between to_char(add_months(:stmmth,-36),'rrrrmm')
and to_char(add_months(:stmmth,-1),'rrrrmm')
order by mmth
----------------------------------------------


Thanks for your patience


Alister
Report message to a moderator
Re: ordering by financial year [message #268443 is a reply to message #267953 ] Tue, 18 September 2007 11:57 Go to previous messageGo to next message
Littlefoot
Messages: 5640
Registered: June 2005
Location: Croatia, Europe
Senior Member
You still didn't provide enough information to successfully compile and run the report: there's the 'DLI' function missing, there's no 'afpfmstr', 'gupsel' nor 'afpblemp' table so ... I'm not going to spend time to decipher what you meant to say here.
Report message to a moderator
Re: ordering by financial year [message #268528 is a reply to message #266124 ] Wed, 19 September 2007 00:27 Go to previous messageGo to next message
alister
Messages: 67
Registered: August 2007
Location: India
Member
Sir,

I am so sorry about the error, I am enclosing a report with only the required part. Please help me.


Alister

Report message to a moderator
Re: ordering by financial year [message #268572 is a reply to message #268528 ] Wed, 19 September 2007 02:13 Go to previous messageGo to next message
Littlefoot
Messages: 5640
Registered: June 2005
Location: Croatia, Europe
Senior Member
OK, I think I know where the problem is: 'G_month' group has only one column: 'month', and - as it is the only one - it must be a break column (as you've created it). But, making it a break column (in Ascending order), months are ordered by this break column and NOT by ORDER BY clause in a query.

Problem is that you can not remove 'break order' proparty for this column. I added another 'dummy' column into a query and made it break column (while 'month' wasn't break column any more), but it created output with data relevant to April only (without other months).

I'll try to figure something out and let you know later.
Report message to a moderator
Re: ordering by financial year [message #268846 is a reply to message #268572 ] Wed, 19 September 2007 22:29 Go to previous messageGo to next message
alister
Messages: 67
Registered: August 2007
Location: India
Member
Thanks for you time


Alister
Report message to a moderator
Re: ordering by financial year [message #317915 is a reply to message #268846 ] Sun, 04 May 2008 22:45 Go to previous message
alister
Messages: 67
Registered: August 2007
Location: India
Member
Dear Friends

Can someone please relook at this problem and tell me how to solve it. I just want the month ordered by financial year ie from Apr to Mar in a matrix report.

Thank you

Alister.
Report message to a moderator
Previous Topic:Discoverer Standard EUL
Next Topic:how to get acrobat reader exe path in oracle reports at runtime
Goto Forum:
  

[ Syndicate this forum (XML) ] [ ]

Current Time: Fri May 16 11:18:37 CDT 2008

Total time taken to generate the page: 0.03864 seconds
.:: Forum Home :: Site Home :: Wiki Home :: Contact :: Privacy ::.