I'm trying to create what I thought would be a fairly simple report. All I am trying to do is show 5 columns: customer, sales for entire period and then sales for the latest three days. I have tried multiple things but I cannot seem to get this to work. Any suggestions?

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

Have you tried a multi-verb request? I don't know what your period is but let's say that it is the current month. Here is some sample code, untested.

-SET &TESTDATE=20100429;
-SET &DAY3=AYMD(&TESTDATE,-3,'I8YYMD');
-TYPE &DAY3
-SET &CURRPER=EDIT(&TESTDATE,'999999');
-SET &BEGDT=&TESTDATE || '01';
DEFINE FILE whatever
DAYSALES3/D20=IF SALESDATE EQ &DAY3 THEN SALES ELSE 0;
DAYSALES2/D20=IF SALESDATE EQ AYMD(&DAY3,1,'I8YYMD') THEN SALES ELSE 0;
DAYSALES1/D20=IF SALESDATE EQ AYMD(&DAY3,2,'I8YYMD') THEN SALES ELSE 0;
END
TABLE FILE whatever
SUM SALES
BY CUSTOMER
SUM DAYSALES3 DAYSALES2 DAYSALES1
BY CUSTOMER
WHERE SALESDATE GE &BEGDT
END

I think you will have to tweak the Dialogue Manager code a bit to suit your needs and to handle your periods and month changes but this should give you an idea of what you need to do.

Or this:

SET ASNAMES=ON
SET HOLDFORMAT=ALPHA
SET HOLDLIST=PRINTONLY

TABLE FILE GGORDER
SUM
QUANTITY AS 'TOT_QTY'
BY PRODUCT_CODE

SUM
QUANTITY
BY PRODUCT_CODE
BY HIGHEST 3 ORDER_DATE

ON TABLE HOLD
END

TABLE FILE HOLD
SUM TOT_QTY
BY PRODUCT_CODE

SUM QUANTITY
BY PRODUCT_CODE
ACROSS ORDER_DATE
END

I was actually able to get it to work using the DEFINE method.


DEFINE FILE SQLOUT
SALES0/D12.2=IF DATE1 EQ TO_DATE THEN SALES ELSE 0;
SALES1/D12.2=IF DATE1 EQ TO_DATE -1 THEN SALES ELSE 0;
SALES2/D20.2=IF DATE1 EQ TO_DATE - 2 THEN SALES ELSE 0;
END

TABLE FILE SQLOUT
SUM
SALES
SALES0
SALES1
SALES2
BY CUSTOMER


But now I am struggling with something new on this report. The TO_DATE field is a parameter in the SQL. I need the column heading on the SALES1 & SALES2 columns to reference the date they refer to. Any suggestions on this?

That's what the ACROSS is for.

That wasn't working. I had to go back and use a series of SET commands to end up getting it to work. thanks all though.

Are we not curious about this "series of SET commands"?