I'm coming in on the back end of a project and I don't understand the following record selection formula. Would someone please explain what it's doing?
if {?@.rptfilter} = 0 then
(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 1 then
({CAS_SP_SolnLosses;1.Contractor} = "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} = "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 2 then
({CAS_SP_SolnLosses;1.Contractor} <> "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} <> "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})This is not an answer to your question, but at least in earlier versions (up to version 8.5) Crystal reports is not able to create an SQL stement based on control structures (If-Then-Else) instead you should convert the record selection formula to a boolean statement using ands and ors and braces. What happens if you don't do this is that Crystal loads the entire answer set to your computer and makes the record selection locally. This has at least two major disadvantages:
1. The query is processed on the database server as a full table join and the query can not make use of any indexes. This can cause performance problems to any DBMS system
2. You cause a lot of network traffic moving unneeded data over the network
This has one implication, which is poor performance.
What concerns your code I have a few suggestions:
(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
could be removed in my opinion, because it compares a field to both null and not null
after this you basically create different joins based on different values of
{?@.rptfilter}
Hope this gives you some clues.
- Jukka|||This is not an answer to your question, but at least in earlier versions (up to version 8.5) Crystal reports is not able to create an SQL stement based on control structures (If-Then-Else) instead you should convert the record selection formula to a boolean statement using ands and ors and braces. What happens if you don't do this is that Crystal loads the entire answer set to your computer and makes the record selection locally. This has at least two major disadvantages:
1. The query is processed on the database server as a full table join and the query can not make use of any indexes. This can cause performance problems to any DBMS system
2. You cause a lot of network traffic moving unneeded data over the network
This has one implication, which is poor performance.
What concerns your code I have a few suggestions:
(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
could be removed in my opinion, because it compares a field to both null and not null
after this you basically create different joins based on different values of
{?@.rptfilter}
Hope this gives you some clues.
- Jukka
I agree that line you mentioned could be deleted. My problem is understanding the syntax of this *ugly* formula. Unfortunately, the developer that wrote this is long gone and, like I mentioned earlier, it's been dumped in my lap. I've looked over the .pdf's on the CD hoping to find some help, no luck. Are there any other sources which would help me get my head around understanding what this code is doing?
TIA
BTW, is there a way to step thru the formula code with a debugger?|||Hello,
I'm doing project using Access as backend and VB6 FrontEnd. I have the following problem during compilation. I'm using Runtime Activex lib of CR 8.5.
Error: Method or Data member not found
===> Private Sub mnu_stock_Click()
With Form1.CrystalReport1
.DataFiles(0) = App.Path & "\MyDB.MDB"
.ReportFileName = App.Path & "\Report\RPT_AVA_STOCK.rpt"
.username = "Admin"
.Password = "1010101010" & Chr(10) & "1010101010"
.Action = 1
.PageZoom (100)
End With
End Sub
Thank you,
Regards,
Niranjan Dixit|||I agree that line you mentioned could be deleted. My problem is understanding the syntax of this *ugly* formula. Unfortunately, the developer that wrote this is long gone and, like I mentioned earlier, it's been dumped in my lap. I've looked over the .pdf's on the CD hoping to find some help, no luck. Are there any other sources which would help me get my head around understanding what this code is doing?
TIA
BTW, is there a way to step thru the formula code with a debugger?
Here is a try (the syntax is slightly different to what I'm used to using Oracle):
You have the following parameters in your report controlling the outcome:
rptfilter
typeid
comments are marked with a "-"
Fuurther I would still make sure that I have all parenthesis in the right places. I would include an extra round of parenthesis where rptfilter changes values
and the code it self:
if {?@.rptfilter} = 0 then
- rpt filter = 0
(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
- This section does nothing
and
(if {?@.typeid} = 0 then
- rptfilter = 0 and typeid = 0
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
- When rptfilter = 0 and typeid = 0 then only record having CAS_SP_SolnLosses;1.Typevalue = "ANY" are included
else
if {?@.typeid} = 1 then
- rptfilter = 0 and typeid = 1
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
- When rptfilter = 0 and typeid = 1 then is joined to CAS_SP_SolnLosses;1.Typevalue = {CAS_SP_SolnLosses;1.Region}, i.e. values have to be equal
- Similar for typeid 2 - 8
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 1 then
- rptfilter = 1
({CAS_SP_SolnLosses;1.Contractor} = "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} = "CAJV")
- rptfilter = 1 and CAS_SP_SolnLosses;1.Contractor = "ARORA" or CAS_SP_SolnLosses;1.Contractor "CAJV"
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
- This is similar to above except that rptfilter has value 1 and further below value 2
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 2 then
({CAS_SP_SolnLosses;1.Contractor} <> "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} <> "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})|||Here is a try (the syntax is slightly different to what I'm used to using Oracle):
You have the following parameters in your report controlling the outcome:
rptfilter
typeid
comments are marked with a "-"
Fuurther I would still make sure that I have all parenthesis in the right places. I would include an extra round of parenthesis where rptfilter changes values
<snip>
Thanks for your help!
No comments:
Post a Comment