Hi,

I am fairly new to WebFOCUS, my company just bought the product a few weeks ago and we are stil setting things up.

I have a very basic question that I could use help with.

I built a guided report and used Graph Asssit to make a simple pie chart against the synonym for a SQL stored procedure. Running the report, I am watching the activity in SQL Profiler and I see my SQL stored procedure is being called exaclty as I set up the Selectin Criteria, and if I paste the same SQL into SSMS, I get the exact 8 records I expect to be returned to the WF server. The RowCounts Event in SQL Profiler also records having returned a payload of 8 records to WF.

However, what I get when I run the report is

  
Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request. 


--------------------------------------------------------------------------------


 0 NUMBER OF RECORDS IN GRAPH=        0  PLOT POINTS=      0
 NO DATA TO GRAPH

my question is really just how does one go about troubleshooting and ascertaining the exact issue? I have enabled traced on the WF Server, but nothing jumps out at me in that very large trace data.

It looks to me like one trace in relevant portions is claiming not to have had any records returned:

>>> C:\ibi\srv77\wfs\edatemp\ts000124.trc <<<
...
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02954: Generated stored procedure call:
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02955: {?=call Part.dbo.Workcenter_Log_Reasons_Analysis_Time_Get(9752,,,,'20120201','201202
CA 29',,,NULL,,,,,,,,)}
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 02448: Connection N1_Dev_IBI assigned for operation
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 02758: m_pIDBCreateSession /0x00000000064D0F88/ ->CreateSession(...)
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 02763: Obtained: pIDBSession /0x0000000005D46388/, hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01653: pIDBCreateCommand /0x0000000005D46388/ ->CreateCommand(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01655: Obtained: m_pICommand /0x00000000064CB260/, hr= 0x00000000
10.52.43 CA SQL: 007B 003F 003D 0063 0061 006C 006C 0020 0050 0061 0072 0074 002E 0064 0062 006F {?=call Part.dbo
10.52.43 CA 002E 0057 006F 0072 006B 0063 0065 006E 0074 0065 0072 005F 004C 006F 0067 005F .Workcenter_Log_
10.52.43 CA 0052 0065 0061 0073 006F 006E 0073 005F 0041 006E 0061 006C 0079 0073 0069 0073 Reasons_Analysis
10.52.43 CA 005F 0054 0069 006D 0065 005F 0047 0065 0074 0028 0039 0037 0035 0032 002C 002C _Time_Get(9752,,
10.52.43 CA 002C 002C 0027 0032 0030 0031 0032 0030 0032 0030 0031 0027 002C 0027 0032 0030 ,,'20120201','20
10.52.43 CA 0031 0032 0030 0032 0032 0039 0027 002C 002C 002C 004E 0055 004C 004C 002C 002C 120229',,,NULL,,
10.52.43 CA 002C 002C 002C 002C 002C 002C 0029 007D ,,,,,,)}
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01665: m_pICommand /0x00000000064CB260/ ->SetCommandText(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01669: hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01675: m_pICommand /0x00000000064CB260/ ->QueryInterface(IID_ICommandPrepare, ...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01678: Obtained: m_pICommandPrepare /0x00000000064CB290/, hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 03535: m_pDBSession /0x0000000005D46388/ ->QueryInterface(IID_ITransactionLocal, ...)
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 03539: Obtained: m_pITransactionLocal /0x0000000005D46398/, hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 03545: Isolation Level is: ISOLATIONLEVEL_READUNCOMMITTED
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 03546: m_pITransactionLocal /0x0000000005D46398/ ->StartTransaction(...)
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 03550: hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01698: m_pICommandPrepare /0x00000000064CB290/ ->Prepare(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01701: hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02726: m_pICommand /0x00000000064CB260/ ->QueryInterface(IID_ICommandWithParameters, ...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02728: Obtained: pICommandWithParameters /0x00000000064CB270/, hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02734: pICommandWithParameters /0x00000000064CB270/ ->GetParameterInfo(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02739: hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 00203: Information received:
10.52.43 CA Number of parameters: 1
10.52.43 CA Param Ordinal: 1
10.52.43 CA pwszName: @RETURN_VALUE
10.52.43 CA wType: DBTYPE_I4
10.52.43 CA ulParamSize: 4 bPrecision: 10 bScale: 0
10.52.43 CA dwFlags: DBPARAMFLAGS_ISOUTPUT | DBPARAMFLAGS_ISSIGNED
10.52.43 CA
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02756: pICommandWithParameters/0x00000000064CB270/ ->Release()
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02798: m_pICommand /0x00000000064CB260/ ->QueryInterface(IID_ICommandWithParameters, ...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02800: Obtained: pICommandWithParameters /0x00000000064CB270/, hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 00232: Parameter Bind Info to be set:
10.52.43 CA Parameter ordinal 1:
10.52.43 CA *pwszDataSourceType: DBTYPE_I4
10.52.43 CA ulParamSize: 4 bPrecision: 10 bScale: 0
10.52.43 CA dwFlags: DBPARAMFLAGS_ISNULLABLE | DBPARAMFLAGS_ISOUTPUT | DBPARAMFLAGS_ISSIGNED
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02845: pICommandWithParameters /0x00000000064CB270/ ->SetParameterInfo(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02850: hr= 0x00040EC2
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02861: pICommandWithParameters/0x00000000064CB270/ ->Release()
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 00124: Output bindings:
10.52.43 CA Item #1 bound with:
10.52.43 CA Ordinal 1
10.52.43 CA Type DBTYPE_I4
10.52.43 CA ParamIO DBPARAMIO_OUTPUT
10.52.43 CA MaxLen 4
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 03855: m_pICommand /0x00000000064CB260/ ->QueryInterface(IID_IAccessor, ...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 03857: Obtained: m_pOutIAccessor /0x00000000064CB198/, hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 03865: m_pOutIAccessor /0x00000000064CB198/ ->CreateAccessor(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 03873: hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02880: Parameter data:
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02885: Parameter Set #1 :
10.52.43 CA Item #1:
10.52.43 CA wType: DBTYPE_I4
10.52.43 CA Status: DBSTATUS_S_OK
10.52.43 CA Length: 0
10.52.43 CA Value: 0
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 01726: Setting Current Command
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02895: m_pICommand /0x00000000064CB260/ ->Execute(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02903: Obtained: *ppIUnknown /0x0000000005D489C8/, hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 01785: Resetting Current Command
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 03016: m_pIMultipleResults /0x0000000005D489C8/ ->GetResult(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 03019: Obtained: m_pIRowset /0x0000000005D49760/, hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 03025: Rows affected = -1
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02194: pIUnknown /0x0000000005D49760/ ->QueryInterface(IID_IColumnsInfo, ...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02196: Obtained: pIColumnsInfo /0x0000000005D49770/, hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02202: pIColumnsInfo /0x0000000005D49770/ ->GetColumnInfo(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02207: hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 00149: Information received:
10.52.43 CA Number of columns: 6
10.52.43 CA Column Ordinal: 1
10.52.43 CA pwszName: Quantity
10.52.43 CA wType: DBTYPE_NUMERIC
10.52.43 CA ulColumnSize: 19 bPrecision: 38 bScale: 5
10.52.43 CA dwFlags: DBCOLUMNFLAGS_MAYBENULL | DBCOLUMNFLAGS_ISFIXEDLENGTH | 0x00000020 (32)
10.52.43 CA Column Ordinal: 2
10.52.43 CA pwszName: Description
10.52.43 CA wType: DBTYPE_STR
10.52.43 CA ulColumnSize: 102 bPrecision: 255 bScale: 255
10.52.43 CA dwFlags: 0
10.52.43 CA Column Ordinal: 3
10.52.43 CA pwszName: Color
10.52.43 CA wType: DBTYPE_STR
10.52.43 CA ulColumnSize: 1 bPrecision: 255 bScale: 255
10.52.43 CA dwFlags: 0
10.52.43 CA Column Ordinal: 4
10.52.43 CA pwszName: Detail_Link
10.52.43 CA wType: DBTYPE_STR
10.52.43 CA ulColumnSize: 278 bPrecision: 255 bScale: 255
10.52.43 CA dwFlags: DBCOLUMNFLAGS_MAYBENULL | 0x00000020 (32)
10.52.43 CA Column Ordinal: 5
10.52.43 CA pwszName: Workcenter_Event_Key
10.52.43 CA wType: DBTYPE_I4
10.52.43 CA ulColumnSize: 4 bPrecision: 10 bScale: 255
10.52.43 CA dwFlags: DBCOLUMNFLAGS_MAYBENULL | DBCOLUMNFLAGS_ISFIXEDLENGTH | 0x00000028 (40)
10.52.43 CA Column Ordinal: 6
10.52.43 CA pwszName: Job_No
10.52.43 CA wType: DBTYPE_STR
10.52.43 CA ulColumnSize: 20 bPrecision: 255 bScale: 255
10.52.43 CA dwFlags: DBCOLUMNFLAGS_MAYBENULL | 0x00000020 (32)
10.52.43 CA
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 02237: pIColumnsInfo/0x0000000005D49770/ ->Release()
10.52.43 BZ <<< OLEFOC SQLDA Block
10.52.43 BZ <<< OLEFOC SQLDA: Contains 0 elements, 6 select list expressions
10.52.43 BZ <<< OLEFOC exited. [SQLMSS), Errcode= 0; DESCRIBE.
10.52.43 BU STP READ: SEGMENT 1 FETCHED:
10.52.43 BU STPREAD : STATUS AT EXIT: pathfst= 1, pathlst= 1, pathsegs= 80 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0
BU 0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0
BU 0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0
BU 0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 STP READ : RETURN: lchar= 8728
10.52.43 BU STP READ:- - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - -
10.52.43 AY gnavig just retreived seg 1 rc 0 lchar 8728
10.52.43 AY converting filter fields
10.52.43 AY converted filter fields
10.52.43 AY calling segtst
10.52.43 AY called segtst result 0
10.52.43 AY in GGTSGX rectype 1 cvtdbkey 1
10.52.43 AT r1ziipdsbl: r1ziipdsbl rc= 0
10.52.43 BU STPREAD: ENTERED: rectype= 1, dbkey= 1, join= FOC, opt_path= N, outer= N
10.52.43 BU STPREAD : STATUS AT EXIT: pathfst= 1, pathlst= 1, pathsegs= 80 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0
BU 0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0
BU 0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0
BU 0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 STP READ : RETURN: lchar= 0
10.52.43 BU STP READ:- - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - -
10.52.43 AY gnavig just retreived seg 1 rc 0 lchar 0
10.52.43 AT r1ziipdsbl: r1ziipdsbl rc= 0
10.52.43 BZ >>> OLEFOC entered. (SQLMSS), Gfun= 1, fun= 26; DEALLOCATE_C. Cursor= 1
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01534: m_pIRowset/0x0000000005D49760/ ->Release()
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01536: Reference Count is zero: 0
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01448: m_pIMultipleResults/0x0000000005D489C8/ ->Release()
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01450: Reference Count is zero: 0
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01593: m_pOutIAccessor /0x00000000064CB198/ ->ReleaseAccessor(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01600: m_pOutIAccessor/0x00000000064CB198/ ->Release()
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01491: m_pICommandPrepare /0x00000000064CB290/ ->Unprepare(...)
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01495: m_pICommandPrepare/0x00000000064CB290/ ->Release()
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01469: m_pICommand/0x00000000064CB260/ ->Release()
10.52.43 CA \winx64\M727703D\source\olecurs.cpp line 01471: Reference Count is zero: 0
10.52.43 BZ <<< OLEFOC exited. [SQLMSS), Errcode= 0; DEALLOCATE_C.
10.52.43 BZ >>> OLEFOC entered. (SQLMSS), Gfun= 1, fun= 5; COMMIT_WORK.
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 03583: m_pITransactionLocal /0x0000000005D46398/ ->Commit(...)
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 03587: hr= 0x00000000
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 03600: m_pITransactionLocal/0x0000000005D46398/ ->Release()
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 03473: m_pDBSession/0x0000000005D46388/ ->Release()
10.52.43 CA \winx64\M727703D\source\olecom.cpp line 03475: Reference Count is zero: 0
10.52.43 BZ <<< OLEFOC exited. [SQLMSS), Errcode= 0; COMMIT_WORK.
10.52.43 BZ >>> OLEFOC entered. (SQLMSS), Gfun= 3, fun= 3; CMD_CLOSE.
10.52.43 BZ >>> OLEFOC Count= 1
10.52.43 BZ <<< OLEFOC exited. [SQLMSS), Errcode= 0; CMD_CLOSE.
10.52.43 BZ <<< OLEFOC Count= 0
10.52.43 AY GNTCLO_LI. LI close is done for MCB 4D56CD0.
10.52.43 AY GNTCLO_CLEAN. Clean_up is done for MCB 4D56CD0.
10.52.43 GD untilEOF: end of data
10.52.43 GD srtcntl NEW_MTX
10.52.43 GD srtcntl FSTERM
10.52.43 GD just1MTX: statcm->records = 0, MORE_force = 0, rowcm->bytot = 0,
10.52.43 GD just1MTX: envir->cartes = 0, smcm->listk = 0, smcm->ndpath = 1,
10.52.43 GD just1MTX: envir->svmtrx = 0; thus
10.52.43 GD just1MTX: single matrix not written to FOCSORT; RETYPE may fail
10.52.43 GD s29_natv_goso: calling cursorDelete
10.52.43 GD s29_natv_goso: returning FALL_THRU
10.52.43 GD s33_psvlpol: entered
10.52.43 GD s33_psvlpol: returning FALL_THRU
10.52.43 GD tabctlx: calling psvlpol
10.52.43 GD tabctlx: calling forset
10.52.43 AT r1pathsplit: curbufstr[7]="OFFLINE"
10.52.43 AT r1pathsplit: path[0]=""
10.52.43 AT r1pathsplit: base[0]=""
10.52.43 AT r1pathsplit: subdir[0]=""
10.52.43 AT r1pathsplit: fn[7]="OFFLINE"
10.52.43 AT r1pathsplit: ext[0]=""
10.52.43 AT r1envget: calling GetEnvironmentVariable( OFFLINE )
10.52.43 AT r1envget: is not set
10.52.43 AT iohqrydd: DDNAME OFFLINE not allocated
10.52.43 AT r1lodli: calling r1mtxlck
10.52.43 AT r1lodli: r1mtxlck returned OK
10.52.43 AT r1lodli: load request for SSPAR - incrementing module count to 12
10.52.43 AT r1lodli: calling r1mtxrel
10.52.43 GD tab_sprgm: entered
10.52.43 GD tab_sprgm: setting lines = 0
10.52.43 GD tab_sprgm: displaying records/lines
10.52.43 AT r1pathsplit: curbufstr[6]="MSGSVC"
10.52.43 AT r1pathsplit: path[0]=""
10.52.43 AT r1pathsplit: base[0]=""
10.52.43 AT r1pathsplit: subdir[0]=""
10.52.43 AT r1pathsplit: fn[6]="MSGSVC"
10.52.43 AT r1pathsplit: ext[0]=""
10.52.43 AT r1pathsplit: curbufstr[6]="MSGSVC"
10.52.43 AT r1pathsplit: path[0]=""
10.52.43 AT r1pathsplit: base[0]=""
10.52.43 AT r1pathsplit: subdir[0]=""
10.52.43 AT r1pathsplit: fn[6]="MSGSVC"
10.52.43 AT r1pathsplit: ext[0]=""
10.52.43 AT ioherrofnh: open name=msgsvc, ibitype=ERRORS , device=DISK
10.52.43 AT fcbcrt: entered with ioname=, iohft=2, subtype=16
10.52.43 AT fcbcrt: after ioh_fcbini binary=0, recfm=1
10.52.43 AT fcbcrt: after PDSFT correction binary=0, recfm=1
10.52.43 AT fcbopn: recfm=1, binary=0, access=1
10.52.43 AT fcbopn: file MSGSVC is to be accessed as V_ALPHA
10.52.43 AT fcbopn: access to device: DISK
10.52.43 AT fcbopn: calling ioh_fcbfndopn
10.52.43 AT r1pathnm: result is
10.52.43 AT fcbfndopn: calling f_fopen with mode=
10.52.43 AT r1fopen: calling fopen( C:\ibi\srv77\wfs\etc\msgsvc.err, rb )
10.52.43 AT r1fopen: fopen failed
10.52.43 AT r1ziipdsbl: r1ziipdsbl rc= 0
10.52.43 AT r1trcerr: r1fopen: err#2, The system cannot find the file specified.
10.52.43 AT fcbfndopn: rc=0
10.52.43 AT r1pathnm: result is
10.52.43 AT fcbfndopn: calling f_fopen with mode=
10.52.43 AT r1fopen: calling fopen( C:\ibi\srv77\home\nls\msgsvc.err, rb )
10.52.43 AT fcbfndopn: rc=1
10.52.43 AT r1fil_isNative: file is PORTABLE
10.52.43 AT fcbfndopn: natos=0
10.52.43 AT fcbopn: ioh_fcbfndopn finished - rc=0
10.52.43 AT ioherrofnh: file C:\ibi\srv77\home\nls\msgsvc.err successfully opened
10.52.43 AT r1ziipdsbl: r1ziipdsbl rc= 0
10.52.43 AT r1feof: calling feof( C:\ibi\srv77\home\nls\msgsvc.err )
10.52.43 AT r1ziipdsbl: r1ziipdsbl rc= 0
10.52.43 AT r1feof: EOF has not been reached
10.52.43 AT ioherrget: read for record #1 returning 80 characters
10.52.43 AT r1ziipdsbl: r1ziipdsbl rc= 0
10.52.43 AT r1feof: calling feof( C:\ibi\srv77\home\nls\msgsvc.err )
10.52.43 AT r1ziipdsbl: r1ziipdsbl rc= 0
10.52.43 AT r1feof: EOF has not been reached
...
10.52.43 AT r1feof: EOF has not been reached
10.52.43 AT ioherrget: read for record #2234 returning 62 characters
10.52.43 AT r1ziipdsbl: r1ziipdsbl rc= 0
10.52.43 AT r1feof: calling feof( C:\ibi\srv77\home\nls\msgsvc.err )
10.52.43 AT r1ziipdsbl: r1ziipdsbl rc= 0
10.52.43 AT r1feof: EOF has been reached
10.52.43 AT r1fclose: closing file
10.52.43 AT r1fclose: done, rc=0
10.52.43 AT io_doread: EOF encountered C:\ibi\srv77\home\nls\msgsvc.err
10.52.43 AT ioherrget: read for record #2235 returning 0 characters
10.52.43 AI nwsig: entered
10.52.43 AI nwsig: begin--> protocol ver=32 (3.2+); sig type=MS
10.52.43 AI nwsig: ngput buf before NLS l=65 MS 0 NUMBER OF RECORDS IN GRAPH= 0 PLOT POINTS= 0
10.52.43 AT ngptput: entered
10.52.43 AT NGodinPut: entered
10.52.43 AT NGtrEvnt: len=65, OPFSIGNAL - "MS 0 NUMBER OF RECORDS IN GRAPH= 0 PLOT POINTS= 0"
10.52.43 AT NGodinPut: compression is not set
10.52.43 AT NGodinPut: encryption is not set
10.52.43 AT ngptSendStream: entered with 69
10.52.43 AI nwsig: entered
10.52.43 AI nwsig: begin--> protocol ver=32 (3.2+); sig type=MS
10.52.43 AI nwsig: ngput buf before NLS l=8 MS
10.52.43 AT ngptput: entered
10.52.43 AT NGodinPut: entered
10.52.43 AT NGtrEvnt: len=8, OPFSIGNAL - "MS "
10.52.43 AT NGodinPut: compression is not set
10.52.43 AT NGodinPut: encryption is not set
10.52.43 AT ngptSendStream: entered with 12
10.52.43 GD tab_sprgm: returning TBP_OK
10.52.43 GD tabctlx: calling tboutp
10.52.43 AT r1lodli: calling r1mtxlck
10.52.43 AT r1lodli: r1mtxlck returned OK
10.52.43 AT r1lodli: load request for GRPH - incrementing module count to 12
10.52.43 AT r1lodli: calling r1mtxrel
10.52.43 AT r1lodli: calling r1mtxlck
10.52.43 AT r1lodli: r1mtxlck returned OK
10.52.43 AT r1lodli: load request for GRPHTM - incrementing module count to 12
10.52.43 AT r1lodli: calling r1mtxrel
10.52.43 AT r1envget: calling GetEnvironmentVariable( _IBIGCF )
10.52.43 AT r1envget: <_IBIGCF> is not set
10.52.43 AT r1pathsplit: curbufstr[7]="OFFLINE"
10.52.43 AT r1pathsplit: path[0]=""
10.52.43 AT r1pathsplit: base[0]=""
10.52.43 AT r1pathsplit: subdir[0]=""
10.52.43 AT r1pathsplit: fn[7]="OFFLINE"
10.52.43 AT r1pathsplit: ext[0]=""
10.52.43 AT r1envget: calling GetEnvironmentVariable( OFFLINE )
10.52.43 AT r1envget: is not set
10.52.43 AT iohqrydd: DDNAME OFFLINE not allocated
10.52.43 AI nwsig: entered
10.52.43 AI nwsig: begin--> protocol ver=32 (3.2+); sig type=MS
10.52.43 AI nwsig: ngput buf before NLS l=23 MS NO DATA TO GRAPH
10.52.43 AT ngptput: entered
10.52.43 AT NGodinPut: entered
10.52.43 AT NGtrEvnt: len=23, OPFSIGNAL - "MS NO DATA TO GRAPH"
...

Thanks for any guidance!

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

WebFOCUS 7.7.04M/8001
Windows Server 2008
Excel, PDF, HTML, AHTML

http://www.plex.com

Try to make your data visible as "TABLE FILE" and -SET &ECHO=ALL;

Hi Tom,

I think the best way to start debugging this is to find out what request is being sent from WebFOCUS to your database server.

In order to do that, you can use the Procedure Viewer in Developer Studio to have easy access to the underlying source code of that procedure.

First things first: it's easier to debut this stuff if you get rid of the GRAPH altogether.

Copy/paste your procedure to get a backup copy of it.

Open any of the copies using "Procedure Viewer" (accessible via right-click) and take a look at your code which should look somewhat similar to:

GRAPH FILE YOUR_DB_TABLE
SUM YOUR_FIELD1
    YOUR_FIELD2
    ...
BY YOUR_SORT_FIELD1
BY YOUR_SORT_FIELD2
...
WHERE YOUR_CONDITION1
WHERE YOUR_CONDITION2
...
HEADING
"Your Heading"
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
...
more ON GRAPH SET stuff here ....
...
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
...
and a whole lot of setXXXX API calls
...
ENDSTYLE
ON GRAPH SET STYLE *
TYPE=HEADING, LINE=1, ITEM=1, OBJECT=TEXT, SIZE=10, STYLE=BOLD, FONT='ARIAL', JUSTIFY=CENTER, $
ENDSTYLE
END

Anyway, as you won't need any of those graph settings for now, just remove every line you find from the first ON GRAPH SET statement down to the last ENDSTYLE statement.

In addition to this, substitute TABLE FILE for GRAPH FILE in the main request (we just want to get a "report" for now).


Your code may end up looking like this:

TABLE FILE YOUR_DB_TABLE
SUM YOUR_FIELD1
    YOUR_FIELD2
    ...
BY YOUR_SORT_FIELD1
BY YOUR_SORT_FIELD2
...
WHERE YOUR_CONDITION1
WHERE YOUR_CONDITION2
...
END

Now, it's time to enable procedure-level tracing to try and capture the SQL statement WebFOCUS is sending to your database server based on the request you have.

Just add the following lines to the very top of the code (before the TABLE FILE ...)

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 132
SET TRACEUSER = ON

SET EMPTYREPORT = OFF
SET XRETRIEVAL = OFF

There is more to tracing than what I showed above; those are just the lines I use in my own debugging in order to capture SQL and to verify that filters and aggregations are being sent to the database to be resolved there.


This may suffix for now. Run the procedure and in your browser you should be able to see the SQL statement that is being sent to the database, filters and all.

Run that one in your database directly and see what you get.

Look for any FOC errors/warning you maybe getting and if you don't mind post them here for some advice.

Additionally, pay special attention to NULL values in your database if you're filtering on them. You need to account for those in WebFOCUS by using the MISSING keyword.

Sorry, this maybe too much to take in but when debugging many things need to be considered. Hopefully you'll get the training you need to better use the tool and then things will make more sense over time.

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

MichaelBalle and njsden:

Thanks for your responses.

I am working in a sandbox with access to everything. but the idea of, in this case, editing the Procedure does not seem to apply.

I am used to some reports I have done where on a report I can right-click on a Graph and pick "Open procedure viewer" frmo the context menu below the Graph Assist options and above "Import Existing Graph". I have gone into various procedures this way to make Procedure-level tweaks, experiment with Graph functions, etc.

However, on this graph, I don't get that option in the context menu; there is only:

Open graph
Graph Assist
Advanced Graph Assist (greyed out)
Import existing graph ...
etc.

What does that tell me?

I do see on the HTML tab of the report designer embedded in there the code I would expect to see in the Procedure:



< ![CDATA[GRAPH FILE PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET
SUM PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.QUANTITY
ACROSS PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION ...

However, for some reason I don't like the idea of editing there directly.

I am going to see if I can find some behind the scenes procedure that is not obvious right now, or make a new report that is a table or ends up offering "Open procedure viewer", but in the interest of my education I'd like it if anyone could clear up for me why the option of viewing/editing this graph's procedure is not obvious...

WebFOCUS 7.7.04M/8001
Windows Server 2008
Excel, PDF, HTML, AHTML

http://www.plex.com

Oh, so you created the graph as an embedded procedure in an HTML page using HTML Composer?

It may be trickier to debug that way.

I would highlight everything within the CDATA portion and copy it to the clipboard.

Then, from the Developer Studio explorer, create a brand new Procedure (choosing Create with: Procedure Viewer) and paste your clipboard's content there and start testing from here.


The HTML page may be irrelevant for now unless you have parameters linked to this Graph which will make it a bit trickier to debug that way ...

If you DO have parameters, you will need to "fake" them in your new procedures via "-DEFAULT" but this testing might be flawed as it won't necessarily represent what you are actually passing from the HTML page in real life.

Please provide as many details as possible about your configuration and how the GRAPH procedure is being accessed so we can tailor our suggestions accordingly.

Is it ture that I "created the graph as an embedded procedure in an HTML page using HTML Composer"? I suppose so, I clicked New... in the folder, picked Insert Graph, used Grpah Assist which I pointed to my sysnonym then I wired up two parameters I needed as they are required by the SQL stored procedure. It is for a simple date range. However, there are two other required parameters that I hard-coded, one with an integer value that other needs to be NULL an I got that to work (from the SQL Profiler point of view) as "missing"

I had already gone down the road of just editing down the contents as you suggsed in a new procedure and the procedure contents now are:

  
-SET &ECHO='ALL';
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 132
SET TRACEUSER = ON
SET EMPTYREPORT = OFF
SET XRETRIEVAL = OFF
TABLE FILE PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET
SUM PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.QUANTITY 
ACROSS PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION 
BY PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION 
WHERE ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@PCN EQ 9752 ) 
  AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_FROM EQ '&@DATE_FROM.Enter the starting date.' ) 
  AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_TO EQ '&@DATE_TO.Enter the end of the date range.' ) 
  AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DOWNTIME_STATUS EQ MISSING );
END

I found the checkbox to prompt for paramters so when I run it, that works out well.

However something seems to be missing or amiss in my newly created .fex, as I run it, enter my parameters and unlike the graph I was working with, SQL Profiler does not detect any execution of my stored procedure. This is my output:

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.


--------------------------------------------------------------------------------


SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 132
SET TRACEUSER = ON
SET EMPTYREPORT = OFF
SET XRETRIEVAL = OFF
TABLE FILE PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET
SUM PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.QUANTITY
ACROSS PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION
BY PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION
WHERE ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@PCN EQ 9752 )
AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_FROM EQ '2012/02/01' )
AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_TO EQ '2012/02/29' )
AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DOWNTIME_STATUS EQ MISSING );
END
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

OK, I found out by trial and error that this line stops the request from reaching the DBMS:

SET XRETRIEVAL = OFF

With or without that, my output is the same

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.


--------------------------------------------------------------------------------


SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 132
SET TRACEUSER = ON
SET EMPTYREPORT = OFF
-* SET XRETRIEVAL = OFF
TABLE FILE PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET
SUM PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.QUANTITY
ACROSS PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION
WHERE ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@PCN EQ 9752 ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_FROM EQ '2012/02/01' ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_TO EQ '2012/02/29' ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DOWNTIME_STATUS EQ MISSING );
END
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0


When I don't have that SQL Profiler verifies the parameters set is the expect one, running it directily in SQL outside of WF I get my 8 rows and Profiler verifies 8 rows going back to WF.

Is the idea of this tracing there will be some .trc files or other trace files?

I don't see any where I expected:

..\srv77\wfs\edatemp

Maybe there is something revealing abot the output that I am not able to detect?

Thanks again for the help

It would seem the problem is in my synonym or something deeper. I don't doubt the adapter, I have other synonyms running through it OK.

But, when I use the Sample Data feature in Synonym Editor and put the same parameters in I am using int he report, I catch them in Profiler, get evidence of the 8 rows returned, but nothing returns in my Sample Data.

My Synonym's .mas file:

FILENAME=part_dbo_workcenter_log_reasons_analysis_time_get, SUFFIX=SQLMSS , $
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@PCN, ALIAS=P0001, USAGE=I11, ACTUAL=I4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@WORKCENTER_KEY, ALIAS=P0002, USAGE=A2000V, ACTUAL=A2000V,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@WORKCENTER_STATUS_KEY, ALIAS=P0003, USAGE=A2000V, ACTUAL=A2000V,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@WORKCENTER_TYPE, ALIAS=P0004, USAGE=A50V, ACTUAL=A50V,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@DATE_FROM, ALIAS=P0005, USAGE=YYMD, ACTUAL=YYMD,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@DATE_TO, ALIAS=P0006, USAGE=YYMD, ACTUAL=YYMD,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@DETAIL_LINK, ALIAS=P0007, USAGE=I6, ACTUAL=I4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@PART_KEY, ALIAS=P0008, USAGE=I11, ACTUAL=I4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@DOWNTIME_STATUS, ALIAS=P0009, USAGE=I6, ACTUAL=I4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@WORKCENTER_GROUP, ALIAS=P0010, USAGE=A50V, ACTUAL=A50V,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@SHIFT_KEY, ALIAS=P0011, USAGE=A50V, ACTUAL=A50V,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@OPERATION_KEY, ALIAS=P0012, USAGE=I11, ACTUAL=I4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@BUILDING_KEYS, ALIAS=P0013, USAGE=A500V, ACTUAL=A500V,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@DEPARTMENT_NO, ALIAS=P0014, USAGE=A2000V, ACTUAL=A2000V,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@JOB_NO, ALIAS=P0015, USAGE=A20V, ACTUAL=A20V,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@EVENT_TYPE_KEY, ALIAS=P0016, USAGE=I11, ACTUAL=I4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@WORKCENTER_EVENT_GROUP, ALIAS=P0017, USAGE=A2000V, ACTUAL=A2000V,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $
SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=QUANTITY, ALIAS=Quantity, USAGE=P33.5, ACTUAL=P16,
MISSING=ON, $
FIELDNAME=DESCRIPTION, ALIAS=Description, USAGE=A102V, ACTUAL=A102V, $
FIELDNAME=COLOR, ALIAS=Color, USAGE=A1V, ACTUAL=A1V, $
FIELDNAME=DETAIL_LINK, ALIAS=Detail_Link, USAGE=A278V, ACTUAL=A278V,
MISSING=ON, $
FIELDNAME=WORKCENTER_EVENT_KEY, ALIAS=Workcenter_Event_Key, USAGE=I11, ACTUAL=I4,
MISSING=ON, $
FIELDNAME=JOB_NO, ALIAS=Job_No, USAGE=A20V, ACTUAL=A20V,
MISSING=ON, $

Tom, the SET XRETRIEVAL=OFF was there on purpose. All I wanted was for you to run the procedure and, from the resulting Internet Explorer browser window you would see the actual SQL statement that WebFOCUS created.

You could then copy that statement, put it in your favourite SQL client, run it against your database and see what you'd get. Let go of the fancy profiler and detailed traces for now. All you need is a query you can run yourself without anything else in the middle.

If you want, run the procedure in WebFOCUS and post the results you obtain in Internet Explorer, but first make sure you set XRETRIEVAL=OFF again. We don't want to see your data, just the SQL statement

One more thing Tom.

When posting code or the contents of your master files, please make sure to include them in code tags (see the ) button in the post message toolbar

That makes sense, but no statement to copy and put in my SQL client returns. This is what is output:

--------------------------------------------------------------------------------

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.


--------------------------------------------------------------------------------


SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 132
SET TRACEUSER = ON
SET EMPTYREPORT = OFF
SET XRETRIEVAL = OFF
TABLE FILE PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET
SUM PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.QUANTITY
ACROSS PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION
WHERE ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@PCN EQ 9752 ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_FROM EQ '2012/02/02' ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_TO EQ '2012/02/29' ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DOWNTIME_STATUS EQ MISSING );
END
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

Here is my .fex file contents:

-SET &ECHO=ALL;

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF

SET TRACEWRAP = 132
SET TRACEUSER = ON

SET EMPTYREPORT = OFF
SET XRETRIEVAL = OFF

TABLE FILE PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET
SUM PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.QUANTITY
ACROSS PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION
WHERE ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@PCN EQ 9752 ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_FROM EQ '&@DATE_FROM.Enter the starting date.' ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_TO EQ '&@DATE_TO.Enter the end of the date range.' ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DOWNTIME_STATUS EQ MISSING );
END

Tom, are you querying an actual table or a Stored Procedure?

Your segment names in the TABLE FILE statement look strange to me (.ANSWERSET1., .INPUT.)


If you're using querying a Stored Procedure I'm afraid there's not much I can help with as I have no experience in that arena.

Assuming that is a stored procedure, you could perhaps try and manually run it from SQL Server Studio using the same set of parameters ("translated" to SQL server native format or course) and see what you can get. That's as far as I can go on this ...

Thanks,

yeah it is for a SQL stored procedure. Running the report, I am watching the activity in SQL Profiler that gives me proof that SQL is receiving the parameters from WF execatly like I expect, I can run that manually run it from SQL Server Studio using the same set of parameters and see that I get the 8 rows that I expect.

I will create a case with IBI. I have another that they acknowledged:

"We do see the problem. I'm am looking to see if this is a known issue or if this is something that our product group needs to look at."

which was a meta-adata (synonym) issue and I am wondering they are related...

Whether it's a "SQL stored procedure" or "SQL Pass Thru", you should have come results in SQLOUT. So, after your SQL code (stored proc or SQL Pass Thru) you should be able to do a TABLE FILE SQLOUT ... END. However, if your SQL isn't returning anything, then there can't be anything to GRAPH. So, I think the real issue is in getting your SQL to return something.

Consider this:

ENGINE SQLORA SET DEFAULT_CONNECTION [name of connection]
SQL SQLORA PREPARE SQLOUT FOR
SELECT [fields],
. . .
ORDER BY  [fields] ;
END
TABLE FILE SQLOUT
PRINT *
END
-RUN
-TYPE *** &RECORDS records, &LINES lines returned from SQLOUT.
-* You can check these values (&RECORDS and &LINES) to branch to NO DATA display messages, etc...
-EXIT

And see what that produces. Pass on your results...
-Doug

Thank you, Doug

There seems to be some doubt about the SQL returning data and if the SQL Stored procedure is getting complete and accurate parameters.

let me be clear on that point: This is for a SQL Stored Procedure, the SQL Stored Procedure is receiving all paramters accurately and is returning 8 rows of data to the WebFOCUS server. Therein is the mystery that prompted me to make the post: I am watching the SQL server receive the complete and accurate stored procedure call from the WebFOCUS server and send back the expected eight rows. So, what would cause WebFOCUS to react as if no data was returned?

Doug's example is for a SELECT statement, so I attempted to modify it for my stored procedure call and am using this:

-SET &ECHO=ALL;

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF

SET TRACEWRAP = 132
SET TRACEUSER = ON

SET EMPTYREPORT = OFF
SET XRETRIEVAL = OFF

TABLE FILE PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET
SUM PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.QUANTITY 
ACROSS PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION 
WHERE ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@PCN EQ 9752 ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_FROM EQ '&@DATE_FROM.Enter the starting date.' ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_TO EQ '&@DATE_TO.Enter the end of the date range.' ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DOWNTIME_STATUS EQ MISSING );

PRINT *
END
-RUN
-TYPE *** &RECORDS records, &LINES lines returned from SQLOUT.
-* You can check these values (&RECORDS and &LINES) to branch to NO DATA display messages, etc...
-EXIT
  

From that my results are:

  
--------------------------------------------------------------------------------

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request. 


--------------------------------------------------------------------------------


 SET TRACEOFF = ALL
 SET TRACEON = SQLAGGR//CLIENT
 SET TRACEON = STMTRACE//CLIENT
 SET TRACEON = STMTRACE/2/CLIENT
 SET TRACESTAMP = OFF
 SET TRACEWRAP = 132
 SET TRACEUSER = ON
 SET EMPTYREPORT = OFF
 SET XRETRIEVAL = OFF
 TABLE FILE PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET
 SUM PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.QUANTITY
 ACROSS PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION
 WHERE ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@PCN EQ 9752 ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_FROM EQ '2012/02/01' ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_TO EQ '2012/02/29' ) AND ( PART_DBO_WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DOWNTIME_STATUS EQ MISSING );
 PRINT *
 END
 -RUN
 (FOC757) WARNING. YOU REQUESTED PRINT * OR COUNT * FOR A MULTIPATH FILE
 0 ERROR AT OR NEAR LINE     21  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC030) ALL VERB OBJECTS MUST BE IN THE SAME PATH AS THEIR SORT FIELDS
 -TYPE ***        0 records,        0 lines returned from SQLOUT.
 ***        0 records,        0 lines returned from SQLOUT.
 -* You can check these values (&RECORDS and &LINES) to branch to NO DATA display messages, etc...
 -EXIT

Thank you everyone for your help amd patience as I climb up the learning curve!

Of course, I should say that I only see SQL Server get the request and return 8 rows without

SET XRETRIEVAL = OFF

which I left in as directed by njsden with his explanation, and removing it does not change that output, it only alliows me to see SQL Server get the request and return the rows.

Just an update. I have been working with IBI on this through a case and we haven't gotten to the bottom of why my SQL is returning records to the WebFOCUS Report Server, but WebFOCUS is behaving as no records are returned.

However, apparently all this effort to use TRACE commands, etc. to see the SQL was for naught as stored procedure (SP) calls cannot be revealed that way. This is how my IBI contact explained it:

"The method for invoking an SP differs from that when accessing a TABLE.

SQL statements are seen in the traces for tables and views, for SPs, we make different calls."

Another update

I did find if I edited the synonym so that FIELDNAME=@DATE_FROM, ALIAS=P0005, USAGE=YYMD, ACTUAL=YYMD, MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $ FIELDNAME=@DATE_TO, ALIAS=P0006, USAGE=YYMD, ACTUAL=YYMD, MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
Changed to

FIELDNAME=@DATE_FROM, ALIAS=P0005, USAGE=YYMD, ACTUAL= DATE, MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $ FIELDNAME=@DATE_TO, ALIAS=P0006, USAGE=YYMD, ACTUAL= DATE, MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $ That is, ACTUAL=YYMD --> ACTUAL=DATE

then data does display in WF. Note I am saying "disaply in WF". In all cases, SQL is returning the data to WF. Note this is a change to the procedure input parameter and does not affect output.

IBI has asked for before/after sysdiag dumps to try and ascertain why WF is behaving this way.

This is one of a few odd things that give me trepidation considering WF as a robust channel into the tens of thousands of stored procedures that we have which are themselves a very rich API into our ERP data..

quote:

(FOC757) WARNING. YOU REQUESTED PRINT * OR COUNT * FOR A MULTIPATH FILE 0 ERROR AT OR NEAR LINE 21 IN PROCEDURE ADHOCRQ FOCEXEC *

It's a long shot, as apparently (as you wrote later on) the master file WF created was incorrect, but do you get output if you specify your output fields explicitly instead of using '*'?

For some reason WF seems to have created a multipath master file and with those you cannot use PRINT *. I'm still getting to grips with multipath files myself, so I cannot comment on why you can't.

Thank you much for the suggestion. We actually narrowed things down quite a bit, working with remote IBI staff and a local resource that came on site. In the end, the code was trimmed even of the PRINT * command:

  
-RUN 
TABLE FILE WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET
SUM WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.QUANTITY 
ACROSS WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.ANSWERSET1.DESCRIPTION 
WHERE ( WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@PCN EQ 9752 ) 
AND ( WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_FROM EQ '20100101' ) 
AND ( WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DATE_TO EQ '20101001' ) 
AND ( WORKCENTER_LOG_REASONS_ANALYSIS_TIME_GET.INPUT.@DOWNTIME_STATUS EQ MISSING );
 
END
- RUN
- TYPE  
- EXIT

IBI is now reviewing sysdaigs from the working synonym versus the non-working one, although both work as far as the SQL server is concerned and returns identical results to WF. Apparently is something deep in the FW internals related to specifically WF accessing SQL stored procedures.