Part A Offense Counts

CJIWave 1
24 reports · 2,803 runs (12 mo) · Medium · Target: Paginated report
Member reports Template Power BI output Validation (17)
Part A Offense Counts.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Description>Part A Offense Counts. Generated by Migration Workbench from family F004; replaces 24 source reports.</Description>
  <DataSources><DataSource Name="RJDW"><DataSourceReference>/Data Sources/RJDW</DataSourceReference></DataSource></DataSources>
  <DataSets>
    <DataSet Name="Main"><Query><DataSourceName>RJDW</DataSourceName><QueryParameters><QueryParameter Name="@Agency"><Value>=Parameters!Agency.Value</Value></QueryParameter><QueryParameter Name="@DateFrom"><Value>=Parameters!DateFrom.Value</Value></QueryParameter><QueryParameter Name="@DateTo"><Value>=Parameters!DateTo.Value</Value></QueryParameter></QueryParameters>
      <CommandText>SELECT o.offense_desc, o.crime_against, COUNT(*) AS offenses FROM incident i JOIN offense_code o ON o.offense_code = i.offense_code WHERE o.nibrs_group = 'A' AND i.agency_id = @Agency AND i.occurred_date BETWEEN @DateFrom AND @DateTo GROUP BY o.offense_desc, o.crime_against</CommandText></Query><Fields><Field Name="offense_desc"><DataField>offense_desc</DataField></Field><Field Name="crime_against"><DataField>crime_against</DataField></Field><Field Name="offenses"><DataField>offenses</DataField></Field></Fields></DataSet>
    <DataSet Name="Agencies"><Query><DataSourceName>RJDW</DataSourceName><CommandText>SELECT a.agency_id, a.agency_name FROM dbo.agency a JOIN sec.user_agency u ON u.agency_id = a.agency_id WHERE u.user_name = SUSER_SNAME() ORDER BY a.agency_name</CommandText></Query><Fields><Field Name="agency_id"><DataField>agency_id</DataField></Field><Field Name="agency_name"><DataField>agency_name</DataField></Field></Fields></DataSet>
  </DataSets>
  <ReportSections><ReportSection><Body><ReportItems>
    <Textbox Name="Title"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Part A Offense Counts</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>16pt</FontSize><FontWeight>SemiBold</FontWeight><Color>#243A5E</Color></Style></TextRun></TextRuns><Style><TextAlign>Left</TextAlign></Style></Paragraph></Paragraphs><Top>0in</Top><Left>0in</Left><Height>0.38in</Height><Width>7.50in</Width><Style><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox>
    <Textbox Name="Subtitle"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>=Lookup(Parameters!Agency.Value, Fields!agency_id.Value, Fields!agency_name.Value, "Agencies") &amp; "   |   " &amp; Format(Parameters!DateFrom.Value, "MM/dd/yyyy") &amp; " to " &amp; Format(Parameters!DateTo.Value, "MM/dd/yyyy")</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>9.5pt</FontSize><FontWeight>Normal</FontWeight><Color>#555555</Color></Style></TextRun></TextRuns><Style><TextAlign>Left</TextAlign></Style></Paragraph></Paragraphs><Top>0.38in</Top><Left>0in</Left><Height>0.24in</Height><Width>7.50in</Width><Style><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox>
    <Tablix Name="Detail"><TablixBody><TablixColumns><TablixColumn><Width>2.0in</Width></TablixColumn><TablixColumn><Width>1.1in</Width></TablixColumn><TablixColumn><Width>1.1in</Width></TablixColumn></TablixColumns><TablixRows>
      <TablixRow><Height>0.28in</Height><TablixCells><TablixCell><CellContents><Textbox Name="h_offense_desc"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Offense</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>8.5pt</FontSize><FontWeight>SemiBold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style><TextAlign>Left</TextAlign></Style></Paragraph></Paragraphs><Style><BackgroundColor>#243A5E</BackgroundColor><VerticalAlign>Middle</VerticalAlign><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="h_crime_against"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Crime Against</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>8.5pt</FontSize><FontWeight>SemiBold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style><TextAlign>Left</TextAlign></Style></Paragraph></Paragraphs><Style><BackgroundColor>#243A5E</BackgroundColor><VerticalAlign>Middle</VerticalAlign><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="h_offenses"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Offenses</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>8.5pt</FontSize><FontWeight>SemiBold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style><TextAlign>Right</TextAlign></Style></Paragraph></Paragraphs><Style><BackgroundColor>#243A5E</BackgroundColor><VerticalAlign>Middle</VerticalAlign><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell></TablixCells></TablixRow>
      <TablixRow><Height>0.22in</Height><TablixCells><TablixCell><CellContents><Textbox Name="v_offense_desc"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>=Fields!offense_desc.Value</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>9pt</FontSize><FontWeight>Normal</FontWeight><Color>#242424</Color></Style></TextRun></TextRuns><Style><TextAlign>Left</TextAlign></Style></Paragraph></Paragraphs><Style><BackgroundColor>=IIF(RowNumber(Nothing) Mod 2 = 0, "#F3F6FA", "White")</BackgroundColor><BottomBorder><Color>#D6DCE5</Color><Style>Solid</Style><Width>0.5pt</Width></BottomBorder><VerticalAlign>Middle</VerticalAlign><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="v_crime_against"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>=Fields!crime_against.Value</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>9pt</FontSize><FontWeight>Normal</FontWeight><Color>#242424</Color></Style></TextRun></TextRuns><Style><TextAlign>Left</TextAlign></Style></Paragraph></Paragraphs><Style><BackgroundColor>=IIF(RowNumber(Nothing) Mod 2 = 0, "#F3F6FA", "White")</BackgroundColor><BottomBorder><Color>#D6DCE5</Color><Style>Solid</Style><Width>0.5pt</Width></BottomBorder><VerticalAlign>Middle</VerticalAlign><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="v_offenses"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>=Fields!offenses.Value</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>9pt</FontSize><FontWeight>Normal</FontWeight><Color>#242424</Color><Format>N0</Format></Style></TextRun></TextRuns><Style><TextAlign>Right</TextAlign></Style></Paragraph></Paragraphs><Style><BackgroundColor>=IIF(RowNumber(Nothing) Mod 2 = 0, "#F3F6FA", "White")</BackgroundColor><BottomBorder><Color>#D6DCE5</Color><Style>Solid</Style><Width>0.5pt</Width></BottomBorder><VerticalAlign>Middle</VerticalAlign><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell></TablixCells></TablixRow></TablixRows></TablixBody>
      <TablixColumnHierarchy><TablixMembers><TablixMember/><TablixMember/><TablixMember/></TablixMembers></TablixColumnHierarchy>
      <TablixRowHierarchy><TablixMembers><TablixMember><KeepWithGroup>After</KeepWithGroup><RepeatOnNewPage>true</RepeatOnNewPage></TablixMember><TablixMember><Group Name="Details"/></TablixMember></TablixMembers></TablixRowHierarchy>
      <DataSetName>Main</DataSetName><Top>0.75in</Top><Left>0in</Left><Height>0.5in</Height><Width>4.20in</Width></Tablix>
    <Textbox Name="RowCount"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>=Format(CountRows("Main"), "N0") &amp; " records"</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>8.5pt</FontSize><FontWeight>Normal</FontWeight><Color>#555555</Color></Style></TextRun></TextRuns><Style><TextAlign>Left</TextAlign></Style></Paragraph></Paragraphs><Top>1.33in</Top><Left>0in</Left><Height>0.22in</Height><Width>3.00in</Width><Style><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox>
  </ReportItems><Height>1.6in</Height></Body><Width>7.50in</Width>
  <Page><PageHeader><Height>0.3in</Height><PrintOnFirstPage>true</PrintOnFirstPage><PrintOnLastPage>true</PrintOnLastPage><ReportItems>
    <Textbox Name="Classification"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>CJI: authorized users only</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>8pt</FontSize><FontWeight>SemiBold</FontWeight><Color>#8A1C1C</Color></Style></TextRun></TextRuns><Style><TextAlign>Left</TextAlign></Style></Paragraph></Paragraphs><Top>0in</Top><Left>0in</Left><Height>0.22in</Height><Width>3.00in</Width><Style><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox>
  </ReportItems></PageHeader>
  <PageFooter><Height>0.3in</Height><PrintOnFirstPage>true</PrintOnFirstPage><PrintOnLastPage>true</PrintOnLastPage><ReportItems>
    <Textbox Name="Footer"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>="Page " &amp; Globals!PageNumber &amp; " of " &amp; Globals!TotalPages &amp; "   |   Run " &amp; Format(Globals!ExecutionTime, "MM/dd/yyyy h:mm tt")</Value><Style><FontFamily>Segoe UI</FontFamily><FontSize>8pt</FontSize><FontWeight>Normal</FontWeight><Color>#616161</Color></Style></TextRun></TextRuns><Style><TextAlign>Left</TextAlign></Style></Paragraph></Paragraphs><Top>0.05in</Top><Left>0in</Left><Height>0.22in</Height><Width>7.50in</Width><Style><PaddingLeft>4pt</PaddingLeft><PaddingRight>4pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox>
  </ReportItems></PageFooter>
  <PageHeight>11in</PageHeight><PageWidth>8.5in</PageWidth><LeftMargin>0.5in</LeftMargin><RightMargin>0.5in</RightMargin><TopMargin>0.4in</TopMargin><BottomMargin>0.4in</BottomMargin></Page>
  </ReportSection></ReportSections>
  <ReportParameters><ReportParameter Name="Agency"><DataType>Integer</DataType><Prompt>Agency</Prompt><ValidValues><DataSetReference><DataSetName>Agencies</DataSetName><ValueField>agency_id</ValueField><LabelField>agency_name</LabelField></DataSetReference></ValidValues></ReportParameter><ReportParameter Name="DateFrom"><DataType>DateTime</DataType><Prompt>Date from</Prompt></ReportParameter><ReportParameter Name="DateTo"><DataType>DateTime</DataType><Prompt>Date to</Prompt></ReportParameter></ReportParameters>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
</Report>