Hi!
I have to generate a report in WEBFOCUS which requires months,year to be generated based on two dates entered by the user. For eg, if 2 dates entered are 01/01/2006 to 03/31/2006, then Jan,06 , Feb,06 and Mar,06 values must be generated.
Subsequently, some calculations need to be done for each of these months and the data needs to be displayed in this manner:
---------------------------------------
Jan,06 Feb,06 Mar,06
Members 10 12 11
-----------------------------------------

The table that is avalaible to me has fields-
Mem_ID, Start_Date & End_Date only.
If Start_Date is < 1Jan and End_Date is >31Jan, then that member is counted under Jan,06 column.

I have tried using HOLD, MORE and nothing seems to work.
Suggestions would be highly welcome.

Thanks

Is your example right? Should you consider inside or ouside of the date boundaries?
Keeping that aside there seems to be more possibilities than your example states. Let us say you have a record as mentioned below
Member_id Start_date End date
M1 01/01/2006 28/03/2006
What should happen in this case? Should this member be consider for only one of the months(in that which month) or all three months?

@Venu
The example given is alright.
When the start_date is 01/01/2006 n End_Date is 28/03/2006, then M1 would be considered for all three months Jan , Feb, March.
Let me explain with another example,
Start_date- 01/01/2006
End_date- 01/28/2006
Then this member isnt counted for Jan or any other month.

-* User selected date in format YYYYMMDD
-SET &c_SEL_FROMDATE = '20051101';
-SET &c_SEL_TODATE = '20060430';

-SET &c_FROMYEAR = EDIT(&c_SEL_FROMDATE,'9999$$$$');
-SET &c_TOYEAR = EDIT(&c_SEL_TODATE, '9999$$$$');
-SET &c_FROMMON = EDIT(&c_SEL_FROMDATE,'$$$$99$$');
-SET &c_TOMON = EDIT(&c_SEL_TODATE, '$$$$99$$');

-* Total no of months in the user selected window period
-SET &c_NOMONTHS = ( &c_TOYEAR.EVAL - &c_FROMYEAR.EVAL - 1) * 12  +   ( 12 - &c_FROMMON.EVAL +1 ) +  &c_TOMON.EVAL ;
-RUN


-*
-* Now your plan should to convert the startdate field into a month no & end date field also into another month no
-* Unfortunately I couldn't find a date field in CAR table, or I could have posted some code
-* Refer at the end this post for some thoughts on what you would need to do
-*

-* Syntax for all the dynamic month columns to be created 
SET HOLDLIST = PRINTONLY
TABLE FILE CAR
PRINT 
	CAR NOPRINT
	-* temp no is the month no
	COMPUTE TEMP_NO/I2 = TEMP_NO + 1 ; NOPRINT 
	-* The following code SEATS is taken as example and checked against current month no
	-* Your actual compute field would have to come with a compute from the combination of StartMonthNo 
	-* EndMonthNo and Current MonthNo. 
	COMPUTE NEW_COL/A200 = 'COMPUTE COL' | EDIT(TEMP_NO) | '/I5 = IF SEATS LT ' | EDIT(TEMP_NO)| ' THEN -1 ELSE IF SEATS EQ ' | EDIT(TEMP_NO) | ' THEN 0 ELSE 1 ;' ; AS ''

	-* the following condition is to generate only the requiered no compute columns
	-* if you know there will be more that what CAR table contains  you need to use another table which can 
	-* generate thsi many rows if compute statements
	WHERE RECORDLIMIT EQ '&c_NOMONTHS.EVAL';
	ON TABLE SAVE AS HLD1 

END
-RUN

TABLE FILE CAR
PRINT
	CAR
	SEATS
	-* Include the dynamically generated compute columns
	-INCLUDE HLD1
ON TABLE PCHOLD FORMAT HTML
END
-RUN


-* 
-*	Startdate < Selected fromdate Month NO could be 0 or -1 any thing below 1 (which is the starting month no)
-* 	because this from date is always a candidate

-*  Startdate > Selected todate, This is never a candidate 
-* 	Month NO should beyond possible no of months, for the above selection it could be be 7 or 700 

-*  If your startdate field falls within fromdate and todate, The start month no should be one of the selected 

-*	Reverse kind logic you need to apply for the End date field  and comeup with end month no

-* Once you come up with Start month no and End month no and your dynamic compute will be based 
-* on the these two values and Temp_No

All the best

Thanks so much Venu. Yesterday i had been trying to generate the report using FML reports..any suggestions on that...

Hi Torque,

For general guidance on financial reporting, the following manual may be of interest:

WebFOCUS Creating Financial Reports 7.1.3

This is for WebFOCUS 7.1.3. If you are on other releases please let me know and I will be more than happy to locate the right one for you.

Hope this helps.

Cheers,

Kerry