Plan to make this view? 1

[RiverGuy]

Hi. I would like some advice on where I should accomplish this query, whether to do it in Crystal, or within SQL Server.

This is what I got:

I have a table where one field is a person identifier. The person can have multiple records, because there are multiple dates, and multiple reasons.

But he can appear only once per date, per reason.

What I would like to do is have a report broke down by date and "reasons". I would like to have date groups, then reason groups, and each persons record within.

But I would like to omit everyone who has more than one reason, and make a group that is "multiple reasons."

My problem is this: I have made a view in SQL Server where I can get all the people who have multiple reasons. But I cannot display the date in the view, because it will group by the date as well, not just by the person.

So, if I omit everyone in the Crystal report that has more than one reason, it will omit pretty much everyone because that view covers all dates. I would only like to omit people who have more than one reason for each date.

Thanks in advance for any suggestions.

 

[Naith]

I'm going to 'fess up up front, and admit that there's a good chance that I only have a partial appreciation of your problem here, but in concept it sounds like what you're attempting to do should be achievable from:

Group on Date, then Reason, then Person.
In the details section, place the person identifier field, and make a running total on it, set to count/reset on change of Person group.
Place the person details to be displayed in Group Footer 3 (Person footer), with a conditional suppression of {#RTotal0}>1 on the section.
Suppress the Person header, and details section.

This will only display, in the lowest level group footer, the people with one reason per date.

Naith

 

[RiverGuy]

Running total--that's the key. My only problem is I don't want all the details. Its OK though, I can probably just hide them. I want summaries of each reason....

But that running total is a good idea man.