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: 101
Registered: August 2007
Location: India
Senior 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: 29
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: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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: 101
Registered: August 2007
Location: India
Senior 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: 913
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: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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: 101
Registered: August 2007
Location: India
Senior 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 1338 times)

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: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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: 101
Registered: August 2007
Location: India
Senior 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: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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: 101
Registered: August 2007
Location: India
Senior 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: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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: 101
Registered: August 2007
Location: India
Senior 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 messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior 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

Re: ordering by financial year [message #321152 is a reply to message #317915] Mon, 19 May 2008 04:30 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
If you want an answer to your question, please create your own post and don't use other old posts. The previous post you used was already solved, so we can not know what the issue is you are running into. Again: please create your own topic and describe clearly what your problem is. Thanks!

Report message to a moderator

Re: ordering by financial year [message #322255 is a reply to message #266124] Thu, 22 May 2008 23:13 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear skooman

The previous post that I used is also my post and that topic was not solved. Since it was some months since anyone posted anything to it I reactivated it. I have uploaded the RDF file also in message #268528 . Please go through it and help me.

Thank you,

Alister

[Updated on: Thu, 22 May 2008 23:14]

Report message to a moderator

Re: ordering by financial year [message #323063 is a reply to message #322255] Tue, 27 May 2008 04:01 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
Okay, to get back to the basics: you want to show a month (like APR-2008, MAY-2008, etc.) and want to order that according to the calendar, right?

If so, include a date (for example last_day of that month) AND the formatted month (ie MAY-2008) in your selection (SQL) and order by the date field. You might get a result something like:

id  last_day    month_col
1   31-JAN-2008 JAN-2008
2   28-FEB-2008 FEB-2008
etc.


Then only show the month_col in your report (and don't add any other ordering...).

Report message to a moderator

Re: ordering by financial year [message #323874 is a reply to message #323063] Thu, 29 May 2008 22:20 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member
I can't see the importance of last_day. please explain.

Like what posted on top, I suggest a dummy column from decode. so you have "decode(mymonth, 'April', 1, 'May', 2...etc) as dummy". Then use that dummy in report order but don't display.

Report message to a moderator

Re: ordering by financial year [message #323879 is a reply to message #266124] Thu, 29 May 2008 22:35 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear skooman & wency

I have tried using last_day as well as decode as wency has said, It works perfectly in PL/SQL but when it is tried in a matrix report, The ordering is in alphabetical order. In the earlier posts I have given the RDF file as well as some sample data can use please try it and tell me if anything has to be done in the RDF file, because I believe that the problem is in the RDF file.

Thank you,

Alister

Report message to a moderator

Re: ordering by financial year [message #323883 is a reply to message #323879] Thu, 29 May 2008 23:04 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member
I don't have reports builder now. let's just clarify it this way.
1. What do you have in query? is it something like this?
select decode(mymonth, 'April, 1,'May',2,'June,3) as dummy, mymonth as fiscal_month from dual

2. Have you included that "dummy" column in your report, at the design mode and hide it(or just display it while on testing)?
3. try to put dummy column (in design view) first before the fiscal month.

[Updated on: Thu, 29 May 2008 23:04]

Report message to a moderator

Re: ordering by financial year [message #323958 is a reply to message #266124] Fri, 30 May 2008 02:49 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
The query I tried was like this
(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                 );


Alister

Report message to a moderator

Re: ordering by financial year [message #324441 is a reply to message #323958] Mon, 02 June 2008 08:43 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
Alister, where is the dummy column in your code?

(like Wency and I suggested, I just added the remark that you could use last_day in case of months, but I still meant using a dummy column to sort on)

Report message to a moderator

Re: ordering by financial year [message #325347 is a reply to message #266124] Thu, 05 June 2008 22:44 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear Friends
As I told you earlier, decode and dummy variables etc are all working fine in PL/SQL but when used in a matrix report the ordering is on that field that is being used only. Please check it in the RDF file I have given using the sample data.

Thank you


Alister

Report message to a moderator

Re: ordering by financial year [message #326037 is a reply to message #325347] Tue, 10 June 2008 00:20 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member
I tried to find way to view your rdf file. You don't have that "dummy" column in your select statement(main query), you only have "order by decode". Do as advised ang post what you got.
Order by in main query won't do that, it's only good in PL/SQL,Toad or SQL plus viewing.

Report message to a moderator

Re: ordering by financial year [message #326263 is a reply to message #266124] Tue, 10 June 2008 22:37 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear Wency,
I tried what you said but the order in the report display is alphabetical. I am attaching the RDF file kindly tell me where I went wrong. Thanks for your patience.


Alister

Report message to a moderator

Re: ordering by financial year [message #326292 is a reply to message #326263] Wed, 11 June 2008 00:07 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member
There is no dummy in your layout model. I told you to do so. Try to change the source of that F_MONTH with dummy and let's see what will happen.
Plus, if you are not adding numbers before the month, what's the use of substr(str,3,3)?

[Updated on: Wed, 11 June 2008 00:16]

Report message to a moderator

Re: ordering by financial year [message #326528 is a reply to message #266124] Wed, 11 June 2008 22:13 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
In the layout model I have the month in the rows, the finyear in the column and the cb field in the cells. Where should I add the dummy variable, because I do not want it in the report. I used substr(str,3,3) because I prefixed the months with 01,02 etc to print in the apr,may,jun ... order, I am sorry I for got to take it off. If using the dummy variable works out then I will not need to use the prefix. Can you please tell me where to use the dummy variable in the layout.


Thank you,

Alister

Report message to a moderator

Re: ordering by financial year [message #326592 is a reply to message #326528] Thu, 12 June 2008 01:40 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member
It's very difficult to teach you.
This is the last. I have this code for your table based on what you have posted.

[Updated on: Thu, 12 June 2008 01:41]

Report message to a moderator

Re: ordering by financial year [message #326594 is a reply to message #326592] Thu, 12 June 2008 01:43 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member
Now here's the report. you can see that what I have done was already advised in this thread. read it from the top.

Report message to a moderator

Re: ordering by financial year [message #326865 is a reply to message #266124] Thu, 12 June 2008 22:11 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear wency

Thank you very much for your patience and the report. Now I understood it. I have also learnt a new concept of working on hidden items.

Thank you once again for your patience and your time.

Alister

Report message to a moderator

Re: ordering by financial year [message #326927 is a reply to message #326865] Fri, 13 June 2008 02:23 Go to previous message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member
you're welcome.

Report message to a moderator

Previous Topic: Number Conver into Words
Next Topic: Report Margin
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 09 00:09:57 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.