[SOLVED]Passing Variable to report using Dialog Manager

September 03, 2015, 12:16 PM

Hello,

I have a report that I'm using Dialog Manager to determine if they want the data for a Day or Month. This will eventually be run from Report Caster. Report Caster will be set to pass in the correct parameters.

When a D (Day) is entered I want to do a BY on the data by a Date by day.
When a M (Month) is entered I want to do a BY on the data by a Dates Month.

I created a Define field that will convert the date to a MDYY and a define field that pulls the Month out of the date. So basically I want to have ONE DEFINE to pull the correct by based on what the &TIMEFRAME is.

My problem is when using the &TIMEFRAME webfocus isn't recognizing it as a field. The error says: "The Fieldname is not recognized: D"

Example of a Define field that I would like to have:

 
IF &TIMEFRAME EQ 'D' THEN HDATE(actualDate, 'MDYY')
ELSE IF &TIMEFRAME EQ 'M' THEN 
            IF      DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 1 THEN 'January'
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 2 THEN 'February'
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 3 THEN 'March'
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 4 THEN 'April'
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 5 THEN 'May'
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 6 THEN 'June'
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 7 THEN 'July'
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 8 THEN 'August'
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 9 THEN 'September' 
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 10 THEN 'October'
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 11 THEN 'November'
            ELSE IF DPART(HDATE(actualDate, 'MDYY'),'MONTH','I2') EQ 12 THEN 'December';

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

September 03, 2015, 12:48 PM

Doug

Hi Kelly, Would you please forward more of the fex, including the initial setting of the DM variables?

September 03, 2015, 03:10 PM

KellyT

 
-IF &TIMEFRAME EQ 'D' THEN GOTO DAILYDATES	ELSE IF &TIMEFRAME EQ 'W' GOTO WEEKLYDATES ELSE IF &TIMEFRAME EQ 'M' GOTO MONTHLYDATES;


-SET &CUSTOMERNAME


-DAILYDATES
-SET &BEGINDATE = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-10),'YYMD','I8YYMD');
-SET &ENDDATE = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-1),'YYMD','I8YYMD');
-SET &PRIORDATE = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-1),'YYMD','I8YYMD');
-SET &SORTBYDATE = 'daysort'
 -GOTO FINISH

-WEEKLYDATES
-SET &BEGINDATE = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-56),'YYMD','I8YYMD');
-SET &ENDDATE = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-1),'YYMD','I8YYMD');
-SET &PRIORDATE = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-1),'YYMD','I8YYMD');
-SET &SORTBYDATE = 'weeksort'
-GOTO FINISH

-MONTHLYDATES
-SET &BEGINDATE = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'M',-2),'YYMD','I8YYMD');
-SET &ENDDATE = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-1),'YYMD','I8YYMD');
-SET &PRIORDATE = DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-1),'YYMD','I8YYMD');
-SET &SORTBYDATE = 'monthsort'
-GOTO FINISH

-FINISH

September 03, 2015, 03:12 PM

KellyT

 
TABLE FILE DETSHIP
PRINT 
     DETSHIP.DETSHIP.proNumber
     DETSHIP.DETSHIP.lessthaneq2
     DETSHIP.DETSHIP.lessthaneq1
     DETSHIP.DETSHIP.between1and2
     DETSHIP.DETSHIP.between2and3
     DETSHIP.DETSHIP.between3and4
     DETSHIP.DETSHIP.greaterthan4
BY  DETSHIP.DETSHIP.day
ON TABLE SUBHEAD
""
WHERE ( DETSHIP.DETSHIP.stopType EQ 'P' ) AND ( DETSHIP.DETSHIP.puLoadType EQ 'LIVE' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = enblue_theme,
$
     TITLETEXT='Loading Recap',
$
TYPE=REPORT,
     STYLE=NORMAL,
$
TYPE=DATA,
     BACKCOLOR='NONE',
$
TYPE=TITLE,
     JUSTIFY=CENTER,
$
ENDSTYLE
END

September 04, 2015, 03:10 AM

Dave

KellyY,

first of all...

The DEFINE looks okay..
..except for the fact that, depending on the value of &TIMEFRAME the output is MMDY or a Char.

Far better solution is

DEFINE FILE 
   dayfield/MDYY = ...formula...;
   weekfield/I2 = ...formula...;
   monthfield/A10 = ...formula...;
END

-SET &SORTFIELD = IF &TIMEFRAME EQ 'D' THEN 'dayfield' ELSE
-                 IF &TIMEFRAME EQ 'W' THEN 'weekfield' ELSE
-                 IF &TIMEFRAME EQ 'M' THEN 'monthfield' ELSE '';

TABLE FILE ....
PRINT ....
      ....
BY    &SORTFIELD
....
END

Good luck,
Dave

September 04, 2015, 06:26 AM

Wep5622

It's not clear what's in &TIMEFRAME, but if that's a DM variable that holds either 'D' or 'M', then you created a define that tries to read a field named either D or M. Those fields probably don't exist in your table.

I suspect what you want is '&TIMEFRAME' in that DEFINE instead of plain &TIMEFRAME.

September 04, 2015, 09:16 AM

KellyT

Thank you for all your help.

Dave that worked and was much cleaner.

Wep5622, yes that was the issue I was running into. I didn't try your suggestion since the other one was already written and worked.

Thanks again