Dates Table

[brxmas]

Crystal 9.0
I would appreicate anyones help in resolving an issue with the following.

I created Stored Procedure with various dates tables in SQL so when Crystal reports are generated I don't have to use parameters.

There are various reports comapring Current Year Period to Last Year Period, Week to Period (e.g. 12/27/2004 - 01/23/2005 compare to 12/29/2003 - 02/01/2004), etc.

The following code is in my 'Report Filter':

{mc_acct_master_view.bus_unit_id} = "MMB" AND
{mc_acct_master_view.pub_id} = "MMB" AND
{mc_ad_insertion.insertion_date}= {paramdates.firstdayyear} TO {paramdates.periodenddate} OR
{mc_period_dates.period_year} = {paramdates.periodyear} AND
{mc_period_dates.period} <= {paramdates.period} AND
//{mc_ad_insertion.insertion_date}= {paramdates.lyfirstdayyear} TO {paramdates.lyperiodenddate} AND
NOT ({mc_ad_cost_view.obj_code} IN ["59", "29", "19", "905", "915"]) AND
{mc_ad_detail.adv_type} = "GE" AND
NOT ({mc_ad_insertion.prod_code} IN ["ELDY"])

If I run the following code without LY paramdates it gives me correct data and processess quickly:

{mc_acct_master_view.bus_unit_id} = "MMB" AND
{mc_acct_master_view.pub_id} = "MMB" AND
{mc_ad_insertion.insertion_date}= {paramdates.firstdayyear}
TO {paramdates.periodenddate}
AND
NOT ({mc_ad_cost_view.obj_code} IN ["59", "29", "19", "905", "915"]) AND
{mc_ad_detail.adv_type} = "GE" and
NOT ({mc_ad_insertion.prod_code} in ["ELDY"])

But, if I add the LY paramdates to the filter, it never finishes processing.

Thanks in advance for your help.

brxmas

 

[synapsevampire]

A Stored Procedure is generally used to tailor results, and any filtering performed against an SP will be done within the report, not passed to the database, hence the performance concerns. But it doesn't appear that you're using a Stored Procedure for the report, perhaps you're just stating that you used an SP to create the dates table, which has nothing to do with the report or performance.

The code you demonstrate with the lyparameters line is remarked out anyway, so it isn't being used.

I would suggest that you seek architectural guidance rather than describing how you want to do something.

That would entail posting:

Database/connectivity used
Example data
Expected outputAnyway, try something like:

(
{mc_acct_master_view.bus_unit_id} = "MMB"
)
AND
(
{mc_acct_master_view.pub_id} = "MMB"
)
AND
(
{mc_ad_insertion.insertion_date}= {paramdates.firstdayyear} TO {paramdates.periodenddate}
OR
(
{mc_period_dates.period_year} = {paramdates.periodyear} AND
{mc_period_dates.period} <= {paramdates.period}
AND
{mc_ad_insertion.insertion_date}= {paramdates.lyfirstdayyear} TO {paramdates.lyperiodenddate}
)
)
AND
(
NOT ({mc_ad_cost_view.obj_code} IN ["59", "29", "19", "905", "915"])
)
AND
(
{mc_ad_detail.adv_type} = "GE"
)
AND
(
NOT ({mc_ad_insertion.prod_code} IN ["ELDY"])
)

Hard to say though, but you should take something away from the use of parentheticals.

-k

 

[brxmas]

synapsevampire

Sorry for not stating that SP were used to create the paramdates tables. (This is my first time in asking a question)....

SQL Server 2000
ODBC connectivity

I really appreciate your help, it worked perfectly. Thanks again.

Now I know the specifics to state when needing help in resolving problems. Thanks again.

brxmas