Sample_Report_Design.doc

Report Design Document System design Document

1. 5-Index Report Specifications

The K-Index is the Committee Listing Containing a given Candidate, it displays all committees that contain in a candidate name has been selected by his/her Office, Party and State.

1.1. Report Overview

There are six tables from two different databases involved in the design of this report.

· DISCLOSURE Database

· DIM_CMTE_IE_INF

· CURRENT_LINK

· DIM_CAND_INF

· SQL-Type Tables For Dropdown box Parameters, No joins, No structural relationships

· Cand_office

· Cand_Party

· Cand_state

1.2. Table Joins

"DISCLOSURE"."DIM_CMTE_IE_INF" "DIM_CMTE_IE_INF" LEFT OUTER JOIN

"DISCLOSURE"."CURRENT_LINK" "CURRENT_LINK" ON

"DIM_CMTE_IE_INF"."CMTE_ID"="CURRENT_LINK"."CMTE_ID") LEFT OUTER JOIN

"DISCLOSURE"."DIM_CAND_INF" "DIM_CAND_INF" ON

"CURRENT_LINK"."CAND_ID"="DIM_CAND_INF"."CAND_ID"

1.3. Report Structure

Table 1. Field Table

1.4. Business Logic

The Crystal reports parameters only allow user to have certain of 5-Reports, In this 5-Index report only for STATE, PARTY, OFFICE oriented candidates, not for particular candidate name. There will be another %-Index Report for a given name/id of a candidate by Criteria of STATE, PARTY, OFFICE

1.5. Crystal Formula Fields

None

1.6. Crystal SQL Expression Fields

None

1.7. Crystal Parameter Fields

Here is the screenshot of the 5-Index report User interface:

· Name = {? elecClYr }

Properties = Period of Report, translation of Election Cycle, Dropdown Box Criteria for Field = CURRENT_LINK.ELECTION_YR

· Name = {? CandOffice }, One of the Combination key search for a Candidate Properties = Dropdown Box, by COMMAND/SQL Type Table(View) Criteria for Field = DIM_CAND_INF.CAND_OFFICE

· Name = {? CandParty }, One of the Combination key search for a Candidate

Properties = Dropdown Box, by COMMAND/SQL Type Table(View) Criteria for Field = DIM_CAND_INF.CAND_

· Name = {? CandState }, One of the Combination key search for a Candidate

Properties = Dropdown Box, by COMMAND/SQL Type Table(View) Criteria for Field = DIM_CAND_INF.CAND_OFFICE_ST

1.8. Crystal Total Fields

None

1.9. Crystal Group Name Fields

None

1.10. Crystal Special Fields

Crystal System Date

Page N of M total

Page Total

Report Title

Page Counts

2. Data Dictionary

Table 2. Data Dictionary

Name

Committee type

Group#1 Lead

2.1. SQL Sample Code

SELECT "DIM_CAND_INF"."CAND_OFFICE", "DIM_CAND_INF"."CAND_NM", "DIM_CAND_INF"."CAND_ID", "DIM_CAND_INF"."CAND_OFFICE_ST", "CURRENT_LINK"."ELECTION_YR", "DIM_CMTE_IE_INF"."CMTE_ID", "DIM_CMTE_IE_INF"."CMTE_NM", "DIM_CAND_INF"."CAND_PTY_AFFILIATION"

FROM ("DISCLOSURE"."DIM_CMTE_IE_INF" "DIM_CMTE_IE_INF" LEFT OUTER JOIN "DISCLOSURE"."CURRENT_LINK" "CURRENT_LINK" ON "DIM_CMTE_IE_INF"."CMTE_ID"="CURRENT_LINK"."CMTE_ID") LEFT OUTER JOIN "DISCLOSURE"."DIM_CAND_INF" "DIM_CAND_INF" ON "CURRENT_LINK"."CAND_ID"="DIM_CAND_INF"."CAND_ID"

WHERE ("CURRENT_LINK"."ELECTION_YR">=1999 AND "CURRENT_LINK"."ELECTION_YR"<=2000) AND "DIM_CAND_INF"."CAND_OFFICE_ST"='US' AND "DIM_CAND_INF"."CAND_OFFICE"='P' AND "DIM_CAND_INF"."CAND_PTY_AFFILIATION"='DEM'

3. Screen-Looked Page Format