Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Select Expert help please Post Reply Post New Topic
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 Topic: Select Expert help please
    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?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet 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?
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Dec 2008 at 2:30pm

No such command exists.
Create a formula field called "group2_suppression" with a formula that would give you this: if Avg1 > 140 or Avg2 > 90 then 0 else 1 and insert it at group 2 to test it. You'll have to remove your other conditional suppression to validate it. This should give you a 0 whenever the client name should be suppressed and a 1 when it should be shown.
Do a sum on this field formula field at group 1.
Now your sum will add it all up and if your sum is > 0 you had 1 record that was not suppressed so you want to show it, if it=0 all of your clients were suppressed so you want to hide group1.
Conditionally suppress the header 1 with this if the sum=0 (meaning all of your clients are hidden).

exact same thing for your other issue:
http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5098
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet 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 {@Avg1} > 140 or {@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.
 
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet 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
 
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet 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 {@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
 
 
IP IP Logged
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.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.