Hi All,

Happy New Year

I have table called month_sales, in which field names are month_name(Jan,Feb,Mar...Dec) and monthsales field with numeric values and year field with 2004,2005,2006
in the selection screen if i select monthname as Jul and year as 2005 i should get the output as

Jul Aug Sep Oct Nov Dec .........Jun
2005 25 30 10 25 10

TABLE FILE month_sales
PRINT
monthsales
BY year
ACROSS month_name

Problem: if i say ACROSS month_name it sorts according to Alphabetical order

Help me in doing custom sort based on the selection parameter.

Thanks in advance
RajTA

Raj

If you take a look at this site you will see some function buttons.
the 3th says "Find" try that to find subjects already discussed.

It's not that we do not want to tell this agian, but it might be quicker if all the new starters would know all the smart things here that are helpfull.

Now your question.

There are many ways to do this.
If your date was a real date field your across would work as you wanted.
So you can convert it to a realdate.

An other solution is create a sortfield by a decode.

DEFINE FILE xxx
SORTFIELD/I2=DECODE MONTH(JAN 1 FEB 2 MAR 3 ... Oct 10 Nov 11 Dec 12);
END

In the report you add an extra across before the across month_name.

You also can spell out the names in the sequence you want

ACROSS month_name COLUMNS JAN AND FEB AND MAR etc

Another alternative would be to define the month as a date, ie,
DEFINE FILE XXX
SORTFIELD/Mt = SALESDATE;
END

This would assume SALESDATE is the date in a smart date format representing say the date of a sale if it were in the master. Works in version 7.2. Then you get a pretty heading which sorts on the underlying order.

Thanks Frank and Leah

But Frank i have already done that and it is working but based on the month selection it should change.
for example
if i select May month and 2007 year

it should come from

Jun Jul Aug Sep Oct Nov
if i select Mar

is should come from
Apr May Jun Jul

and so on
based on the inpur parameter i select i should get the next month name from the Across field
and the datatype for the month_name field is varchar

Thanks
Raj

Raj

You can do this.

If you select a month that should be the starting month.
suppose this is may (number5)

-SET &SORT2/I2=DECODE &MONTH(JAN 1 FEB 2 MAR 3 ... Oct 10 Nov 11 Dec 12);
DEFINE FILE xxx
SORT1/I2=DECODE MONTH(JAN 1 FEB 2 MAR 3 ... Oct 10 Nov 11 Dec 12);
SORTFIELD/I2=IF SORT1 GE &SORT2 THEN SORT1-&SORT2+1 ELSE  SORT1-&SORT2+13;
END

Now when you select MAY the new sequence will be MAY=1 JUN=2 etc

I hope this will help some steps...

Most of the time we'll handle it with a NOPRINT.

ACROSS MONTHS NOPRINT
ACROSS MONTH_DATEX AS ''

In this example MONTHS is the actual date (or datetime) column, and MONTH_DATEX is the formatted (display) version of the month. Ultimately though, if they are a DATE or DATETIME format, they should sort correctly. You may want to look at your formats...

Trav

Your suggestion is ok, but the original question was a bit different, they want a flexibel sorting where the starting month is influenced by the end user.

My solution could do that. the straitforward solution was not suitable here.

Yeah I didn't catch that his report could span years -- if not, then it could be simplified but obviously that was not the case (and rarely is!). I got hung up on the fact that it was sorting alphabetically and thought it was a format issue -- which for the OP I would still pay close attention to the difference in dates, datetimes, and alphas.

In retrospect, Frank your solution is pretty much spot-on to what we've done here with similar reports and graphs and works nicely in both scenarios.

RajTA

Assuming you have field like:

 MTH/YYMD 

then you can use:

 ACROSS MTH/YYMtrD 

This sorts MTH as a YYMD field so 20071231 comes before 20080101 but displays according to the YYMtrD in the ACROSS statement so you get full names.

Fernando

Hi All,

Thanks for the answers but

I have tried with the following code but it gives the output sorted according to month_name from febrauary to January,
but if i select month april in the html selection screen year as 2005 then the output month should start from may jun jul oct.


month name should dynamically change the order.

in across columns instead of hardcoding can i do any parameteration declare parameter which takes care of the order.

The column MONTHS is varchar datatype in SQL Server



DEFINE FILE TIME_DIM
MONTH/A20 = DECODE MONTHS (12 'Jan' 1 'Feb' 2 'Mar' 3 'Apr' 4 'May' 5 'Jun' 6 'Jul' 7 'Aug' 8 'Sep' 9 'Oct' 10 'Nov' 11 'Dec');
END
TABLE FILE TIME_DM
SUM
EXP_AMT NOPRINT
USE_AMT NOPRINT
COMPUTE Rate/P20.2 = EXP_AMT / USE_AMT;
BY REGION
BY YEAR
ACROSS MONTH COLUMNS 'Feb' AND 'Mar' AND 'Apr' AND 'May' AND 'Jun' AND 'Jul' AND 'Aug' AND 'Sep' AND 'Oct' AND 'Nov' AND 'Dec' AND 'Jan'
WHERE YEAR EQ '2003';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END

Did you look at Frank's example above? It has everything you need for your code to work. The only thing I think you'd have to change is that you would want to add 1 to &SORT2 in his example because you want 1 month greater than the one selected.

In your code example, you are not using the input from the html screen at all.

And the idea is to sort across using that constructed SORTFIELD noprinted.

Please play around with Frank's code and see if you can't make it work for you.

You only have to have a basic level of understanding of FOCUS and read what folks write to get your results.

Use "real" dates (as Frank mentioned) also known as "smart dates" i.e. those with a format of YYMD or the like. Leah actually gave you what you needed but you seemed to ignore it? If you didn't understand then perhaps you should push for some basic training with IB from your manager?

The following code gives a years worth of data from apr 1996 to mar 1997 and will automatically show different months as the across column heading when you change the selected date range -

DEFINE FILE GGSALES
YEAR/YY  = DATE;
MONTH/Mt = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS
ACROSS YEAR NOPRINT
ACROSS MONTH AS ''
BY REGION
BY ST
BY CATEGORY
WHERE YEAR FROM 1996 TO 1997
WHERE DATE FROM 19960401 TO 19970331
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF, $
ENDSTYLE
END

T

Like everyone has previously pointed out, the months are sorted correctly in this example because the month column (MTH) is defined as a "Smart Date" and not as alpha.

DEFINE FILE TIME
DATE1/A8YYMD = DATE;
MTH/Mtr = DATE1;
DUMMY1/P5 = 1;
END
TABLE FILE TIME
SUM
DUMMY1
ACROSS MTH

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLESHEET *
BORDER=1, BORDER-COLOR=SILVER,
FONT='ARIAL', SIZE=8, $
END

Result:

  
MTH 
January February March April  May June July August September October November December 
   1600     1462  1600  1550 1600 1551 1600   1600      1550    1600     1550     1600