record selection vs. SQL Query 1

[wysiwygGER01]

Hi,

I'm using CR 10.
My report takes very long to run so I set up the below selection statements to speed up the time it takes to produce my report.

(if {?AllCountries}<>"1" then {@Country} ={?SpecificCountry} else {@Country} <>"")
AND year(pwformatdate({GLPOST.JRNLDATE})) = ToNumber({?Year})
AND {GLPOST.SRCETYPE} <> "CL"
AND (MID({GLPOST.ACCTID},3,4) = "5340" 
    OR MID({GLPOST.ACCTID},3,4) = "5342"
    OR MID({GLPOST.ACCTID},3,4) = "5722" 
    OR MID({GLPOST.ACCTID},3,4) = "5210"
    OR MID({GLPOST.ACCTID},3,4) IN ["4110" TO "4430"]) 
AND {GLPOST.JRNLDATE} >= 0 AND pwformatdate({GLPOST.JRNLDATE}) <= {@EndDate}

When I look at the SQL query though it seems to ignore the statement where I try to limit my results for 1 year only.
"year(pwformatdate({GLPOST.JRNLDATE})) = ToNumber({?Year})"
It therefore returns records for all years.
Am I doing something wrong or is this the way it works?

This is my SQL query returned from Crystal.

 SELECT "GLAMF"."ACSEGVAL02", "GLAMF"."ACSEGVAL03", "GLAMF"."ACCTDESC", "GLPOST"."ACCTID", "GLPOST"."SRCETYPE", "GLPOST"."JRNLDATE", "GLPOST"."TRANSAMT"
 FROM   "cap100"."dbo"."GLAMF" "GLAMF" INNER JOIN "cap100"."dbo"."GLPOST" "GLPOST" ON "GLAMF"."ACCTID"="GLPOST"."ACCTID"
 WHERE  "GLPOST"."SRCETYPE"<>'CL' AND "GLPOST"."JRNLDATE">=0

 

[lbass]

There are multiple clauses that are not passing. You could speed things up by converting as many formulas as possible to SQL expressions.

1) country clause: What is the content of {@Country}? Something in that formula is preventing that clause from passing.

2) year(pwformatdate({GLPOST.JRNLDATE})) = ToNumber({?Year})
First you should change the {?Year} parameter to be a number parameter. Ideally you would set up a SQL expression {%year} using the year function, but I'm not sure you can access custom functions like pwformatdate within SQL expressions, and I have no way of testing that--but you could. See if there is a year function in the function list for SQL expressions, and then see if it will accept the custom function. If there is no year function just try:

year(pwformatdate(GLPOST.JRNLDATE))

...adding the field from the field list so the correct punctuation is applied. Then you would replace the clause in your selection formula with:

{%year} = {?Year}

3) MID({GLPOST.ACCTID},3,4)

Replace this with a SQL expression {%acctid} that uses either a substring function from the function list

{fn substring(GLPOST.ACCTID,3,4)}

Or try:

substr(GLPOST.ACCTID,3,4)

Then use this in your selection formula:

{%acctid} in ["5340","5342","5722","5210","4110" TO "4430"]

4) {@Enddate}--what is the content of this? Either the formula is the problem or the custom function is.

Of course, these suggestions presume that SQL expressions are available to you.

-LB

 

[wysiwygGER01]

Awesome! Thank you so much for pointing me to SQL Expressions!
I did not know what they can be used for.
I now have the following selections.
{?Year} has to be a string value unfortunately.

(if {?AllCountries}<>"1" then {%Country} ={?SpecificCountry} else {%Country} <>"")
AND {%Year} =ToNumber({?Year})
AND {GLPOST.SRCETYPE} <> "CL"
AND {%AccountID} IN ["5340","5342","5722","5210","4110" TO "4430"] 
AND  {%EndDate} <= ToNumber({?EndDate})

The SQL query it gives me is as follows:

 SELECT "GLAMF"."ACSEGVAL02", "GLAMF"."ACSEGVAL03", "GLAMF"."ACCTDESC", "GLPOST"."ACCTID", "GLPOST"."SRCETYPE", "GLPOST"."JRNLDATE", "GLPOST"."TRANSAMT", ({fn YEAR("GLPOST"."FISCALYR")}), ({fn SUBSTRING("GLPOST"."ACCTID",3 ,4 )}), ({fn SUBSTRING("GLPOST"."ACCTID",1 ,2 )}), ("GLPOST"."JRNLDATE")
 FROM   "cap100"."dbo"."GLAMF" "GLAMF" INNER JOIN "cap100"."dbo"."GLPOST" "GLPOST" ON "GLAMF"."ACCTID"="GLPOST"."ACCTID"
 WHERE  ({fn SUBSTRING("GLPOST"."ACCTID",1 ,2 )})='10' AND ({fn YEAR("GLPOST"."FISCALYR")})=2011 AND "GLPOST"."SRCETYPE"<>'CL' AND ((({fn SUBSTRING("GLPOST"."ACCTID",3 ,4 )})>='4110' AND ({fn SUBSTRING("GLPOST"."ACCTID",3 ,4 )})<='4430') OR ({fn SUBSTRING("GLPOST"."ACCTID",3 ,4 )})='5210' OR ({fn SUBSTRING("GLPOST"."ACCTID",3 ,4 )})='5340' OR ({fn SUBSTRING("GLPOST"."ACCTID",3 ,4 )})='5342' OR ({fn SUBSTRING("GLPOST"."ACCTID",3 ,4 )})='5722') AND ("GLPOST"."JRNLDATE")<=20110531

 

[lbass]

I don't see why {?Year} has to be a string.

It looks like everything is passing except the country clause. Try changing it to:

(
(
{?AllCountries}<>"1" and
{%Country} ={?SpecificCountry}
) or
(
{?AllCountries}="1" and
{%Country} <> ""
)
)

-LB