| Author |
Message |
aliryder
Newbie
Joined: 20 May 2015
Online Status: Offline
Posts: 17
|
   Topic: Summarizing data with one-to-many relationships
Posted: 28 May 2015 at 7:04am |
|
Hi there,
I have a database that collects the income information of service users. I'm trying to generate a report summarizing the incomes of clients. However, there are a few hiccups.
Clients are stored in a table with the primary key of ClientID. Incomes are stored in a table with a primary key of ClientIncomeID, which of course links back to ClientID. However, a client can have multiple sources of income (i.e. welfare, employment, child support) and each source is a distinct ClientIncome.
ClientIncome includes a field called MonthlyAmount, which is the one I'm looking at. It also, for the record, has IncomeTypeID, which links to another table, but I'm not sure if that's relevant.
I created a running total field, called TotalMonthlyIncome that summarizes MonthlyAmount (sum), Reset on change of field ClientIncomes.ClientID.
When I display this in details, displaying the fields ClientID, MonthlyAmount, and then TotalMonthlyIncome, it works as intended.
However, for my purposes, I don't want to know the individual MonthlyAmounts, I only want the grand total, so if a client has 2 income sources, I only want the total value (which is why I created the running total), but I'm not sure I'm doing it correctly.
I want to create a Group that displays income in some form if it is in a range, say, 0-500, 501-1000, 1001-1500, etc. I know how to do that part, but I'm basically struggling with the correct usage of either a running total field or maybe a formula field that gets at the total.
In the end, I would like to create a cross tab, that has the Grouped incomes on the left and other stuff across the top.
Any assistance would be appreciated!
|
 IP Logged |
|
|
Erik
Groupie
Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
|
Posted: 28 May 2015 at 9:14am |
I think what you'd want to do is group by ClientID while keeping your information in the Details section. Then in the Group Header or Footer you can add a summary of Income per Client.
sum({@ClientIncome}, {@Client})You could use that sum value provided to do your 0-500, 501-1000, etc. breakdowns to return different results in a Formula.
|
|
IP Logged |
|
aliryder
Newbie
Joined: 20 May 2015
Online Status: Offline
Posts: 17
|
Posted: 28 May 2015 at 9:40am |
|
Wow, quick response and good answer! That did exactly what I need. Sorry, I'm new to Crystal.
Thank you!
|
|
IP Logged |
|
aliryder
Newbie
Joined: 20 May 2015
Online Status: Offline
Posts: 17
|
Posted: 02 Jun 2015 at 7:16am |
So, to follow up, I now have a Formula Field called Formula_TotalMonthlyIncome which is
Sum ({HIFIS_ClientIncomes.MonthlyAmount}, {HIFIS_Clients.ClientID})I've made a second formula called IncomeBracket, which is
if {@Formula_TotalMonthlyIncome} < 501 then "$0 - $500"
else if {@Formula_TotalMonthlyIncome} < 1001 then "$501 - $1000"
else if {@Formula_TotalMonthlyIncome} < 1501 then "$1001 - $1500"
else if {@Formula_TotalMonthlyIncome} < 2001 then "$1501 - $2000"
else if {@Formula_TotalMonthlyIncome} < 2501 then "$2001 - $2500"
else "$2500+"I would like to group based on Income Bracket, but group expert only allows you to group on fields. So right now I have it grouped by clientID, and GH and Details are suppressed and we have simply Formula_TotalMonthlyIncome (or you could use Sum of MonthlyIncome). How do I take the next step?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 Jun 2015 at 7:40am |
you can't group on that value as you have calculated it using group data.
you can sort on it.
why do you need the group?
|
|
IP Logged |
|
aliryder
Newbie
Joined: 20 May 2015
Online Status: Offline
Posts: 17
|
Posted: 02 Jun 2015 at 8:03am |
|
In the end, I want to say there were 5 people with an income of $0-500, and 7 people with an income of $501-1000, etc. etc.
But I'm starting with distinct income sources (i.e. welfare, employment, child support), so first I need to total the income per client, and then i need to group or summarize the TOTAL income by bracket.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 Jun 2015 at 8:12am |
try a cross tab to get your final numbers.
in the CT use the IncomeBracket formula as teh row grouping and then summary as a distinct count of the clientid
|
|
IP Logged |
|
aliryder
Newbie
Joined: 20 May 2015
Online Status: Offline
Posts: 17
|
Posted: 02 Jun 2015 at 8:14am |
Originally posted by DBlanktry a cross tab to get your final numbers.
in the CT use the IncomeBracket formula as teh row grouping and then summary as a distinct count of the clientid Oh my god, that was so easy. Thank you!
|
|
IP Logged |
|
|
|
