Limting results on a report 1

[ktatpe]

Hello all.

I have created a report that is giving me more information than i need. I used the select expert to try and limit the information, but then I lose half of my information that i'm looking for.

Here's the situation:

I'm trying to see what our customers' entire buying history are that have purchased a product that is considered CLASS "m"

for example:

Customer "x" orders on 3 different Occasions
Order # "1"
purcahses items from CLASS "m", "n",
Order # "2"
purcahses items from CLASS "x", "y"
Order # "3"
purcahses items from CLASS "z"

Because one of his orders includes CLASS "m" product, I need the information for all 3 Orders.
Also, If a customers' Buying history doesn't include CLASS "m", then i don't want it to display on my report.

I have tried using the select expert on the CLASS field.
{table.CLASS}="m"
This only gives me all the CLASS "m" products that the customer has purchased.

Any suggestions would be greatly appreciated. Thank you in advance for your help.

ktatpe

 

[GMcNamara]

You need to use the condition {table.CLASS} = 'm' in a subquery such as:

select customer_id,
order_id,
.
.
.
from customers........

where customer_id in (select table.customer_id from table where table.CLASS = 'm')

The subquery should return a list of all customers who have purchased a CLASS m item. The main query will use this list to return info regarding these customers and ALL purchases they have made.

 

[crystalreporting]

If you only want to work within Crystal, and do not want to utilize a separate query, you could remove any selection criteria that relates to class, and use conditional formatting to suppress the detail section so that only the relevant values are displayed.

Peter Shirley

http://www.linkcorp.com

 

[GMcNamara]

In CR 9, you could use an SQL Command and use the above query to build your report from. Also, in 8.5 you can modify the WHERE clause of the SQL from within the Show SQL Query box. So, regardless of version, the subquery method will work completely within Crystal, no seperate query is needed.

Peter,
I would be curious to see how you would conditionally suppress in this case.

 

[crystalreporting]

GMcNamara - group by order, sort by class - in ktatpe's example, M CLASS orders would be first - set a flag based on this fact, conditionally suppress any orders where the flag was not set, reset the flag when a new group occurs.

Peter Shirley

http://www.linkcorp.com

 

[GMcNamara]

Yes, BUT, ktatpe wants to show ALL orders for anyone who purchased an item of m class. The above will supress only the items of m class. Also, if you had a customer who only had one order, which was of class m, the customer's name would appear on the report, but the order would be supressed.
I am sure it can be done this way but, isn't it easier, and more efficient, to handle it on the server with a simple modification to the SQL?

 

[crystalreporting]

It would work either way - however, without info. to the contrary, I can't assume to know the database type, or driver being used.

Peter Shirley

http://www.linkcorp.com

 

[GMcNamara]

Fair enough.

 

[ktatpe]

Hello Peter and GMcNamara,

First of all Thank you for putting your time and effort into give me some ideas to work off of. I'm brand new to SQL and have some Crystal experience. I wound up using GMcNamara first idea.

What I did was:
-i created a "view" SQL, using GMcNamara's idea of :
select customer_id,
order_id,
.
.
.
from customers........

where customer_id in (select table.customer_id from table where table.CLASS = 'm')

.
-i then added that newly created view into crystal and linked it to my existing tables.
-Instead of grouping by the CustomerId in the customer table
i used the CustomerId of the new table that was created.
-that gave me exactly what i was looking for.

Now i face a new challenge with the report. the boss wants the data limited even more. if i run into any problems with that, you'll be hearing from me again.

Once again thank you both
ktatpe

ps the only reason i didn't try your method peter, is because i wasn't sure how to do it within crystal. if you have a sec, would you mind posting how this could be accomplished?