Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Select Expert help please Post Reply Post New Topic
<< Prev Page  of 4 Next >>
Author Message
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet Posted: 31 Dec 2008 at 11:13am
they're both refering to the same field on the same table yes.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Dec 2008 at 11:26am

I must be missing something then.Shocked

The statement {client_code_id} in ["362.11", "401.9", "402.00", "402.01", "402.1", "402.10", "402.11", "402.9", "402.90", "402.91", "403.00", "403.01", "403.11", "403.91", "404.00", "405.01", "405.09", "405.11", "405.19", "405.91", "405.99", "425.1", "437.2", "997.91"] will only return rows where any of these items is found. Since these items cannot be one of the items listed above AND one of the items listed in the second half or your select statement (and {client_code_id} <> ["250" to "250.93", "362.00" to "362.02", "357.2", "366.41"]) it is not needed.
It evaluates each row seperately.
If you have a customer with 5 rows of data with 5 different problems the first part of youselect statement would evaluate each row passing or failing it. I think you are trying to account for the grouping where you do not need to.
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet Posted: 31 Dec 2008 at 11:50am
i just need to make sure that
 
first part of the statement is there.
 
BUT
 
if the first part of the statement is TRUE and they also have something from the list of exclusions. i DO NOT want it on the report.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Dec 2008 at 11:56am
Sorry I just want to be clear on this...
Example:
Client A has 2 rows of data: Row 1 has {client_code_id} = 362.11  and Row 2 has {client_code_id} = 250.
Do you want to include Client A with Row 1 and exclude row 2
or
exclude Client A and both rows 1 and 2?
 
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet Posted: 31 Dec 2008 at 11:57am

excluse both rows.

i don't even want that client's name to show up.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Dec 2008 at 12:15pm
Is this a SQL data source? If so I would do this by using views. It may be possible to do what you want in your select statement but I do not know how. Ususally your select statement is in reference to meeting conditions in one row per table not conditions on multiple rows in the same table.
Can anyone else assist on this one?
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet Posted: 31 Dec 2008 at 1:06pm
Yes we are using a SQL database source. i've never used views really before. i know i've suppressed on condition before like the first solution you gave me the other day. but it's a little more tedious and would really rather attempt to find a more efficient way.
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet Posted: 31 Dec 2008 at 2:24pm
Alright, tell me what you think please =)
 
i made:
client_code_idinclude:
if {client_code_id} in ["250" to "250.93", "362.00" to "362.02", "357.2", "366.41"] then
    1 else 0
 
and client code_idexclude:
if {client_code_id} in ["250" to "250.93", "362.00" to "362.02", "357.2", "366.41"] then
    1 else 0
 
then did sums of these.
 
then i used the select expert
sum of @client_code_idinclude >0
and another for
sum of @client_code_idexclude = 0
 
i THINK it's working. still kind of trying to go back and make sure...
does this logically make sense? any foresight of horrible failure?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Dec 2008 at 2:32pm
If you want to try it with views it will be hard to walk you through it this way but I can try. There is probably a more elegant case statement process but here is one way. I use a lot of views to manipulate and pair down the data before i bring it into crystal.
Go into Enterprise Manager and under your DB and right click on views.
Select new view.
Add your client code table to the view.
Add the client name and client code into the view.
Group by client name
Uncheck the output next to the clientcode field (you only need the client name for this).
in the criteria on the client code field add your select statement (in ["362.11", "401.9", "402.00", "402.01", "402.1", "402.10", "402.11", "402.9", "402.90", "402.91", "403.00", "403.01", "403.11", "403.91", "404.00", "405.01", "405.09", "405.11", "405.19", "405.91", "405.99", "425.1", "437.2", "997.91"])
Save the view. I highly recommend using a good naming structure for views or you will get lost in them later.
Add another view and repeat the process but this time in the criteria get your second set of criteria ({client_code_id} in ["250" to "250.93", "362.00" to "362.02", "357.2", "366.41"])). here you want to find these not exclude them.

Create a third view but instead of adding a table add the first and second views you just created and join them on the client name (only item in each view) and make the join as <>. Add the client name from view1 to this view. You now have alist of clinets that meet your criteria.
Basically you are creating a table (view1) that lists all of clients you MAY want to show in the report (condition 1 met). In table/view2 you are creating a list of all clients you MAY want to exclude from the report (condition 2 met). View 3 compares them and gives you a list of clients that you ultimately do want to include (conditionas 1 and 2 met).
From here you just add view3 into your report and inner join it back to your table on the client name. This will then only return records from your table where these are matching and remove the need for your select statement.
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet Posted: 31 Dec 2008 at 2:42pm
And you sir are genious =)
thank you once more
IP IP Logged
<< Prev Page  of 4 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.