INTEGRATED COMPLIANCE INFORMATION SYSTEM
ICIS Reports - Schedule Evaluation
November 19, 2009
-------
Schedule Report Demo
The goal of this document is to step through the process of modifying a basic
schedule list report to show the compliance history and status of schedules
through the use of alerters, variables and measures.
The AYT Schedule Demo Base Report is a list of reports and schedules that
have a Scheduled Date between a user supplied date range. This base report
will be enhanced with Alerters to highlight non compliant conditions and provide
derived data fields using Formulas that show why a schedule or report is non
compliant. Finally, behavior of Dimension variables will be demonstrated and
modified to eliminate data that is not relevant to the desired report goal.
The AYT Schedule Demo Base used for the demonstration is located in the ICIS
Public Folders> Share Community folder.
AYT Schedule Demo Base Query View:
- TjEcft Query [F] Edit Ftoport
i|®|a|Hl dJAdd Query | T | ^ " ^Isftl
Data Properties
H - $1 ICIS-NPDES Ad Hoc Universe _^_
} 4 Number of Parent FRS Facilities
•* Number of Facility Interests
03- &D Fac Identifying & Descriptive Info
H" &) Facility Location Info.
i B3- &J FRS Parent Data
1 ®"SD All Classifications
: 03 • &) All Policies
E &l All Originating Programs
: ffl S3 Environmental Justice
I 03- &J Tribal Lands
: 03- &) Facility SICs
• 03- !£j) Facility NAICSs
! 03 Id3 Latlong
: 03- l£j Construction LatLong
Mttj ""acuity Comments
/ Facility Int. Zip Code Prompt
i "/ Facility Int. County Code Prompt
/ Facility Int. City Code Prompt
} Y Facility is not blank
• Y Exclude Migrated Facilities
i • - / Select Migrated Facilities
• / Exclude No Parent FRS Records
L / Select No Parent FRS Records
03- 1£] Permits
03 Ssl Narrative Condition/Comp Schedules
H &) Schedule Events
•> Number of Schedule Events
• • T Schedule Event Code
1 Schedule Event Desc
h-a Schedule Event RNC Category
'• ~l Schedule Event Type Flag
1 Schedule Event Comments
i E8BS1H8B3
T Actual Date
x
Last Refresh Date: November 4, 2009 1:31 :20 PM
-------
AYT Schedule Demo Base Report View:
0 3 - | (3 E ' Et)B* Que
y [D EdS Repor
•SRefreshData $f | 6J | 9 -
;tSj£J3f:i%'Vl19l&l«V'lT|::lzi-2: ' %'IE I^View Structure Q, Drill ttjD I
i jj jj B / u | m
Data Tetrifi^l'--". MaiJ P'Opcrtia:
: Data ¥
T Actual Date
L ~l Facility Name
i - NPDES ID
f Report Received Dale
: Sched Type Flag
i Schedule Date
; Schedule Event Code
Schedule Event Comments
Schedule Event Desc
~\ Schedule Event RNC Categoi
'• ~1 Schedule Event Type Flag
ll 1 JJ
Arranged by: Alphabetic order '
• affll * - A -|
iPage K <
1 » H
j Schedule Report 11/4W9
I BLOUNT SEAFOOD CORPORATION
I RI0001121
1 CS011
j CS011
CS011
[I 96193
i CS011
} CS011
j 96199
[! CE011
j 50008
i CS016
J 21599
) 55093
j CS032
BRADFORI
RIOOUTJ043
•-•-:.
! 02399
02610
( 02610
) 02610
X
Last Refresh Date: November 4, 2009 1:55:12 PM
\jDemrj,,. | | [? | j^i
-------
Alerters
The following group of sub alerters will be added to the Schedule Date field to
indicate compliance conditions through color:
Report/Schedule is late
Report/Schedule is not achieved
Report/Schedule has been received and achieved, but was late
Sub-Alerter1
1. Report or Schedule has not been received as of today.
Scheduled Date < CurrentDateQ and,
Report Received Date is null and
Show it as Red!
Creating Sub-Alerter 1
Click the Alerters button from the Report toolbar ^
1. The Alerters dialog box appears. Click New to define a new alerter.
2. From the Alerter Editor, shown below, enter name and a description for
the alerter.
3. Under Filtered Object or cell, select the Schedule Date field from the
object chooser button drop down -^
4. Under Operator, click the down arrow and specify Less than.
5. In the Operand(s) box, type in the CurrentDateQ function.
6. Click the Format... button and click the Color down arrow to select red.
7. To add the second condition (Report Received Date isnull), click the -±J
button and repeat steps 3-5.
8. Click OK to close the Alerter Editor
f* Alerter Editor
/w ... Alerter name:
Description:
Filtered object or celt
ScheduleDate
Operator Operand(s)
(Schedule Dale ...| | Less than T j JGurrentDateQ ...| -|
jeport Received Date _.„] | Is null _^J
If the above is true , then display: c e ' ' c ° nte nte Format
Add Sub-Alerter j Remove Sub-Alerter | F" Formula
OK | Cancel | Help
-------
Sub-Alerter 2
2. Report or Schedule has not been achieved as of today.
Schedule Date < CurrentDateQ and,
Report Received Date is not null and,
Actual Date is null
Show it as Green!
Creating Sub-Alerter 2
9. Select Add Sub-Alerter.
10. Repeat steps from Sub-Alerter 1.
| f: Alerter Editor
i
1
/w_. Alerter name:
Description:
Filtered object or cell
••••^^^^ x||
ScheduleDate
Operator Operand(s)
[Schedule Date ...| |l_essthan •*• | |currentDate() ...| — |
|eport Received Date _.J |ls null _^J
If the above is true, then display: Ml oontente Fomat
Add Sub-Alerter | Remove Sub-Alerter f~ Formula
Filtered object or cell
(schedule Date ^
Operator Operand(s)
J |l_essthan _^J |currentDateO _^JjJ
And jeport Received Date ...| |ls not null
(Actual Date ^
J |lsnull jj
If the above is true, then display: \ pormat il
Add Sub-Alerter | Remove Sub-Alerter | f~ Formula
OK | Cancel Help
-------
Sub-Alerter 3 and 4
3. Report or Schedule was received or achieved late
Actual Date > Schedule Date or,
Report Received Date > Schedule Date
Make it Blue!
Creating Sub-Alerter 3 and 4
11. Select Add Sub-Alerter.
12. Repeat steps from Sub-Alerter 1.
f; Alerter Editor
Alerter name: jScheduleDate|
Description:
If the above is true, then display:
Add Sub-Alerter | Remove Sub-Alerter |
Sub Alerter
Filtered object or cell Operator
Operand(s)
(schedule Date
Less than
H |currentDateO
And |ieport Received Date J j Is not null
[Actual Date _J |ls null
If the above is true, then display:
Cell contents
Format.
f~ Formula
Format ...
Add Sub-Alerter I Remove Sub-Alerter I
Sub-Alerter
Filtered object or cell Operator
Operand(s)
JActual Date _.J (Greater than J»J |schedule Date
If the above istrue.then display: Ce" conterlls
Add Sub-Alerter | Remove Sub-Alerter |
Sub-Alerter
Filtered object or cell Operator
Operand(s)
[Report Received Date ...| [Greaterthan ^| (scheduleDate
If the above istrue.then display: Ce" contenl5
Add Sub-Alerter | Remove Sub-Alerter |
f~ Formula
Format ...
f~ Formula
Format...
f" Formula ~^
OK
Cancel
Help
Alerter Behavior: (from Business Objects XI, The Complete Reference,
McGraw Hill)
Alerters are evaluated from top to bottom: if the first condition is met, then the
formatting specified in that condition applies; if the first condition is not met
then Web Intelligence proceeds to evaluate the second condition. If none of
the conditions are met, then the cell content and format remain unaffected.
-------
Formulas
View a Formula
P
1. Click the Sf. button to open the formula toolbar.
2. Click on the Report Title cell and view the formula. This formula has the
text "Schedule Report" concatenated(+) with the CurrentDateQ function.
Notice that the text is surrounded by quotations and the CurrentDateQ
function is not. Functions, variables and data fields do not need to be
surrounded by quotations.
3. Click on Properties tab to access the Format properties of the cell.
1 fr - 1 6
qM m |
;p»ge H <
[f H I
Data | Templates | Map | Properties | fx •$*) X V ^"Schedule Report " + CurrentDateQ
Actual Date
Faciltty Name
NPDES ID
Report Received Date
Sched Type Hag
Schedule Date
Schedule Event Code
Schedule Event Comments
Schedule Event Desc
Schedule Event RNC Category
'"Schedule Report " + CurrentDateO
-[HPDES ID]
=[ScherJulE=[SthPiJi.i =[B
l =[3rher!ulo =[Atlual D.sli =[RsnortR
-------
Create a Formula
Use a Formula to create a custom report heading.
F^l
1. Click the ! button and select Data/Properties.
2. Click Templates tab and find the Report Elements>Free-Standing
Cells>Formula and Text Cell> Blank Cell. Drag the Blank Cell to the
report below the Report Title.
3. Click the f* to open the Formula Editor window.
4. Click the Data tab and drag the State Code field to the formula box.
5. Type + "" + to add a space to the formula.
6. Drag the Curr. Major Minor Status field to the formula bar.
7. Type + " Permittee Schedules From " +
8. Click on the fx Functions tab. Make sure cursor is blinking at the end of
the formula text
9. Drag the Min() function to the blinking cursor.
10. Click on the Data tab and drag the Schedule Date field and drop between
the parenthesis.
11.Type + "To" +
12. Click the fx Function tab and drag the MaxQ function.
13. Click the Data tab and drag the Schedule date and drop between the
parenthesis.
14. Click OK button.
15. View report and adjust format of the cell as needed.
3Sfl
[$lData | fx Fund.. Hi Qper.. |
B -$1 AYT Schedules Demo Complet
~l Actual Date
"1 Curr. Major Minor Status
1 Facility Name
!• ~l NPDESID
: "1 Report Received Date
; ~\ RNC Detection Code
• ~\ RNC Detection Desc
f ~t RNC Resolution Code
[• ~t RNC Resolution Desc
"1 Sched Type Flag
- ~\ Schedule Date
1 ~l Schedule Event Code
- ~l Schedule Event Comments
; T Schedule Event Desc
~l Schedule Event RNC Categ
~l Schedule Event Type Flag
~t Schedule Violation Code
T State Code
j«L I >
X v
=[State Code] + " " + [Curr. Major Minor Status] + " Permttee Schedules From " + Min([Schedule Date]) t " To " + Max([Schedule Date])!
OK Cancel Help
*]
1
-------
Variables
A variable is used to store a formula to calculate the number of days late a
report has been received and achieved.
Create a Variable
1.
2.
3.
4.
6.
*:i
Click the i Variable Editor button.
Enter vRecvLateDays into Name box. Variable naming convention uses
"v" preceding the variable name.
Click the down arrow of the Qualification box and select Measure.
Drag or double click the DaysBetweenQ function to the Formula box.
Notice the format in the Description box.
Drag or double click the Scheduled Date and Report Received Date
separated by a ; to the Formula box.
Click the OK button.
B Variable Editor
Data fx Functions
Operators
I AYT Schedules Demo Final
-~J Actual Date
•-~l Curr. Overall RNC Status Code
111 "1 Facility Name
~! Narrative Condition Code
~) Narrative Condition Desc
~) NPDESID
~) Regional Docket Number
~J Report Received Date
•~l RNC Detection Code
~\ RNC Detection Date
~) RNC Detection Desc
-~l RNC Resolution Code
~1 RNC Resolution Date
~1 RNC Resolution Desc
"I Scried Number
~) Sched Type Flag
~l Schedule Date
•"1 Schedule Desc
•~l Schedule Event Code
•~) Schedule Event Comments
•~1 Schedule Event Desc
~) Schedule Event RNC Category
~) Schedule Event Type Flag
Variable Definition
Name:
Qualification: J Measure
Type:
Formula:
X N
=DaysBetween([Schedule Date]; [Report Received Date])
Description
OK
Cancel
Help
-------
7. Repeat steps 2 - 5 to create the vAchvLateDays variable using the Actual
Date field.
8. Drag the variables onto the report.
g» Variable Editor
) Data
fx Functions | EJ Operators |
AYT Schedules Demo Base
-~l Curr. Major Minor Status
"I Curr. Overall RNC Status Code
• ~) Facility Name
1 NPDESID
• "I Report Received Date
~1 RNC Detection Code
"1 RNC Detection Desc
•"I RNC Resolution Code
-~l RNC Resolution Desc
~l Sched Type Flag
- ~l Schedule Date
~1 Schedule Event Code
• ~l Schedule Event Comments
-"1 Schedule Event Desc
-~l Schedule Event RNC Category
~\ Schedule Event Type Flag
-~\ Schedule Violation Code
~l State Code
J vRecvLateDays
Variable Definition
Name: |vAchvLateDays
Oualification: '•* Measure
Type:
unknown
Formula: -
X s
:DaysBetween([Schedule Date];[Actual Date]p
Description
Actual Date
OK
Cancel
Help
6 Jj:i
obild =5590238:togon Token =RUBV.RTPNC.EPA.GOV@iS7264O JkTQlmb
•o i A i it
[F| Edit Report
|fe - [1C B1 View Res Js 'VDrill
jj B
| Properties | fx
AYT Schedules Demo Base
~1 Actual Date
"I Curr. Major Minor Status
•T Curr. Overall RNC Status Coi
~\ Facility Name
1 NPDESID
"I Report Received Date
"1 RNC Detection Code
"1 RNC Detection Desc
~l RNC Resolution Code
"I RNC Resolution Desc
1 Sched Type Flag
"I Schedule Date
~l Schedule Event Code
~l Schedule Event Comments
T Schedule Event Desc
"1 Schedule Event RNC Categoi
~l Schedule Event Type Flag
~1 Schedule Violation Code
~l State Code
-ort " + CurrentDateQ
" +[Curr. Major Minor Status] + " Permittee Schedules From "+ Min([Schedule Date]) + " To " + Msx([Schedule Date])
£,Event Desc] =[Scheciule Event Comments]
=[Report Re< icvLateDay;
-------
Report View:
=RUBV.RTPNC.EPA.GOVi9561354Jev(fCXqBCjcFMh - Windo
D 13 - | ai I B - ®Edil Query |j|Eit Report
t» Refresh Data
B B>Viewarudur» | r4 Drill HI
JJ9
/ D |gg
Pase
fx #i X V |-[Acti»LateD»ys]
Schedule Report 11/5J09
Rl Major Permittee Schedules From 1/1/90 To 12/31/08
BLOUNT SEAFOOD CORPORATION
RI0001121
CS011 Plan, Report, or Scope of Worl PRODUCTION REPORT 5 Annual Repor R 2 P
CS011 Plan, Report, or Scope of Worl PRODUCTION REPORT 4 Annual Rspor R 2 P
CS011 Plan, Report, or Scope of Worl PRODUCTION REPORT 3 Annual Repor R
96199 Toxics Prescreening Monitonr PPSCAM 2 S
CS011 Plan, Report, or Scope of Worl PRODUCTION REPORT 2 Annual Repor R
CS01 1 Plan, Report, or Scope of Worl EVALUATE BOD, TSS, GREASE Draft En( R
96199 Toxics Prescreening Monitorir PPSCAN 1 S
C801 1 Plan, Report, or Scope ofWorl PRODUCTION REPORT 1 Annual Rspor R
50008 Study Results RESULTS OF DATA COLLECTED 1 2E R
2
2
1
2
2
2
2
P
P
P
J
P
P
J
1/15(08
1/15/07
1/15(06
1/15(05
1/15/05
4/25(04
1/15/04
1/15/04
8/31/98
1/18/08
1/16/07
4/1 4/06
1/12/05
3/31/05
4/23/04
2/1 6/05
7/1/04
8/27/98
3 1/18/08
1 1/16/07
89 4/14(06
-3 1/19/05
75 3/31/05
-2 4/23/04
398 2/16/05
168 7/1/04
-4 8/27/98
3
1
89
4
75 1
-2
398
168
-4 1
Note: The number of days late is negative(-) when a report has been
received or achieved before the due date.
10
-------
Use Alerter and the color "white" to make data invisible
Reports that are not late result in a negative number being displayed. This
information can be suppressed by using an Alerter to change the text font
color to white.
1. Click on AchvLateDays column and click on the ^ Alerter Button.
2. Click New button and enter ActualLate in the Alerter name box.
3. Keep the AchvLateDays in the Filtered object or cell box.
4. Select Less than or Equal to from Operator drop down.
5. Set Operand to 0.
6. Click Format button and select white from the drop down.
7. Click OK button.
8. Repeat for RecvLateDays column.
Sub-Alerter
Filtered object or cell Operator
Operand(s)
JAchvLateDays ...| |Less than or Equal to jj |0
If the above is true, then display:
Format...
Add Sub-Alerter | Remove Sub-Alerter |
f~ Formula
OK |
i Cancel
|
Help
Report View:
LJCJ -
es | ED - ' (BE
(tit Query
\i\ Eta Report
^Refresh Data
iPage I-
i
Kt I
Schedule Report 11(5(09
Rl Major Permittee Schedules From 1/1/90 To 12/31/08
BLOUNT SEAFOOD CORPORATION
RI0001121
EventCode EventDesc
jEventType |EventCat :SchedType|SchDate ActDate AchvLateDajRecDate |RecvUteDa]
CS011 Plan, Report, or Scope ofWorl PRODUCTION REPORT 5 Annual Repor R
CS011 Plan, Report, or Scope ofWorl PRODUCTION REPORT 4 Annual Repor R
CS011 Plan, Report, or Scope ofWorl PRODUCTION REPORT 3 Annual Repor R
96199 Toxics Prescreening Monitorir PPSCAN 2 S
CS011 Plan, Report, or Scope ofWorl PRODUCTION REPORT 2 Annual Repor R
CS011 Plan, Report, or Scope orworl EVALUATE BOD, TSS, OREASE Draft Ent R
96199 Toxics Prescreening Monitorir PPSCAN 1 S
C3011 Plan, Report, or Scope ofWorl PRODUCTION REPORT 1 Annual Repor R
50008 Study Results RESULTS OF DATA COLLECTED 12E R
1/15(08
1(15/07
1/15/06
1/15/05
1/15/05
4/25/04
1/15/04
1/15/04
8(31/98
1/18/08
1/16(07
4(14(06
1(12(05
3(31(05
4(23(04
2(16/05
7/1/04
8/27/98
3
1
89
75
398
168
1/18(08
1(16(07
4(14(06
1(19(05
3(31(05
4(23(04
2(1 6(05
7(1(04
8(27/98
3
1
89
4
7:5
398
168
11
-------
Changing a Dimension to a Measure to Eliminate Duplicate Records
The RNC Detection Code is a "dimension" variable. When this field is added
as a column to the report duplicate records will occur.
1. Drag the RNC Detection Code Field to the report.
2. Notice 2 rows for one report where RNC Detection Code has 2 different
values. There are 2 "dimensions" of RNC represented by the "N" and the
null.
View Report:
ob1Id=56Q640&logonToken =RUBY.RTPNC.EPA.GOV^562265JWXkngH67q5E 1 el -
| E * i 12)6* Query U Eiil Report
Si fr
I A I A • I T I =1 11 •
• I tl ES'View Structure | rJ.Dril
/ B
hedule Report 11(6/09
Major Permittee Schedules From 1/1/90 To 12/31/08
OUNT SEAFOOD CORPORATION
1001121
1011
:011
1011
:011
1«9
:011
(611
Plan, Report, or Scope of Worl PRODUCTION REPORT 5 Annual Repor R 2 P
Plan, Report, or Scope of Worl PRODUCTION REPORT 4 Annual ReporR 3 P
Plan, Report, or Scope of Worl PRODUCTION REPORT 3 Annual ReporR 2 P
Plan, Report, or Scope of Worl PRODUCTION REPORT 3 Annual ReporR 2 P
Toxics Prescreening Monitorir PPSCAN 2 S 2 P
Plan, Report, or Scope ofWorl PRODUCTION REPORT 2 Annual Repor R 2 P
Plan, Report, or Scope ofWorl PRODUCTION REPORT 2 Annual Repor R J2 [P'
1/15108
1/15/07
1/15(06
1/15(06
1(15(06
1(15(05
1(15(05
1(18/08
1(16/07
4(1 4(06
4(1 4(06
1(12(05
3(31(05
3(31(05
3 1(18(08 3
1 1(16(07 1
89 4/14(06 89
89 4(14(06 89
1(19(05 4
75 3(31(05 75
75 3(31(05 75
N
N
The not null value of the dimension field can be captured by creating a variable
and saving RNC Detection Code as a "measure" variable.
1. Click '-*' and enter RNC Detect Code into Name box.
2. Select Measure from Qualification drop down box.
3. Drag RNC Detection Code data field into Formula box.
4. Click OK button.
View Report:
*/?ob(Id=56Q64Q&logonToken=RUBY.RTPNC.EPA.GQV(a561354JevircXqBCjcFMh - Windows Internet Explorer
' [ dl | Q ' i ^Edft Query , 35 Edit Report '
|tfl % y I -0 I & I & - I Y I -I Ji - £ - | fr - | Q] I^View Structure j Q.MI Ijj |
S-RefreshData ^ \
H X
IPagem 1 KM
hedule Report 11/5(09
Major Permittee Schedules From 1/1/90 To 12/31/08
OUNT SEAFOOD CORPORATION
1001121
W11
i011
139
Plan, Report, or Scope OfWorl PRODUCTION REPORT 5 Annual Repor
Plan, Report, or Scope of Worl PRODUCTION REPORT 4 Annual Repor
Plan, Report, or Scope ofWorl PRODUCTION REPORT 3 Annual Repor
Toxics Prescreening Monitorir PPSCAN 2
}Q11 Plan, Report, or Scope ofWorl PRODUCTION REPORT 2 Annual Repor
R 2 P
R
R
S
R
2
2
2
2
P
f
P
P
1/15(08
1/15/07
1/15(06
1/15/05
1(15(05
1(18(08
1(16/07
4(1 4(06
1(12(05
3(31(05
3
1
89
75
1/18/08 3
1/16/07 1
4/1 4/06 89
1/19/05 4
3/31/05 75
N
N
5.
Create Measure variable for RNC Resolution Code, RNC Resolution
Description, and RNC Detection Description and add to report.
12
-------
Use Max() function to show the worst violation
Schedule violation data field is a dimension. A single schedule or report can
have more than one schedule violation condition.
1. Drag Schedule Violation Code to report.
2. View report and notice that schedules with more than one violation code
have more than one row.
3. Create a measure variable called Sch Vio Code. Enter Max(Schedule
Violation Code) into the Formula box.
4. Drag the Sch Vio Code variable over the Schedule Violation Code to
replace.
5. View report and notice that only the worst violation for each schedule is
listed.
View Report:
'obiId=560640&:loqonToken=RUB¥.RTPNC.EPA.GOV(ft5624233HVr07AB8H¥t8P6 - Windows Internet Enpl
Q a -\g\rn
it Query \f\ Edit Report
" | IB i>View Structure | r-i Drill I
jdL
PnTrp-hHcrtci
iPass M <
J
es From 1/1/90 To 12/31/08
TION
BrertTvpe Ei/ervtCat Schei(Tyi)e!schDate
e ofWorl PRODUCTION REPORT 5 Annual Repor R
e ofWorl PRODUCTION REPORT 4 Annual Repor R
e ofWorl PRODUCTION REPORT 3 Annual Repor R
Monitorir PPSCAN 2
e ofWorl PRODUCTION REPORT 2 Annual Repor R
e OfWorl EVALUATE BOD, TSS, GREASE Drafl Em R
Monitorir PPSCAN 1
e ofWorl PRODUCTION REPORT 1 Annual Repor R
R 2
R
S
R
R
S
2
2
2
2
2
R 2
P
P
P
P
J
f
P
1)15/07
1)15106
1)15)05
1)15)05
4(25(04
1(15)04
1)15)04
1(18)08
1 (16)07
4(14/08
1(12)05
3(31)05
4(23)04
2(16)05
7(1)04
3 1)18)08
1 1)16)07
89 4)14)06
1)19)05
75 3/31)05
4/23)04
398 2)16)05
168 7)1(04
3 C20
1 C20
89 C40
4
75 C40
Highlight Actual and Report Received date that may have caused RNC
detect code.
1. Click on ActDate Column, then click on ^' Alerter button.
2. Click on New and enter RNC Detect in the Name box.
3. Choose RNC Detect from the filtered object or cell box.
4. Select is not null form the Operator drop down box.
5. Format cell contents to be red.
6. Click Ok button.
7. Apply RNC Detect Alerter to RecDate column by clicking on the
Alerter button and select RNC Detect checkbox.
Show the Final Current RNC status for the permittees.
1. Drag the Curr. Overall RNC Status Code to the report.
13
------- |