Somewhere in my office there is a copy of "1001 ways to work with dates in WebFocus" but I can't find it among the chaos ...

I've had this problem before and worked around it with a decode, but now I'd like to get to the root of the issue.

DEFINE FILE xyz
YEAR/YY=BLDATE;
MONTH/Mt=BLDATE;   (smartdate)
END

TABLE FILE xyz
SUM QUANTITY
ACROSS YEAR
ACROSS MONTH
END

gives:

2013                                            2014
Dec Jan Mar Apr May Jun Jul Aug Sep Oct Nov Dec Dec Jan Mar Apr .. etc

In every case Feb is missing and the year starts with December ..

If I do

DEFINE FILE xyz
YEAR/YY=BLDATE;
MONTH/M=BLDATE;
MONTH2/A3=DECODE MONTH(1 'Jan' 2 'Feb' 3 'Mar' 4 'Apr' 5 'May' 6 'Jun' 7 'Jul' 8 'Aug' 9 'Sep' 10 'Oct' 11 'Nov' 12 'Dec');
END

TABLE FILE xyz
SUM QUANTITY
ACROSS YEAR
ACROSS MONTH NOPRINT
ACROSS MONTH2
END

I get results as expected ....

2013                                            2014
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr .. etc

(How very irritating ... even though the output appears with 2014 correctly over Jan in this editor, it's shifted to the left in the Email Digest (And yes, I did put it between code tags)

This message has been edited. Last edited by: George Patton,

Before tearing out hair, see what happens with

DEFINE FILE xyz
YEAR/YY=BLDATE;
YM/YM = BLDATE;
MONTH/Mt=YM; 
END

TABLE FILE xyz
SUM QUANTITY
ACROSS YEAR
ACROSS YM
ACROSS MONTH
END

If that behaves, you can NOPRINT the ACROSS YM. If not, at least you know what's broken -- and you are freed from keeping MONTH sortable, so it can be defined as /A3 and you're still in business.

I get this to work okay (7.705/8.007):

TABLE FILE GGSALES
PRINT DOLLARS 
COMPUTE NDATE/YYMD = DATECVT(DATE, 'I8YYMD', 'YYMD');
BY REGION
ON TABLE HOLD
END
DEFINE FILE HOLD
YDATE/YY = NDATE;
MDATE/Mt = NDATE;
END
TABLE FILE HOLD
SUM DOLLARS
ACROSS YDATE
ACROSS MDATE
END

What we do, although we do this because our financial year starts in April:

DEFINE FILE xyz
 YEAR/YY=BLDATE;
 MONTH/Mt=BLDATE; (smartdate)
END
TABLE FILE xyz
SUM QUANTITY
ACROSS YEAR
ACROSS MONTH COLUMNS 'Apr' AND 'May' AND 'Jun' AND 'Jul' AND 'Aug' AND 'Sep' AND 'Oct' AND 'Nov' AND 'Dec' AND 'Jan' AND 'Feb' AND 'Mar'
END

Works a treat, especially with GRAPH requests where any extra ACROSS columns end up in the result. However, if you're dependent on the order of those months in calculations, you're in for a surprise or two; don't use this with cumulative sums, for example.

But surely Mt (or Mtr) are just supposed to be *display* options for M ...

Jack, Alan and Wep's solutions - all good, essentially are workarounds (in fact, as per Jack, I do have a YYM defined field in the MFD already).

I guess my question is: Is this a bug? Or something that has just escaped the notice of the WF programmers. (I could be wrong here, but I swear this was never an issue back in the days of PC FOCUS 6.0)

We aren't seeing this issue here (7704M), we would have noticed. That seems to imply that you're looking at "unintended behaviour", or a little feature with six legs.

Perhaps it's an issue specific to your adapter? What is the datasource? FOCUS? Some relational database?

If the latter, what SQL is being generated? (I'm sure you're familiar with statement tracing, but if not, that's what to search for)

More specifically: We recently had an odd issue where WF generated some invalid SQL for an Oracle 11g database (duplicate nested COUNT DISTINCT statements). The workaround provided to us was to set:

SQL SQLORA SET OPT NOAGGR

That statement disables aggregation optimization on the WF server and leaves that to the database server (the healthy thing to do, IMHO). I have yet to test it though, as we worked around the issue before IBI came with this option (hey, I need to be able to buy bread at the end of the day!).

Perhaps you can apply that to your flavour of database?

I thought that Jack and Alan gave a "check this first" and "it works for me" responses respectively and only Wep gave a workaround?

Using Mt works OK for me so I am wondering if you have a rogue MONTH column lurking which is taking precedence?

Following on from Alan, this sample against GGSALES gives the correct order -

DEFINE FILE GGSALES
  YEAR/YY         = DATE;
  MNTH/Mt         = DATE;
END
TABLE FILE GGSALES
   SUM DOLLARS AS ''
ACROSS YEAR    AS ''
ACROSS MNTH    AS ''
ON TABLE SET PAGE NOLEAD
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET STYLE *
  grid=off, size=9, $
ENDSTYLE
END

T

Actually Jack's solution doesn't work, and Alan's involves creating a hold file. Wep's initial post is sort of specific to the fiscal year situation (and interesting for that in its own right) but no shorter really than my own DECODE.

All 3 solutions suggest more than one statement to get to the defined field Mt, which is why I called them workarounds (as is my DECODE).

When I run Tony's example against GGSALES, things show up properly, and things also display properly against my own Focus database.

This makes me think that Wep may be on to something with his second post. In this case I'm using the SQLJDBC driver to access a FIREBIRD database.

And I'm thinking that in the past it was probably when I was reporting against a DB2 database that I saw this same problem.

The SQL SQLORA SET OPT NOAGGR makes no difference - and I don't know why it would, because the actual data column values are correct - it's only the column titles that are in the wrong place.

For now I'll call this worked around - I too need to be able to buy bread at the end of the day...

I've also changed the subject line to be perhaps more descriptive of the problem in case someone is searching in future.

Thanks for the discussion...

Hmmm -- so your data for each year do map into 12 perportedly "monthly" buckets per YEAR, just that months 1,2 are labelled Dec,Jan.

Did you check whether the quantities shown for months 1-12 are the correct values for Jan-Dec, resp. (regardless of how the buckets are labeled)?

Here's a test case using standard IBI sample data, and deriving Year and Month from a Smart (MDYY) date (as opposed to GGSALES's dumb DATE/I8YYMD)

APP PREPENDPATH IBISAMP

DEFINE FILE CASHFLOW
YEAR/YY=CASH_DATE;
MONTH/Mt=CASH_DATE;  
END

TABLE FILE CASHFLOW
SUM LONG_TERM_DEBT
ACROSS YEAR
ACROSS MONTH
END

Works correctly in 7.6.10. What happens in on your release?

Hi Jack,

This seems to be an adapter issue. The column values are actually fine. It's just the titles that come out wrong, in a consistent manner - Dec appears where it should be Jan, Jan appears where it should be Feb, and then Feb doesn't appear at all. The rest of the months are fine, ending up with Dec in the usual place.

I don't have the issue with Focus databases, hold files or flat files.

How is BLDATE declared in the synonyn?

If you define Date/YYMD=BLDATE; does Date display the correct values?

Jack: (et alii)

Take me out behind the barn and shoot me.

I lied when I said that this problem only happened with non-Focus databases. This acually IS a Focus database. And the BLDATE field is properly defined as YYMD.

The problem has nothing to do with the date format. It's the output format.

I've been using EXL07 as the output. I only discovered this morning that if I change to HTML or PDF, everything is hunky-dory. It's only with EXL07 that I have the issue.

I should mention that I am opening the EXL07 output with OpenOffice, not Excel. However I haven't discovered any other irregularities like this with EXL07 and OpenOffice or Lotus Notes Symphony. EXL97 works properly. (I'm unable to open EXL2K without Excel installed).

DEFINE FILE GGSALES
  YEAR/YY         = DATE;
  MNTH/Mt         = DATE;
END
TABLE FILE GGSALES
   SUM DOLLARS AS ''
ACROSS YEAR    AS ''
ACROSS MNTH    AS ''
ON TABLE SET PAGE NOLEAD
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD FORMAT EXL07
ON TABLE SET STYLE *
  grid=off, size=9, $
ENDSTYLE
END

George

This message has been edited. Last edited by: George Patton,