Report on families having a certain postal code?

Question

0

Report on families having a certain postal code?

Henrik Korslind posted 6 Years Ago

Hi!

I love Rock and I've spent some time trying to figure out how I can create a report or data view where all families of a certain postal code shows up. Lets say I want to see how many lives in a certain part of the city. Possible?

God bless!

// Henrik

Jeremy Hoff · Answer 1 · 2017-06-16T08:26-07:00

0

Jeremy Hoff replied 6 Years Ago

Hi Henrik - Welcome!

Let's see if this will do what you need:

Using Rock > Tools > Data Views, you can setup a new data view that "Applies To" the Person, and then choose Filter Type as "Distance From". While this doesn't provide families from a certain postal code, it might prove useful to use "Point" or "Geography Picker". (I know in our situation, searching by postal codes don't always work for our needs, since there are many living outside our zip code that are geographically closer than many with the same zip code.)

Another option to select family groups by Postal Code is using a Dynamic Data Block with the following SQL Code (replacing '91325' with your Postal Code of choice)':

use Rock --[ Assumes your Rock database is labeled 'Rock' ]

select top 100 G.Id --[ Select Family (Group) names and street addresses ]
, G.Name 
, L.Street1 
, isnull(L.Street2,'') as Street2 
, L.City , L.State 
, L.PostalCode 

from [dbo].[Group] G  
left join [dbo].[GroupLocation] GL on G.Id = GL.GroupId  
left join [dbo].[Location] L on GL.LocationId = L.Id 

where L.PostalCode Like '91325%' 
  and GL.GroupLocationTypeValueId = 19 -- [ limit to Home Addresses based on ID ]

The above will grab families and their addresses so that you can snail-mail them. The query can be expanded to include other details depending on your need: number of family members represented, specific family members, etc.

I hope that helps,

Edit Answer

Hi Henrik - Welcome!Let's see if this will do what you need: Using Rock &gt; Tools &gt; Data Views, you can setup a new data view that "Applies To" the Person, and then choose Filter Type as "Distance From". &nbsp;While this doesn't provide families from a certain postal code, it might prove useful to use "Point" or "Geography Picker". &nbsp;(I know in our situation, searching by postal codes don't always work for our needs, since there are many living outside our zip code that are geographically closer than many with the same zip code.)Another option to select family groups by Postal Code is using a Dynamic Data Block with the following SQL Code (replacing '91325' with your Postal Code of choice)':<pre>use Rock --[ Assumes your Rock database is labeled 'Rock' ]

select top 100 G.Id --[ Select Family (Group) names and street addresses ]
, G.Name 
, L.Street1 
, isnull(L.Street2,'') as Street2 
, L.City , L.State 
, L.PostalCode

from [dbo].[Group] G  
left join [dbo].[GroupLocation] GL on G.Id = GL.GroupId  
left join [dbo].[Location] L on GL.LocationId = L.Id

where L.PostalCode Like '91325%' 
 and GL.GroupLocationTypeValueId = 19 -- [ limit to Home Addresses based on ID ]</pre>The above will grab families and their addresses so that you can snail-mail them. &nbsp;The query can be expanded to include other details depending on your need: number of family members represented, specific family members, etc.I hope that helps,

<p>Hi Henrik - <b>Welcome</b>!</p><p>Let's see if this will do what you need:<br><br>Using Rock &gt; Tools &gt; Data Views, you can setup a new data view that "Applies To" the Person, and then choose Filter Type as "Distance From". &nbsp;While this doesn't provide families from a certain postal code, it might prove useful to use "Point" or "Geography Picker". &nbsp;(I know in our situation, searching by postal codes don't always work for our needs, since there are many living outside our zip code that are geographically closer than many with the same zip code.)</p><p>Another option to select family groups by Postal Code is using a Dynamic Data Block with the following SQL Code (replacing '91325' with your Postal Code of choice)':</p><pre>use Rock --[ Assumes your Rock database is labeled 'Rock' ]

select top 100 G.Id --[ Select Family (Group) names and street addresses ]
, G.Name 
, L.Street1 
, isnull(L.Street2,'') as Street2 
, L.City , L.State 
, L.PostalCode 

from [dbo].[Group] G  
left join [dbo].[GroupLocation] GL on G.Id = GL.GroupId  
left join [dbo].[Location] L on GL.LocationId = L.Id 

where L.PostalCode Like '91325%' 
  and GL.GroupLocationTypeValueId = 19 -- [ limit to Home Addresses based on ID ]</pre><p>The above will grab families and their addresses so that you can snail-mail them. &nbsp;The query can be expanded to include other details depending on your need: number of family members represented, specific family members, etc.</p><p>I hope that helps,</p>

<p>Hi Henrik - <b>Welcome</b>!</p><p>Let's see if this will do what you need:<br><br>Using Rock &gt; Tools &gt; Data Views, you can setup a new data view that "Applies To" the Person, and then choose Filter Type as "Distance From". &nbsp;While this doesn't provide families from a certain postal code, it might prove useful to use "Point" or "Geography Picker". &nbsp;(I know in our situation, searching by postal codes don't always work for our needs, since there are many living outside our zip code that are geographically closer than many with the same zip code.)</p><p>Another option to select family groups by Postal Code is using a Dynamic Data Block with the following SQL Code (replacing '91325' with your Postal Code of choice)':</p><pre>use Rock --[ Assumes your Rock database is labeled 'Rock' ]

select top 100 G.Id --[ Select Family (Group) names and street addresses ]
, G.Name 
, L.Street1 
, isnull(L.Street2,'') as Street2 
, L.City , L.State 
, L.PostalCode 

from [dbo].[Group] G  
left join [dbo].[GroupLocation] GL on G.Id = GL.GroupId  
left join [dbo].[Location] L on GL.LocationId = L.Id 

where L.PostalCode Like '91325%' 
  and GL.GroupLocationTypeValueId = 19 -- [ limit to Home Addresses based on ID ]</pre><p>The above will grab families and their addresses so that you can snail-mail them. &nbsp;The query can be expanded to include other details depending on your need: number of family members represented, specific family members, etc.</p><p>I hope that helps,</p>

Save Cancel

Jim Michael · Answer 2 · 2017-06-17T19:58-07:00

0

Jim Michael replied 6 Years Ago

You can also do this with two dataviews. 1. A dataview that returns location a given zip code. 2. A second dataview that returns Person using the "in location dataview" filter.

Question

0

Report on families having a certain postal code?

0

0