Management Report Request & Construction

1. Management Report Request & Construction Authored and Presented by Barry Kaye, EPA Region2 RCRAInfo System Administrator 2008 RCRAInfo National Users Conference, Aug2-5 2005 Nashville,Tennessee

2. Course Goal: Replicate the following

3. Why use Bwr_Basic to compute Gen status? See page 6 for answer.. Report Requirements Fields: Id, Name, Address, Latest Evaluation (Inspection) Date (fitting WHERE Criteria), RCRA GenStatus, BRS Tons (from Bwr_Basic), BRS GenStatus (Calculated from BRS Tons) For: ‘State’ Active Generators (IOR_TDR_ID) per 2003 BRS Shipper Totals (from BWR_BASIC) Where: Last RCRA Inspection was greater than ‘X’ yrs ago (or Never Inspected) Parameters: Server-side: State; YR (specify years since last inspection) Client-side: LandType; GeoOption (By Cnty/Name; State_District/Name or Name) Reasons I chose this report to showcase: 1) New features in CR9 2) CME based report – fits in with Conference theme 3) Limited display fields – easier to recreate in class than a comprehensive report 4) Combines RCRA and BRS data and criteria –interesting, uncommon and useful 5) Quick executing (given smaller States and/or ‘X’ yrs chosen Hmmm.. .let’s see – how can I torture the RCRA DBA today? I’ve got it! ...a new inspection targeting scheme combining data and conditions from RCRA and BRS data with no prior template to draw any help from…grrreat! Norm, transpose my thoughts on this to Barry Reasons for Report (What is Management Thinking?) 1) Need, Users? EPA Regions and their States are constantly looking for effective means to target inspections. This is one method. This report targets facilities that have not been inspected in the last 5 years but which “were previously inspected” and are Active according to 2003 BRS Commercial TSD Waste Received data 2) Purpose? Target active LQG and SQG facilities that would be of higher interest than random inspections. 3) What different? This report uses non-standard data coming from the BRS BWR_Basic file. Alternately, this report can be generated using State Manifest data of all generators in a given time period. 4) Land Type Parameter? Primarily to target Municipal, State or Federal Facilities using current manifest or Biennial Report Waste Commercial TSD Waste Received data. Land Type is available in RCRAInfo Management and RCRARep Reports, except that BRS Waste Received and/or Manifest data is not used with those reports.

4. Data Flow for Management Report HHandler2 / HReport_Univ Cevaluation (or CMECOMP) WAR (Bwr_Basic) SQL SQL Result Set Not Inspected in “X” yrs or Never Inspected Result Set Active 2003 Shippers RCRA & BRS Result Sets Referenced / Joined in CR or Join RCRA & BRS Results via SQL first Reference Combined Results in CR

5. WAR MODULE Generator TSD Generator

6. Making use of Bwr_Basics TSD/Generator “Many-to-Many” Relationship Looking for: Sum of all waste shipped per Generator (to any/all TSD’s) to later compute it’s Generator Status (LQG, SQG, CEG, etc) Io_Tdr_qty is the waste TSD’s report to BRS as received from each Generator TSD (Handler_id) Gen (Io_tdr_id) TSD1 Receives waste from Gen1..Gen4 (Sum all waste into TSD1) 1 Io-tdr-qty 1 Why use Bwr_basic to compute Gen status? Bwr_basic has an advantage over Bgm_basic because Bgm_basic only includes those LQG’s that “self-reported” to EPA or it's authorized States for that year. However, the universe of all generators per State can be orders of magnitude larger. Ex: In 2003 roughly 59 facilities reported as LQG’s to Puerto Rico Environmental Quality Board (PREQB). However, approx 800 PR generators manifested waste to commercial TSDs nationally, 113 computing as Reg2 LQG’s (almost twice the LQG’s self-reporting to PREQB). The National report provided c:\workshop\Nat-NeverInsp.pdf & Nat-NoInsp5.pdf shows most LQG’s are inspected every 5 years. However, “active” SQGs are also good inspection targets. By using Bwr_basic we can capture these SQGs (and CEG’s) for targeting. In addition, we can compare Bwr_basic against Bgm_basic, to verify if the reporting LQGs universe is generally compliant. BWR_BASIC 2 Io-tdr-qty 2 PROOF THERE CAN BE > 1 TSD/GEN SELECT DISTINCT B."IO_TDR_ID", COUNT(DISTINCT B."HANDLER_ID") TSD_CNT FROM "RCRA"."BWR_BASIC" B WHERE B."HANDLER_ID" LIKE 'PR%' and B."REPORT_CYCLE" = 2003 GROUP BY B."IO_TDR_ID" HAVING COUNT(DISTINCT B."HANDLER_ID") > 1 ORDER BY 2 DESC, 1 C:\Workshop\Proof.qry 3 Io-tdr-qty 3 4 Io-tdr-qty 4 Waste Sent from Gen4 to TSD1..TSD4 (Sum all Waste from Gen4)

7. BRS CODE (c:\workshop\brsdata.qry) SELECT DISTINCT B."IO_TDR_ID" IO_TDR, SUM(B."RECEIVED_TONS_CALC") TONS, DECODE(SUBSTR(SUM(B."RECEIVED_TONS_CALC")-1.2,1,1), '-', 'CEG', DECODE(SUBSTR(SUM(B."RECEIVED_TONS_CALC") - 12 , 1,1), '-', 'SQG', 'LQG')) TONSUNIV FROM "RCRA"."BWR_BASIC" B WHERE B."REPORT_CYCLE" = '2003' and ((SUBSTR(B."IO_TDR_ID", 1,2) in ({?STATE})) or ('1' in ({?STATE}))) GROUP BY B."IO_TDR_ID" Notes on SQL GROUP: Do not aggregate fields being Grouped WHERE: or ('1' in ({?STATE})) if {?STATE} Parameter = 1, then to 1 = 1 (always true) letting every Id to be selected, resulting in a National Report SELECT: DECODE(SUBSTR(SUM(B."RECEIVED_TONS_CALC")-1.2,1,1), '-', 'CEG', DECODE(SUBSTR(SUM(B."RECEIVED_TONS_CALC") - 12 , 1,1), '-', 'SQG', ‘LQG')) TONSUNIV The above DECODE transforms the Summed TONS into it’s Gen Status based on RCRA regulations. The TONS (SUM(B."RECEIVED_TONS_CALC") is substituted into the DECODE wherever the precalculted TONS would be specified. This SQL calculated the TONS twice (in a sense) – but it is faster than the alternative coding, which would be: SELECT DISTINCT B.”IO_TDR”, TONS, DECODE(SUBSTR(TONS-1.2,1,1), '-', 'CEG', DECODE(SUBSTR(TONS - 12 , 1,1), '-', 'SQG', 'LQG')) TONSUNIV, FROM "RCRA"."BWR_BASIC" B, (SELECT DISTINCT B."IO_TDR_ID" IO_TDR,SUM(B."RECEIVED_TONS_CALC") TONS, FROM "RCRA"."BWR_BASIC" B WHERE B."REPORT_CYCLE" = '2003' and ((SUBSTR(B."IO_TDR_ID", 1,2) in ({?STATE})) or ('1' in ({?STATE}))) GROUP BY B."IO_TDR_ID“) WHERE B."REPORT_CYCLE" = '2003' and B."IO_TDR_ID" = IO_TDR and ((SUBSTR(B."IO_TDR_ID", 1,2) in ({?STATE})) or ('1' in ({?STATE}))) ORDER BY B.”IO_TDR “ “In-Line View” (One Pass, Virtual Table)

8. Get: 1) Id, Max(EvalDate) from Cevaluation 2) Id, Name, Address, GenStatus, and LandType(s) from Hreport_Univ 3) “Virtually Sever Join” to Cevaluation for “Never Inspected” Option - Must do this to get Hreport_Univ id’s that do not exist in Cevaluation Last RCRA Inspection greater than ‘X’ yrs ago (or Never Inspected) HReport_Univ Hhandler_Id Activity_Location Cevaluation Fk_Hbasichandler_Id Activity_Location Outer Join: Id & Activity Outer Join allows us to apply Max/Min Conditions to Cevaluation to retrieve only those applicable ids, and still get all Hreport_Univ id’s not having any Evaluations (Never Inspected id) Note: Equal Join limits Result Set Id’s to - Id’s in Cevaluation - Further limit Id’s to Cevaluation WHERE clauses Outer Join lifts both above restrictions

9. What does the Outer Join do? It creates in memory, a file of everything from the Left Table and whatever applies (ex: MaxEVDate) from the Right Table. Can now determine “Inspected” & “Not Inspected” facilites simultaneously. Use SQL to select from these results based on report Option

10. Inspection Code (c:\workshop\inspdata.qry) SELECT DISTINCT U."REGION" REG, U."STATE" ST, U."HANDLER_ID" ID, U."HANDLER_NAME" NAME, U."GENSTATUS" GEN, U."STATE_DISTRICT" DISTRICT, DECODE(U."LOCATION_STREET_NO", null, U."LOCATION_STREET1",U."LOCATION_STREET_NO" || ' ' || U."LOCATION_STREET1") LSTREET1, U."LOCATION_CITY" LCITY, U."LOCATION_ZIP" LZIP, U."LOCATION_COUNTY_NAME" COUNTY, DECODE(U."LAND_TYPE", null, '-', ' ', '-', U."LAND_TYPE") LANDTYPE, DECODE(U."OWNER_TYPE", null, '-', ' ', '-', U."OWNER_TYPE") OWNTYPE, DECODE(U."OPERATOR_TYPE", null, '-', ' ', '-', U."OPERATOR_TYPE") OPTYPE, MAX(C."EVALUATION_DATE") MAXEVDATE FROM "RCRA"."HREPORT_UNIV" U, "RCRA"."CEVALUATION" C WHERE ( (U."STATE" in ({?STATE})) or ('1' in ({?STATE})) ) and U."ACTIVITY_LOCATION" = U."STATE" and U."HANDLER_ID" = C."FK_HBASICHANDLER_ID"(+) and U."ACTIVITY_LOCATION" = C."ACTIVITY_LOCATION"(+) GROUP BY U."REGION", U."STATE", U."HANDLER_ID", U."HANDLER_NAME", U."GENSTATUS", U."STATE_DISTRICT", DECODE(U."LOCATION_STREET_NO", null, U."LOCATION_STREET1", U."LOCATION_STREET_NO" || ' ' || U."LOCATION_STREET1"), U."LOCATION_CITY", U."LOCATION_ZIP", U."LOCATION_COUNTY_NAME", U."LAND_TYPE", U."OWNER_TYPE", U."OPERATOR_TYPE" HAVING DECODE({?YR}, '0', DECODE(MAX(C."EVALUATION_DATE"), NULL, TO_DATE('01011500', 'MMDDYYYY'), MAX(C."EVALUATION_DATE")), MAX(C."EVALUATION_DATE")) < DECODE({?YR}, '0', TO_DATE('01021500', 'MMDDYYYY'), ADD_MONTHS(SYSDATE, - (12* {?YR})) Why use U.Activity (not C.Activity) for {?STATE} conditions? Join Statements Notes on SQL GROUP: Do not aggregate fields being Grouped 1) Specifiy all SELECT’ed fields in GROUP BY clause to not enforce Aggregation of those fields in SELECT or 2) Aggregate all SELECT’ed fields so that only ID needs to be Grouped. HAVING: Having applies conditions on the results of the GROUP BY. In this case on the combined data (showing the Conditionally and Never Inspected rows). Toggles the DECODE between a parameter of 0 (Never Inspected) and “Not 0” (Not inspected in “Not 0’ years) ADD_MONTHS(SYSDATE, - (12* {?YR})) This function allow the addition or substraction of ‘X’ months from a reference date) WHERE: or ('1' in ({?STATE})) if {?STATE} Parameter = 1, then to 1 = 1 (always true) letting every Id to be selected, resulting in a National Report and U."ACTIVITY_LOCATION" = U."STATE“ ensures we only gets activities that match the State (1st two digits if Id) of interest

11. Query/SQL Methods Note: The SQL needed to achieve these results can Not be achieved via the Crystal Standard Report Wizzard. SQL must be coded directly for Crystal to report off of. DIRECT SQL USAGE (Two Methods) 1) Crystal Query (.qry): Use Crystal SQL Designer (CSD) to create a proprietary SQL file that CR can use as a datasource for reporting. The .qry is really a .txt file 1) containing SQL you produced at it’s interface 2) Saved with specific Crystal and Database Logon information (ASCII Characters). The .qry is flexible, can be run directly through the CSD, giving back spreadsheet/column-style results, or can be used as a CR datasource, where applicable logon and data parameters will be passed to CR (except the password) upon running the report. CR will then apply the users format to the result set of the .qry. CR can further restrict the .qry results thorugh report Selections – but can never get more data than the .qry SQL code allows for. _____________________________________________________________________________________ Advantage: Run SQL directly through CSD and analyze data before CR reports on it Use SQL to aggregate data on HQ Server. Limit I/O and Processing Time Disadvantage: Need to maintain a separate .qry file. _____________________________________________________________________________________ Choose .qry as datasource upon creating report. Must enter Password and selection for any Pass-Through Parameters the CSD is applying to RCRAInfo Choose ODBC connection, logon; Choose Tables/Views/Stored Procedures RCRAInfo CR (.rpt) CSD (.qry) Can choose from available .qry fields, add restrictions, formatting and/or client-side parameters to manipulate .qry result set Static SQL, or SQL /w Pass-Through Parameters to RI Saved w/ or w/o Data Saved w/ or w/o Data Q: What does a .qry with “Saved” data do to the .rpt

12. CSD = cqw32.exe Only available up to CR8.5, however CR9+ can connect to .qry’s Reason: CR9+ supports direct SQL entry (Add_Command)…however there are still advantages to .qry’s (see next page)…

13. 2) “ADD Command” (New to CR9). Shows as the first item in list of Database Schemas (See Next Page). When selected, acts as an editor for saving typed/pasted SQL. Can be Joined to other actual Tables/Views. ________________________________________________________________________________ Advantage: - No Need to maintain separate file (ex: .qry) - Create multiple Add_Commands and Join them - Join Add_Command to other Table(s) or .qry(s) - Can Add to Repository (for future use with a separate Report) Disadvantage: - Cannot execute directly (ex: .qry) - No Save /w Data option – always live hit - No Visual Interface for creating SQL (unlike CSD) - If Joined, fields not Indexed as Oracle (slows performance) Same as .qry: - Supports Parameters - CR limited to result set - Edit/ReSave at any time

14. Right Click for Edit/View Command Menu SQL Typed/Pasted Here CR Parameters Created to Correspond with SQL Parameter

15. Data Flow for Management Report HHandler2 / HReport_Univ Cevaluation (or CMECOMP) WAR (Bwr_Basic) What are Advantages and Disadvantages of creating each SQL block as a .qry or Add_Command? SQL SQL Result Set Not Inspected in “X” yrs or Never Inspected Result Set Active 2003 Shippers RCRA & BRS Result Sets Reference and Join in CR What could be drawback of this method? or Join RCRA & BRS Result via SQL first Reference Combined Results in CR

16. Q: What are Advantages / Disadvantages of creating each SQL block as a .qry or Add_Command? A: WAR SQL: Because WAR is static, there seems no need to run a live query for each report run. Since Add_Command are always live, a .qry (saved with data) seems a good option. However, how much data is being saved? A States worth? a Region? National? Depends on the need. If all National data is saved, any request can be covered, but if the report only requests “PR”, the data is there, but it takes more time to filter through it, since the Indexing is poor compared to Oracle Tables directly. Alive run again PR might be quicker, but a live run against the Nation may prove difficult. One needs to test. _______________________________________________________________________________________ Insp SQL: Because this query is date sensitive (from SYSDATE minus X yrs), it must be live; Add_Command or .qry (saved without data) can be used. This live SQL can be very slow (ex: Regional, National) or very quick (ex: PR). _______________________________________________________________________________________ Note: Crystal will allow a GUI-based joining of these queries; However the actual “reading of records” to CR is slow due to each side not indexed.

17. Join Results of BRS and RCRA Queries a) Run and Save via SQL Designer directly; b) Connect a datasource to CR; c) Paste as Add_Command in CR SELECT * FROM (SELECT DISTINCT B."IO_TDR_ID" IO_TDR, SUM(B."RECEIVED_TONS_CALC") TONS, DECODE(SUBSTR(SUM(B."RECEIVED_TONS_CALC")-1.2,1,1), '-', 'CEG', DECODE(SUBSTR(SUM(B."RECEIVED_TONS_CALC") - 12 , 1,1), '-', 'SQG', 'LQG')) TONSUNIV FROM "RCRA"."BWR_BASIC" B WHERE B."REPORT_CYCLE" = ‘2003‘ and ((SUBSTR(B."IO_TDR_ID", 1,2) in ({?STATE})) or (‘1' in ({?STATE}))) GROUP BY B."IO_TDR_ID“ ), (SELECT DISTINCT U."REGION" REG, U."STATE" ST, U."HANDLER_ID" ID, U."HANDLER_NAME" NAME, U."GENSTATUS" GEN, DECODE(U."LAND_TYPE", null, '-', ' ', '-', U."LAND_TYPE") LANDTYPE, DECODE(U."OWNER_TYPE", null, '-', ' ', '-', U."OWNER_TYPE") OWNTYPE, DECODE(U."OPERATOR_TYPE", null, '-', ' ', '-', U."OPERATOR_TYPE") OPTYPE, MAX(C."EVALUATION_DATE") MAXEVDATE, U."STATE_DISTRICT" DISTRICT, DECODE(U."LOCATION_STREET_NO", null, U."LOCATION_STREET1", U."LOCATION_STREET_NO" || ' ' || U."LOCATION_STREET1") LSTREET1, U."LOCATION_CITY" LCITY, U."LOCATION_ZIP" LZIP, U."LOCATION_COUNTY_NAME" COUNTY FROM "RCRA"."HREPORT_UNIV" U, "RCRA"."CEVALUATION" C WHERE ((U."STATE" in ({?STATE})) or ('1' in ({?STATE})) ) and U."ACTIVITY_LOCATION" = U."STATE" and U."HANDLER_ID" = C."FK_HBASICHANDLER_ID"(+) and U."ACTIVITY_LOCATION" = C."ACTIVITY_LOCATION"(+) GROUP BY U."REGION", U."STATE", U."HANDLER_ID", U."HANDLER_NAME", U."GENSTATUS", U."STATE_DISTRICT", DECODE(U."LOCATION_STREET_NO", null, U."LOCATION_STREET1", U."LOCATION_STREET_NO" || ' ' || U."LOCATION_STREET1"), U."LOCATION_CITY", U."LOCATION_ZIP", U."LOCATION_COUNTY_NAME", U."LAND_TYPE", U."OWNER_TYPE", U."OPERATOR_TYPE" HAVING DECODE({?YR}, '0', DECODE(MAX(C."EVALUATION_DATE"), NULL, TO_DATE('01011500', 'MMDDYYYY'), MAX(C."EVALUATION_DATE")), MAX(C."EVALUATION_DATE")) < DECODE({?YR}, '0', TO_DATE('01021500', 'MMDDYYYY'), ADD_MONTHS(SYSDATE, - (12* {?YR}))) WHERE IO_TDR = ID

18. Exercise: Attaching and Joining Tables (Let’s assume the SQL is correct, and we’re connecting CR to both and JOINing them) Find: C:\NoInspection.qry C:\BRS_BASIC2.qry 1) Open NoInspection.qry in the CSD, (use Edit /Query menu). Then copy/paste (CNTL-C/CNTL-V) into the ADD_Command of our replica report. 2) Use CR Database Expert to Find/Select the Crystal Query (BRS_BASIC2.qry) and add it. 3a) In the Database Expert/Links tab, use the mouse the drage and drop a line (join) between the table ids. 3b) Right Click on the new join, and select “Link Option” to verify the type of join to is. Should be an =/inner join. This restricts the ids to those in common between the “tables”.

19. Start Management Report Replication Run and Save Final SQL in Crystal SQL Designer directly. Execute Query See Page 12 of ManageRpt.ppt - Open C:\CR9Workshop\FinalSQL.txt in Notepad; CNTL-C to Copy to Clipboard - Open CSD; Start New Query; Select “Enter SQL Statement Directly”; Logon to RCRAProd when prompted; - Hit OK, then YES to “Process Query Now” - Will be prompted to create a STATE parameter. Create using Edit/Parameter menu. Use ‘PR’ as default value…(include single quotes) - Hit “Lightning Bolt” on top menu to Execute query - CSD will prompt to create a YR parameter…. Create using Edit/Parameter menu. Use 5 as default value…(no quotes) - Hit “Lightning Bolt” on Top menu to Execute query - Since the SQL contain no more parameters to create in the CSD, you will now be prompted to use “current or new parameters”. Select New / OK. Toggle between parmeter tabss to see their defaults – hit OK to use these defaults…report will execute. … Save/Title Query - Give Title via Edit/QueryTitle menu – call it FinalSQL - Drop down FILE menu – uncheck “Save Data with Query”, Save As: C:\Workshop\FinalSQL.qry Use FinalSQL.qry as Datasource for new CR Management Report -Open CR9 (Follow steps 1 and 2 on Page 4 of C:\Workshop\CR9.ppt). At step 3, under “Create New Connection” – choose “Crystal Queries“ - Find and Select C:|workshop\FinalSQL.qry (what you just created) - login when prompted (will get screen below). - Highlight “Query” on left – use top Arrow to add “Query” into right Queue; (Note: More than one .qry can be added)

20. ….Con’t • - Will be prompted for Parameter Values. Leave Defaults provided from the CSD – press OK. • - Choose Fields as shown below – hit NEXT at bottom on page. NEXT two more times and FINISH (Skip • GROUPing/SELECT’ing and Template Options) – report will EXECUTE • Save Report • - Title (File/Summary Info) • - File/Save As C:\CR9Workshop\FinalSQL-FromQry.rpt (include ”Save Data wirh Report”) • Exit Report, Close Down CR9. • Open CR9. Re-open C:\Workshop\FinalSQL.rpt –what happens? Hit Lightning Bolt –what happens?

21. SWAP .qry for ADD_COMMAND. Highlight and Copy (CNTL-C) SQL from C:\Workshop\FinaSQL.qry by: Database/”Show SQL Query” menu. In Database Expert, choose RCRAProd Click ADD_Command; Edit window will open (See Screen Shot below) (See Screen Shot below) Login when prompted CNTL-V SQL into window Must Create STATE and YR Parameters with Defaults before OK Hit OK again…CR will process Command and return both Command and .qry as part of Report (Haven’t removed .qry yet. Report still referencing .qry fields) SQL HERE

22. ….Con’t - Highlight .qry and “Left Arrow” to Remove (CR will prompt about removing fields that exist in report – say OK, and OK again at bottom) Note: All fields will dissapear from report – since all fields were reference as {Query.Field}. Click “Field Explorer” menu icon as shown: - DropDown Database/Command - Switch Report Tab from Preview to Design - Drag Command fields into the D (Details) Section (Creates identically named Column Titles in PH (Page Header) - Switch back to Preview (acts as Auto-Refresh) (Data will refresh from new Command source) - Resave report as C:\Workshop\FinalSQL-FromCommand.rpt

23. Report should look like this….

24. Apply Basic Formatting to Report • From Menu • 1) File/Page Setup – Change all Margine to 0.12 (good for Adobe Distiller) • 2) File/Printer Setup – Change to Landscape • Resave Report • In Design Tab, reformat to look like: Right click – unsuppress Report Header

25. Insert 4 “Groups” using menu: INSERT/GROUP • Note: Start this while first in “Design Mode” (why is this better than Preview?) • Region • State • County • Name • Change to: • and Preview • Can we remove • The Group Name Field • - redundant?

26. Yes. Removing Group Field (Name) still leaving the Group Header/Footer Containers – which can be forced to “0” width. Report starting to take some shape

27. Oops! Meant to add another Group: “Calculated BRS Universe”, Note: Want between County and Name Groups Add this Group Change position using menu: Report/Group Expert (Notice it’s default position) Highlight Group, use Up Arrow to move up. OK

28. See Preview. Notice BAYAMON has two Universe Divisions Hmmm. Want to save space (that’s a Management Report issue) Universe on same line as Detail Data

29. Moved Group Name #4 into Detail Section Made GH4 zero width Made GF4 less width Changed Font of G4 Name to match other Detail Objects (Arial 9) Cleaner looking output, but, Universe is now repeating (because it’s not “in the Group” anymore). We want only first instance to show – how? Need a “Conditional Suppression” ..cont next page

30. …cont Creating a Conditional Suppression Right Click on the duplicating Universe Field Choose “Format Field” – go to “Common” Tab Check “Suppress” and Click Formula Box to create Conditional Suppression Recreate Formula Save and Close Formula Close out of Dialogue Boxes. Note: “Suppress if Duplicated” option will not work here Why does this formula work?

31. Highlighting Data of Interest (Conditional Highlighting) • Condition: Id, GEN, and TONUNIV, if GEN <> TONUNIV else Id, GEN, and TONUNIV • Note: Since these fields currently share the same properties – we can format (apply new formula) to all • together, using FORMAT OBJECTS. All objects will inherit properties of 1st selected object. • - Hold down SHIFT, while left clicking the three fields. All will stay highlighted (let go). • Menu: Format/ Format Objects • Change to FONT Tab • Open Formula for Color attribute • Copy in Formula Shown • Save/Close • Exit, See Results • Extra • Turn TONSUNIV • Color = Blue • Style = Italic

32. Changing Background Color For Readability • Change GH3 (County Group) to light gray • In Design Mode: • Right Click GH3, Choose Section Expert • Change to COLOR Tab, click Background Color • Dropdown Colors box – choose MORE • - Replicate Custom Colors Chosen in picture • (Slide Bar or Direct RGB entry) • Add to Custom Colors / OK / OK / Preview Mode

33. Inserting a Parameter Value as Part of a Report Title (Management Friendly) • Create a Title • In Design Mode: • Menu: Insert / Text Object. Drop the Text Box, now attached to the mouse pointer, into Report Header • Type Title: Active Generators from 2003 BRS Shipper Totals • Preview Report • Save Report • Insert “State” Parameter into Title • First, See this Parameter in detail first • - Open Field Explorer Menu: View / Field Explorer; Expand Parameter Fields, Right Click STATE, Select EDIT • - Fill in “Prompting Text” (nothing supplied from CSD upon report creation) • - Select “Set Default Values”: Add ‘VI’ to Dropdown List; “Define Descriptions”: Puerto Rico / Virgin Islands; OK • Remove both Parameters from List using << arrow • - “Import pick List”; C:\Workshop\STATELIST.txt • - Scroll to bottom, notice “National” value ‘1’ (Recall SQL: ((U."STATE" in ({?STATE})) or ('1' in ({?STATE})) )

34. Now - put our updated STATE parameter into the Title! • In Design Mode • Double click (to edit) the Text Box (Title) – put cursor at beginning of box… • From the Field Exporer – drag the STATE Paramter into the Title…it will go where the cursor sits • Embed a space and PREVIEW • Two Problems • STATE contains quotes from Parameter allow values…. • Would show ‘NJ’,’NY’,’PR’,‘VI’ (not Region2) if Region2 (from parameter description) was chosen • A Formula would help! Create STATE Formula (next page!)

35. Creating a Formula Purpose here: Transform quoted {?STATE} values into Title friendly displays Design Mode Field Explorer / Formula / Right Click / NEW Specify Formula Name: Title State Choose “Formula Editor”, Replicate Formula Shown, Save

36. Swap {?STATE} with {@STATE} in Title • Design Mode • - Right Click Title, choose “Edit Text” • - Highlight {?STATE} and DELETE (Key) • Field Explorer / Expand “Formula” section • Drag {@Title State} into Title • PREVIEW (See PR without quotes) • Refresh Report using {?STATE} = your Region (increase YR to speed up report) • ________________ • Would repeat process to show YR parameter in a SubTitle, as shown below.

37. Add a CROSS-TAB – to Show Summary Information in a Grid In Design Mode - Insert/Cross-Tab (Place cross tab in Report Footer) - Fill in Values as shown in Cross-Tab Expert below - PREVIEW, go to last page • Clicking on PREVIEW’ed cells allows formatting (resize, color, font, etc) • Title is just a Text Box placed above Cross-Tab

38. Specify Group Order…. • - Cross Tab Expert • “Group Options” for Columns (TONSUNIV) • Select Specified Order • Input Valid Values in Order from Left to Right