If you see this report, Month and Year are By Fields and CAR1 CAR2 are across fields…

Code like

TABLE FILE XXX
SUM
COST
BY MONTH
BY YEAR
ACROSS CAR
END
-EXIT

Actually, July 10 has no data in the Database itself. But I need to show that as well in the Report like this, with all the across columns values as SPACE or Zeros(See the RED row).

What is the optimal way of getting this done ??

This message has been edited. Last edited by: Kerry,

You need to left-join a calendar table that contains each month as a record to the CARs table to get that result set.

If I understand your question, then please consider "COLUMNS", as in:

TABLE FILE CAR
SUM DCOST 
ACROSS COUNTRY COLUMNS 'ENGLAND' AND 'FRANCE' AND 'ITALY' AND 'JAPAN' AND 'W GERMANY' AND 'The USA'
END

Note that there's no data for 'The USA', but it's listed.

BTW, this can be done with ROWS / OVER, as follows: BY COUNTRY ROWS 'ENGLAND' OVER 'FRANCE' OVER 'ITALY' OVER 'The USA'. Note this method can also be used for eliminating unwanted rows. Note that I omitted (no offense intended) 'JAPAN' and 'W GERMANY' this time.

He also seems to need data for July even if it does not exist. So using BY ... ROWS ... might also help.

That, some McGyver or as Anatess said getting all needed combinations of Month/Year and OUTER join that to the actual measurements he needs. That actually sounds like MATCH .. OLD-OR-NEW ... there seems to be many an approach to this.

But, "sounds like MATCH .. OLD-OR-NEW cannot get info that's not in the DB. I'm thinking that, when the data "does not exist". it "does not exist". So, it, in turn cannont be extracted. The use of ROWS and COLUMNS is used a lot in "calendar" reports, where a month is missing.

So, Ramkumar - Webfous: What's your thoughts on this?

Hi All,

Sorry for the much delayed reply.

Was off for a vacation, and was not lookin to the Forums past week.

I will try both the options. That left outer JOIN options seems to suit my requirement. But I Dynamically has to to create the CALENDAR focus mart based on the user inputs, and that is feasible I guess.

Thanks for all your overwhelmed response. Will try out options and let you know.

Hi All,

Sorry for the much delayed reply.

Was off for a vacation, and was not lookin to the Forums past week.

I will try both the options. That left outer JOI

A perfect fit for McGuyver:

  
-* File Ramkumar01.fex
FILEDEF FSEQ DISK FOCALPOINT/FSEQ.MAS
TABLE FILE CAR
SUM SALES
BY COUNTRY 
BY SEATS 
ON TABLE HOLD AS H1
END

JOIN BLANK WITH SEATS IN H1 TO BLANK IN FSEQ AS M_
DEFINE FILE H1
BLANK/A1 WITH SEATS = ' ';
CSEATS/I3=DECODE COUNTER(1 2 2 4 3 5 ELSE 0);
CSALES/I5S=IF SEATS EQ CSEATS THEN SALES ELSE 0;
END
TABLE FILE H1
SUM CSALES 
BY COUNTRY
BY CSEATS
IF COUNTER LE 3
END

The FSEQ master (it is in the FOCALPOINT application):

  
 FILE=FSEQ, SUFFIX=FIX
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

I don't think you can just McGyver this because the only way you'll know that you have missing data is if you bounce it off a calendar. Make sense?

But, if your report is always going to have 12 rows, one row for each month of the calendar starting from January and end in December, then you can do the BY MONTH ROWS 'Jan' OVER 'Feb' OVER 'Mar'... etc.

If you're going by Month/Year with variable range, then the rows-over won't work. Joining to a calendar would work.

You can either build the calendar table using the minimum and maximum date values from your table with McGyver, or you can just join to an existing calendar table - data warehouses usually have these tables for payroll or project management applications.

This message has been edited. Last edited by: Anatess,