Two conditions in SELECT Expert 2

[epoh]

Hi all:

Is there a way to filter out records that meet two conditions?

SELECT EXPERT:

{Daily.UserName} <> "user" and
{Daily.Type} <> "Early"

I only want to filter out Username "user" and Type "Early" not
"user" and "Late" or
"joe" and "Early.

Thanks

 

[synapsevampire]

Try:

(
{Daily.UserName} <> "user"
and
{Daily.Type} <> "Early"
)

-k

 

[epoh]

Thanks, I tried your suggestion. I am using CRXI Standard.

I surrounded the last two lines in () and CR removed them. And filtered out all records with "user" and all records with "late"

I am just wanting to filter out the records where "user" is "late"

Here is my complete SELECT EXPERT function

datediff("d",{Daily.TimeGen},(dateAdd("d", -1, currentdate)))=0 and
{Daily.EventID} <> 59 and
({Daily.UserName} <> "user" and
{Daily.Type} <> "Late")

Thanks

 

[lbass]

Your current formula should work perfectly.

-LB

 

[synapsevampire]

A good test is to check the Database->Show SQL Query, this will show you what is being passed to the database.

Also read out my FAQ for a better overall understanding:

faq767-3825

-k

 

[epoh]

Still not returning correct results.

This is my SQL Query:

SELECT "Daily"."TimeGenerated", "Daily"."ComputerName",
"Daily"."EventID", "Daily"."UserName", "Daily"."Type",
"Daily"."CompDesc", "Daily"."UsrName", "Daily"."UserNamewDN", "Daily"."UserDisplayName", "Daily"."OrigMsg" FROM "EventLogs"."dbo"."Daily" "Daily"
WHERE NOT ("Daily"."UserName"='user2' OR "Daily"."UserName"='user')
AND "Daily"."EventID"<>59 AND "Daily"."Type"<>'late'
ORDER BY "Daily"."ComputerName", "Daily"."TimeGenerated"

Thanks for your help

 

[synapsevampire]

OK, let's try to force it:

(
{Daily.UserName} <> "user"
and
{Daily.Type} <> "Late"
)
and
(
datediff("d",{Daily.TimeGen},(dateAdd("d", -1, currentdate)))=0
)
and
(
{Daily.EventID} <> 59
)

Sometimes Crystal has difficulties parsing it out without the explicit use of parentheticals.

-k

 

[synapsevampire]

btw, this:

datediff("d",{Daily.TimeGen},(dateAdd("d", -1, currentdate)))=0

is the same as:

{Daily.TimeGen} = currentdate-1

-k

 

[epoh]

Thanks for the valuable tip:
"{Daily.TimeGen} = currentdate-1"

Odd, am i doing this in the right place?

I am using SELECT Expert for this. It keeps removing the parenthesis and giving me the unwanted results.

 

[synapsevampire]

No, don't use the Select Expert, it's overriding you because it's not an expert

Use Report->Selection Formulas->Record

-k

 

[epoh]

Deja vu.

I took out the formula from the SELECT Expert and used Report->Selection Formulas->Record and got the same results.

The user and the type are both filtered out completely.

What do you mean "its not an expert"

I am using CRXI Standard

 

[synapsevampire]

Hmmm, never tried CR XI Standard, perhaps that has something to do with it, dunno.

Thyat's basically a very trimmed down version of Crystal, and I've never seem any version override the syntax placed there, nor do I know of a means to have it automatically do so.

Presumably you use Save and Close.

Try doing an Alt+C in there, does it modify it or error, or??? That states check the syntax.

-k

 

[lbass]

The parens are irrelevant when using "ands" with no "ors". Are you sure you didn't leave an "or" between the two clauses for user and type?

-LB

 

[epoh]

Alt + C says "No errors found."

{Daily.TimeGen} = currentdate-1 and
({Daily.UserName} <> "user2" and {Daily.UserName} <> "user") and
({Daily.EventID} <> 59 and {Daily.Type} <> "late")

At least now it is saving my formula, but it still is omitting all eventids with 59 and all types with late

 

[synapsevampire]

...

The parens are not irrelevant, LB, certainly with older versions of Crystal and passing the proper SQL to the database.

I thought that we'd discussed this topic before at length, check numerous threads here from days gone by, even seemingly much less important devising can impact what is passed, and how it is passed.

They'd already posted their formula, WITHOUT ORs, stating that it did not work, rather, that it's being rewritten, perhaps someone with XI Standard can shed some light on this as I'm unable to test.

You'd be amazed at the subtle differences we've had to allow for to properly pass SQL and get the right results, at least I have been...

-k

 

[epoh]

Quoting Lbass "The parens are irrelevant when using "ands" with no "ors". Are you sure you didn't leave an "or" between the two clauses for user and type?"

My full formula is above.

 

[Turkbear]

Hi,
Try this variant ( may be slower)

(
Trim({Daily.UserName}) + Trim({Daily.Type}) <> "userLate" 
)
and
(
datediff("d",{Daily.TimeGen},(dateAdd("d", -1, currentdate)))=0
) 
and
(
{Daily.EventID} <> 59
)

Or Create a formula to concatenate those 2 fields and use that formula in the criteria..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Turk: Any thoughts on why it would rewrite the syntax? Is that a XI Standard thing?

Also, I doubt that Crystal will pass that to tyhe database, mebbe...

Since they aren't using a SQL database (I believe that Standard does not have big kid database connectivity), it's impossible to check what's hapening...

-k

 

[epoh]

Quoting Turk: "Create a formula to concatenate those 2 fields and use that formula in the criteria"

That worked!!!!!

Thanks everyone!!

 

[Turkbear]

Hi,
My thought was that to compare 2 fields in the same record
you need some odd construct ( like a concatenation), otherwise the code you posted would not be precise enough ( a very rare thing):
Because:

(
{Daily.UserName} <> "user"
and
{Daily.Type} <> "Late"
)
and
(
datediff("d",{Daily.TimeGen},(dateAdd("d", -1, currentdate)))=0
)
and
(
{Daily.EventID} <> 59
)

Is intepreted a a series of AND statements ( regardless of the parens), so it will eliminate any record whose
username is 'user' AND any whose Type is Late, but not JUST those where BOTH are true...


Maybe a formula that does this
@CheckBoth

WhileReadingRecords
If (
{Daily.UserName} =  "user"
AND
{Daily.Type} = "Late" )
Then 1 
else 0

Then Perhaps this would work:

@CheckBoth = 0
and
(
datediff("d",{Daily.TimeGen},(dateAdd("d", -1, currentdate)))=0
) 
and
(
{Daily.EventID} <> 59
)

Maybe...






To Paraphrase:"The Help you get is proportional to the Help you give.."