Main report and subreport parameters 2

[hencyt]

This is the select criteria for the sub-report. The sub-report parameter values are populated from the container reports param values.
-----------------------------
(if {?Pm-?Select Fragrances} <> &quot;ALL&quot; then ({IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE30} = {?Pm-?Select Fragrances}or
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE20} = {?Pm-?Select Fragrances} or
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_40} = {?Pm-?Select Fragrances} or
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE10} = {?Pm-?Select Fragrances})) and
(if {?Pm-?Style} <> &quot;ALL&quot; then {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_STYLE} = {?Pm-?Style} else {?Pm-?Style} = &quot;ALL&quot and
(if {?Pm-?CountryOrigin} <>&quot;ALL&quot; then {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_COUNTRY_ORIGIN} = {?Pm-?CountryOrigin} else {?Pm-?CountryOrigin} = &quot;ALL&quot; ) and
{Component.InvoiceDate} >= {?Pm-?Beginning Date} and
{Component.InvoiceDate} <= {?Pm-?Ending Date}
-------------------------------
Because we have so many values in the fragrance field, there are four fields for the same thing.
I have an alomst identical select criteria in the container report and it work perfectly. But for the subreport, if a fragrance value is not chosen- then no records returned. But as long as a fragrance is chosen (and not left to ALL) then all other criteria work.

You may need more info, I was just trying to keep it simple. As the report is kinda convoluted.

Thanks,
Sunny

 

[synapsevampire]

The record selection appears to have some odd criteria in it, such as:

else
{?Pm-?Style} = &quot;ALL&quot;

This would do nothing.

Perhaps you mean to have

else IF
{?Pm-?Style} = &quot;ALL&quot;

Again you do it on this line, which would do nothing:

else
{?Pm-?CountryOrigin} = &quot;ALL&quot;

I suspect that you may be having problems getting the SQL to pass to the database too. I have a FAQ in the Crystal Formulas forum which addresses record selection formulas:

faq767-3825

I would place an ELSE IF.... TRUE for each IF and use parens to block out each section, or nest accordingly.

-k

 

[Ngolem]

(if {?Pm-?Select Fragrances} <> &quot;ALL&quot; then ({IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE30} = {?Pm-?Select Fragrances}or
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE20} = {?Pm-?Select Fragrances} or
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_40} = {?Pm-?Select Fragrances} or
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE10} = {?Pm-?Select Fragrances}))

*****************************
Is {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_40} typed wrong??? Should it be {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE40} to be consistant with the others....I used this form below so correct it if you try this

SV is a lot better at this for passing it down to the server but let us just look at making this better.

I don't like the way this is set up...perhaps something like this

(if {?Pm-?Select Fragrances} = &quot;ALL&quot; then
True
else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE40} = {?Pm-?Select Fragrances} then
True
else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE30} = {?Pm-?Select Fragrances} then
True
else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE20} = {?Pm-?Select Fragrances} then
True
else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE10} = {?Pm-?Select Fragrances} then
True
else
False and

(if {?Pm-?Style} = &quot;ALL&quot; then
True
else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_STYLE} = {?Pm-?Style} then
True
else
False and

(if {?Pm-?CountryOrigin} = &quot;ALL&quot; then
True
else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_COUNTRY_ORIGIN} = {?Pm-?CountryOrigin} then
True
else
False; ) and

({Component.InvoiceDate} >= {?Pm-?Beginning Date} and
{Component.InvoiceDate} <= {?Pm-?Ending Date})

Backets should surround the last 2 items.

Perhaps this will work better for you.....but as I said earlier...SV does this better than I.


Jim Broadbent

 

[hencyt]

Thanks for the replies. I agree that the setup is not pretty. I have tried without the = &quot;all&quot; and setting them to true before and it did not work. Perhaps I had something else wrong before. I will try it this way.

No- the {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_40}is not typed wrong. All of these fields referenced are user defined fields- I created them thru our application in the db. Thus I also named them and I created this one on a different day and did it different. By the time I realized it- users had already filled it in with data, so I could not rename it without losing that data.

(We are using MAS 200 SQL db and if you are not familiar- what they have done is written a provide-x converter to SQL- very annoying and convoluted)

Thanks- will let you know how it goes.

Sunny

 

[hencyt]

That seems to have worked brilliantly. I should have seen that before. I guess after you look at something for so long it starts to look the same...

I would like to rewrite the selection formula for the container report in the same way. It works as is, but I am hoping rewriting will make it more efficient. I think I have it but I want to make sure I have chosen the best way. Here it is-

{ARO_InvHistoryDetail.LineType} = &quot;4&quot; and
(if {?Select Fragrances} = &quot;ALL&quot; then true else
({IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE10} in {?Select Fragrances}) or
({IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE20} in {?Select Fragrances}) or
({IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE30} in {?Select Fragrances}) or
({IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_40} in {?Select Fragrances})) and
(if {?FragClass}<> &quot;ALL&quot; then {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_CLASS}={?FragClass}else {?FragClass}=&quot;ALL&quot and
(if {?CountryOrigin}<>&quot;ALL&quot; then {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_COUNTRY_ORIGIN}={?CountryOrigin} else {?CountryOrigin}=&quot;ALL&quot and
(if {?Product}<>&quot;ALL&quot; then {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PRODUCT} = {?Product} else {?Product}=&quot;ALL&quot and
(if {?Item Type}<>&quot;ALL&quot; then {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_ITEM_TYPE} = {?Item Type} else {?Item Type}=&quot;ALL&quot and
(if {?Style}<> &quot;ALL&quot; then {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_STYLE} = {?Style}else {?Style}=&quot;All&quot and
(if {?Color} <>&quot;ALL&quot; then {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_COLOR} = {?Color} else {?Color}=&quot;ALL&quot and
(if {?Procdesc} <> &quot;ALL&quot; then {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PROCESS_DESC} = {?Procdesc}else {?Procdesc}=&quot;ALL&quot and
(if {?ProductClass} <>&quot;ALL&quot; then {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PRODUCT_VAR} = {?ProductClass}else {?ProductClass}=&quot;ALL&quot and
{ARN_InvHistoryHeader.InvoiceType} = &quot;I&quot; and
{ARN_InvHistoryHeader.InvoiceDate} >= {?Beginning Date} and
{ARN_InvHistoryHeader.InvoiceDate} <={?Ending Date} and
{ARO_InvHistoryDetail.SOWhse} = &quot;001&quot;

Thanks so much-
Sunny

 

[Ngolem]

Glad it worked for you...

But I am not impressed with this second &quot;attempt(?)&quot;

In my opinion, you have not tried to rewrite this formula at all since the problems are exactly the same as the previous formula....you just dumped the original formula into this post.

You don't learn anything by not trying it yourself....look at the structure of the answer that was given and apply it to the formula you have shown here...it should be easy to do....if you have problems certainly we will help....but you have to make an attempt as well.

Jim Broadbent

 

[hencyt]

Agreed. I didn't post my solution. I was going to compare what was posted to what I have here. Sorry for the confusion.
Sunny

 

[hencyt]

FYI This is what I changed it to. It doesn't seem to run any faster, I hoped it would. But it is working.

{ARO_InvHistoryDetail.LineType} = &quot;4&quot; and
{ARN_InvHistoryHeader.InvoiceType} = &quot;I&quot; and
{ARN_InvHistoryHeader.InvoiceDate} >= {?Beginning Date} and
{ARN_InvHistoryHeader.InvoiceDate} <={?Ending Date} and
(if {?Select Fragrances} = &quot;ALL&quot; then true else
if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE10} in {?Select Fragrances} then true else
if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE20} in {?Select Fragrances} then true else
if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE30} in {?Select Fragrances} then true else
if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_40} in {?Select Fragrances} then true else false) and
(if {?FragClass}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_CLASS} = {?FragClass} then true else false) and
(if {?CountryOrigin}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_COUNTRY_ORIGIN} = {?CountryOrigin} then true else false) and
(if {?Product}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PRODUCT} = {?Product} then true else false) and
(if {?Item Type}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_ITEM_TYPE} = {?Item Type}then true else false) and
(if {?Color}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_COLOR} = {?Color} then true else false) and
(if {?ProductClass}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PRODUCT_VAR} = {?ProductClass} then true else false) and
(if {?Fragrance Number}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.ML_UDF_IMH_FRAG_NUMB} = {?Fragrance Number} then true else false) and
(if {?Pantone Number}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.ML_UDF_IMH_PANTONE} = {?Pantone Number} then true else false) and
(if {?Procdesc}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PROCESS_DESC} = {?Procdesc} then true else false) and
(if {?Style}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_STYLE} = {?Style} then true else false) and
{ARO_InvHistoryDetail.SOWhse} = &quot;001&quot;


Thanks again,
Sunny

 

[Ngolem]

that's better...

the only change I would make is to put brackets around your dates.

{ARO_InvHistoryDetail.LineType} = &quot;4&quot; and
{ARN_InvHistoryHeader.InvoiceType} = &quot;I&quot; and
({ARN_InvHistoryHeader.InvoiceDate} >= {?Beginning Date} and {ARN_InvHistoryHeader.InvoiceDate} <={?Ending Date}) and
(if {?Select Fragrances} = &quot;ALL&quot; then true else
if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE10} in {?Select Fragrances} then true else
if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE20} in {?Select Fragrances} then true else
if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE30} in {?Select Fragrances} then true else
if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_40} in {?Select Fragrances} then true else false) and
(if {?FragClass}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_CLASS} = {?FragClass} then true else false) and
(if {?CountryOrigin}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_COUNTRY_ORIGIN} = {?CountryOrigin} then true else false) and
(if {?Product}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PRODUCT} = {?Product} then true else false) and
(if {?Item Type}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_ITEM_TYPE} = {?Item Type}then true else false) and
(if {?Color}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_COLOR} = {?Color} then true else false) and
(if {?ProductClass}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PRODUCT_VAR} = {?ProductClass} then true else false) and
(if {?Fragrance Number}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.ML_UDF_IMH_FRAG_NUMB} = {?Fragrance Number} then true else false) and
(if {?Pantone Number}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.ML_UDF_IMH_PANTONE} = {?Pantone Number} then true else false) and
(if {?Procdesc}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PROCESS_DESC} = {?Procdesc} then true else false) and
(if {?Style}= &quot;ALL&quot; then true else if {IM_90_UDF_IM_Masterfile.LB_UDF_IMH_STYLE} = {?Style} then true else false) and
{ARO_InvHistoryDetail.SOWhse} = &quot;001&quot;

SV is the best guy for optimizing these things....but when you say it runs slow.....how slow? My criteria for any report is that it generates the result in less than 1 minute any longer than that and the user usually complained a lot.

What you might do is look at the SQL after the report has run to see how much of this record selection got passed to the server...then we can look at the parts that didn't make it (hence were evaluated on the second pass) and see how we can improve on that.

There are many things that affect the speed of a report. The fact that you are running a subreport is a major time consumer in itself depending on how often it is run.

Jim Broadbent

 

[hencyt]

Jim,

Thanks. I did have parentheses around the date fields- but Crystal removes them after I accept the selection criteria. I tried moving the dates to different order in the formula but it still removed the brackets.

It takes a little more than a minute to generate. Probably an acceptable time given the subreport and amount of data the report is accessing. The subreport is the the report footer, so it only runs once.

When you suggested looking at the SQL- could you explain that further? Do you mean choosing show SQL query from the crystal database menu? Or going to the SQL server management tools and looking at the query that was sent through current activity? Or am I wrong on both counts?

When I look at my process on the SQL server while running the report this is the script it shows me is running:

SELECT ARN_InvHistoryHeader.&quot;InvoiceNumber&quot;, ARN_InvHistoryHeader.&quot;InvoiceType&quot;, ARN_InvHistoryHeader.&quot;InvoiceDate&quot;, ARO_InvHistoryDetail.&quot;LineType&quot;, ARO_InvHistoryDetail.&quot;SOItemNumber&quot;, ARO_InvHistoryDetail.&quot;SODescription&quot;, ARO_InvHistoryDetail.&quot;SOWhse&quot;,

Thanks again for sharing your knowledge!

Sunny

 

[Ngolem]

Do you mean choosing show SQL query from the crystal database menu?

Yes this is what I mean.

By viewing the query shown here you can see how much of the Section formula has been pushed down to the server for processing. Ideally you want everything pushed down for maximum speed...since the part that isn't pushed down is evaluated on a second pass through the data.

This is a good diagnostic tool for a couple of reasons.

1. For the reason mentioned above.
2. if you are not getting the data records that you expect, you can copy and paste the formula into a SQL tool and see exactly what records are being collected. Often you will find out that your &quot;report problems&quot; are in reality &quot;database problems&quot;. I have discovered many database problems this way in the process of &quot;defending the honor&quot; of my report

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[hencyt]

Awesome idea. I have checked the SQL query before but I will pay more attention to it now. Thanks.

FYI Here is what is in the Show SQL query for the main container report:
SELECT
ARN_InvHistoryHeader.&quot;InvoiceNumber&quot;, ARN_InvHistoryHeader.&quot;InvoiceType&quot;, ARN_InvHistoryHeader.&quot;InvoiceDate&quot;,
ARO_InvHistoryDetail.&quot;LineType&quot;, ARO_InvHistoryDetail.&quot;SOItemNumber&quot;, ARO_InvHistoryDetail.&quot;SODescription&quot;, ARO_InvHistoryDetail.&quot;SOWhse&quot;, ARO_InvHistoryDetail.&quot;SOQtyShipped&quot;, ARO_InvHistoryDetail.&quot;SOExtChargeAmount&quot;,
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE_CLASS&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_COUNTRY_ORIGIN&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_PRODUCT&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_ITEM_TYPE&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_PROCESS_DESC&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_STYLE&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_COLOR&quot;, IM_90_UDF_IM_Masterfile.&quot;ML_UDF_IMH_PANTONE&quot;, IM_90_UDF_IM_Masterfile.&quot;ML_UDF_IMH_FRAG_NUMB&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_PRODUCT_VAR&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE10&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE20&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE30&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE_40&quot;
FROM
{ oj (&quot;MAS_HCC&quot;.&quot;dbo&quot;.&quot;ARN_InvHistoryHeader&quot; ARN_InvHistoryHeader INNER JOIN &quot;MAS_HCC&quot;.&quot;dbo&quot;.&quot;ARO_InvHistoryDetail&quot; ARO_InvHistoryDetail ON
ARN_InvHistoryHeader.&quot;InvoiceNumber&quot; = ARO_InvHistoryDetail.&quot;InvoiceNumber&quot
INNER JOIN &quot;MAS_HCC&quot;.&quot;dbo&quot;.&quot;IM_90_UDF_IM_Masterfile&quot; IM_90_UDF_IM_Masterfile ON
ARO_InvHistoryDetail.&quot;SOItemNumber&quot; = IM_90_UDF_IM_Masterfile.&quot;ItemNumber&quot;}
WHERE
ARO_InvHistoryDetail.&quot;LineType&quot; = '4' AND
ARN_InvHistoryHeader.&quot;InvoiceType&quot; = 'I' AND
ARN_InvHistoryHeader.&quot;InvoiceDate&quot; >= {ts '2003-09-01 00:00:00.00'} AND
ARN_InvHistoryHeader.&quot;InvoiceDate&quot; <= {ts '2003-09-30 00:00:00.00'} AND
ARO_InvHistoryDetail.&quot;SOWhse&quot; = '001'
ORDER BY
ARO_InvHistoryDetail.&quot;SOItemNumber&quot; ASC,
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE10&quot; ASC,
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE20&quot; ASC,
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE30&quot; ASC,
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE_40&quot; ASC

Show SQL query on the subreport is greyed out. Why might that be? The subreport involves 2 different SQL datasources, could that be it?

Sunny

 

[Ngolem]

It might be that It really doesn't know the query since the subreport COULD be executed several times....So what one should it show??? That is my guess.

What this query shows though is very important. See how the structures for determining the Fragance are not pushed down to the server !!

So this is what is slowing down your report. This section of the Report Selection formula is evaluating on a second pass through the records....the same is true for the evaluation of Country, Product, etc.

So there is room for improvement there.

SynapseVampire is the real expert in this and I have learned a lot from his approach (but I keep forgetting the details )

Let's try to improve your Record Selection formula

He uses a structure like this:

( if {?State = L.State} <> &quot;All&quot; then
{L.State} = {?State}
else if {?State = L.State} = &quot;All&quot; then
true

so lets apply it as much as possible to your siuation...try this for your Record Selection FOrmula....and if this improves things then apply a similar one to your Subreport Record Selection:

{ARO_InvHistoryDetail.LineType} = &quot;4&quot; and
{ARN_InvHistoryHeader.InvoiceType} = &quot;I&quot; and
({ARN_InvHistoryHeader.InvoiceDate} >= {?Beginning Date} and {ARN_InvHistoryHeader.InvoiceDate} <={?Ending Date}) and

(if {?Select Fragrances} <> &quot;ALL&quot; then
(
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE10} in {?Select Fragrances} or
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE20} in {?Select Fragrances} or
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE30} in {?Select Fragrances} or
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_40} in {?Select Fragrances}
)
else if {?Select Fragrances} = &quot;ALL&quot; then
True and

(if {?FragClass}<> &quot;ALL&quot; then
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_CLASS} = {?FragClass}
else if {?FragClass}= &quot;ALL&quot; then
True and

(if {?CountryOrigin}<> &quot;ALL&quot; then
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_COUNTRY_ORIGIN} = {?CountryOrigin}
else if {?CountryOrigin}= &quot;ALL&quot; then
True and

(if {?Product}<> &quot;ALL&quot; then
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PRODUCT} = {?Product}
else if {?Product}= &quot;ALL&quot; then
True and

(if {?Item Type}<> &quot;ALL&quot; then
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_ITEM_TYPE} = {?Item Type}
else if {?Item Type}= &quot;ALL&quot; then
True; )and

(if {?Color}<> &quot;ALL&quot; then
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_COLOR} = {?Color}
else if {?Color}= &quot;ALL&quot; then
True and

(if {?ProductClass}<> &quot;ALL&quot; then
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PRODUCT_VAR} = {?ProductClass}
else if {?ProductClass}= &quot;ALL&quot; then
True and

(if {?Fragrance Number}<> &quot;ALL&quot; then
{IM_90_UDF_IM_Masterfile.ML_UDF_IMH_FRAG_NUMB} = {?Fragrance Number}
else if {?Fragrance Number}= &quot;ALL&quot; then
True and

(if {?Pantone Number}<> &quot;ALL&quot; then
{IM_90_UDF_IM_Masterfile.ML_UDF_IMH_PANTONE} = {?Pantone Number}
else if {?Pantone Number}= &quot;ALL&quot; then
True and

(if {?Procdesc}<> &quot;ALL&quot; then
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_PROCESS_DESC} = {?Procdesc}
else if {?Procdesc}= &quot;ALL&quot; then
True and

(if {?Style}<> &quot;ALL&quot; then
{IM_90_UDF_IM_Masterfile.LB_UDF_IMH_STYLE} = {?Style} else if {?Style}= &quot;ALL&quot; then
True and

{ARO_InvHistoryDetail.SOWhse} = &quot;001&quot;

I am not as confident on the section relating to {?Select Fragrances} as far as getting pushed to the server ....but the others should and this will significantly reduce the amount of data that Crystal has to process.

Try this and see how it works....You can see the benefit of checking out the query that Crystal produces.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[hencyt]

Wow! That really made a difference! It only takes about 30 secs to run it now. All the parameters passed to the server. I will go make the same changes to my subreport.
Here is what is passed to the server now:

SELECT
ARN_InvHistoryHeader.&quot;InvoiceNumber&quot;, ARN_InvHistoryHeader.&quot;InvoiceType&quot;, ARN_InvHistoryHeader.&quot;InvoiceDate&quot;,
ARO_InvHistoryDetail.&quot;LineType&quot;, ARO_InvHistoryDetail.&quot;SOItemNumber&quot;, ARO_InvHistoryDetail.&quot;SODescription&quot;, ARO_InvHistoryDetail.&quot;SOWhse&quot;, ARO_InvHistoryDetail.&quot;SOQtyShipped&quot;, ARO_InvHistoryDetail.&quot;SOExtChargeAmount&quot;,
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE_CLASS&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_COUNTRY_ORIGIN&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_PRODUCT&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_ITEM_TYPE&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_PROCESS_DESC&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_STYLE&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_COLOR&quot;, IM_90_UDF_IM_Masterfile.&quot;ML_UDF_IMH_PANTONE&quot;, IM_90_UDF_IM_Masterfile.&quot;ML_UDF_IMH_FRAG_NUMB&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_PRODUCT_VAR&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE10&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE20&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE30&quot;, IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE_40&quot;
FROM
{ oj (&quot;MAS_HCC&quot;.&quot;dbo&quot;.&quot;ARN_InvHistoryHeader&quot; ARN_InvHistoryHeader INNER JOIN &quot;MAS_HCC&quot;.&quot;dbo&quot;.&quot;ARO_InvHistoryDetail&quot; ARO_InvHistoryDetail ON
ARN_InvHistoryHeader.&quot;InvoiceNumber&quot; = ARO_InvHistoryDetail.&quot;InvoiceNumber&quot
INNER JOIN &quot;MAS_HCC&quot;.&quot;dbo&quot;.&quot;IM_90_UDF_IM_Masterfile&quot; IM_90_UDF_IM_Masterfile ON
ARO_InvHistoryDetail.&quot;SOItemNumber&quot; = IM_90_UDF_IM_Masterfile.&quot;ItemNumber&quot;}
WHERE
ARO_InvHistoryDetail.&quot;LineType&quot; = '4' AND
ARN_InvHistoryHeader.&quot;InvoiceType&quot; = 'I' AND
((IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE10&quot; = 'CLOVE SPC' OR
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE10&quot; = 'APPLE SPC') OR
(IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE20&quot; = 'CLOVE SPC' OR
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE20&quot; = 'APPLE SPC') OR
(IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE30&quot; = 'CLOVE SPC' OR
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE30&quot; = 'APPLE SPC') OR
(IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE_40&quot; = 'CLOVE SPC' OR
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE_40&quot; = 'APPLE SPC')) AND
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_COUNTRY_ORIGIN&quot; = 'USA' AND
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_STYLE&quot; = 'FROSTED' AND
ARN_InvHistoryHeader.&quot;InvoiceDate&quot; >= {ts '2003-09-01 00:00:00.00'} AND
ARN_InvHistoryHeader.&quot;InvoiceDate&quot; <= {ts '2003-09-17 00:00:00.00'} AND
ARO_InvHistoryDetail.&quot;SOWhse&quot; = '001'
ORDER BY
ARO_InvHistoryDetail.&quot;SOItemNumber&quot; ASC,
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE10&quot; ASC,
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE20&quot; ASC,
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE30&quot; ASC,
IM_90_UDF_IM_Masterfile.&quot;LB_UDF_IMH_FRAGRANCE_40&quot; ASC


The fragrance selection that was passed to SQL seems repetitive to me. Have we chosen the best way to write it? I am not complaining it runs fast enough already- just wondering.

I definitely see the importance and benfits of checking the SQL query from Crystal. I hope by following this example I am able to make the same efficiency changes in other reports.

I have learned so much from this post. Thanks for your help.

 

[Ngolem]

I guess the selection for the other items in the record selection were not shown because you selected ALL...hence the result was true...was it?

I have learned a lot from reading synapsevampire's posts. We have a good group of &quot;experts&quot; in this forum...we all learn off of eachother. You would never get this kind of consultation or advice from Crystal Decisions themselves.

It seem repetitive but I don't think it is....I would leave it be...

I will offer one more piece of advice though....this is with respect to Bullet Proofing user input.

for example: You ask your user to enter a value...let us say for sake of argument it is &quot;ALL&quot; and they enter &quot;All&quot; instead....Crystal is case sensitive so the answer is false when the comparison is made.

In my opinion, every user inputed value should be converted to uppercase before a comparison is made. So something like this should be modified to this:

(if ucase({?FragClass})<> &quot;ALL&quot; then
ucase({IM_90_UDF_IM_Masterfile.LB_UDF_IMH_FRAGRANCE_CLASS}) = ucase({?FragClass})
else if ucase({?FragClass})= &quot;ALL&quot; then
True and

now you can tolerate any flavour of user input as long as they spell it right

regards

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[hencyt]

Yes- ALL was chosen for all other parameters. I tested it several times and if any parameter is ALL then it does not show in the SQL query, but if the user makes a selection on it, it does show.

I found that in the parameter setup- if you setup default values (for this report all values are from a prearranged drop down list) and make the TOP value &quot;ALL&quot;- then when the user is prompted to fill in the parameters- he only has to mark the parameters he wants. I am using crystal 8.5 and if you do not make a selection for a parameter- then it automatically chooses the first one from the default list for you. I don't know if it is a fluke or not-but I have used this on several reports. I am glad it does it because my users won't enter ALL- they just skip them and wonder why no records are returned. I can barely get them to click ADD for the parameters they do want...

You are right about user input- that is why I make them choose from a drop down list. I would be wasting my time if I allowed them to type in what they wanted...

Let me know if anyone else finds the same is true about parameters.
Sunny

 

[Ngolem]

no problem....seems you have it under control

Just thought I'd add a parting word about bullet proofing.

Good luck with upgrading other reports

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!