Tabular report - How to Supress the records?

[Sankarguru Mani]

Hello Guys,

I have the following requirement

User wants modify the report (R03B413B) as follows.

Initial Reuirement:

User do not want include the Doc Type "RU" while processing.

They requested for a new processing for this. If the processing option is 1, do not include the doc type "RU".

What i did is, modified the processing option.

and added a following code in the Initialize section to not to include the doc type RU.

Set Selection Append Flag( <Yes>)
Set User Selection(BC Amount Open (F03B11), <Not Equal To>, <Zero>, <And>)

\\ My code Begins

If PO cExcludeRU is equal to "1"
Set User Selection(BC Document Type (F03B11), <Not Equal To>, "RU", <None>)
End If

\\ My code Ends

Modified Requirement:

After delivering the above solution they came up with the new requirement as follows.

User will enter a GL date range and the report will exclude the RU's of that period only . add 2 more processing option for date range of RU and take the date from the new processing option and filter by RU doc type to exclude these RUs.

My Research:

R03B413B is a tabular report. In the customised version, level break (data sequence) is set based on Company (CO) and Address Number (AN8).

I want to display the records based on the new requirement. I have added two more processing option for date range (From date and To Date) and added the following code in the Initialize section.


Set Selection Append Flag( <Yes>)
Set User Selection(BC Amount Open (F03B11), <Not Equal To>, <Zero>, <And>)
\\ My code Begins
If PO cExcludeRU is equal to "1"
Set User Selection(BC Document Type (F03B11), <Not Equal To>, "RU", <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian (F03B11), <Greater Than or Equal To>, PO jdFromDateForGLandVoucher, <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian (F03B11), <Less Than or Equal To>, PO jdToDateForGLandVoucher, <None>)
End If
\\ My code Ends

But the above condition ignores all the records with DOC Type "RU" and the other document types which does not meet the data range.my Code failed to get the expected records.

And I tried a new option. To suppress the records in the "Do Section" which does not meet the criteria and I have added the following code in the Do section of the report.

If PO cExcludeRU is equal to "1"
If BC Document Type (F03B11) is equal to "RU"
If BC Date - For G/L (and Voucher) - Julian (F03B11) is greater than or equal to PO jdFromDateForGLandVoucher
And BC Date - For G/L (and Voucher) - Julian (F03B11) is less than or equal to PO jdToDateForGLandVoucher
Suppress Section Write
End If
End If
End If

This code is also failed. Because in the Tabular report, Do section will be called only when the level break happens. In my case only when Company (CO) and Address Number (AN8) changes. my second try is also failed.

I have never worked on Tabular report. I doubt, whether my requirement is really achievable in the tabular report ? Is there anyway to fix this?

Any ideas on this would be really helpful.

I am extremely sorry, if the post is too big..

Thanks in advance.

-Sankarguru Mani
Enterprise One XE, B733- SP23_I1

 

[peterbruce]

Sankarguru,

Have you got a debug log that shows the SQL that is used? If you have, please post it. If you have not, please get a debug log showing the SQL and post the SQL.

You may find some information on data selection in this thread.

The actual data selected also depends on the section data selection, as well as the user selection in the init section.

 

[Sankarguru Mani]

Peter,

Thanks for your reply.

We are running this UBE without any dataselection (it means, it will include all data's) and also there is no overrides in the version data selection as well.

In the ER, following code is there.

Set Selection Append Flag( <Yes>)
Set User Selection(BC Amount Open (F03B11), <Not Equal To>, <Zero>, <And>)

when i run this UBE, query generated as follows

SELECT T0.ABAN8, T0.ABAC01, T0.ABAC02, T0.ABAC03, T0.ABAC04, T0.ABAC05, T0.ABAC06, T0.ABAC07, T0.ABAC08, T0.ABAC09, T0.ABAC10, T0.ABAC11, T0.ABAC12, T0.ABAC13, T0.ABAC14, T0.ABAC15, T0.ABAC16, T0.ABAC17, T0.ABAC18, T0.ABAC19, T0.ABAC20, T0.ABAC21, T0.ABAC22, T0.ABAC23, T0.ABAC24, T0.ABAC25, T0.ABAC26, T0.ABAC27, T0.ABAC28, T0.ABAC29, T0.ABAC30, T1.RPDOC, T1.RPDCT, T1.RPKCO, T1.RPSFX, T1.RPAN8, T1.RPDGJ, T1.RPDIVJ, T1.RPICUT, T1.RPICU, T1.RPDICJ, T1.RPFY, T1.RPCTRY, T1.RPPN, T1.RPCO, T1.RPGLC, T1.RPAID, T1.RPPA8, T1.RPPOST, T1.RPISTR, T1.RPPST, T1.RPAG, T1.RPAAP, T1.RPADSC, T1.RPADSA, T1.RPATXA, T1.RPATXN, T1.RPSTAM, T1.RPBCRC, T1.RPCRRM, T1.RPCRCD, T1.RPCRR, T1.RPDMCD, T1.RPACR, T1.RPFAP, T1.RPCDS, T1.RPCDSA, T1.RPCTXA, T1.RPCTXN, T1.RPCTAM, T1.RPTXA1, T1.RPEXR1, T1.RPGLBA, T1.RPMCU, T1.RPPTC, T1.RPDDJ, T1.RPDDNJ, T1.RPSMTJ, T1.RPVINV, T1.RPUNIT, T1.RPMCU2, T1.RPRMK, T1.RPALPH, T1.RPAR01, T1.RPAR02, T1.RPAR03, T1.RPAR04, T1.RPAR05, T1.RPAR06, T1.RPAR07, T1.RPAR08, T1.RPAR09, T1.RPAR10, T1.RPJCL FROM CRPLSCDTA.F0101 T0,CRPLSCDTA.F03B11 T1 WHERE ( T1.RPAAP <> 0.000000 ) AND ( T0.ABAT1 NOT BETWEEN 'D ' AND 'D ' ) AND ( T0.ABAN8=T1.RPAN8 ) ORDER BY T1.RPCO ASC,T1.RPAN8 ASC,T1.RPALPH ASC .

 

[Sankarguru Mani]

Peter,

If I want suppress the records in a tabular report, where should I do it?

Do Section of the Report or Do Variable or Column Inclusion of Variable

 

[peterbruce]

Sankarguru,

Have a look at the "where" clause of the SQL:

WHERE ( T1.RPAAP <> 0.000000 )
AND ( T0.ABAT1 NOT BETWEEN 'D ' AND 'D ' )
AND ( T0.ABAN8=T1.RPAN8 )

There appears to be more to the data selection than:

Set Selection Append Flag( <Yes>)
Set User Selection(BC Amount Open (F03B11), <Not Equal To>, <Zero>, <And>)

The Search Type (ABAT1) is included in an unusual way (NOT BETWEEN 'D ' AND 'D '). I would have thought it would have been: <> 'D'.

Put your set user selection ER back in Section Init ER, set the processing options, run the UBE and get the SQL from the debug log and post it along with the Init Section ER.

 

[Sankarguru Mani]

Hi Peter,

the following query is getting generated with my code,

SELECT T0.ABAN8, T0.ABAC01, T0.ABAC02, T0.ABAC03, T0.ABAC04, T0.ABAC05, T0.ABAC06, T0.ABAC07, T0.ABAC08, T0.ABAC09, T0.ABAC10, T0.ABAC11, T0.ABAC12, T0.ABAC13, T0.ABAC14, T0.ABAC15, T0.ABAC16, T0.ABAC17, T0.ABAC18, T0.ABAC19, T0.ABAC20, T0.ABAC21, T0.ABAC22, T0.ABAC23, T0.ABAC24, T0.ABAC25, T0.ABAC26, T0.ABAC27, T0.ABAC28, T0.ABAC29, T0.ABAC30, T1.RPDOC, T1.RPDCT, T1.RPKCO, T1.RPSFX, T1.RPAN8, T1.RPDGJ, T1.RPDIVJ, T1.RPICUT, T1.RPICU, T1.RPDICJ, T1.RPFY, T1.RPCTRY, T1.RPPN, T1.RPCO, T1.RPGLC, T1.RPAID, T1.RPPA8, T1.RPPOST, T1.RPISTR, T1.RPPST, T1.RPAG, T1.RPAAP, T1.RPADSC, T1.RPADSA, T1.RPATXA, T1.RPATXN, T1.RPSTAM, T1.RPBCRC, T1.RPCRRM, T1.RPCRCD, T1.RPCRR, T1.RPDMCD, T1.RPACR, T1.RPFAP, T1.RPCDS, T1.RPCDSA, T1.RPCTXA, T1.RPCTXN, T1.RPCTAM, T1.RPTXA1, T1.RPEXR1, T1.RPGLBA, T1.RPMCU, T1.RPPTC, T1.RPDDJ, T1.RPDDNJ, T1.RPSMTJ, T1.RPVINV, T1.RPUNIT, T1.RPMCU2, T1.RPRMK, T1.RPALPH, T1.RPAR01, T1.RPAR02, T1.RPAR03, T1.RPAR04, T1.RPAR05, T1.RPAR06, T1.RPAR07, T1.RPAR08, T1.RPAR09, T1.RPAR10, T1.RPJCL FROM CRPLSCDTA.F0101 T0,CRPLSCDTA.F03B11 T1 WHERE ( ( T1.RPAAP <> 0.000000 AND T1.RPDGJ >= 111091 AND T1.RPDGJ <= 111114 ) ) AND ( T0.ABAT1 NOT BETWEEN 'D ' AND 'D ' ) AND ( T0.ABAN8=T1.RPAN8 ) ORDER BY T1.RPCO ASC,T1.RPAN8 ASC,T1.RPALPH ASC

I have attached the ER code for ur reference.

Thanks,
Sankarguru Mani

 

[peterbruce]

Sankarguru,

You are missing the use selection that excludes Document Type "RU".

If you add the following line after the date selection (and before the END IF) then you should get The SQL below.

Set User Selection(BC Document Type (F03B11), <Not Equal To>, "RU", <And>)

SQL:
SELECT T0.ABAN8, T0.ABAC01, T0.ABAC02, T0.ABAC03, T0.ABAC04, T0.ABAC05, T0.ABAC06, T0.ABAC07, T0.ABAC08, T0.ABAC09, T0.ABAC10, T0.ABAC11, T0.ABAC12, T0.ABAC13, T0.ABAC14, T0.ABAC15, T0.ABAC16, T0.ABAC17, T0.ABAC18, T0.ABAC19, T0.ABAC20, T0.ABAC21, T0.ABAC22, T0.ABAC23, T0.ABAC24, T0.ABAC25, T0.ABAC26, T0.ABAC27, T0.ABAC28, T0.ABAC29, T0.ABAC30, T1.RPDOC, T1.RPDCT, T1.RPKCO, T1.RPSFX, T1.RPAN8, T1.RPDGJ, T1.RPDIVJ, T1.RPICUT, T1.RPICU, T1.RPDICJ, T1.RPFY, T1.RPCTRY, T1.RPPN, T1.RPCO, T1.RPGLC, T1.RPAID, T1.RPPA8, T1.RPPOST, T1.RPISTR, T1.RPPST, T1.RPAG, T1.RPAAP, T1.RPADSC, T1.RPADSA, T1.RPATXA, T1.RPATXN, T1.RPSTAM, T1.RPBCRC, T1.RPCRRM, T1.RPCRCD, T1.RPCRR, T1.RPDMCD, T1.RPACR, T1.RPFAP, T1.RPCDS, T1.RPCDSA, T1.RPCTXA, T1.RPCTXN, T1.RPCTAM, T1.RPTXA1, T1.RPEXR1, T1.RPGLBA, T1.RPMCU, T1.RPPTC, T1.RPDDJ, T1.RPDDNJ, T1.RPSMTJ, T1.RPVINV, T1.RPUNIT, T1.RPMCU2, T1.RPRMK, T1.RPALPH, T1.RPAR01, T1.RPAR02, T1.RPAR03, T1.RPAR04, T1.RPAR05, T1.RPAR06, T1.RPAR07, T1.RPAR08, T1.RPAR09, T1.RPAR10, T1.RPJCL FROM CRPLSCDTA.F0101 T0,CRPLSCDTA.F03B11 T1 WHERE ( ( T1.RPAAP <> 0.000000 AND T1.RPDGJ >= 111091 AND T1.RPDGJ <= 111114 AND T1.RPDCT <> 'RU') ) AND ( T0.ABAT1 NOT BETWEEN 'D ' AND 'D ' ) AND ( T0.ABAN8=T1.RPAN8 ) ORDER BY T1.RPCO ASC,T1.RPAN8 ASC,T1.RPALPH ASC

However this will only return data for between the two dates. If you want to return all data, but not Document Type RU between the dates (ie return document type RU outside the date range), that is possible, but a bit more complex.

 

[Sankarguru Mani]

Thanks for ur help Peter.

I finally added the following code in Column Inclusion event of a variable.

If PO cExcludeRU is equal to "1"
If BC Document Type (F03B11) is equal to "RU"
If BC Date - For G/L (and Voucher) - Julian (F03B11) is greater than or equal to PO jdFromDateForGLandVoucher
And BC Date - For G/L (and Voucher) - Julian (F03B11) is less than or equal to PO jdToDateForGLandVoucher
Suppress Section Write
End If
End If
End If
Because this event is getting called for each and every record and that solved my purpose.

Thank you once again.

Regards,
Sankarguru Mani

 

[JNI]

Why don't we go for implementing this with ER data selection itself... I have given some solution below, Please Correct me if am wrong.

Existing code for ER Data Selection:

Set append flag -yes
SUS Amt Open != 0 none
SUS Dcto !=RU and
SUS GL date >=POFromDate and
SUS GL date >=POToDate and

Proposed Solution(adding three more lines)

Set append flag - yes
SUS Amt Open != 0 none
SUS Dcto !=RU and
SUS GL date >=POFromDate and
SUS GL date <=POToDate and
SUS Amt Open != 0 or
SUS GL date <POFromDate and
SUS GL date >POToDate and


Note: SUS - Set User Selection
Thanks,
Niv