Hi All,

I am new to the webfocus.

I used the SP to get the data from DB2 with from_date and to_date as in input.

I tried to use drill down on account number but showing an error as "0 records in a table" even though the data is present for that date range.

Below is the main Proc from where I have provided the drilldown on CUST_ACC_NO

ENGINE DB2 SET DEFAULT_CONNECTION CONN_NAME
-DEFAULT &FROM_DATE='2015/07/21'
-DEFAULT &TO_DATE='2015/08/21'
-*COMPUTE NEW11/YY-M-D='&par1';
-*COMPUTE NEW22/YY-M-D='&par2';
SQL DB2 EX SYSPROC.TEST_WEBFOCUS_NEW('&FROM_DATE','&TO_DATE')@
;
TABLE FILE SQLOUT
HEADING CENTER
"STOP PAYMENT DETAILS"
PRINT
CUST_ACCT_NO AS CUSTOMER_ACCOUNT
PYMT_SRCE_DS AS PYMT_INIT_CHANNEL
PYMT_TRANS_PRCS_LOC_TS AS PYMT_INIT_TS
STOP_PYMT_LOC_TS AS PYMT_STOPPED_TS
5 AS LOB
PYMT_SRCE_DS AS PYMT_STOPPED_CHANNEL
PYMT_CURR_TRANS_AM AS AMOUNT
COMPUTE FDATE/YYMD='&FROM_DATE'; NOPRINT
COMPUTE TDATE/YYMD='&TO_DATE'; NOPRINT
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/STP/_EDAHOME/ETC/endeflt.sty,
$
TYPE=DATA,
COLUMN=CUST_ACCT_NO,
FOCEXEC=/WFC/Repository/Projectname/Proc_in.fex( \
ACC_NO=CUST_ACCT_NO \
FDATE1=FDATE \
TDATE1=TDATE \
),
$
ENDSTYLE
END
****************************
And below is the drill down proc, with ACCT_NO, FDATE1 AND TDATE1 as an input from the above procedure

ENGINE DB2 SET DEFAULT_CONNECTION CONN_NAME
-DEFAULT &FDATE1='2015/07/21';
-DEFAULT &TDATE1='2015/08/21';
-*-DEFAULT &par3='371056897009000'

SQL DB2 EX SYSPROC.TEST_WEBFOCUS_NEW('&FDATE1','&TDATE1')@
;
TABLE FILE SQLOUT
PRINT
CUST_ACCT_NO
WHERE CUST_ACCT_NO='&ACC_NO'

END
********************

Can anyone suggest the solution for this.

This message has been edited. Last edited by: <Kathryn Henning>,

How do I pass the parameter from one report to another.

User 1,

In your script you're saying:
PRINT CUST_ACCT_NO AS CUSTOMER_ACCOUNT

and later reference to the field CUST_ACCT_NO in your drilldown.

try SET ASNAMES = ON

and then

ACC_NO=CUSTOMER_ACCOUNT

quote:

TYPE=DATA,
COLUMN=CUST_ACCT_NO,
FOCEXEC=/WFC/Repository/Projectname/Proc_in.fex( \
ACC_NO=CUST_ACCT_NO \
FDATE1=FDATE \
TDATE1=TDATE \

Hi,

Thanks for your response.

Small doubt,where do I need to use SET ASNAMES = ON.

At the beginning of the procedure or just before
ACC_NO=CUST_ACCT_NO ??

Kind regards,
User1

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

Hi, yes it is common practice to put the SET commands on top most of the time.

Hi,

Thanks for this, but its not working

I did some changes, Below is the main report,


ENGINE DB2 SET DEFAULT_CONNECTION CNAME
-DEFAULT &FROM_DATE='2015/07/21'
-DEFAULT &TO_DATE='2015/08/21'
-SET ASNAMES = ON
SQL DB2 EX SYSPROC.TEST_WEBFOCUS_NEW('&FROM_DATE','&TO_DATE')@
;
TABLE FILE SQLOUT
HEADING CENTER
"STOP PAYMENT DETAILS"
PRINT
CUST_ACCT_NO AS CUSTOMER_ACCOUNT
PYMT_SRCE_DS AS PYMT_INIT_CHANNEL
PYMT_TRANS_PRCS_LOC_TS AS PYMT_INIT_TS
STOP_PYMT_LOC_TS AS PYMT_STOPPED_TS
5 AS LOB
PYMT_SRCE_DS AS PYMT_STOPPED_CHANNEL
PYMT_CURR_TRANS_AM AS AMOUNT
COMPUTE FDATE/YYMD='&FROM_DATE'; NOPRINT
COMPUTE TDATE/YYMD='&TO_DATE'; NOPRINT
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/STP/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
FOCEXEC=/WFC/Repository/Stop_Payments/Proc_in.fex( \
ACC_NO=N1 \
FDATE1=N8 \
TDATE1=N9 \
),
$
ENDSTYLE
END

******************************************
Drill down Report-->>

ENGINE DB2 SET DEFAULT_CONNECTION CNAME
-DEFAULT &FDATE1='2015/07/21';
-DEFAULT &TDATE1='2015/08/21';
-DEFAULT &ACC_NO='371056897009000'
SQL DB2 EX SYSPROC.TEST_WEBFOCUS_NEW(&FDATE1,&TDATE1)@
;
TABLE FILE SQLOUT
HEADING CENTER
"COUNT OF STOP PAYMENT BY ACCOUNT"
SUM CNT.CUST_ACCT_NO AS COUNT_STOPPED_PYMT
BY CUST_ACCT_NO
WHERE CUST_ACCT_NO EQ '&ACC_NO'
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/STP/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

Not able to figure out the issue.

Note:: CUST_ACCT_NO -->> char datatype in DB

Thanks ,
User1

User1,

There is a difference between -SET en SET command. SET ASNAMES is without the dash, so

SET ASNAMES = ON

Furthermore, what message is displaying? Try to use -SET &ECHO = 'ALL'; (WITH dash) for further details...

Hi,

Thanks a lot..!!!!

-SET &ECHO = 'ALL'; , This command help me to figure out the issue.

The issue is solved now,
The problem was with the date format.

And in the below code, I need to add the total of the count at the end, how to do this,

ENGINE DB2 SET DEFAULT_CONNECTION CNAME
-DEFAULT &FDATE1='2015/07/21';
-DEFAULT &TDATE1='2015/08/21';
-DEFAULT &ACC_NO='371056897009000'
SQL DB2 EX SYSPROC.TEST_WEBFOCUS_NEW(&FDATE1,&TDATE1)@
;
TABLE FILE SQLOUT
HEADING CENTER
"COUNT OF STOP PAYMENT BY ACCOUNT"
SUM CNT.CUST_ACCT_NO AS COUNT_STOPPED_PYMT
BY CUST_ACCT_NO
WHERE CUST_ACCT_NO EQ '&ACC_NO'
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/STP/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

As I am new to the WebFOCUS , Could you please explain why WebFOCUS, advantages of WebFOCUS over other reporting tool and how WebFOCUS communicate with the database.



Kind Regards,
Usre1

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

Hi User1

If it is solved, you can edit your first post and add [SOLVED] to the beginning of the subject.