Crystal Reports - Pull records matching specific criteria from a date range

I want to pull records for a report within a date range where field values are equal to certain conditions.
Below is the formula I created to pull records if the table1 value = 6 AND the table2 value = 1.

It is not working:

Formula Name  = Report Date Range

if {table.field1} = 6  and {table.field2} = 1 then
{elms_lead_status_history.date_modified}
else
Date ({@Null})

I created a Date Range parameter and selection formula as follows:

{@Report Date Range} = {?Date Range}

Thanks
Gary DemosPresidentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Why don't you do it all in the selection formula

 {table.field1} = 6  and {table.field2} = 1
AND
{elms_lead_status_history.date_modified}  =  {?Date Range}

mlmcc
Gary DemosPresidentAuthor Commented:
James, that appears to work for the whole report, but I need to perform a similar routine on a couple other data records in this report, so I'd like to avoid doing it all in the report selection formula. Any ideas?
Gary DemosPresidentAuthor Commented:
Hmmm I guess my previous post doesn't make sense since that is what I was essentially doing in my first attempt with my formula. Is there a way to do this with multiple formulas?
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

mlmccCommented:
What other things are you trying to do?

mlmcc
Gary DemosPresidentAuthor Commented:
I have similar scenarios to pull records on - such as
table.field1 = 4 and table.field2 = 3 then ...

But all of these would fall under the primary Date Range parameter
mlmccCommented:
Do you want all the data then manipulate it or do you only want a subset of the data that meet the criteria?

To get the subset

(
    {table.field1} = 6  and {table.field2} = 1
    OR
    table.field1 = 4 and table.field2 = 3
)
AND
 {elms_lead_status_history.date_modified}  =  {?Date Range}

Open in new window


mlmcc
Gary DemosPresidentAuthor Commented:
I need to get the results (total count when the formula is true)  independently from each scenario, then use the results in an equation to find percentages, ratios, etc. based on the different scenarios.

Thanks - (I've been off during the holidays)
mlmccCommented:
So you want to pull all records from a particular date range then calculate summaries and percentages based on given scenarios is that correct?

If so then use the date range as the report selection criteria
{elms_lead_status_history.date_modified}  =  {?Date Range}

Open in new window


You can then create formulas to get the counts for the various scenarios


Scenario 1
Name - IsScenario1
If {table.field1} = 6  and {table.field2} = 1 then
     1
else
    0

Open in new window


Calculate percentage that meet Scenario 1
   Sum({@IsScenario1} / Count(Table.keyfield}) * 100

Open in new window


Similarly for each scenario

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary DemosPresidentAuthor Commented:
Perfect! - Thanks James!
James0628Commented:
I'm not sure why you referred to mlmcc as "James", twice.  I don't think that's his name.  Maybe I helped you with another question and you got confused?  Anyway, I just happened to be following this question and just felt like I had to say something.

 James
Gary DemosPresidentAuthor Commented:
Thanks for the clarification.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.