Report writing

1. Report writing

2. Building NASIS Reports

3. Purpose of Reports • To get data from the database for presentation to users and customers. • Reports generally query the selected set • Permanent data can be accessed • Query portion of the report selects specified data elements to be used in generation of the output.

4. Query Data manipulation Output Reports

5. Query • SQL required structure • EXEC SQL • SELECT columns statement • FROM tablestatement Example: EXEC SQL SELECT compname FROM component;.

6. Parts of Report Scripts • Optional parts • WHERE clause • table join statements • conditional clauses • Sort statement • Aggregation statements • Data manipulation • Templates • Section and Column formatting • Page formatting

7. Parts of Scripts • Data manipulation • Multiple queries • DERIVE statements • DEFINE statements • INTERPRET statements • PARAMETER statements

8. Overview:Ways to visualize the report process • Report process flow chart • Report styles • Data transformations

9. NASIS Reports Flow Chart

10. Report Styles • Tabular • headings • columns • Narrative • bullets • paragraphs • Export • delimited text

11. Data TransformationsStructure of SQL output

12. Data TransformationsStructure of report variables

13. Open the Reports Explorer On menu or toolbar choose to open new report Enter report name Enter report format Creating a NASIS Report

14. Creating a list of map units • Purpose: Demonstrates default report format. • Problem: List each national mapunit symbol and mapunit name. • Tables: mapunit. • References: • EXEC SQL (p. 22) • table structure report

15. EXEC SQL SELECT nationalmusym, muname FROM mapunit;. Report SQL begins with EXEC SQL and ends with semicolon and period Selects the columns needed for the report Identifies the table they columns reside Map units

16. EXEC SQL SELECT nationalmusym, muname FROM mapunit WHERE muname matches ‘Harney silt loam, 0 to 1 percent slopes’;. Adding conditions Need to understand the data types and comparisons Adding conditions

17. EXEC SQL SELECT nationalmusym, muname FROM mapunit WHERE muname matches ‘Harney *’; SORT BY muname. Notice the semicolon and period Adding sorts

18. EXEC SQL SELECT nationalmusym, muname, compname FROM mapunit INNER JOIN correlation by default INNER JOIN datamapunit by default INNER JOIN component by default WHERE muname matches ‘Harney *’; SORT BY nationalmusym. Adding tables

19. Based on what you know, create a default format report that shows the area symbol, map unit symbol, national mapunit symbol, mapunit name, and corresponding data mapunit description for mapunits in the survey area where you reside. Sort the report by mapunit symbol. Email to paul.finnell@usda.gov Exercise Rpt 1

20. Using outer joins • Explained in queries • INNER JOIN 1:1 join • OUTER JOIN • LEFT • RIGHT • FULL

21. Exercise 2 • Add to exercise 1 and add to the report all components in the survey area and include the component restriction records that are available. • Send me a screen shot of the results in both txt and html format

22. EXEC SQL SELECT areasymbol, nationalmusym, muname, compname, reskind FROM area INNER JOIN legend by default INNER JOIN lmapunit by default INNER JOIN mapunit by default INNER JOIN correlation by default INNER JOIN datamapunit by default INNER JOIN component by default LEFT OUTER JOIN corestrictions by default; SORT BY nationalmusym. Exercise 2 answer

23. Aggregation • Aggregation can be done in two methods • Group by • Aggregate • Use the previous query and use the Group By to count the number of reskind for each map unit.

24. EXEC SQL SELECT areasymbol, nationalmusym, reskind, count(*) as rowcount FROM area INNER JOIN legend by default INNER JOIN lmapunit by default INNER JOIN mapunit by default INNER JOIN correlation by default INNER JOIN datamapunit by default INNER JOIN component by default LEFT OUTER JOIN corestrictions by default GROUP BY areasymbol, nationalmusym, reskind;. Group by example

25. AGGREGATE • More functionality than GROUP BY • Aggregation by column • Allows Crosstab formatting • Aggregate by ROWS and by COLUMN • Row aggregation allowed in first SQL only • Sum, Average, First, Last, Min, Max, None, Unique and List

26. EXEC SQL select musym, muname, areaname, lmuaoverlap.areaovacres acres from mapunit, lmapunit, lmuaoverlap, laoverlap, area where join area to laoverlap and join laoverlap to lmuaoverlap and join lmapunit to lmuaoverlap and join lmapunit to mapunit; SORT BY musym SYMBOL, areaname AGGREGATE ROWS BY musym COLUMN muname UNIQUE, acres SUM CROSSTAB areaname CELLS acres. Aggregate example

27. aggregation

28. Using a default html report, create a report that will provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name Exercise 3

29. EXEC SQL SELECT nationalmusym, muname, compname, comppct_r, repdmu FROM mapunit LEFT OUTER JOIN correlation by default LEFT OUTER JOIN datamapunit by default LEFT OUTER JOIN component by default; SORT BY nationalmusym SYM, comppct_r DESC AGGREGATE ROWS BY nationalmusym COLUMN compname NONE. Example 3 answer

30. Example 4 • Purpose: Demonstrates the use of Templates, Sections and Column formats. • Problem: Change default report output from 3a to formatted output using these features. • Tables: mapunit, correlation, datamapunit, component. • References: • TEMPLATE statement (p. 58) • SECTION statement (p. 43) • Column Layout specs (p. 53)

31. Example 5 • Purpose: Demonstrates the use of Aggregation. • Problem: You wish to eliminate duplicated data in columns, using script from Example 4. • Tables:mapunit, correlation, datamapunit, component. • References: • Aggregation specs (p.26)

32. Exercise 5a • Purpose: Use knowledge from previous examples. • Problem: Using 3a script, create a new report with formatted columns and headings that show mapunit symbols, mapunit names, component names and percentages. • Sort by mapunit symbol • show dominant components first • eliminate duplicate symbols and mu names

33. Exercise 5a answer

34. Example 6 • Purpose: Demonstrates the use of Section Conditional statements. • Problem: Organize previous output in different fashion. • Tables: mapunit, correlation, datamapunit, component. • References: • SECTION statement (p. 43)

35. Example 7 • Purpose: Demonstrates the use of multiple queries and defining variables. • Problem: The list of components and associated crops are on different db paths. • Tables: mapunit, correlation, datamapunit, component. • References: • BASE TABLE statement (p. 4) • DEFINE statement (p. 5) • NMCASE statement (p.10)

36. Example 8 • Purpose: Demonstrates use of Property output. • Problem: You want to produce a listing of total AWC for each component in each map unit. Sort by musym, and rep comp percent • Tables: mapunit, correlation, datamapunit, component. • References: • DERIVE statement (p. 21)

37. Exercise 8a • Purpose: to use knowledge learned to create a data export format report. • Problem: Develop a data export report in comma-delimited format showing mapunit symbol, component name, and minimum representative depth to seasonal high water table for the dominant component in each mu. Sort by mapunit symbol.

38. Example 10/11 • Purpose: Demonstrates the use of headers to improve appearance of report. • Problem: Add page header to each page and a report title. List mapunit symbols and names. Show ssa name in report title. • References: • Header (p. 32) • Line specs (p. 47)