Solved

Report on a single record when there are multiples

Posted on 2012-04-10
12
321 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

14 Experts available now in Live!

Get 1:1 Help Now