Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Drill down issue
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Drill down issue
 Login/Join
 
Silver Member
posted
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>,
 
Posts: 37 | Registered: September 05, 2015Reply With QuoteReport This Post
Silver Member
posted Hide Post
How do I pass the parameter from one report to another.


WebFOCUS 8
Windows, All Outputs
 
Posts: 37 | Registered: September 05, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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 \


WebFOCUS 8105m
Windows 7, All Outputs

Member of the Benelux Usergroup
 
Posts: 198 | Location: Amsterdam | Registered: August 24, 2011Reply With QuoteReport This Post
Silver Member
posted Hide Post
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,


WebFOCUS 8
Windows, All Outputs
 
Posts: 37 | Registered: September 05, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi, yes it is common practice to put the SET commands on top most of the time.


WebFOCUS 8105m
Windows 7, All Outputs

Member of the Benelux Usergroup
 
Posts: 198 | Location: Amsterdam | Registered: August 24, 2011Reply With QuoteReport This Post
Silver Member
posted Hide Post
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


WebFOCUS 8
Windows, All Outputs
 
Posts: 37 | Registered: September 05, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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...


WebFOCUS 8105m
Windows 7, All Outputs

Member of the Benelux Usergroup
 
Posts: 198 | Location: Amsterdam | Registered: August 24, 2011Reply With QuoteReport This Post
Silver Member
posted Hide Post
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,


WebFOCUS 8
Windows, All Outputs
 
Posts: 37 | Registered: September 05, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi User1

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


WebFocus 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 154 | Registered: March 29, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Drill down issue

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.