Filtering grouped records in crystal reports

Question

I have an issue that I'm not sure how to overcome. I need to filter a my groups in crystal reports based on a field within the group. Not only that I need it to filter the groups based on if there are two different values in this field within the same group. For example, say I have a table of issues and votes for a council. There is one entry per vote on an issue containing the issue name and the vote cast (either yes, no, or abstain). I will group the table by issue name and I want to filter the groups to show only the issues with a vote split between yes and no (i.e. no abstains and not unanimous). How do I go about doing this?

you need to create 2 formulas with if else conditions and place both in details or footers as per your requirement — Siva
You should post the code that you've tried already as part of your question's body. — ManoDestra

David T. Macknet David T. Macknet · Accepted Answer · 2016-04-13T16:39:04

The data you're feeding into the top-level of the grouping needs to be pre-aggregated so as to show which Votes have multiple different responses. You'd need to do this in whatever your back-end data source is. If I were to do it in SQL, e.g., given the tables:

create table dbo.Issues (
IssueID int identity(1,1) not null ,
constraint pkc_Issues primary key clustered ( IssueID ) ,
IssueText varchar(1000) )

--Note - not putting anything unique on VoterName because there may be 2 Joe Blows in the voter population.
create table dbo.Voters (
VoterID int identity(1,1) not null ,
constraint pkc_Voters primary key clustered ( VoterID ) ,
VoterName varchar(512) not null ) )

create table dbo.Votes (
VoteID int identity(1,1) not null ,
constraint pkn_Votes primary key nonclustered ( VoteID ) ,
VoterID int not null ,
constraint fk_VoterID@Votes foreign key ( VoterID ) references dbo.Voters ( VoterID ) ,
IssueID int not null ,
constraint fk_IssueID@Votes foreign key ( IssueID ) references dbo.Issues ( IssueID ) ,
constraint uci_IssueID_VoterID@Votes unique clustered ( IssueID , VoterID ) ,
VoteResponse varchar(16) null )

I'd pull the data using multiple steps (but feel free to do subqueries, if you think that's more understandable):

select IssueID , Count(VoteResponse) as ResponseCount
into #hasMultiple from (select distinct IssueID , VoteResponse from Votes)

I'd then join back to that, to feed to Crystal:

select dbo.Issues.IssueID ,
dbo.Issues.IssueText ,
cast(case when #hasMultiple.ResponseCount > 1 then 1 else 0 end as bit) as HasMultiple ,
dbo.Votes.VoteID ,
dbo.Votes.VoterID ,
dbo.Votes.VoteResponse ,
dbo.Voters.VoterName
from dbo.Issues
inner join dbo.Votes
on dbo.Issues.IssueID = dbo.Votes.IssueID
left join dbo.Voters
on dbo.Votes.VoterID = dbo.Voters.VoterID
left join #hasMultiple
on dbo.Issues.IssueID = #hasMultiple.IssueID

Filtering grouped records in crystal reports

1 Answers