Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: return results based on mixed conditions Post Reply Post New Topic
Author Message
Female
Newbie
Newbie
Avatar

Joined: 25 May 2016
Online Status: Offline
Posts: 2
Quote Female Replybullet Topic: return results based on mixed conditions
    Posted: 27 Oct 2016 at 7:10am
I'm sure this is simple but I just can't wrap my head around it.

Fields are as follows:
product# purchase year dept sub debt

I need only those with dept equal to hardware or software and sub debt equal to 0000 for hardware and 0001 for sub debt for the same years.

So....for example:

product# purchase year dept sub debt
111 2013 hardware 0000
111 2013 software 0000
111 2014 hardware 0000
111 2015 hardware 0000
111 2015 software 0001
111 2016 hardware 0000

I would ONLY see the results for 2015 in my query. I already have a formula set up to return duplicated years but I need it to drill further to only return the corresponding debt and sub debt.

Thanks in advance!
IP IP Logged
Female
Newbie
Newbie
Avatar

Joined: 25 May 2016
Online Status: Offline
Posts: 2
Quote Female Replybullet Posted: 28 Oct 2016 at 4:15am
In case anyone is interested I found a solution. I joined the table to itself via aliases. Here's the example I found. Hope it helps the next person.

SELECT

t1.field1,
t2.field1,
t1.field2,
t2.field2,
t1.field3,
t2.field3

FROM

    `tablename` t1

JOIN

    `tablename` t2

ON

    t1.field1 = t2.field1 and t1.field2 = t2.field2

WHERE

    t1.field1 = 'search criteria1' and t1.field2 = 'search critera2'

AND

    t2. = 'search criteria x' and t2.field2 = 'search critera y"
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 02 Nov 2016 at 5:43am
Hi,

In record selection use below formula: (Note : please replace the fields with exact field in your report)

Report-- Selection Formulas-- Record :

{Sheet1_.purchase} in ['Software','Hardware'] and

({Sheet1_.purchase} = 'Hardware' and {Sheet1_.dept} = '0000'
or
{Sheet1_.purchase} = 'Software' and {Sheet1_.dept} = '0001')


Report-- Selection Formulas-- Group :

count({Sheet1_.purchase},{Sheet1_.year}) = 2

Thanks,
Sastry
Thanks,
Sastry
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.