Counting someone in a single constituency group

Karen Diener 2

Another hopefully simple issue to resolve here. I have a giving by constituency report I am trying to create. The main focus is on three constituent codes: Alumni, Parent, Graduate parent. Everyone else is lumped into "Other". The problem is that someone could technically meet all three criteria of being an Alumni, Parent and a Graduate parent. The hierarchy is as I have written it, so if you happen to be all three, I want you to appear in Alumni, and in Alumni only. Similarly, if you are a Parent and Graduate Parent, I want you in the Parent category only. In end, it should look very much like a pivot report. Constituent codes as rows, and various totals as columns for each group (like total number of donors percentage of the total constituency, total number who have donated to a particular program, etc.) Blackbaud had a seemingly overcomplicated knowledgebase solution, which I'll use if necessary. It just seemed like there might be a more simple option. Now, who will response first? Josh or Melissa? :-) Thanks!!

Patrick Manning

Karen Diener:
Another hopefully simple issue to resolve here. I have a giving by constituency report I am trying to create. The main focus is on three constituent codes: Alumni, Parent, Graduate parent. Everyone else is lumped into "Other". The problem is that someone could technically meet all three criteria of being an Alumni, Parent and a Graduate parent. The hierarchy is as I have written it, so if you happen to be all three, I want you to appear in Alumni, and in Alumni only. Similarly, if you are a Parent and Graduate Parent, I want you in the Parent category only. In end, it should look very much like a pivot report. Constituent codes as rows, and various totals as columns for each group (like total number of donors percentage of the total constituency, total number who have donated to a particular program, etc.) Blackbaud had a seemingly overcomplicated knowledgebase solution, which I'll use if necessary. It just seemed like there might be a more simple option. Now, who will response first? Josh or Melissa? :-) Thanks!!

Start by making sure your constituent codes are sorted hierarchically in Raiser's Edge.  You can sort them under Config / Tables / Constituent Codes.  Then, when you export Constituent Codes from Raiser's Edge, do the following:

For each Constituent, enter the number of Constituent Codes to export:  1

Include these Constituent Codes: leave all the codes to the left (appearing like you are not exporting any)

Base hierarchy on:  Table.

Karen Diener 2

Patrick Manning:

Start by making sure your constituent codes are sorted hierarchically in Raiser's Edge.  You can sort them under Config / Tables / Constituent Codes.  Then, when you export Constituent Codes from Raiser's Edge, do the following:

For each Constituent, enter the number of Constituent Codes to export:  1

Include these Constituent Codes: leave all the codes to the left (appearing like you are not exporting any)

Base hierarchy on:  Table.

I considered that, but it isn't reliable enough. I've never been able to count on constituent codes staying in the correct order, and it is definitely not a data element that I want to be babysitting. Looking for a systematic way to handle it. Karen

Patrick Manning

Karen Diener:
I considered that, but it isn't reliable enough. I've never been able to count on constituent codes staying in the correct order, and it is definitely not a data element that I want to be babysitting. Looking for a systematic way to handle it. Karen

If people in your organization are going into Configuration and changing the order of the Constituent Code table, remove their priveledges (for only that table or others) under Admin / Security.

Just to make sure we're on the same page, when you choose to base the hierarchy on the table (during the export), you are bypassing whatever order the codes exist on the constituent record.  The export will strictly follow the exact order in the table regardless as to how it's entered on the person's record.

Karen Diener 2

Patrick Manning:

If people in your organization are going into Configuration and changing the order of the Constituent Code table, remove their priveledges (for only that table or others) under Admin / Security.

Just to make sure we're on the same page, when you choose to base the hierarchy on the table (during the export), you are bypassing whatever order the codes exist on the constituent record.  The export will strictly follow the exact order in the table regardless as to how it's entered on the person's record.

I completely get what you're saying, but we have our cons code table sorted in a way for general usability, which doesn't match how I would need it to be sorted for this report. Again, I don't really believe in having to babysit data (i.e. rearrange the code table before running this report, then change it back) unless I have to. Plus, there are good reasons why I cannot limit every else's access to the table. I'm sure there is a way to programmatically to account for this, and I just need to find that solution. I definitely appreciate your input!

James Andrews

Karen Diener:
I completely get what you're saying, but we have our cons code table sorted in a way for general usability, which doesn't match how I would need it to be sorted for this report. Again, I don't really believe in having to babysit data (i.e. rearrange the code table before running this report, then change it back) unless I have to. Plus, there are good reasons why I cannot limit every else's access to the table. I'm sure there is a way to programmatically to account for this, and I just need to find that solution. I definitely appreciate your input!

Did Blackbaud's overly complicated solution involve variables? Because that's the only way I can think of to do it, and even that would take me a while to figure out.

Karen Diener 2

James Andrews:
Did Blackbaud's overly complicated solution involve variables? Because that's the only way I can think of to do it, and even that would take me a while to figure out.

Yuck, I think it did. There was a subreport, and I think a variable passed between the two or something like that. It really didn't seem like it should be that difficult, but maybe it is!

James Andrews

Karen Diener:
Yuck, I think it did. There was a subreport, and I think a variable passed between the two or something like that. It really didn't seem like it should be that difficult, but maybe it is!

Yeah, the issue is, if you can't control what data is in the export, you're going to have to make the report loop through it to find what you're looking for. And unfortunately the only way I know of to do that is by using variables.

You might also be able to do it by writing SQL commands in the report that return only the codes you're looking for and comparing the resulting fields, but that takes away your ability to run the report directly from RE - you'd have to export and then run it in a full version of Crystal.

Joshua Bekerman bCLO bCRE

Karen Diener:
Another hopefully simple issue to resolve here. I have a giving by constituency report I am trying to create. The main focus is on three constituent codes: Alumni, Parent, Graduate parent. Everyone else is lumped into "Other". The problem is that someone could technically meet all three criteria of being an Alumni, Parent and a Graduate parent. The hierarchy is as I have written it, so if you happen to be all three, I want you to appear in Alumni, and in Alumni only. Similarly, if you are a Parent and Graduate Parent, I want you in the Parent category only. In end, it should look very much like a pivot report. Constituent codes as rows, and various totals as columns for each group (like total number of donors percentage of the total constituency, total number who have donated to a particular program, etc.) Blackbaud had a seemingly overcomplicated knowledgebase solution, which I'll use if necessary. It just seemed like there might be a more simple option. Now, who will response first? Josh or Melissa? :-) Thanks!!

I've been reading along and just had an epiphany.  I hope this works for you. 

In your (Constituent?) Export, use separate output fields for each cons code.  So under the Constituent Codes heading, bring over a field (long description perhaps?) and select "1" for the number of cons codes to export and bring "Alumni" over to the right for "Include these Constituent Codes".  Then do the same thing for "Parent" and "Graduate Parent".  In order to bring over two more instances of the Cons Code field, you'll need to highlight the top line in the Output window,which will be the name of the Export if you have already saved it.  Then bring over the additional cons code field.  Rinse.  Repeat. 

Run the export.

Then, in the Crystal Report, create a formula field.  For the formula, do this:

If {WhateverConsCode1field} = "Alumni" then "Alumni" else

If {WhateverConsCode2field} = "Parent" then "Parent" else

If {WhateverConsCode3field} = "Graduate Parent" then "Graduate Parent" else "Other"

Use this formula field for your Group and hopefully it works!  I tested it with two constituents and two cons codes on each, and the report only returned two records (instead of four).

Let us know if this works please.

Josh

[8-|]

 

James Andrews

Josh Bekerman:

I've been reading along and just had an epiphany.  I hope this works for you. 

In your (Constituent?) Export, use separate output fields for each cons code.  So under the Constituent Codes heading, bring over a field (long description perhaps?) and select "1" for the number of cons codes to export and bring "Alumni" over to the right for "Include these Constituent Codes".  Then do the same thing for "Parent" and "Graduate Parent".  In order to bring over two more instances of the Cons Code field, you'll need to highlight the top line in the Output window,which will be the name of the Export if you have already saved it.  Then bring over the additional cons code field.  Rinse.  Repeat. 

Run the export.

Then, in the Crystal Report, create a formula field.  For the formula, do this:

If {WhateverConsCode1field} = "Alumni" then "Alumni" else

If {WhateverConsCode2field} = "Parent" then "Parent" else

If {WhateverConsCode3field} = "Graduate Parent" then "Graduate Parent" else "Other"

Use this formula field for your Group and hopefully it works!  I tested it with two constituents and two cons codes on each, and the report only returned two records (instead of four).

Let us know if this works please.

Josh

[8-|]

 

Oh, that's some good stuff right there.

Nicole S

James Andrews:
Oh, that's some good stuff right there.

 We have times when we look at gifts by constituency.  We have the gift constituency field in our batches and make sure that we code appropriately for each gift.  This way, we are looking at the codes one each specific gift and not on the constituent record.  This works if you are looking at specific gifts, but not summary info.

Karen Diener 2

Josh Bekerman:

I've been reading along and just had an epiphany.  I hope this works for you. 

In your (Constituent?) Export, use separate output fields for each cons code.  So under the Constituent Codes heading, bring over a field (long description perhaps?) and select "1" for the number of cons codes to export and bring "Alumni" over to the right for "Include these Constituent Codes".  Then do the same thing for "Parent" and "Graduate Parent".  In order to bring over two more instances of the Cons Code field, you'll need to highlight the top line in the Output window,which will be the name of the Export if you have already saved it.  Then bring over the additional cons code field.  Rinse.  Repeat. 

Run the export.

Then, in the Crystal Report, create a formula field.  For the formula, do this:

If {WhateverConsCode1field} = "Alumni" then "Alumni" else

If {WhateverConsCode2field} = "Parent" then "Parent" else

If {WhateverConsCode3field} = "Graduate Parent" then "Graduate Parent" else "Other"

Use this formula field for your Group and hopefully it works!  I tested it with two constituents and two cons codes on each, and the report only returned two records (instead of four).

Let us know if this works please.

Josh

[8-|]

 

Josh - thanks for the idea! I had been going back and forth between all conscodes in one field, and exporting them individually. I considered If Then Else, but maybe had it in the wrong place. I'll definitely give it a try and see what I find, but probably next week. Just to address a couple of others . . . I also cannot count on the gift cons code being accurate, because it isn't a focus for us at all. I did also search the knowledgebase, which is here I found the solution I wasn't very fond of that involved passing variables. Excited to play with this! Thanks everyone!

Karen Diener 2

Josh Bekerman:

I've been reading along and just had an epiphany.  I hope this works for you. 

In your (Constituent?) Export, use separate output fields for each cons code.  So under the Constituent Codes heading, bring over a field (long description perhaps?) and select "1" for the number of cons codes to export and bring "Alumni" over to the right for "Include these Constituent Codes".  Then do the same thing for "Parent" and "Graduate Parent".  In order to bring over two more instances of the Cons Code field, you'll need to highlight the top line in the Output window,which will be the name of the Export if you have already saved it.  Then bring over the additional cons code field.  Rinse.  Repeat. 

Run the export.

Then, in the Crystal Report, create a formula field.  For the formula, do this:

If {WhateverConsCode1field} = "Alumni" then "Alumni" else

If {WhateverConsCode2field} = "Parent" then "Parent" else

If {WhateverConsCode3field} = "Graduate Parent" then "Graduate Parent" else "Other"

Use this formula field for your Group and hopefully it works!  I tested it with two constituents and two cons codes on each, and the report only returned two records (instead of four).

Let us know if this works please.

Josh

[8-|]

 

OMG! Before I reply, can someone please tell me how to insert line breaks and spaces??? I tried HTML tags and that doesn't seem to do it, and can't find any setting. All of my text insists on remaining in one giant paragraph. Ugh!

Grace Strother

Karen Diener:
OMG! Before I reply, can someone please tell me how to insert line breaks and spaces??? I tried HTML tags and that doesn't seem to do it, and can't find any setting. All of my text insists on remaining in one giant paragraph. Ugh!

Hi, Karen. I apologize for the inconvenience. I've previously discussed formatting issues on this forum with another user. It basically comes down to this forum platform being relatively old and not being fully compatible with certain browsers, like Chrome. If you use the HTML box to compose your response, I recommend <p> tags. Those generally work for me.

Our hope is to upgrade to a newer platform, though I can't say when that will be. Thanks!

Karen Diener 2

Josh Bekerman:

I've been reading along and just had an epiphany.  I hope this works for you. 

In your (Constituent?) Export, use separate output fields for each cons code.  So under the Constituent Codes heading, bring over a field (long description perhaps?) and select "1" for the number of cons codes to export and bring "Alumni" over to the right for "Include these Constituent Codes".  Then do the same thing for "Parent" and "Graduate Parent".  In order to bring over two more instances of the Cons Code field, you'll need to highlight the top line in the Output window,which will be the name of the Export if you have already saved it.  Then bring over the additional cons code field.  Rinse.  Repeat. 

Run the export.

Then, in the Crystal Report, create a formula field.  For the formula, do this:

If {WhateverConsCode1field} = "Alumni" then "Alumni" else

If {WhateverConsCode2field} = "Parent" then "Parent" else

If {WhateverConsCode3field} = "Graduate Parent" then "Graduate Parent" else "Other"

Use this formula field for your Group and hopefully it works!  I tested it with two constituents and two cons codes on each, and the report only returned two records (instead of four).

Let us know if this works please.

Josh

[8-|]

 

Finally have a chance to play around with this briefly this morning.

First, I exported just one constituent code and tried the following:

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Alumni" then "Alumni" else

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Parent" then "Parent" else

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Graduate Parent" then "Graduate Parent" else "Other"

This counted someone once for every group they hit. So if they were an Alumni and a Parent, they were included in both totals. Not surprising.

Then, I exported four constituent codes separately, with Alumni as the first, Parent second, Graduate Parent third, and everything else fourth. This is my formula:

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Alumni" then "Alumni" else

if {CnCnstncy_2.CnCnstncy_2_CodeLong}="Parent" then "Parent" else

if {CnCnstncy_3.CnCnstncy_3_CodeLong}="Graduate Parent" then "Graduate Parent" else "Other"

This isn't working well at all. It counts the number of Alumni correctly, but no other constituent codes are represented. It seems to count the Alumni and then stop, because if I use browse data for the formula, I ONLY see Alumni

Any other thoughts?

Patrick Manning

Karen Diener:
Finally have a chance to play around with this briefly this morning.

First, I exported just one constituent code and tried the following:

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Alumni" then "Alumni" else

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Parent" then "Parent" else

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Graduate Parent" then "Graduate Parent" else "Other"

This counted someone once for every group they hit. So if they were an Alumni and a Parent, they were included in both totals. Not surprising.

Then, I exported four constituent codes separately, with Alumni as the first, Parent second, Graduate Parent third, and everything else fourth. This is my formula:

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Alumni" then "Alumni" else

if {CnCnstncy_2.CnCnstncy_2_CodeLong}="Parent" then "Parent" else

if {CnCnstncy_3.CnCnstncy_3_CodeLong}="Graduate Parent" then "Graduate Parent" else "Other"

This isn't working well at all. It counts the number of Alumni correctly, but no other constituent codes are represented. It seems to count the Alumni and then stop, because if I use browse data for the formula, I ONLY see Alumni

Any other thoughts?

You probably have NULL values in each of the {CnCnstncy*} tables.  So if a person is an 'Alumni' but not a 'Parent' the table that contains the 'Parent' code will be NULL for that person.  Therefore, you need to add 'If Not IsNull....' conditions.

As for your line breaks and spaces - is that within Crystal Reports or exported into an HTML file?  There are commands in Crystal you can add to formulas (if your paragraph text exists within a formula) that will insert carriage returns +Chr(13)+ or spaces +Chr(32)+.

Joshua Bekerman bCLO bCRE

Karen Diener:
Finally have a chance to play around with this briefly this morning.

First, I exported just one constituent code and tried the following:

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Alumni" then "Alumni" else

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Parent" then "Parent" else

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Graduate Parent" then "Graduate Parent" else "Other"

This counted someone once for every group they hit. So if they were an Alumni and a Parent, they were included in both totals. Not surprising.

Then, I exported four constituent codes separately, with Alumni as the first, Parent second, Graduate Parent third, and everything else fourth. This is my formula:

if {CnCnstncy_1.CnCnstncy_1_CodeLong}="Alumni" then "Alumni" else

if {CnCnstncy_2.CnCnstncy_2_CodeLong}="Parent" then "Parent" else

if {CnCnstncy_3.CnCnstncy_3_CodeLong}="Graduate Parent" then "Graduate Parent" else "Other"

This isn't working well at all. It counts the number of Alumni correctly, but no other constituent codes are represented. It seems to count the Alumni and then stop, because if I use browse data for the formula, I ONLY see Alumni

Any other thoughts?

Which section of the report are you placing the formula field?

I don't think Browse Data works with formula fields.  Do you see Parent when you Browse Data on {CnCnstncy_2.CnCnstncy_2_CodeLong} and Graduate Parent on {CnCnstncy_3.CnCnstncy_3_CodeLong}?

 

Karen Diener 2

Josh Bekerman:

Which section of the report are you placing the formula field?

I don't think Browse Data works with formula fields.  Do you see Parent when you Browse Data on {CnCnstncy_2.CnCnstncy_2_CodeLong} and Graduate Parent on {CnCnstncy_3.CnCnstncy_3_CodeLong}?

 

Maybe I'm misinterpreting the browse data. But in field explorer, I right-click on my formula for the cons code and get "browse data" as an option. Alumni is the only one. Also, to double-check that, I tried to sort my group in specified order, and Alumni was the only option - no Parent or Graduate Parent.

I do see the correct values when I browse data on each field - Alumni when browsing ConsCode 1, Parent when browsing ConsCode 2, etc..

I added a group, grouped by my formula field, and placed the summary (which was a count) in the group footer. In Preview, I have 7 blank pages, and then on page 8 finally have "Alumni" listed with a count of 167 (which is correct). The tree on the left doesn't show any of the other codes.

When I did this with one code I get the following:

Parent 10

Graduate Parent 98

Other 121

I think Patrick is on to something with the null values - it makes sense logically, but I have no idea how to put it in a formula. I definitely understand what you're saying conceptually but don't know the syntax well enough (yet!) to be able to figure it out.

Consultant here today distracting me. But I'm trying to work on this in between meetings!

James Andrews

Karen Diener:
Maybe I'm misinterpreting the browse data. But in field explorer, I right-click on my formula for the cons code and get "browse data" as an option. Alumni is the only one. Also, to double-check that, I tried to sort my group in specified order, and Alumni was the only option - no Parent or Graduate Parent.

I do see the correct values when I browse data on each field - Alumni when browsing ConsCode 1, Parent when browsing ConsCode 2, etc..

I added a group, grouped by my formula field, and placed the summary (which was a count) in the group footer. In Preview, I have 7 blank pages, and then on page 8 finally have "Alumni" listed with a count of 167 (which is correct). The tree on the left doesn't show any of the other codes.

When I did this with one code I get the following:

Parent 10

Graduate Parent 98

Other 121

I think Patrick is on to something with the null values - it makes sense logically, but I have no idea how to put it in a formula. I definitely understand what you're saying conceptually but don't know the syntax well enough (yet!) to be able to figure it out.

Consultant here today distracting me. But I'm trying to work on this in between meetings!

You'd do something like:

if (not (isnull ({conscode1})) and {conscode1} = 'Alumni') then 'Alumni'<br><br/>else if (not (isnull ({conscode2})) and {conscode2} = 'Parent') then 'Parent'

and so on.

Basically you have to test for nulls in each field before defining what the criteria is for the field.

Karen Diener 2

James Andrews:
You'd do something like:

if (not (isnull ({conscode1})) and {conscode1} = 'Alumni') then 'Alumni'<br><br/>else if (not (isnull ({conscode2})) and {conscode2} = 'Parent') then 'Parent'

and so on.

Basically you have to test for nulls in each field before defining what the criteria is for the field.

James! Genius!! That did the trick!!

I more to do, but hopefully now I can just insert summaries and take it from here.

Thank you - and everyone else- SO much!

James Andrews

Karen Diener:
James! Genius!! That did the trick!!

I more to do, but hopefully now I can just insert summaries and take it from here.

Thank you - and everyone else- SO much!

Well to be fair, that's what Patrick was talking about above: testing for nulls.

Actually considering that you're exporting each constituent code separately, you might not even need the second part of each clause (testing for 'Alumni', 'Parent', whatever), just the if not isnull part.

if not (isnull ({conscode1}) then 'Alumni'

and so on.

Karen Diener 2

James Andrews:
Well to be fair, that's what Patrick was talking about above: testing for nulls.

Actually considering that you're exporting each constituent code separately, you might not even need the second part of each clause (testing for 'Alumni', 'Parent', whatever), just the if not isnull part.

if not (isnull ({conscode1}) then 'Alumni'

and so on.

Just wanted to close the loop on this and say that the report is working beautifully now. Just in case someone comes across this in the future, I wanted to be sure that my solution (thanks to everyone's advice and input) was recorded here!

I exported each cons code individually, and the following is my formula:

If (not (isnull({CnCnstncy_1.CnCnstncy_1_CodeLong})) and {CnCnstncy_1.CnCnstncy_1_CodeLong}="Alumni") then "Alumni" else

if (not (isnull({CnCnstncy_2.CnCnstncy_2_CodeLong})) and {CnCnstncy_2.CnCnstncy_2_CodeLong}="Parent") then "Parent" else

if (not (isnull({CnCnstncy_3.CnCnstncy_3_CodeLong})) and {CnCnstncy_3.CnCnstncy_3_CodeLong}="Graduate Parent") then "Graduate Parent" else "Other"

Once someone was "assigned" their constituent code, all of the other formulas worked like a charm.

Thanks again! It is a really useful report and my boss and Annual Giving Director are very excited. It is already the jumping-off point for a couple of other reports too.