Report Design: Select expert for a if formula?

Print Page | Close Window

Select expert for a if formula?

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
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=17159
Printed Date: 03 May 2024 at 4:09am

Topic: Select expert for a if formula?

Posted By: althomas
Subject: Select expert for a if formula?
Date 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.

Replies:

Posted By: kevlray
Date 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.

Posted By: kevlray
Date 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.

Posted By: hilfy
Date 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

-------------
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: althomas
Date 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:

mailto:%7b@% - {@% Variance} < -0.10 OR mailto:%7b@% - {@% Variance} > 0.10

where @% variance =

could the "if" statement have anything to do with it?

Posted By: hilfy
Date 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

-------------
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: althomas
Date 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.

Print Page | Close Window