﻿<?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="AOCds">
      <DataSourceReference>AOCds</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>3bb36fbc-e2eb-4827-a261-3884637325bb</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="committees">
      <Query>
        <DataSourceName>AOCds</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@ChurchID">
            <Value>=Parameters!ChurchID.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>SELECT c.CommitteeID,CommitteeName 
,MembersActive=(select count(distinct cx.memberid)  from 
   xrpt.CommitteeMembers_vw cx
   join xrpt.Members_vw mx on mx.memberid=cx.memberid and mx.statuscode = 'ACTIVE'
    where c.CommitteeID=cx.CommitteeID)
,MembersTotal=(select count(distinct cx.memberid)  from 
   xrpt.CommitteeMembers_vw cx
    where c.CommitteeID=cx.CommitteeID)
  FROM xrpt.Committees_vw c
  where ChurchID =@ChurchID
  and not exists (select 1 from 
	  xrpt.CommitteeMembers_vw cm
	  join [xrpt].[Members_vw] m
	  on cm.MemberID=m.MemberID 
		and m.statuscode = 'ACTIVE'
	  where cm.CommitteeID=c.CommitteeID)
	order by CommitteeName</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </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>
        <Field Name="MembersActive">
          <DataField>MembersActive</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="MembersTotal">
          <DataField>MembersTotal</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="MyChurches">
      <Query>
        <DataSourceName>AOCds</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>
  </DataSets>
  <ReportSections>
    <ReportSection>
      <Body>
        <ReportItems>
          <Textbox Name="textbox1">
            <CanGrow>true</CanGrow>
            <KeepTogether>true</KeepTogether>
            <Paragraphs>
              <Paragraph>
                <TextRuns>
                  <TextRun>
                    <Value>Committees w/o Members</Value>
                    <Style>
                      <FontFamily>Segoe UI Light</FontFamily>
                      <FontSize>24pt</FontSize>
                      <FontWeight>Bold</FontWeight>
                    </Style>
                  </TextRun>
                </TextRuns>
                <Style />
              </Paragraph>
            </Paragraphs>
            <rd:DefaultName>textbox1</rd:DefaultName>
            <Height>0.43in</Height>
            <Width>5.34375in</Width>
            <Style>
              <PaddingLeft>2pt</PaddingLeft>
              <PaddingRight>2pt</PaddingRight>
              <PaddingTop>2pt</PaddingTop>
              <PaddingBottom>2pt</PaddingBottom>
            </Style>
          </Textbox>
          <Tablix Name="table1">
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>4.34375in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
              </TablixColumns>
              <TablixRows>
                <TablixRow>
                  <Height>0.22in</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="textbox2">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Committee Name</Value>
                                  <Style>
                                    <FontFamily />
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>#666666</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>textbox2</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>LightGrey</Color>
                            </Border>
                            <BottomBorder>
                              <Style>Solid</Style>
                            </BottomBorder>
                            <FontFamily />
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox3">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>non-Acitve count</Value>
                                  <Style>
                                    <FontFamily />
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>#666666</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox3</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>LightGrey</Color>
                            </Border>
                            <BottomBorder>
                              <Style>Solid</Style>
                            </BottomBorder>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                  </TablixCells>
                </TablixRow>
                <TablixRow>
                  <Height>0.21in</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="CommitteeName">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>=Fields!CommitteeName.Value</Value>
                                  <Style>
                                    <FontFamily />
                                    <FontWeight>Bold</FontWeight>
                                    <TextDecoration>Underline</TextDecoration>
                                    <Color>Blue</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>CommitteeName</rd:DefaultName>
                          <ActionInfo>
                            <Actions>
                              <Action>
                                <Hyperlink>="https://app.gabrielsoft.com/auth/ohcndiocese/" &amp; Parameters!ChurchID.Value &amp; "/Committee/Edit/" &amp; Fields!CommitteeID.Value</Hyperlink>
                              </Action>
                            </Actions>
                          </ActionInfo>
                          <Style>
                            <Border>
                              <Color>LightGrey</Color>
                            </Border>
                            <BottomBorder>
                              <Style>Solid</Style>
                            </BottomBorder>
                            <FontFamily />
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="MembersTotal">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>=Fields!MembersTotal.Value</Value>
                                  <Style>
                                    <FontFamily />
                                    <Color>#333333</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>MembersTotal</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>LightGrey</Color>
                            </Border>
                            <BottomBorder>
                              <Style>Solid</Style>
                            </BottomBorder>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                  </TablixCells>
                </TablixRow>
              </TablixRows>
            </TablixBody>
            <TablixColumnHierarchy>
              <TablixMembers>
                <TablixMember />
                <TablixMember />
              </TablixMembers>
            </TablixColumnHierarchy>
            <TablixRowHierarchy>
              <TablixMembers>
                <TablixMember>
                  <KeepWithGroup>After</KeepWithGroup>
                  <RepeatOnNewPage>true</RepeatOnNewPage>
                  <KeepTogether>true</KeepTogether>
                </TablixMember>
                <TablixMember>
                  <Group Name="table1_Details_Group">
                    <DataElementName>Detail</DataElementName>
                  </Group>
                  <TablixMembers>
                    <TablixMember />
                  </TablixMembers>
                  <DataElementName>Detail_Collection</DataElementName>
                  <DataElementOutput>Output</DataElementOutput>
                  <KeepTogether>true</KeepTogether>
                </TablixMember>
              </TablixMembers>
            </TablixRowHierarchy>
            <DataSetName>committees</DataSetName>
            <Top>0.43in</Top>
            <Height>0.43in</Height>
            <Width>5.34375in</Width>
            <ZIndex>1</ZIndex>
            <Style />
          </Tablix>
        </ReportItems>
        <Height>0.86in</Height>
        <Style />
      </Body>
      <Width>5.34375in</Width>
      <Page>
        <LeftMargin>1in</LeftMargin>
        <RightMargin>1in</RightMargin>
        <TopMargin>1in</TopMargin>
        <BottomMargin>1in</BottomMargin>
        <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>
  </ReportParameters>
  <ReportParametersLayout>
    <GridLayoutDefinition>
      <NumberOfColumns>1</NumberOfColumns>
      <NumberOfRows>1</NumberOfRows>
      <CellDefinitions>
        <CellDefinition>
          <ColumnIndex>0</ColumnIndex>
          <RowIndex>0</RowIndex>
          <ParameterName>ChurchID</ParameterName>
        </CellDefinition>
      </CellDefinitions>
    </GridLayoutDefinition>
  </ReportParametersLayout>
  <Language>en-US</Language>
  <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>f0dd623d-114c-4989-9640-0653aa2411d2</rd:ReportID>
</Report>