Solved

Report on a single record when there are multiples

Posted on 2012-04-10
12
320 Views
Last Modified: 2012-04-11
I need to create a report that reports on a single client per case and calculates unduplicated counts of clients served.

The simplest of these reports is for gender. If there are two (or more) clients in the case, I need to report the gender of one of them. And if the client I report on has more than one case, I need to count that client only once in the report.

My first attempt was to do a distinct count of ClientID grouped by gender. That gets the unduplicated count but it counts every ClientID in a case, so if there's a male and a female client in the case, they get counted once as a male and again as a female (or as two females if there are two female clients, etc.). I have to count one or the other, not both.

I've attached a file that has screen shots in it of the report design, the Database Expert links, and the output.

I've tried all sorts of groupings including ClientID (the field is NameCardID), CaseID (the field is FileID), @Gender (a formula that returns "Male", "Female", or "Unknown"), and various combinations of the three.

Where do I go from here?
Report-Design.docx
0
Comment
Question by:chenegar
  • 6
  • 3
  • 3
12 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 37828870
WHat data do you have?

Is it 1 record per case or do you have joined tables so you get multiple records per case if a case has multiple clients?

If Joe has a case and Joe and Tom have a case is that counted as 1 or 2?

I don't think this can be done with just grouping.

mlmcc
0
 

Author Comment

by:chenegar
ID: 37828935
I have joined tables so I get multiple records per case if a case has multiple clients.

There's a case table and a clients table and a third table that assigns clients to cases.

If Joe has a case and Tom & Joe have a case, that's 2 cases but it should be counted as only 1 client.
0
 

Author Comment

by:chenegar
ID: 37829037
Given all my experimentation, I'm confident it can't be done with grouping!

Does that mean I need help with setting up formulas and passing values? I've figured out how to get a single ClientID to show up in the report (put ClientID in the Case group footer) but I can't figure out what to do next.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37829064
The problem is you need to track the clients who have been counted

Consider this
Case 1      Joe
Case 2      Joe Tom Harry
Case 3      Tom Harry

Is that 1 or 2?

Are you just trying to get a summary or are you trying to show some details?

mlmcc
0
 

Author Comment

by:chenegar
ID: 37829281
That's 2 clients. Cases 1 & 2 have 1 client. Case 3 has 1 client.

I'm trying to get a summary: An unduplicated count of all clients that are male, female, or unknown.

And it gets more complicated. In another report I need to report on income data that's stored only at the case level but needs to be reported for unduplicated clients.

The Joe Tom Harry case makes me realize just how complicated this is going to get. That kind of case doesn't occur very often.

The most common kind of cases with multiple clients is:

Case 1    Joe
Case 2    Joe Tom

This is 1 client.

Less often is this type:

Case 1   Joe
Case 2   Joe Tom
Case 3   Tom

That's 2 clients.

Perhaps I should consider a SQL view that gives me one client per case?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 400 total points
ID: 37830962
I have trouble understanding why TOM from case 2 doesn't count but TOM from case 3 does.

How would you choose the one client per case

Consider

Case 1   Joe Pete
Case 2   Joe Tom
Case 3   Tom Pete

Is that 2 or 3?
If you choose Pete, Joe, Tom it would be 3
but if you choose  Joe, now case 2 doesn't count, then choose either Tom or Pete gives 2

Case 1   Pete Joe
Case 2   Joe Tom
Case 3   Tom Pete
Is that any different?

Is there an indication of which is the primary client?

mlmcc
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 34

Assisted Solution

by:James0628
James0628 earned 100 total points
ID: 37831309
Going back to your original post:

 > That gets the unduplicated count but it counts every ClientID in a case, so if
 > there's a male and a female client in the case, they get counted once as a male
 > and again as a female (or as two females if there are two female clients, etc.).
 > I have to count one or the other, not both.

 So, if you have something like:

Case 1  Pete Sarah

 That will count as one male, or one female, client (one or the other)?  What's the point of a gender count when you are arbitrarily not counting all of the genders?  It's like if you wanted a count of the clients that were over 50, but when there were multiple people associated with a case, you randomly picked one and checked their age, and ignored the rest.  What could that really tell you?

 Maybe it makes sense to you, but I'm really not seeing it.  The better we understand what you're trying to do, the more likely it is that we'll be able to help.

 James
0
 

Author Comment

by:chenegar
ID: 37832734
It only makes sense to me because that's what I'm required to do. It's a report for a government agency and the directions state very clearly that we are to report only one client per case and it is to be an unduplicated count.

I get that they're trying to find out how many people were assisted, which is where the unduplicated count comes from, but I don't see why they don't want to know the full count.

As an aside, I created a report for another government agency that required the same thing and required that client data not change (like household size and income, both of which change with some regularity in our client population). For that report I pull data from the production database then put it into an Access database to "freeze" it. Whenever I have to report on that client, I use the original "frozen" data regardless of whether it has changed.

So in Michael's first example . . .

Case 1   Joe Pete
Case 2   Joe Tom
Case 3   Tom Pete

. . . there would be two clients counted, not three. That's because there can only be one client per case. If I count Joe in Cases 1 & 2, I can count only Tom or Pete in Case 3.

In the second example, there would also be only two clients:

Case 1   Pete Joe
Case 2   Joe Tom
Case 3   Tom Pete

If I count Pete in Case 1, I also have to count him in Case 3. Then I'd count either Joe or Tom in Case 2.

Remember, I'm not counting cases, I'm counting clients but I can only have one client per case. I count cases in a different report that is straightforward.

Since there is no identification of a primary client, I've decided that the client who is created first (and has a "lower" ID) will be the primary client. That person is usually the one who called for help. The second client gets added later as details emerge. I know there are situations when I'll count a client that I shouldn't, but I can't think of another way to decide on the primary client.

Here's an example: A person calls for help with a foreclosure. A case is created for him and while getting details on the case the advocate learns that his wife is also on the mortgage. Now we have two clients and one case. As far as I'm concerned, two people have been helped. But for this report, I'll report only on the husband because his record got created first.

If the wife then calls for help on another problem and her husband is also a client, the husband will be counted as the primary client because his existing record was created first.

I know it doesn't make sense.
0
 

Author Closing Comment

by:chenegar
ID: 37832942
Although we didn't come up with a solution in Crystal Reports, your questions really helped me to think through what I needed to do. My last post made me realize I needed a SQL view to solve this problem.

I created a view that selects the "minimum" ClientID, which gives me one client per case. I plugged that view into my Crystal Report and now I'm getting the one client per case that I need.

It's crazy that I have to do this, but "[mine] is not to reason why."
0
 
LVL 34

Expert Comment

by:James0628
ID: 37833544
FWIW, this does seem like the kind of thing that might be better handled by manipulating the data before it gets to the report, so doing it in SQL might be the best solution.

 I still don't really get it.  It just seems so arbitrary.  But as long as you're getting what you need.  :-)

 James
0
 

Author Comment

by:chenegar
ID: 37833600
Thanks, James.

I agree that Crystal Reports wasn't the right tool, but I had to work through it before I realized that.

It doesn't seem arbitrary, it is arbitrary. I think it may actually be a holdover from the days when case management systems wouldn't let you have more than one client. Still . . .
0
 
LVL 34

Expert Comment

by:James0628
ID: 37833728
You gotta do what you gotta do.  :-)

 James
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now