Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2015Report 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, 2015Report 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, 2011Report 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, 2015Report 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, 2011Report 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, 2015Report 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, 2011Report 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, 2015Report 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 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders