Select Expert help please
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5115
Printed Date: 27 Apr 2024 at 2:39am
Topic: Select Expert help please
Posted By: Takesen
Subject: Select Expert help please
Date Posted: 30 Dec 2008 at 12:15pm
|
Alright so, again i'm still quite new to CRXI
my situation currently is basically this.
I have values that i'm adding up, and a count to count how many values
deviding total by the count to bring the average.
Now, i was wondering is it possible to use the formula for the average in the select expert so say;
Avg1 > 140 or
Avg2 > 90
or am i going to need to do a conditional suppression?
|
Replies:
Posted By: hilfy
Date Posted: 30 Dec 2008 at 1:24pm
|
You're going to have to use conditional suppression - you can't use summary formulas (count, sum, avg, etc.) in the Select Expert.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: DBlank
Date Posted: 30 Dec 2008 at 1:36pm
|
if the average was created directly by the Summary function (like using the calculate this summary as "Average" on a numeric field) you could use it in your select expert. if you are using a formula field that takes a summary to get your average (I believe this is what you described you are doing) you cannot use that in the select expert and would have to conditionally suppress it. .
The formula field cannot be used in the select expert because it must be calculated after all of the data is presented then summed and then averaged.
|
Posted By: Takesen
Date Posted: 30 Dec 2008 at 2:14pm
|
Alright, i just wanted to see if there was a possible other solution. Thank you.
If you don't mind i have one more question to a situation i've been having a lot lately...
I'm grouping inside of groups i.e.;
Department
------ Client name
I'm suppressing the Client name when
Avg1 > 140 or
Avg2 > 90
as said above.
And what i want to do, is If ALL Client names have been suppressed under any given department i want the department name suppressed.
I've tried a count but it counts the suppressed names.
I've tried my own count formula which still is counting the suppressed names.
is there a command in CRXI perhaps to count JUST unsuppressed data?
|
Posted By: DBlank
Date Posted: 30 Dec 2008 at 2:30pm
|
No such command exists. exact same thing for your other issue: |
Posted By: Takesen
Date Posted: 30 Dec 2008 at 2:52pm
| Yea, it is but for some reason, i'm not sure why but i can't put a sum summary on my "group2_suppression" formula like i could for my other issue. |
Posted By: DBlank
Date Posted: 30 Dec 2008 at 3:27pm
|
Hmmm. You are correct, that trick won't work there. Sorry about that.
You might be able to use conditional running totals but I will have to think about that.
Let's rule out the easy way first. How are you calculating the Avg1 and Avg2?
Can you use the Summary Average function to get these instead of the formula you described first? If so you could then go ahead and use that Summary in your select expert to filter the unwanted records out making your counts and supression of group1 very easy.
Try it to see if the numbers come out the same as your formula fields.
In case you have not used the average summary before, select the field you want to average, click on the insert summary, change the calculate this summary to "Average" and pick the summary location of group 2 (i think that is where you want it) to test it out.
|
Posted By: Takesen
Date Posted: 30 Dec 2008 at 3:47pm
|
alright i'm getting the avg1 and avg 2 like this; Avg1 Sum(data1, person.name)/ DistinctCount (person_encounters, person.name)
Avg2 Sum(data2, person.name)/DistinctCount (person_encounters, person.name)
then i took the formula you gave me
if mailto:%7b@Avg1 - {@Avg1 } > 140 or mailto:%7b@Avg2 - {@Avg2 } > 90 then 0 else 1
which is working fine. i attempted to use the running total field,
but i'm not too sure how i would set it up in this situation. or if it's even usable which from your describtion earlier it didn't sound like it.
|
Posted By: Takesen
Date Posted: 30 Dec 2008 at 4:12pm
|
i kind of wanna do something like this, but it doesn't seem to be very accuate and unreliable but i was thinking something like this...
in group 2:
global numbervar icount;
if Avg1 > 140 or Avg2 > 90 then
icount := icount + 1
in group1: (to reset the count at each change of department)
global numbervar icount;
icount := 0
then suppress Group1 conditionally:
if icount > 0
|
Posted By: Takesen
Date Posted: 30 Dec 2008 at 4:55pm
|
i believe i got it. Just have 1 obvious error i'll have to reevaluate it in the morning BUT what i did to suppress group one was this;
1.created a grp2_sum:
if avg1 > 140 or avg2 > 90 then 0 else 1
2. created a count "icount":
shared numbervar icount; //global numbervar didn't work so i had to use shared.
if mailto:%7b@group2_sum - {@group2_sum } = 1 then
icount := icount + 1
else
icount := icount //had to do this otherwise it kept going stating 0.
3. created a reset for icount to place in group 1:
shared numbervar icount;
icount := 0
4. created another shared number var to store icount into at end of each grp:
shared numbervar icount;
shared numbervar isumcount;
isumcount := icount
5. put condition into group1 to suppress using the section expert:
shared numbervar isumcount;
isumcount = 0 //suppresses grp1 if isumcount = 0
it seems to be working fine EXCEPT for the first department.
i'm not too sure why but it's suppressing the first department even tho the count = 5. after that it works perfectly. or so it appears i'm on my way out the door and i will double check the rest of the report in the morning. any advice until then is appreciated.
thank you for all your help =)
-Takesen
|
Posted By: DBlank
Date Posted: 31 Dec 2008 at 8:07am
|
If you are still having problems I may have another approach. I tested and you can use running totals to conditionally suppress. Ithink the below woud do what you need. Create a running total summarizing your client name as a distinct count. set the Evaluate to use a formula. Make your formula the same conditions we used for your formula field "avg1 > 140 or avg2 > 90". In the Reset have it reset on change of group 2. Place this running total in the group footer 2 (running totals don't work in headers). You should get a distinct count of the clients that you are suppressing. Create a Summary of a distinct count of clients at group 2. now you have a total of clients and a total of suppresed clients that you can compare to conditinally suppress group1 (running total=summary total : the 2 fields you just created).
|
Posted By: Takesen
Date Posted: 31 Dec 2008 at 8:39am
|
Hmm, can't seem to fully understand you're idea.
when i rest on group 2. all i get is another 0 or 1 count. which the count doesn't seem to be accurate either.
As for my concept. i still can't figure out why the very first department is getting suppressed either.
|
Posted By: Takesen
Date Posted: 31 Dec 2008 at 9:21am
|
Alright, so i tried something else, still seems to ALMOST works, but not quite...
i created an additional 2 formulas;
avg1>140:
shared numbervar avg1;
shared numbervar avg1value;
if avg1 > 140 then
avg1value := 0 else
avg1value := 1
avg2>90:
shared numbervar avg2;
shared numbervar avg2value;
if avg2 > 90 then
avg2value := 0 else
avg1value := 1
i made it this way because i don't want to see the suppressed.
if all are suppressed in that department i want to keep it at 0 so i could suppress >0.
after these two formulas were made i went backing into the running total editor:
summarizeing client_name
distrinct count
evaluate:
use formula:
shared numbervar avg1value;
shared numbervar avg2value; avg1value = 0 or avg2value = 0 reset on change of group1 (department)
my only issue with this is it's still showing a count of 1 even when all has been suppressed. and if there's only 1 client name under the department then it's still coming up as 1 aswell.
|
Posted By: DBlank
Date Posted: 31 Dec 2008 at 9:44am
Sorry, I need some more coffee  . In my testing of this I only had 1 group and my suggestion was not accurate. Maybe with some tweaking it may work. Did you try using the Summary average function I suggested before. Without seeing your data it is hard to tell but when I tried to replicate your average formula for avg1 against the average summary function on some data here I was getting the same results. If you could replace your avg1 and avg2 formula fields with the summary function you could just use them in your select statement to omit all of your records that you currently have to suppress. That would make your suppression at group 1 a lot easier to handle.
|
Posted By: Takesen
Date Posted: 31 Dec 2008 at 9:53am
|
Haha, it's all good man =) i think we went threw the summary part before... but i'll look back into it. i could have over looked something. i believe i was having trouble working with the summary before and the varables made it simpler to munipulate with other functions. |
Posted By: Takesen
Date Posted: 31 Dec 2008 at 10:07am
Alright. i feel plan stupid now 
haha. Yea. I was able to use the average summary. and then entered into select expert and it seems to work fine now...
i'm gonna keep looking threw the report to make sure there's no errors but yea. it's looking fine now. and since it went threw the select expert those departments that have no clients that meet those critiria they're eliminated aswell.
I'll letcha know how it turns out. Thank you
|
Posted By: Takesen
Date Posted: 31 Dec 2008 at 10:30am
|
alright it looks fine.
=D
my only concern about this report now is. i used the select expert before to "choose these codes, and exclude these ones"
and since they're from the same field i'm not sure if it's working right or rather if i wrote it correctly.
{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"] and
{client_code_id} <> ["250" to "250.93", "362.00" to "362.02", "357.2", "366.41"] i'm not sure if the 2nd half of my statement is really written correctly...
|
Posted By: DBlank
Date Posted: 31 Dec 2008 at 10:36am
|
Isn't the second half unnecessary? they are referencing the same field in the same table correct. the first part {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"] would already omit any of the items in teh second half.
|
Posted By: Takesen
Date Posted: 31 Dec 2008 at 10:45am
|
Normally i would think so, but since they can have more than 1 it would not. sorry i edited the actual field name (since a lot of what i'm working with is confidencial) but it's more along the line of things they have.
client_diag_code_id would be a better relable i guess. kinda like... say a computer.... it's motherboards's bad (402.00) graphic's card went bad (402.1) harddrive failed (402.11) they could have multiple of these at 1 given time. and i want to exclude certain ones. so say the one's i wanna exclude are viruses that were diagnosed.
so if the graphic's card's bad and he has a virus.
i still don't wanna see that on my report.
does that make sense?
|
Posted By: DBlank
Date Posted: 31 Dec 2008 at 11:10am
| are both parts referencing the same field in the same table or is part 2 referencing another field? |
Posted By: Takesen
Date Posted: 31 Dec 2008 at 11:13am
| they're both refering to the same field on the same table yes. |
Posted By: DBlank
Date Posted: 31 Dec 2008 at 11:26am
|
I must be missing something then. 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.
|
Posted By: Takesen
Date 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.
|
Posted By: DBlank
Date 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?
|
Posted By: Takesen
Date Posted: 31 Dec 2008 at 11:57am
|
excluse both rows. i don't even want that client's name to show up. |
Posted By: DBlank
Date 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?
|
Posted By: Takesen
Date 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. |
Posted By: Takesen
Date 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?
|
Posted By: DBlank
Date 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. |
Posted By: Takesen
Date Posted: 31 Dec 2008 at 2:42pm
|
And you sir are genious =)
thank you once more
|
Posted By: DBlank
Date Posted: 31 Dec 2008 at 2:48pm
|
Not even close but thanks. I also think you are on the right track with your other process. I assume you summed these at the client group level.Note that my views above do not take that into account. They will exclude clients only base on codes and clients and are not look at the department at all. your process above is doing this if you summed at group 2. Not sure if you have clients that crossover departments. If so you need to decide how you want to address it. You can always adjust the views to include them, group and join as necessary depending on how you need to handle it.
|
Posted By: Takesen
Date Posted: 31 Dec 2008 at 3:11pm
|
Yes, they are grouped in grp2. it seems to look fine. just need to verify.
and i honestly hate it still showing the large amount of records when there's only about 1500ish being displayed. i was thinking it would bring the numbers back down since i was using the select expert again. But perhaps it's because it's still a selection after the report's created? much like as if i were to use a suppress formula?
|