Select Formula Exclusions

[alembong]

CR 9
Oracle DB

I've got an existing selection formula that I need to add one more bit of logic to. The table has this info:

Transaction Nbr BFY Document Nbr
10001 2005 AB10000
10002 2004 AB10001
10003 2004 BB11111
10004 2005 BB11234
10005 2004 BB12345

I have a parameter, ?RptLevel, asking to include a certain level where the value can either be "Y" or "N".

When the ?RptLevel = "N" then it needs to include all transaction numbers. When ?RptLevel = "Y" then if BFY < 2005 and Document Nbr starts with "BB" then exclude the transaction.

My intial go was:

If ?RptLevel = "Y"
Then If BFY < "2005"
   Then not {DocumentNbr} startswith "BB"

But then when you select "N", the report was blank.

I then tried:

If ?RptLevel = "Y"
Then If BFY < "2005"
   Then not {DocumentNbr} startswith "BB"
Else {TransNbr} = {TransNbr}

Now the report takes forever.

Any help would be greatly appreciated.

 

[Naith]

It's probably not Crystal's fault that the report takes forever when you select 'N'.

If you submit the comparable SQL directly against the database, is it any quicker?

I suspect the answer to this will be no, which would suggest to me that the indices on the table are not being used. If you have a clustered index on the table, you'll only be able to utilise it if you select on the other fields making up the index, even if the report doesn't need them.

If this doesn't apply to you, show me the SQL that Crystal is generating which is causing the report to take forever, and add an excerpt illustrating how you want the SQL to look.

Naith

 

[alembong]

Thanks for the direction

I'm running a few tests of the report before and after my change and it looks like it just really does take forever for the parameters that I'm using. I was using the user's parameters. I tried a different set of parameters and it just a few seconds. This user's particular group must just have tons of data.

Thanks again.