﻿<?xml version="1.0" encoding="utf-8"?>
<!-- Copyright Archdiocese of Cincinnati use with permission only c/o bmaly@CatholicAOC.org-->
<Report MustUnderstand="df" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily">
  <df:DefaultFontFamily>Segoe UI</df:DefaultFontFamily>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="AOC">
      <DataSourceReference>AOCds</DataSourceReference>
      <rd:SecurityType>DataBase</rd:SecurityType>
      <rd:DataSourceID>ca9db7bd-b480-4ad8-9c6d-7d50827b04a5</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="MyChurches">
      <Query>
        <DataSourceName>AOC</DataSourceName>
        <CommandText>SELECT
  ChurchID
  ,Churches_vw.ChurchName+ ' - ' + Churches_vw.City [ChurchName]
FROM
  xrpt.Churches_vw AS Churches_vw
order by Churches_vw.ChurchName+ ' - ' + Churches_vw.City</CommandText>
      </Query>
      <Fields>
        <Field Name="ChurchID">
          <DataField>ChurchID</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="ChurchName">
          <DataField>ChurchName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="MyStatuses">
      <Query>
        <DataSourceName>AOC</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@ChurchID">
            <Value>=Parameters!ChurchID.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>Select * from 
(SELECT distinct 
 StatusCode
FROM xrpt.Families_vw 
where Churchid = @ChurchID
union all
select ' All Statuses')tst
order by StatusCode</CommandText>
      </Query>
      <Fields>
        <Field Name="StatusCode">
          <DataField>StatusCode</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="FamilyTags">
      <Query>
        <DataSourceName>AOC</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@ChurchID">
            <Value>=Parameters!ChurchID.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>Select * from 
(SELECT
FamilyTags_vw.TagID
  ,FamilyTags_vw.TagName
FROM
  xrpt.FamilyTags_vw 
where FamilyTags_vw.ChurchID = @ChurchID
union all 
select 0, ' All Tags' 
union all
select 1, ' No Tag' ) tst
order by TagName</CommandText>
      </Query>
      <Fields>
        <Field Name="TagID">
          <DataField>TagID</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="TagName">
          <DataField>TagName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="Families">
      <Query>
        <DataSourceName>AOC</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@Column">
            <Value>=Parameters!Column.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@Statuses">
            <Value>=Parameters!Statuses.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@Row">
            <Value>=Parameters!Row.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@End">
            <Value>=Parameters!End.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@GTorEQ">
            <Value>=Parameters!GTorEQ.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@mStatuses">
            <Value>=Parameters!mStatuses.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@Begin">
            <Value>=Parameters!Begin.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@ListType">
            <Value>=Parameters!ListType.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@ChurchID">
            <Value>=Parameters!ChurchID.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@Age">
            <Value>=Parameters!Age.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@Committees">
            <Value>=Parameters!Committees.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@FamilyTags">
            <Value>=Parameters!FamilyTags.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@MaritalStatus">
            <Value>=Parameters!MaritalStatus.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>--Declare @ChurchID int= 2
--declare @Statuses  varchar(15) = ' All Statuses'
--declare @mStatuses varchar(15) = ' All Statuses'
--declare @committees int  =0
--declare @Age int = 60
--declare @Begin date = '12/1/2019'
--declare @End date = '02/01/2020'
--Declare @GTorEQ int =1
--Declare @FamilyTags int =1
--Declare @ListType int = 1

--declare @BirthDate date ='03/1/1900'
--select case when @BSM &gt; @ESM then 
--		Case when month(@BirthDate)*12 +day(@BirthDate) &lt;= @ESM or month(@BirthDate)*12 +day(@BirthDate) &gt;= @BSM then 1 else 0 end
--		else 
--		Case when month(@BirthDate)*12 +day(@BirthDate) &lt; @ESM and month(@BirthDate)*12 +day(@BirthDate) &gt; @BSM then 1 else 0 end 
--		end 
--declare @MaritalStatus table ([Value] varchar(30))

--insert into @MaritalStatus
--Select * from (
--SELECT distinct
--  m.MaritalStatus 
--FROM xrpt.Families_vw f
--  left join xrpt.Members_vw m
--	on m.churchid= f.churchid 
--	and m.FamilyID=f.FamilyID
--where case when ' All Statuses' in (@Statuses) then 1 else 
--	case when f.StatusCode in (@Statuses) then 1 else 0 end end =1
--and case when ' All Statuses' in (@mStatuses) then 1 else 
--	case when m.statusCode in (@mStatuses) then 1 else 0 end end =1
--and (0 in (@Committees)
-- or exists (select 1 from xrpt.CommitteeMembers_vw cm
--	where m.FamilyID = cm.FamilyID
--	and m.MemberID = cm.MemberID
--	and m.ChurchID = cm.ChurchID 
--	and CommitteeID in (@Committees)))
--and f.churchid = @ChurchID
--and isnull(m.MaritalStatus ,'') &lt;&gt;''
--and (0 in (@FamilyTags)
--	or exists (select 1 from xrpt.FamilyTagMembers_vw t 
--	where t.churchid= f.churchid 
--	and t.FamilyID=f.FamilyID
--	and t.tagID in (@FamilyTags))
--	or (1 in (@FamilyTags) 
--		and not exists (select 1 from xrpt.FamilyTagMembers_vw t 
--	where t.churchid= f.churchid 
--	and t.FamilyID=f.FamilyID
--	and t.tagID in (@FamilyTags)))

--	)
--union all
--Select ' All Statuses'
--union all
--Select ' No Status Stated'
--)txt
--order by MaritalStatus

Declare @blanks table (id int identity(1,1),x char(2))
declare @cnt int = 1
while @cnt &lt;= (@Column-1)*10+(@Row-1)
begin 
insert into @blanks
select convert(char(2),@cnt)
set @cnt=@cnt+1
end


declare @BSM int =  month(@Begin)*1200+day(@Begin)
declare @ESM int =  month(@End)*1200+day(@End)
Declare @set int = @GTorEQ
declare @selections table (expx varchar(50))

while @set = 1 begin 
insert into @selections
SELECT min(convert(int,(value)))  FROM string_split(replace(@Age,' ',''),',')
set @set = 2 end 

while @set = 0 begin 
insert into @selections
select convert(int,value)  FROM string_split(replace(@Age,' ',''),',')
set @set = 2 end
declare @select int = @ListType
While @select = 0 Begin
select * from (
SELECT distinct
  f.familyid
,f.BudgetNumber
,f.IsRegistered
,convert(varchar(12),f.registrationdate ,101) RegistrationDate
--,mLabel=case @Ntype 
--	when 0 then ml.MailName 
--	when 1 then f.firstname 
--	when 2 then f.salutation 
--	else '' end 
,f.FirstName
  ,f.LastName
  ,f.Salutation
  ,f.Addr1
  ,f.Addr2
  ,f.City
  ,f.[State]
  ,f.ZipCode  
,ml=case when f.addr1&lt;&gt;ml.addr1 then 1 else 0 end
  ,ml.MailName
  ,ml.Addr1 mlAddr1
  ,ml.Addr2 mlAddr2
  ,ml.City mlCity
  ,ml.[State] mlState
  ,ml.ZipCode mlZipCode
  ,F.PhoneNumber
  ,F.StatusCode
  ,F.EmailAddress
   ,m.LastName mLastName
   ,m.FirstName mFirstName
   ,m.MiddleName mMiddleName
   ,m.Salutation mSalutation
,m.statusCode mStatus
,convert(varchar(10),m.BirthDate ,101) BirthDate
,BirthMonth = Month(m.BirthDate)
,datediff(yy,m.BirthDate,GetDate()) Age
,datediff(yy,ms.DateReceived,GetDate()) Anniversary
,(((year(ms.DateReceived)*12)+month(ms.DateReceived))*100)+day(ms.DateReceived) bdsn
,convert(varchar(10),m.DeceasedDate ,101) DeceasedDate
,m.EmailAddress mEmailAddress
,m.PhoneNumber mPhoneNumber
   ,m.MaritalStatus 
   ,m.MaidenName
   ,m.Gender
,convert(varchar(10),ms.DateReceived,101) DateReceived
   ,Sacraments = isnull(STUFF((SELECT distinct '|' + isnull(ms.SacramentName,'')
			+case when isnull(ms.SacramentName,'')='Matrimony' then 
				case when isnull(ms.SpouseName,'') = '' then '' else isnull(' :spouse-'+ms.SpouseName,'') end else '' end 
			+case when isnull(ms.DateReceived,'') = '' then '' else  isnull(' :date-'+convert(varchar(10),ms.DateReceived,101) ,'') end
			+case when isnull(ms.LocationOfSacrament,'') = '' 
			or ms.LocationOfSacrament='-' then '' else  isnull(' :loc-'+ms.LocationOfSacrament ,'') end
			+case when isnull(ms.Celebrant,'') = '' then '' else  isnull(' :cel-'+ms.Celebrant ,'') end
		FROM xrpt.MemberSacraments_vw ms
			WHERE ms.MemberID=m.MemberID 
			AND ms.ChurchID=@ChurchID
			FOR XML PATH ('')), 1, 1, '') ,'')
FROM xrpt.Families_vw f
join xrpt.MailingLabels_vw ml
on ml.FamilyID=f.FamilyID
  left join xrpt.Members_vw m
	on m.churchid= f.churchid 
	and m.FamilyID=f.FamilyID
  left join xrpt.MemberSacraments_vw ms	
	on ms.ChurchID=m.ChurchID 
	and ms.MemberID=m.MemberID 
	and MS.SacramentID = 4
where case when ' All Statuses' in (@Statuses) then 1 else 
	case when f.StatusCode in (@Statuses) then 1 else 0 end end =1
and case when ' All Statuses' in (@mStatuses) then 1 else 
	case when m.statusCode in (@mStatuses) then 1 else 0 end end =1
and (0 in (@Committees)
 or exists (select 1 from xrpt.CommitteeMembers_vw cm
	where m.FamilyID = cm.FamilyID
	and m.MemberID = cm.MemberID
	and m.ChurchID = cm.ChurchID 
	and CommitteeID in (@Committees)))
and f.churchid = @ChurchID
and (0 in (@FamilyTags)
	or exists (select 1 from xrpt.FamilyTagMembers_vw t 
	where t.churchid= f.churchid 
	and t.FamilyID=f.FamilyID
	and t.tagID in (@FamilyTags))
	or (1 in (@FamilyTags) 
	and not exists (select 1 from xrpt.FamilyTagMembers_vw t 
	where t.churchid= f.churchid 
	and t.FamilyID=f.FamilyID
	and t.tagID in (@FamilyTags)))
	)
and (' All Statuses' in (@MaritalStatus) 
	or
	m.MaritalStatus in (@MaritalStatus) 
	or 
	(' No Status Stated' in (@MaritalStatus) and isnull(m.MaritalStatus,'') =''))
and case when @BSM &gt; @ESM then 
		Case when month(DateReceived)*1200 +day(DateReceived) &lt;= @ESM or month(DateReceived)*1200 +day(DateReceived) &gt;= @BSM then 1 else 0 end
		else 
		Case when month(DateReceived)*1200 +day(DateReceived) &lt;= @ESM and month(DateReceived)*1200 +day(DateReceived) &gt;= @BSM then 1 else 0 end 
		end =1
	) datax join @selections tsearch 
 on case when @GTorEQ = 1 and datediff(yy,DateReceived,@Begin) &gt;= expx then 1 else 
case when datediff(yy,DateReceived,@Begin) = expx then 1 else 0 end end =1
order by LastName
set @select = 2
end

While @select = 1 Begin
select * from (
SELECT distinct
  f.familyid
,f.BudgetNumber
,f.IsRegistered
,convert(varchar(12),f.registrationdate ,101) RegistrationDate
,f.FirstName
  ,f.LastName
  ,f.Salutation
  ,f.Addr1
  ,f.Addr2
  ,f.City
  ,f.[State]
  ,f.ZipCode  
,ml=case when f.addr1&lt;&gt;ml.addr1 then 1 else 0 end
  ,ml.MailName
  ,ml.Addr1 mlAddr1
  ,ml.Addr2 mlAddr2
  ,ml.City mlCity
  ,ml.[State] mlState
  ,ml.ZipCode mlZipCode
  ,F.PhoneNumber
  ,F.StatusCode
  ,F.EmailAddress
   ,'' mLastName
   ,'' mFirstName
   ,'' mMiddleName
   ,'' mSalutation
,m.statusCode mStatus
,'' BirthDate
,'' BirthMonth
,'' Age
,datediff(yy,ms.DateReceived,GetDate()) Anniversary
,(((year(ms.DateReceived)*12)+month(ms.DateReceived))*100)+day(ms.DateReceived) bdsn
,'' DeceasedDate
,'' mEmailAddress
,'' mPhoneNumber
   ,m.MaritalStatus MaritalStatus 
   ,'' MaidenName
   ,'' Gender
,convert(varchar(10),ms.DateReceived,101) DateReceived
   ,Sacraments = ''
FROM xrpt.Families_vw f
join xrpt.MailingLabels_vw ml
on ml.FamilyID=f.FamilyID
  left join xrpt.Members_vw m
	on m.churchid= f.churchid 
	and m.FamilyID=f.FamilyID
  left join xrpt.MemberSacraments_vw ms	
	on ms.ChurchID=m.ChurchID 
	and ms.MemberID=m.MemberID 
	and MS.SacramentID = 4
where case when ' All Statuses' in (@Statuses) then 1 else 
	case when f.StatusCode in (@Statuses) then 1 else 0 end end =1
and case when ' All Statuses' in (@mStatuses) then 1 else 
	case when m.statusCode in (@mStatuses) then 1 else 0 end end =1
and (0 in (@Committees)
 or exists (select 1 from xrpt.CommitteeMembers_vw cm
	where m.FamilyID = cm.FamilyID
	and m.MemberID = cm.MemberID
	and m.ChurchID = cm.ChurchID 
	and CommitteeID in (@Committees)))
and f.churchid = @ChurchID
and (0 in (@FamilyTags)
	or exists (select 1 from xrpt.FamilyTagMembers_vw t 
	where t.churchid= f.churchid 
	and t.FamilyID=f.FamilyID
	and t.tagID in (@FamilyTags))
	or (1 in (@FamilyTags) 
	and not exists (select 1 from xrpt.FamilyTagMembers_vw t 
	where t.churchid= f.churchid 
	and t.FamilyID=f.FamilyID
	and t.tagID in (@FamilyTags)))
	)
and (' All Statuses' in (@MaritalStatus) 
	or
	m.MaritalStatus in (@MaritalStatus) 
	or 
	(' No Status Stated' in (@MaritalStatus) and isnull(m.MaritalStatus,'') =''))
and case when @BSM &gt; @ESM then 
		Case when month(DateReceived)*1200 +day(DateReceived) &lt;= @ESM or month(DateReceived)*1200 +day(DateReceived) &gt;= @BSM then 1 else 0 end
		else 
		Case when month(DateReceived)*1200 +day(DateReceived) &lt;= @ESM and month(DateReceived)*1200 +day(DateReceived) &gt;= @BSM then 1 else 0 end 
		end =1
	) datax join @selections tsearch 
 on case when @GTorEQ = 1 and datediff(yy,DateReceived,@Begin) &gt;= expx then 1 else 
case when datediff(yy,DateReceived,@Begin) = expx then 1 else 0 end end =1
order by LastName
set @select = 2
end</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="familyid">
          <DataField>familyid</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="BudgetNumber">
          <DataField>BudgetNumber</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="IsRegistered">
          <DataField>IsRegistered</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="registrationdate">
          <DataField>RegistrationDate</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="statuscode">
          <DataField>StatusCode</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="FirstName">
          <DataField>FirstName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="LastName">
          <DataField>LastName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Salutation">
          <DataField>Salutation</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="MailName">
          <DataField>MailName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Addr1">
          <DataField>Addr1</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="City">
          <DataField>City</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Addr2">
          <DataField>Addr2</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="State">
          <DataField>State</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="ml">
          <DataField>ml</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="ZipCode">
          <DataField>ZipCode</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mlAddr1">
          <DataField>mlAddr1</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mlAddr2">
          <DataField>mlAddr2</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="PhoneNumber">
          <DataField>PhoneNumber</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mlCity">
          <DataField>mlCity</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="EmailAddress">
          <DataField>EmailAddress</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mLastName">
          <DataField>mLastName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mlState">
          <DataField>mlState</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mlZipCode">
          <DataField>mlZipCode</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mFirstName">
          <DataField>mFirstName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mMiddleName">
          <DataField>mMiddleName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mSalutation">
          <DataField>mSalutation</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mStatus">
          <DataField>mStatus</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="BirthDate">
          <DataField>BirthDate</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="BirthMonth">
          <DataField>BirthMonth</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="Age">
          <DataField>Age</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="bdsn">
          <DataField>bdsn</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="Anniversary">
          <DataField>Anniversary</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="MaritalStatus">
          <DataField>MaritalStatus</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="DeceasedDate">
          <DataField>DeceasedDate</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="MaidenName">
          <DataField>MaidenName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mEmailAddress">
          <DataField>mEmailAddress</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Gender">
          <DataField>Gender</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="mPhoneNumber">
          <DataField>mPhoneNumber</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="DateReceived">
          <DataField>DateReceived</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Sacraments">
          <DataField>Sacraments</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="expx">
          <DataField>expx</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="mStatuses">
      <Query>
        <DataSourceName>AOC</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@ChurchID">
            <Value>=Parameters!ChurchID.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>Select * from 
(SELECT distinct 
 StatusCode
FROM xrpt.Members_vw 
where Churchid = @ChurchID
union all
select ' All Statuses')tst
order by StatusCode</CommandText>
      </Query>
      <Fields>
        <Field Name="StatusCode">
          <DataField>StatusCode</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="MyCommittees">
      <Query>
        <DataSourceName>AOC</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@ChurchID">
            <Value>=Parameters!ChurchID.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>Select * from 
(SELECT distinct [CommitteeID]
      ,[CommitteeName]
  FROM [xrpt].[CommitteeMembers_vw]
  where ChurchID = @ChurchID
union all 
Select 0, ' All Committees') tst
order by CommitteeName</CommandText>
      </Query>
      <Fields>
        <Field Name="CommitteeID">
          <DataField>CommitteeID</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="CommitteeName">
          <DataField>CommitteeName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="MaritalStatus">
      <Query>
        <DataSourceName>AOC</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@Statuses">
            <Value>=Parameters!Statuses.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@mStatuses">
            <Value>=Parameters!mStatuses.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@Committees">
            <Value>=Parameters!Committees.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@ChurchID">
            <Value>=Parameters!ChurchID.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@FamilyTags">
            <Value>=Parameters!FamilyTags.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>Select * from (
SELECT distinct
  m.MaritalStatus 
FROM xrpt.Families_vw f
  left join xrpt.Members_vw m
	on m.churchid= f.churchid 
	and m.FamilyID=f.FamilyID
where case when ' All Statuses' in (@Statuses) then 1 else 
	case when f.StatusCode in (@Statuses) then 1 else 0 end end =1
and case when ' All Statuses' in (@mStatuses) then 1 else 
	case when m.statusCode in (@mStatuses) then 1 else 0 end end =1
and (0 in (@Committees)
 or exists (select 1 from xrpt.CommitteeMembers_vw cm
	where m.FamilyID = cm.FamilyID
	and m.MemberID = cm.MemberID
	and m.ChurchID = cm.ChurchID 
	and CommitteeID in (@Committees)))
and f.churchid = @ChurchID
and isnull(m.MaritalStatus ,'') &lt;&gt;''
and (0 in (@FamilyTags)
	or exists (select 1 from xrpt.FamilyTagMembers_vw t 
	where t.churchid= f.churchid 
	and t.FamilyID=f.FamilyID
	and t.tagID in (@FamilyTags))
	or (1 in (@FamilyTags) 
		and not exists (select 1 from xrpt.FamilyTagMembers_vw t 
	where t.churchid= f.churchid 
	and t.FamilyID=f.FamilyID
	and t.tagID in (@FamilyTags)))

	)
union all
Select ' All Statuses'
union all
Select ' No Status Stated'
)txt
order by MaritalStatus</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="MaritalStatus">
          <DataField>MaritalStatus</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportSections>
    <ReportSection>
      <Body>
        <ReportItems>
          <Tablix Name="Tablix2">
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>2.8125in</Width>
                </TablixColumn>
              </TablixColumns>
              <TablixRows>
                <TablixRow>
                  <Height>1.0312in</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Addr1">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>=iif(Parameters!type.Value=0,Fields!MailName.Value,iif(Parameters!type.Value=1,Fields!FirstName.Value &amp; " " &amp;Fields!LastName.Value,Fields!Salutation.Value)) 
&amp; Chr(10) &amp; Fields!Addr1.Value &amp; " " &amp; Fields!Addr2.Value &amp; Chr(10) &amp; Fields!City.Value &amp; ", " &amp; Fields!State.Value &amp; " " &amp; Fields!ZipCode.Value 
&amp; iif(Fields!ml.Value=1, chr(10) &amp; "Mail Address:" &amp; Chr(10) &amp; Fields!mlAddr1.Value &amp; " " &amp; Fields!mlAddr2.Value &amp; Chr(10) &amp; Fields!mlCity.Value &amp; ", " &amp; Fields!mlState.Value &amp; " " &amp; Fields!mlZipCode.Value,"")</Value>
                                  <Style />
                                </TextRun>
                              </TextRuns>
                              <Style>
                                <TextAlign>Left</TextAlign>
                              </Style>
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Addr1</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>White</Color>
                              <Style>None</Style>
                            </Border>
                            <VerticalAlign>Middle</VerticalAlign>
                            <PaddingLeft>15pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                  </TablixCells>
                </TablixRow>
              </TablixRows>
            </TablixBody>
            <TablixColumnHierarchy>
              <TablixMembers>
                <TablixMember />
              </TablixMembers>
            </TablixColumnHierarchy>
            <TablixRowHierarchy>
              <TablixMembers>
                <TablixMember>
                  <Group Name="Details" />
                </TablixMember>
              </TablixMembers>
            </TablixRowHierarchy>
            <DataSetName>Families</DataSetName>
            <Height>1.0312in</Height>
            <Width>2.8125in</Width>
            <Style>
              <Border>
                <Style>None</Style>
              </Border>
            </Style>
          </Tablix>
        </ReportItems>
        <Height>1.0312in</Height>
        <Style>
          <Border>
            <Color>White</Color>
            <Style>None</Style>
          </Border>
        </Style>
      </Body>
      <Width>2.8125in</Width>
      <Page>
        <PageFooter>
          <Height>0.01042in</Height>
          <PrintOnFirstPage>true</PrintOnFirstPage>
          <PrintOnLastPage>true</PrintOnLastPage>
          <Style>
            <Border>
              <Style>None</Style>
            </Border>
          </Style>
        </PageFooter>
        <TopMargin>0.1875in</TopMargin>
        <Columns>3</Columns>
        <ColumnSpacing>0.02135in</ColumnSpacing>
        <Style />
      </Page>
    </ReportSection>
  </ReportSections>
  <ReportParameters>
    <ReportParameter Name="ChurchID">
      <DataType>Integer</DataType>
      <Prompt>Select Church</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>MyChurches</DataSetName>
          <ValueField>ChurchID</ValueField>
          <LabelField>ChurchName</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
    <ReportParameter Name="Statuses">
      <DataType>String</DataType>
      <DefaultValue>
        <Values>
          <Value> All Statuses</Value>
        </Values>
      </DefaultValue>
      <Prompt>Select Family Statuses</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>MyStatuses</DataSetName>
          <ValueField>StatusCode</ValueField>
          <LabelField>StatusCode</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="FamilyTags">
      <DataType>Integer</DataType>
      <DefaultValue>
        <Values>
          <Value>0</Value>
        </Values>
      </DefaultValue>
      <Prompt>Family Tags</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>FamilyTags</DataSetName>
          <ValueField>TagID</ValueField>
          <LabelField>TagName</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="mStatuses">
      <DataType>String</DataType>
      <DefaultValue>
        <Values>
          <Value> All Statuses</Value>
        </Values>
      </DefaultValue>
      <Prompt>Select Member Statuses</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>mStatuses</DataSetName>
          <ValueField>StatusCode</ValueField>
          <LabelField>StatusCode</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="Committees">
      <DataType>Integer</DataType>
      <DefaultValue>
        <Values>
          <Value>0</Value>
        </Values>
      </DefaultValue>
      <Prompt>Member Committees</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>MyCommittees</DataSetName>
          <ValueField>CommitteeID</ValueField>
          <LabelField>CommitteeName</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="MaritalStatus">
      <DataType>String</DataType>
      <DefaultValue>
        <Values>
          <Value> All Statuses</Value>
        </Values>
      </DefaultValue>
      <Prompt>Select Marital Status</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>MaritalStatus</DataSetName>
          <ValueField>MaritalStatus</ValueField>
          <LabelField>MaritalStatus</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="Begin">
      <DataType>DateTime</DataType>
      <Prompt>Start Date</Prompt>
    </ReportParameter>
    <ReportParameter Name="End">
      <DataType>DateTime</DataType>
      <Prompt>End Date</Prompt>
    </ReportParameter>
    <ReportParameter Name="GTorEQ">
      <DataType>Integer</DataType>
      <Prompt>Type of Selection</Prompt>
      <ValidValues>
        <ParameterValues>
          <ParameterValue>
            <Value>1</Value>
            <Label>Greater than or Equal to # Year Anniv</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>0</Value>
            <Label>Equal to one or more Year Anniv </Label>
          </ParameterValue>
        </ParameterValues>
      </ValidValues>
    </ReportParameter>
    <ReportParameter Name="Age">
      <DataType>String</DataType>
      <Prompt>Year (single or a CS list)</Prompt>
    </ReportParameter>
    <ReportParameter Name="ListType">
      <DataType>Integer</DataType>
      <DefaultValue>
        <Values>
          <Value>1</Value>
        </Values>
      </DefaultValue>
      <Prompt>List Type</Prompt>
      <Hidden>true</Hidden>
      <ValidValues>
        <ParameterValues>
          <ParameterValue>
            <Value>0</Value>
            <Label>Members</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>1</Value>
            <Label>Couples</Label>
          </ParameterValue>
        </ParameterValues>
      </ValidValues>
    </ReportParameter>
    <ReportParameter Name="Details">
      <DataType>Boolean</DataType>
      <DefaultValue>
        <Values>
          <Value>true</Value>
        </Values>
      </DefaultValue>
      <Prompt>Expanded Detail?</Prompt>
      <Hidden>true</Hidden>
      <ValidValues>
        <ParameterValues>
          <ParameterValue>
            <Value>true</Value>
            <Label>Hide</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>false</Value>
            <Label>Show</Label>
          </ParameterValue>
        </ParameterValues>
      </ValidValues>
    </ReportParameter>
    <ReportParameter Name="type">
      <DataType>Integer</DataType>
      <DefaultValue>
        <Values>
          <Value>0</Value>
        </Values>
      </DefaultValue>
      <Prompt>Name on first line</Prompt>
      <ValidValues>
        <ParameterValues>
          <ParameterValue>
            <Value>0</Value>
            <Label>Mail Name</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>1</Value>
            <Label>Family First Name</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>2</Value>
            <Label>Family Salutation</Label>
          </ParameterValue>
        </ParameterValues>
      </ValidValues>
    </ReportParameter>
    <ReportParameter Name="Column">
      <DataType>String</DataType>
      <DefaultValue>
        <Values>
          <Value>1</Value>
        </Values>
      </DefaultValue>
      <Prompt>Column</Prompt>
    </ReportParameter>
    <ReportParameter Name="Row">
      <DataType>String</DataType>
      <DefaultValue>
        <Values>
          <Value>1</Value>
        </Values>
      </DefaultValue>
      <Prompt>Row</Prompt>
    </ReportParameter>
  </ReportParameters>
  <ReportParametersLayout>
    <GridLayoutDefinition>
      <NumberOfColumns>3</NumberOfColumns>
      <NumberOfRows>5</NumberOfRows>
      <CellDefinitions>
        <CellDefinition>
          <ColumnIndex>0</ColumnIndex>
          <RowIndex>0</RowIndex>
          <ParameterName>ChurchID</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>1</ColumnIndex>
          <RowIndex>0</RowIndex>
          <ParameterName>Statuses</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>2</ColumnIndex>
          <RowIndex>0</RowIndex>
          <ParameterName>FamilyTags</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>0</ColumnIndex>
          <RowIndex>1</RowIndex>
          <ParameterName>mStatuses</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>1</ColumnIndex>
          <RowIndex>1</RowIndex>
          <ParameterName>Committees</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>2</ColumnIndex>
          <RowIndex>1</RowIndex>
          <ParameterName>MaritalStatus</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>0</ColumnIndex>
          <RowIndex>2</RowIndex>
          <ParameterName>Begin</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>1</ColumnIndex>
          <RowIndex>2</RowIndex>
          <ParameterName>End</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>2</ColumnIndex>
          <RowIndex>2</RowIndex>
          <ParameterName>GTorEQ</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>0</ColumnIndex>
          <RowIndex>3</RowIndex>
          <ParameterName>Age</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>1</ColumnIndex>
          <RowIndex>3</RowIndex>
          <ParameterName>ListType</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>2</ColumnIndex>
          <RowIndex>3</RowIndex>
          <ParameterName>Details</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>0</ColumnIndex>
          <RowIndex>4</RowIndex>
          <ParameterName>type</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>1</ColumnIndex>
          <RowIndex>4</RowIndex>
          <ParameterName>Column</ParameterName>
        </CellDefinition>
        <CellDefinition>
          <ColumnIndex>2</ColumnIndex>
          <RowIndex>4</RowIndex>
          <ParameterName>Row</ParameterName>
        </CellDefinition>
      </CellDefinitions>
    </GridLayoutDefinition>
  </ReportParametersLayout>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>8f51e90d-03ad-4d71-a959-99ce486f8559</rd:ReportID>
</Report>