It seems like this should be easy, but I can't seem to make it work.

My report is summing values across dates. I'm pulling the data into Excel via WebQuery and updating charts based on the values.

I need to include columns for 12 months of data, even if I only actually have 7 months of data (FY is July 1 thru June 30, I currently have data for July thru January) and always have the row-total in column 13 (so the Excel Chart always looks in column M for my total).

Maybe tomorrow it will all make sense and the solution will truly be easy. However, any input or help you have to offer will certainly make my job easier, once again.

Thanks,
Deb

say deb, how about making a vector of months
DEFINE FILE ..
DATEYM/YYM=somesmartdate in yymd format;
END
TABLE FILE ..
BY DATEYM
IF DATEYM FROM 200507 TO 200606
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS BASE
END
-* if you don't have these dates in your file, which you might not, since its future, then macgyver it.
then join this hold file to your data
TABLE FILE ..
SUM UNITS DOLLARS BY DATEYM
ON TABLE HOLD AS GOODSTUF
END
JOIN DATEYM IN BASE TO DATEYM IN GOODSTUF AS J1
TABLE FILE BASE
SUM DOLLARS OVER UNITS ACROSS HIGHEST DATEYM
AND ROW-TOTAL
END
..
btw, Are you building your webquery from scratch or are you using Export to Excel, and letting Mr. Gates do the heavy lifting for you?
...

Thought about trying that, but never had much luck with the macgyver technique. Maybe I'll give it another try. There may not be a better way to accomplish this.

Thanks for the example, that always helps a bunch. I'll post back to let you know how it goes.

We are building our WebQuery from scratch (aka, the hard way) because they're MRE reports. We built an Excel file where you just plug in the folder, fex & domain names (plus any needed variables) and it builds the address for you. We can then just copy and paste the "built" address into the WebQuery address. Saves many headaches when setting up the WebQuery from scratch.

Haven't been able to use the RCXX yet because there's no "real" self-serv apps to run from (slacker=me!). However, I've showed too many people the RCXX; so, by popular demand, we're heading that direction quickly. It'll be a REAL time-saver when we get there.

Thanks for the suggestions,
Deb

well, you could cheat on MacGyver and just build your file of dates from scratch using DM
set up some loop from some startdate to some enddate
and a tempfile in the agent...
-WRITE MYTEMPFL 200507
etc
definitely not MacGyver, (maybe call it Baywatch?)

As I've mentioned in several posts before (and Susan you should remember in relation to graphs)
use ACROSS COLUMNS

TABLE FILE CAR
SUM DEALER_COST AND ROW-TOTAL
ACROSS COUNTRY COLUMNS 'ENGLAND' AND 'W GERMANY' AND 'JAPAN'
END
-RUN

AS TA kindly wrote up with regards sparse matrix population (Dense Data or No Data) the same technique works for ACROSS and will deliver what you need.

Having solved that I will expand fully on what TA published and add the detail that he forgot.

When generating the output directly using the ROWS OVER method everything works fine.
However If the data that you are generating is a hold file for subsequent use then there are a number of issues that you must be aware of.

1st the hold file does not contain the field name of the column used with the BY OVER command.
The reason is that you are actually using FML syntax and what WebFocus internally issues is a FOR statement.
This means that the column in the HOLD file has an alias but no fieldname

2nd the format of the held column is not the usage of the original column but is an alpha value equal to the maximum physical length of the original columns data content

What this means is that to reference the column in a hold file you must use its alias (Enn) and if you want it in its original format you must either issue a DEFINE or COMPUTE to convert it back.

If you want to use it in a JOIN then it needs to be a DEFINE based join, because you cannot join different formats.

Additionally the method ONLY works if at least 1 of the OVER values exists in the source data.

If there is not a least 1 instance of 1 value then it returns 0 records.

To get around this issue and produce a totally measure empty matrix with just your keys
you must trick WebFocus into delivering it.

for example the following generates a driver file that can then be used to join to another data source to get measures for all possible combinations.

DEFINE FILE CAR
DUMMY/A11='A';
END
TABLE FILE CAR
BY COUNTRY
BY DUMMY ROWS 'A' OVER 'Soft Top' OVER '4 X 4' OVER 'Sportster' OVER 'V8 Turbo' OVER 'Donkey'
ON TABLE HOLD
END
-RUN
DEFINE FILE HOLD
BODYTYPE/A24=E02;
END
TABLE FILE HOLD
BY COUNTRY
BY BODYTYPE
WHERE E02 NE 'A';
END
-RUN

Without the define you get 0 lines.

I might be over simplifying the problem here but try the following. All I did was create a DEFINE field that contains only the month piece and then did an ACROSS with COLUMNS. This should work irregardless of the year. The PERSINFO file is a sample database that comes with FOCUS so you should be able to run this.

DEFINE FILE PERSINFO
BIRTH_MONTH/MT=BIRTHDATE;
END

TABLE FILE PERSINFO
SUM CNT.PIN
BY COUNTRY AS ''
ACROSS BIRTH_MONTH
COLUMNS '07' AND '08' AND '09' AND '10' AND
'11' AND '12' AND '01' AND '02' AND
'03' AND '04' AND '05' AND '06'
END

the prob, mick, is that in financial data, you don't have all the months, untilyour fiscal yr is over. so you have to fake it, creating room for them in the output matrix.
MacGyver-ing a calendar to join to works , bulletproof.

I must still be missing something. The code I posted seems like it will still do the job. If you are always going to look at columns from Jul to Jun, regardless of the FY, then COLUMNS should do the trick. The key to my example is that the MONTH portion is isolated and therefore you can do a plain vanilla COLUMNS statement with month numbers. This will force FOCUS to create all 12 month columns even when there are not 12 months of data. You should not need to resort to MacGyver techniques to do what a standard ACROSS COLUMNS statement is designed to accomplish. Am I still not seeing the whole picture?

I modified the code to show that it works without a full year of data.

DEFINE FILE PERSINFO
BIRTH_MONTH/MT=BIRTHDATE;
END

TABLE FILE PERSINFO
SUM CNT.PIN/I9S ROW-TOTAL
BY COUNTRY AS ''
WHERE BIRTH_MONTH GT 6
ACROSS BIRTH_MONTH
COLUMNS '07' AND '08' AND '09' AND '10' AND
'11' AND '12' AND '01' AND '02' AND
'03' AND '04' AND '05' AND '06'
END

I tried the McGyver option first. Using McGyver (and Susannah's example), I created a file of dates for the fiscal year. Joined my FY data (held previously) to my new calendar file and created my report. Worked like a charm.

THEN,
Along comes JG and Mickey.

I had tried the ACROSS . . COLUMNS previous to posting (I'm not a COMPLETE rookie). However, it didn't accomplish what I needed (the way I coded it). BUT, I didn't try it across just the month numbers, as in Mickey's example.

I modified my fex to try using just the Months in an across . . columns statement. It all worked just as Mickey expected it should. My report now shows July '05 thru June '06 columns with missing data for the Feb - June months (so my totals and averages still calc. correctly). EXCELLENT ! ! !

As usual with WebFOCUS, more often than not there's more than one way to achieve the desired report. I LOVE VERSATILITY ! ! !

Thanks Susannah. Because of you and your examples, I can now say that I've used the McGyver technique successfully (and have an example to build from).

And Thanks to JG and Mickey. Because of you, I now have a simple solution to a problem that can and will be applied to many new procedures.

We're all so much better because you guys are SO good. Thanks for taking the time to share your knowledge.

Deb

Your welcome Deb. I'm glad we all could give you a few solutions to meet your needs. Based on past experience, things you learn from Focal Point, you may not use right away but SOME DAY you will suddenly have a need for everything you learned here.