How to force crystal to filter records on SQL server

[WaelYassin]

Hi all, i have CR 9 and SQL server 2000,
i am dealing with a huge amount of data about 5 million records.

i want crystal to pass selection formula to SQL server and let it filter on the selected records and pass them back to crystal.
what really happens after choosing parameters, is that CR shows (access database)then reading data (and read the whole 5 million records!!) and then filter on the selected records and shows them.

thanks for your time.

 

[bdreed35]

Can you post what you are using as your Record Selection criteria?
From the Report menu, choose Selection Formulas->Record.
Copy and paste the contents of the formula here please so we can see what you are trying to do.

~Brian

 

[synapsevampire]

Check out my FAQ for advanced tips:

faq767-3825

-k

 

[WaelYassin]

thanks for your replies, here is the selection formulas of the report:-


(
(IsNull({BRNDH.BRNDH_DESC}) AND{?prm_inc_Nulls}) or
(UpperCase({BRNDH.BRNDH_DESC}) IN UpperCase({@frm_prm_Brand}))
)
AND
(
(IsNull({Denomination.DenomDesc})AND{?prm_inc_Nulls}) or
(UpperCase({Denomination.DenomDesc}) IN UpperCase({@frm_prm_denomination}))
)
AND
(
(isnull({LOC.LOC_DESC})AND{?prm_inc_Nulls}) or
(UpperCase({LOC.LOC_DESC}) IN UpperCase({@frm_prm_Location}))
)
AND
(
(isnull({Region.LOC_DESC}) AND{?prm_inc_Nulls})or
(UpperCase({Region.LOC_DESC}) IN UpperCase({@frm_prm_Region}))
)
AND
(
(isnull({CLL.INFileDate}) AND{?prm_inc_Nulls})or
totext(Year({CLL.INFileDate}),0,"") in {?prm_Year}
)
AND
(
(isnull({CLL.INFileDate}) AND{?prm_inc_Nulls})or
MonthName(Month({CLL.INFileDate})) in {?prm_Month}
)
AND
(
(isnull({CLL.INFileDate}) AND{?prm_inc_Nulls})or
totext(Day({CLL.INFileDate}),0,"") in {?prm_Day}
)
AND
(
(isnull({CLLTYP.CLLTYP_DESC}) AND{?prm_inc_Nulls})or
(UpperCase({CLLTYP.CLLTYP_DESC}) in {?prm_Call_Type})
)

 

[synapsevampire]

You honestly thought that we'd know what's in {@frm_prm_Brand} and the 3 other formulas?

I pointedyou to my FAQ, did you read it?

Did you identify wht is NOT being passed to the database?

You also need to make sure that you need the uppercase, it tends to prevent SQL pass through,and many database settings don't require that you use it.

Now the solution is to do an uppercase in a SQL Expression on those fields, then use the SQL Expressions inyour record selection formula.

You're on a topic that I've probably spent more time on than anyone here, however you have to put some forethought and effort into your posts for us to help you.

-k

 

[WaelYassin]

hi synapse,
thanks for your interest, about each formula it just use the join function to concatenate the values i select from the parameter which is configured to allow multiple values.
here is a sample of one formula ({@frm_prm_Region}):-
join({?prm_Region},", ")

all the mentioned formulas the same like this one.

i read your post but i got lost in the details and didn't figure out what can pass and what can't pass.if you can brief it i will be thankful.
i appreciate your efforts.
thanks.

 

[synapsevampire]

It's not a brief topic.

In general, use Database->Show SQL Query to see the SQL generated.

Use Report->Selection Formula->Record to learn what should be passed to the SQL, if not, we need to work on those areas.

To assist with this, create a SQL Expression (type of formula, very powerful) in crystal of:

upper(table.field)

Now the server will uppercase the field for you, which is faster, and allows us to use the uppercase function on the Crystal parameters or formulas, which will be passed in the SQL.

I would also tweak your formulas to do the uppercase in there, instead of in the RecordSelection, it's just a bit tidier,sothat wouldchange:

{@frm_prm_Region}
uppercase(join({?prm_Region},", "))

So this should get everything to pass that I can think of, though I see noreason why you'd have formulas for Month, Week and Day, just use the parms in the recordselection, they're numerics, right?

Don't preprocess, it buys nothing, in fact it slows you down a tiny bit, and hides what you are doing.

I understand doing this to uppercase text in a parameter, using a formula helps to assure that it passes in this instance.

-k