Focal Point
[CLOSED] IA Filter processing and BY fields

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8517079096

December 11, 2018, 10:58 AM
DWaybright
[CLOSED] IA Filter processing and BY fields
I have a curious case. In InfoAssist a user created a report summing a few amount fields with several BY fields and a totals line. One of the filters excludes records with a 0 value in one of the amount fields. When he removes some of the BY fields to create a summary version of the report, the totals change. It seems to center around the filter that excludes the 0 value records. When that filter is removed, both versions of the report have totals that match.
This makes me wonder how the filter is being applied that it could affect the totals. My thinking is that all those records would be excluded first, then the grouping of the BY fields is applied, but it seems like maybe the grouping is being applied and that amount field is being summed and then looked at for 0 value? Can anyone confirm how this is processed?

Thanks!

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


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
December 11, 2018, 12:57 PM
BabakNYC
Can you show us the syntax you've used for the filter as well as the format of the fields involved in creating the filter?


WebFOCUS 8206, Unix, Windows
December 11, 2018, 01:06 PM
DWaybright
The Amount field is a P13.2. The filter is simply WHERE Amount NE 0


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
December 11, 2018, 01:21 PM
BabakNYC
Is it using an RDBMS? Do you have joins? Are you using a cluster master file? If this is a relational table, turn on the SQL Trace and see what the difference is between the summary and detail when you use the where test.


WebFOCUS 8206, Unix, Windows
December 11, 2018, 01:37 PM
MartinY
We're in the dark as result that Babak is asking for many questions. Otherwise we're doing assumptions or not asking for the right thing.

Please, as already requested, share your code. It may help a lot to help you.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
December 11, 2018, 03:10 PM
DWaybright
It is joining two relational SQL tables. They are joined on four key fields, all defined the same on both tables.
As it is in InfoAssist, I hesitate to share the code -- the user who created the report only works in IA, not AppStudio and doesn't code, so if you give a coding solution, he can't (won't be permitted) to use it.
How does one turn on the SQL Trace?

Thanks.


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
December 11, 2018, 03:16 PM
BabakNYC
For diagnostics, have the user share the code so you can test it. Then edit in text editor and add

  
-SET &ECHO=ALL;
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF

-*** Show SQL statements
SET TRACEON    = STMTRACE//CLIENT

-*** Show SQL generated sub-statement trace
SET TRACEON    = STMTRACE/2/CLIENT

-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF

-*** Set trace line wrapping - # of characters
SET TRACEWRAP  = 132

-*** Activate SQL tracing
SET TRACEUSER  = ON


to the beginning of the code generated by IA. Remember, when you reference a joined structure, and only use columns in one of the tables, the join won't be in effect. Make sure to reference at least one column from each table so the join is in effect. Alternatively, create a RDBMS view and use that instead. That way, the join is always enforced.


WebFOCUS 8206, Unix, Windows
December 12, 2018, 08:49 AM
DWaybright
Thank you for that trace code. It was very instructive. The SQL generated a HAVING clause to look at the sum of that amount field rather than having it as a simple WHERE clause. That explains the difference in the totals between the two reports.

I love this forum!


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs