Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: Select expert for a if formula? Post Reply Post New Topic
Author Message
althomas
Newbie
Newbie


Joined: 09 Nov 2010
Online Status: Offline
Posts: 36
Quote althomas Replybullet Topic: Select expert for a if formula?
    Posted: 30 Jul 2012 at 8:29am
Hello,
  I was wondering if it was possible use the "Select Expert" for a group. The information in the group is created using a "IF" formula, so when i try to use the select expert, i get the message "the formula cannot be used because it must be evaluated later". Anyone know how i can get around this issue.
basically what i did was created a percent formula:
if Sum ({BAQReportResult.TotalVariance}, {BAQReportResult.PartTran.JobNum}) = 0 then 0 else
(Sum ({BAQReportResult.TotalVariance}, {BAQReportResult.PartTran.JobNum}) /
Sum ({BAQReportResult.ActualTotal}, {BAQReportResult.PartTran.JobNum})) * 100
 
I place this formula in one of the groups i created. I wanted to only see jobs which have a >10% or < -10%. When i try to use the select expert for this group, i get the error.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 30 Jul 2012 at 10:41am
Basically you cannot do a 'selection' based on a summary.  If you can create an SQL statement (Stored Procedure, View?) with the summary information as part of the results, then you can filter on that.

I hope this helps.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 30 Jul 2012 at 10:45am
I just remembered, you can do some filtering with summaries in a group.  You may be able to do a group selection (it has been a while since I have done it).

I hope this helps.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 30 Jul 2012 at 11:17am
You can definitely set up group selection criteria that use summaries.  I would take your code above and use it to create a formula (I'll call it {@percent} here.)  Then, in the group select formula, put something like this:
 
{@percent} < -10 or {@percent} > 10
 
-Dell
IP IP Logged
althomas
Newbie
Newbie


Joined: 09 Nov 2010
Online Status: Offline
Posts: 36
Quote althomas Replybullet Posted: 31 Jul 2012 at 2:18am

i was getting the error message in the group selection for the "the formula cannot be used because it must be evaluated later" error.

This is what i used for a formula in the group selection:
{@% Variance} < -0.10 OR {@% Variance} > 0.10
 
where @% variance =
if Sum ({BAQReportResult.TotalVariance}, {BAQReportResult.PartTran.JobNum}) = 0 then 0 else
(Sum ({BAQReportResult.TotalVariance}, {BAQReportResult.PartTran.JobNum}) /
Sum ({BAQReportResult.ActualTotal}, {BAQReportResult.PartTran.JobNum})) * 100
 
could the "if" statement have anything to do with it?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 31 Jul 2012 at 2:28am
The "if" is not the problem - the "sum" is the issue.
 
How are your SQL skills?  I can see a way around this by using a Command, which is a SQL select statement.  This method would also filter data in the database instead of having Crystal process it in memory.
 
-Dell
IP IP Logged
althomas
Newbie
Newbie


Joined: 09 Nov 2010
Online Status: Offline
Posts: 36
Quote althomas Replybullet Posted: 31 Jul 2012 at 2:40am
unfortunately, my SQL skills are below novice. i guess, i am going to have to show the whole report and use "Hightlight" expert for anything over or under 10% to show in different color.
IP IP Logged
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.